Using array constants in excel vba function

Question

I have a function in VBA of the type

Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function

which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error.

I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).

Does someone have a suggestion for a flexible solution, which would permit either calling method: by range, as well as by an array constant?

Thanks in advance!

Answer 1

You could declare your two parameters as Variant types, then in your function check what has been passed to them using the TypeName(varname) function.