function w/ dynamic inputs

Hello tadwegner,

First, your macro needs to be a Function, not a Sub, and second, you can use a dynamic list by declaring it as a ParamArray. This declaration has to be the last argument in the function and must be a Variant. Nums holds the array of numbers returned from the cells. There can be none to as many as you list.

Function sss(ParamArray nums() As Variant)
...

End Function

Sincerely,
Leith Ross

Thanks! i didnt know about the paramarray. i see in the locals window that all the inputs are located in the nums() variant (sweet!). but, how do i use the nums() variant now?

i have again hit a brick wall here. i dont know how to 'count' the number of entries in the variant. also i dont know how to access more than the first 'array' (the 0 value entry) of that variant.

if its not too much trouble can you just lay out how i would SUM all the entries in the nums() variant. i will be able to take it from there.

again thank you sooo much!

Hello tadwegner,

Here is an example macro to sum the values...

Function sss(ParamArray nums() As Variant)

  Dim I As Integer
  Dim Total As Double
  
    For I = LBound(nums) To UBound(nums)
      Select Case TypeName(nums(I))
        Case Is = "Integer", "Long", "Single", "Double"
          Total = Total + nums(I)
        Case Is = "Range"
          Total = Total + WorksheetFunction.Sum(nums(I))
      End Select
    Next I

    sss = Total

End Function

Sincerely,
Leith Ross