#Name and VBA function

Question

Creating a new formula to more easily deal with weighted median. Function works as a sub but not as a Function. So values are parsed in the first range and weights in the second i.e. {10,20,15,30,15}, {1,2,0,3,4} Not sure were the error is creaping in.

Function weightedMedian(valueRng As Range, weightRng As Range)

Dim weightValueArr(), valueArr()      As Double
    ReDim weightValueArr(Application.Sum(weightRng) - 1)
    ReDim valueArr(valueRng.Rows.Count - 1)
    For k = 0 To valueRng.Rows.Count - 1
        valueArr(k) = valueRng(k + 1)
    Next

Dim n, m                    As Integer
    n = 0
    m = 0

For Each j In weightRng
    For i = 0 To j - 1
        weightValueArr(m) = valueArr(n)
        m = m + 1
    Next
    n = n + 1
Next

weightedMedian = Application.Median(weightValueArr)

End Function

Using your script as is on Excel 2013 and your sample data I get a result of 17.5. No #Name error. Can you place a breakpoint at the start of your script and then using F8 step through each step and review what values are populating and where your error occurs. What version are you using? — nbayly, Jul 17 '15 at 19:09
@nbayly my guess is that you are not using Option Explicit, so the function returned a variant by default. — David G, Jul 17 '15 at 19:17
Did you put the function in a separate module (as you should have) or in a worksheet? — GSerg, Jul 17 '15 at 19:34
Do you really want to redim the weightValueArr array to have a ubound that is the sum of the weightRng range? — , Jul 17 '15 at 19:39
`For i = 0 To j - 1` <-j is a range assigned in the For Each, not a number — , Jul 17 '15 at 19:51
@Jeeped True, so using `j` in the value context [yields `j.Value`](http://stackoverflow.com/a/19200523/11683). — GSerg, Jul 17 '15 at 19:57

score 0 · Answer 1 · answered Jul 17 '15 at 19:14

0

You need to give the type of your function, unlike a sub, right at the start.

Function weightedMedian(valueRng As Range, weightRng As Range) As double

Of course, this is the only way to know what the type of value returned IS, since you never dim it or receive it as an argument.

answered Jul 17 '15 at 19:14

David G

2,315
1
24
39

2

Functions that are not explicitly typed have the `Variant` type. It is a matter of style to use `As Variant` explicitly. It does not cause an error. – GSerg Jul 17 '15 at 19:32

#Name and VBA function

1 Answers1