Why doe my Excel VBA function return a #VALUE error when the debug print is correct?-CodePudding

Sorry if this question seems a bit basic by I am only a amateur. I tried to write a small VBA function to filter the bottom values from a range on a sheet and then average the remaining values. When I ran this through a subroutine it printed off the correct value, however, when I used it as a function (with the exact same input values) it returns a #VALUE error on my worksheet.

I recently found that the same thing can be accomplished by using a small function and an if statement in excels default average function. But its really bugging me why this didn't work. Would anybody have any suggestions?

Below is my Code:

Function TOPSCORES(SheetArray As Variant, Length As Integer) As Variant

'Commented code are the additions in the sub routing variant that print the correct value

'Dim SheetArray() As Variant
'Dim Length As Integer
'SheetArray = Range("B3:B14")
'Length = 10

Dim SortVal As Variant
Dim i As Integer
Dim j As Integer
Dim Final As Variant

'Filter top scores
For i = 1 To UBound(SheetArray)
For j = i   1 To UBound(SheetArray)
If SheetArray(j, 1) > SheetArray(i, 1) Then
SortVal = SheetArray(i, 1)
SheetArray(i, 1) = SheetArray(j, 1)
SheetArray(j, 1) = SortVal
End If
Next j
Next i

'Average top scores
For i = 1 To Length
Final = Final   (SheetArray(i, 1))
Next i
Final = Final / Length

'Debug.Print (Final)

TOPSCORES = Final

End Function

CodePudding user response：

A Variant/Object/Range is not the same as an array of Variant.

Your SheetArray As Variant coming from a user defined function, which gets called =TOPSCORES(B3:B14,10), is a Variant/Object/Range. The code line SheetArray = Range("B3:B14") in a Sub results in an array of Variant. That is because no Set keyword is used and so SheetArray cannot be an object. So SheetArray = Range("B3:B14") defaults to use SheetArray = Range("B3:B14").Value.

To make your function working with a Variant/Object/Range coming from the user defined function it should be like so:

Function TOPSCORES(SheetValues As Variant, Length As Integer) As Variant

 Dim SheetArray As Variant
 SheetArray = SheetValues
...

That works if SheetValues comes as Variant/Object/Range because SheetArray = SheetValues then implicit defaults to SheetArray = SheetValues.Value since no Set keyword. And it also works if SheetValues is an array already since it then simply sets SheetArray = SheetValues.