1

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

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

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

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?

Improve this question
1
  • Are there any built-in functions that allow either a range or array constant as arguments? In fact, are there any built-in functions that even just allow array constants? (I thought there was, but all the ones that I have checked are actually using parameter list arrays instead) Commented Oct 1, 2009 at 13:20

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

Maybe you could convert your array in a range object while calling the function.

Result = MyFunc(1,98,Range(10,11), Range(20,30))

Improve this answer

1 Comment

Thanks for the suggestion. I've just tried this, and unfortunately, it fails.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.