2

In Excel VBA, I understand how to use ParamArray() but is there a way to require sets of parameters instead of just one at a time. For example; sum(val1, val2, val3, ...) lets you enter one value at a time, but with sumifs(A:A, rng1, val1, rng2, val2, ...) you are required to put the rng and val as a set. Is there a way to do that with user defined functions?

I feel like it is just something I will need to do in the code but I would like it to correct from the start.

Function foo(bar As String, ParamArray names() As String, ParamArray attributs() As Range) As String
    ...
End Function

in the cell

=foo("hello", "name", A2:A5, "age", B2:B5, "address", C2:C5)
Improve this question
2
  • if no type is specified to the paramarray variable, then any type can be passed stackoverflow.com/questions/20783170/pass-array-to-paramarray Commented Jan 11, 2017 at 20:31
  • 3
    If you need your ParamArray to contain pairs of values then check the ubound and raise an error if it's even. Pretty sure there's no up-front way to "force" the user to call it using the correct number of arguments. Commented Jan 11, 2017 at 20:37
Related questions
1
Variable number of arguments in ParamArray ArgList()
21
Pass array to ParamArray
2
How can I turn a ParamArray with ranges, individual cells and/or direct inputs to an Array?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.