How to make a public function procedure with undefined number of r

[Melwin]

I am making a user defined function in Excel that takes an unlimited amount
of ranges as input. How shall I do this?

I have figured out that a one range function could be as follows:

Public Function MySum(MyRange As Range) As Double

The question now is how to make a function with an unlimited amount of
ranges and to be able to uses these ranges in a function.

Could someone please illustrate how to do this by e.g. replicating the Sum
function in excel?

The following code is only summing one range, how should it be changed to
take in unlimited ranges?


Public Function MySum(MyRange As Range) As Double
Dim i As Integer
Dim Temp As Double
Dim rows As Integer
Dim columns As Integer

rows = MyRange.rows.Count
columns = MyRange.columns.Count

For i = 1 To columns
Temp = Temp + MyRange(1, i)
Next

For i = 1 To rows
Temp = Temp + MyRange(i, 1)
Next

MySum = Temp

End Function

Thanx

 

[Melanie Breden]

Hi Melwin,

The question now is how to make a function with an unlimited amount of
ranges and to be able to uses these ranges in a function.

Could someone please illustrate how to do this by e.g. replicating the Sum
function in excel?

for one range you can use the simple Application.Sum method like this:

Public Function MySum2(rng As Range) As Double
MySum2 = Application.Sum(rng)
End Function

For unlimited ranges declare the argument as ParamArray:

Public Function MySum3(ParamArray varRanges()) As Double
Dim varArea As Variant

For Each varArea In varRanges
MySum3 = MySum3 + Application.Sum(varArea)
Next varArea
End Function

 

[Melwin]

Thanks, this was what I needed
--
Melwin

Hi Melwin,



for one range you can use the simple Application.Sum method like this:

Public Function MySum2(rng As Range) As Double
MySum2 = Application.Sum(rng)
End Function

For unlimited ranges declare the argument as ParamArray:

Public Function MySum3(ParamArray varRanges()) As Double
Dim varArea As Variant

For Each varArea In varRanges
MySum3 = MySum3 + Application.Sum(varArea)
Next varArea
End Function