I'm trying to develop a function with an array as input. However, I'm receiving #VALUE! error. The function code is as follows:
Function foo(x() As Double) As Double
foo = x(1) + x(2)
End Function
The call is done with the following formula:
=foo($E$8:$E$9)
E8 and E9 correspond to two numbers.
What is wrong with my code?
$E$8:$E$9 is of type Range and not an array of type Double. Your UDF fails there.
Once you change your param type to Range then it will work for x(1) + x(2) as long as you pass 2X1 (2Rows, 1Column) range with numeric values.
Function foo(rng As Range) As Double
foo = rng(1) + rng(2)
End Function
To make a meaningful UDF you will need to check the dimensions, data types of the range in your code.
foo = rng(1) + rng(2) part is just to demonstrate that the verty first issue with OP's code is parameters data type. Just using 'Range` as the input, one certainly needs handle dimensions, data types etc to come up with something useful.I would suggest using a variant as the parameter: more efficient and less error prone
Function foo(x As Variant) As Double
x=x.value2
foo = x(1, 1) + x(2, 1)
End Function
Range? The UDF is clearly given a Range here. Losing IntelliSense is probably a bad idea when you're a green coder that doesn't know all that's going on under the hood.
Variant parameter becomes misleading if the function really only works with a Variant/Range - I'd take a Range instead, and have the array be a local variable instead if reassigning the parameter, which is implicitly passed by reference, so using that UDF in regular VBA code would have unintended consequences in the calling code; a rather confusing runtime type mismatch, probably.My solution is to use variant as parameter. It can be array and Range. Test if it is a single cell/value, it will be not converted to array. In this case return the value of the cell. Test if it is Range object. If it is the case convert it to array. Array is quick.
For summing I would loop through the array with for each to avoid any complication of indexing in the array. (One or two dimensional). Additionally the values shall be tested if they are numbers. The small sub is for testing the function call from a sub.
Function foo(x As Variant) As Double
If VarType(x) < vbArray Then 'Single element
foo = IIf(IsNumeric(x), x, 0) ' if x is number return x else 0
Exit Function
End If
If TypeName(x) = "Range" Then x = x.Value2 ' if Range converts to array
Dim v As Variant
' sum all values of x
For Each v In x
'sum only numbers, ignor anything else
If IsNumeric(v) Then foo = foo + v
Next v
End Function
Sub testFoo()
'Test for non Range
Debug.Print foo(Array(1, 34, 5)) 'Test for array
Debug.Print foo(5.2) ' Test for a number
Debug.Print foo("Apple") ' Test for not number
End Sub
Results:
40; 5,2; 0
