Loop Through Sequential Variables for UDF

[Veritan]

Hi all. I am trying to see if it is possible to loop through a series of variables with a standard naming convention. I am using this to create a UDF for me to use. What I am hoping to achieve is something like this where I can choose to enter up to a certain number of ranges as arguments for the function:

Function MyFunction(arg1 As Range, Optional arg2 As Range, Optional arg3 As Range, Optional arg4 As Range, Optional arg5 As Range)
   Dim rngMasterRange As Range
   Dim i As Integer
  
   Set rngMasterRange = arg1
   On Error Resume Next
   For i = 2 To 5
      Set rngMasterRange = Union(rngMasterRange, "arg" & i)
   Next i
   On Error GoTo 0
  
   MySub rngMasterRange
End Function

Private Sub MySub(rngMasterRange As Range)
   'Do stuff
End Sub

However, that code errors out as soon as it tries to compile. Does anyone know if there is a way to loop through a series of variables in VBA? I could put them in individually, but I'd like to allow the function to have a large number of optional arguments in it, so a loop would certainly expedite that process. Thanks in advance for any help with this.

 

[dmt32]

seems like a ParamArray is what you are looking for
Have a read here & see if it helps you: Parameter Arrays - Visual Basic

Dave

 

[Domenic]

As per the suggestion by @Dave , try

Function MyFunction(rng As Range, ParamArray argList() As Variant)

   Dim rngMasterRange As Range
   Dim arg As Variant
 
   Set rngMasterRange = rng
 
   For Each arg In argList
    Set rngMasterRange = Union(rngMasterRange, arg)
   Next arg
 
   MySub rngMasterRange
 
End Function

Note, in order for the array argList to accept an indefinite number of arguments, it must be the last argument, it must be preceded by the keyword ParamArray, and it must be declared as Variant. Also note that it's always an optional argument, even though you don't use the Optional keyword.

Hope this helps!

 

[Veritan]

You both are amazing, thank you so much for that! I'd mark both your answers as the solution if I could. The sample code and the MS documentation about Parameter Arrays (which I'd never heard of before) were both extremely helpful. Thank you again!!