Any number of input variables.

[biker]

I want to make a function “myFunc” that makes some calculations on numbers in different cells. The number of cells are not known and they can be in different sheets. How do I declare the function to make it capable to handle any number of input values?

It is intended to be used from an excel worksheet like =myFunc(A1, D4, G2) or = myFunc(A1, D4, G2, H6, L2) …

How do I declare it?
(this will not do:

Public Function myFunc(inputCells As Range) As Variant

since the cells can be spread out)

How is the built in SUM function declared? It works with =SUM(A1:A4) as well as =SUM(A1, B5, H6) ?

 

[Richard Schollar]

Hi Biker

You need to use the keyword ParamArray in the arguments list to your functions. An example of a Sum function could be:

Function Summer(ParamArray rngs()) As Double
Summer = Application.WorksheetFunction.Sum(rngs)
End Function

You can access the individual values in rngs() like you would in anormal array:

rngs(1) acceses the first valuepassed to ParamArray, rngs(2) the second and so on. Only the last argument in the function can be declared as a ParamArray.

Hope this helps!

Richard

 

[biker]

I found an answer my self:

Function myFunc(ParamArray VList() As Variant) As Variant
Dim ArrayItem As Variant
Dim i As Integer


'loop through items.
For Each ArrayItem In VList
   For i = 1 To ArrayItem.cells.Count
         
      'do something useful, ex:
      if ArrayItem.cells(i).Value=...
   Next i
Next ArrayItem

End Function

 

[biker]

thanks Richard,

you were faster...