CODEWITHSUNDEEP
excelrangeuser-defined-functionsvba
siberman
siberman

Reputation: 23

UDF: Handling ranges and variable numbers of arguments

I'm trying to write a UDF (user-defined function) to create an average for non-numeric data (I'm converting it into numeric form then back again at the end). I can get the UDF to work if I list individual cells; I get a #VALUE! error if I try to refer to a range of cells. There may be a mix of both ranges and individual cells to process.

Any ideas?

The code so far is below.

Function avlvl(ParamArray av() As Variant)
Dim a As Integer
'creates an average ks3 level from data in format "5a"
a = 0
n = 0
total = 0
Do While a < UBound(av()) + 1
   'ignore blank or zero cells
    If av(a) = 0 Or av(a) = "" Then
        a = a + 1
    Else
       'convert data into numeric value - split into level and sub level
        level = Val(Left(av(a), 1))
        sl = Right(av(a), 1)
        If sl = "c" Then
            sublevel = 0
        ElseIf sl = "C" Then
            sublevel = 0
        ElseIf sl = "b" Or sl = "B" Then
            sublevel = 1 / 3
        ElseIf sl = "a" Or sl = "A" Then
            sublevel = 2 / 3
        Else
            sublevel = 0
        End If
       'score is numeric value of the data
        score = level + sublevel
       'total is teh toatl of the cells so far
        total = total + score
        a = a + 1
        n = n + 1
    End If
Loop
ave = total / n
'reconvert into format level and sublevel (a,b,c)
averagelevel = Application.WorksheetFunction.RoundDown(ave, 0)
asl = ave - averagelevel
If asl < 0.17 Then
    averagesublevel = "c"
ElseIf asl < 0.5 Then
    averagesublevel = "b"
ElseIf asl < 0.84 Then
    averagesublevel = "a"
ElseIf asl < 1 Then
    averagelevel = averagelevel + 1
    averagesublevel = "c"
Else
    averagesublevel = "c"
End If

avlvl = averagelevel & averagesublevel

End Function

Upvotes: 2

Views: 2367

Answers (2)

chuff
chuff

Reputation: 5866

What's going on is that the range is coming in as a single object of type Range, and your code is trying to treat is as though it is coming in as an array.

The best approach would be to create a new array within the body of the function, and then assign the elements in the range to the new array. You need to test for the type of the elements of the ParamArray. If an element is type String, then put it directly in the new array; if an element is type Range, loop through it, assigning its cell values to the new array.

Then you would do your processing on the new array.

The following code provides the machinery to pass in ranges as well as individual cells or values. I've not included your code but have indicated where it would go.

Function avlvl(ParamArray av() As Variant) As Variant

    Dim a As Integer
    Dim i As Long
    Dim avArr()
    Dim element As Variant

    a = 0
    i = 0
    Do While a < UBound(av) + 1
        If TypeName(av(a)) = "String" Then
            avArr(i) = av(a)
            i = i + 1
        ElseIf TypeName(av(a)) = "Range" Then
            For Each element In av(a)
                ReDim Preserve avArr(0 To i)
                avArr(i) = element
                i = i + 1
            Next
        Else
            avlvl = CVErr(xlErrValue)
            Exit Function
        End If
        a = a + 1
    Loop
    i = 0
    Do While i < UBound(avArr) + 1
        '...
        'now process the elements of avArr()
        '...
        i = i + 1
    Loop
End Function

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If you have a disjoint range of cells and you want to pass them to a UDF, one approach is to create a Defined Name and pass it to the UDF as a single argument.

Upvotes: 0

Related Questions