Function Formula Possible ?????????

[Alan]

I am trying to achieve the following. Is this possible.

I want one range variable in the formula.

Eg. =TestRange(RangeReferences as range)

Can I have 1 range variable but insert numerous separate range cells in the
single reference. If so, how?

Eg. =TestRange(A1, B10,D2)

Also, if this can be achieved will the formula writer in Excel allow you to
select the different ranges?

Thank you in advance

 

[Dave Peterson]

Here's one way:

Option Explicit
Function testRange(rng As Range) As Variant
Dim myCell As Range
Dim myTotal As Double

myTotal = 0
For Each myCell In rng.Cells
myTotal = myTotal + myCell.Value
Next myCell

testRange = myTotal

End Function

but use:
=testrange((a1,a9,b13,c19))
in your worksheet cell. The addresses surrounded by parentheses mean that it
should be treated as one range.

But I find that difficult to remember--and if you share the function with
others, you might spend more time on the phone reminding them how to enter the
function.

An alternative:

Option Explicit
Function testRange2(ParamArray myArray() As Variant) As Variant

Dim myCell As Range
Dim myElement As Variant
Dim myTotal As Long

myTotal = 0
For Each myElement In myArray
If TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
myTotal = myTotal + myCell.Value
Next myCell
Else
If VarType(myElement) = vbDouble Then
myTotal = myTotal + myElement
End If
End If
Next myElement

testRange2 = myTotal

End Function

Then you could use this kind of function call:

=testrange2(A1,A3,A5,A7:A10)
Which looks more "normal".

And you could even make it look like =sum()
=testrange2(A1,A3,A5,A7:A10, 3, 6, 9)

 

[Alan]

Dave

Thanks for the help.

Here's one way:

Option Explicit
Function testRange(rng As Range) As Variant
Dim myCell As Range
Dim myTotal As Double

myTotal = 0
For Each myCell In rng.Cells
myTotal = myTotal + myCell.Value
Next myCell

testRange = myTotal

End Function

but use:
=testrange((a1,a9,b13,c19))
in your worksheet cell. The addresses surrounded by parentheses mean that it
should be treated as one range.

But I find that difficult to remember--and if you share the function with
others, you might spend more time on the phone reminding them how to enter the
function.

An alternative:

Option Explicit
Function testRange2(ParamArray myArray() As Variant) As Variant

Dim myCell As Range
Dim myElement As Variant
Dim myTotal As Long

myTotal = 0
For Each myElement In myArray
If TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
myTotal = myTotal + myCell.Value
Next myCell
Else
If VarType(myElement) = vbDouble Then
myTotal = myTotal + myElement
End If
End If
Next myElement

testRange2 = myTotal

End Function

Then you could use this kind of function call:

=testrange2(A1,A3,A5,A7:A10)
Which looks more "normal".

And you could even make it look like =sum()
=testrange2(A1,A3,A5,A7:A10, 3, 6, 9)