Using array constants in user-defined excel vba function

**MiloMan** · 10-01-2009, 08:09 PM

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!

**shg** · 10-01-2009, 08:35 PM

Try passing as a Variant.

Some of the function code may need to change as well.

**MiloMan** · 10-01-2009, 08:44 PM

Originally Posted by shg

Try passing as a Variant.

Some of the function code may need to change as well.

Thank you! This solved it - I can now call the function with both types of input arguments, and if I need to find out what it's been called with, I can just use TypeName.

Thanks again,
MiloMan