How to work with reference unions (A1,A3,A5)?

[Joe User]

What kind of object is passed to a VB function when called from Excel with
reference union like (A1,A3,A5)?

How can I determine the size of the union (number of references)?

Consider the following paradigm:

Function myIRR(myVal As Range, _
Optional myGuess As Double = 0.1, _
Optional n As Long = 0)
Dim dVal() As Double
If n <= 0 Then n = myVal.Rows.Count
ReDim dVal(1 To n)
n = 0
For Each cell In myVal: n = n + 1: dVal(n) = cell: Next
myIRR = IRR(dVal, myGuess)
End Function

That works with (A1,A3,A5), but I must pass n=3. myVal.Rows.Count is 1
unless myVal is a contiguous range like A1:A5.

PS: I know that I can use WorksheetFunction.IRR instead for this example.
I am using IRR just for demonstration purposes.

 

[Rick Rothstein]

I would use a ParamArray to process the comma delimited ranges and then
iterate each range individually, something like this...

Function Test(ParamArray R() As Variant) As Long
Dim X As Long, C As Range
For X = LBound(R) To UBound(R)
For Each C In R(X)
Debug.Print "Cell " & C.Address & ": " & C.Value
Next
Next
End Function

Just replace my Debug.Print statement (which prints the cell addresses and
cell values to the Immediate Window) with whatever processing you want to do
the individual cells.

 

[Rick Rothstein]

A couple of points about the code I posted...

First, I did not return any value from the function as I assumed you will do
that with whatever code you replace my Debug.Print statement with or you
will do that after the loops have processed whatever you are doing with the
cells inputted by the user. Second, the structure I have provided allows
your UDF to take in single cell references mixed with multi-cell
references... the inner loop will see each individual cell. So, if you
formula using this UDF was...

=Test(A1,B2:C3,D4)

then the inner loop would see cell references, one at a time, for the cells
A1, B2, B3, C2, C3 and D4.

 

[Joe User]

I would use a ParamArray to process the comma
delimited ranges and then iterate each range
individually, something like this...
Function Test(ParamArray R() As Variant)

Forgive me, but I don't see how that addresses my question.

I specifically asked about passing a reference union of the form (A1,A3,A5).
The issue is determining its size.

Function Test(ParamArray R() As Variant) As Long
Dim X As Long, C As Range
For X = LBound(R) To UBound(R)
For Each C In R(X)
Debug.Print "Cell " & C.Address & ": " & C.Value
Next
Next
End Function

How is that any different or better than simply the following?

Function myIRR(myVal as Variant, _
Optional myGuess as Double = 0.1)
Dim dVal() as Double, n as Long
If TypeName(myVal) <> "Range" Then Exit Function 'not relevant
'count size of range, in case it is a reference union
n = 0
For Each cell In myVal: n = n + 1: next
Redim dVal(1 to n)
n = 0
For Each cell In myVal: n = n + 1: dVal(n) = cell: Next
myIRR = IRR(dVal, myGuess)
End Function

I guess I could do that. But is it the best I can do?

I was wondering if a parameter of the form (A1,A3,A5) is a different type of
object than A1:A5. So if I declared myVal as Variant instead of Range,
which I should do anyway for an unrelated reason, perhaps I could
distinguish a reference union in some way programmically (e.g. like
TypeName, but I know that does not do the trick), and some property of that
object would give me its size. As I noted previously, myVal.Rows.Count does
not suffice.

PS: I realize that the first parameter of Excel IRR is not limited to a
range. It can also be an array of constants. But that is not germane to my
question.

PPS: I am using Excel 2003 with VBA 6.0.


----- original message -----

 

[Rick Rothstein]

I would use a ParamArray to process the comma

Forgive me, but I don't see how that addresses my question.

I specifically asked about passing a reference union of the form
(A1,A3,A5).
The issue is determining its size.

.....<snip>

I was wondering if a parameter of the form (A1,A3,A5) is a different type
of
object than A1:A5. So if I declared myVal as Variant instead of Range,
which I should do anyway for an unrelated reason, perhaps I could
distinguish a reference union in some way programmically (e.g. like
TypeName, but I know that does not do the trick), and some property of
that object would give me its size. As I noted previously,
myVal.Rows.Count does not suffice.

Sorry, but I totally missed the point of your question when I first read
your message. I think you may be looking to count the areas, not the rows.
Given this function declaration...

Function myIRR(myVal as Variant, Optional myGuess as Double = 0.1)

consider this...

NumberOfRanges = myVal.Areas.Count

This would properly count three even if one of the ranges was a contiguous
range reference. For example, if the union was (A1,A4:A7,A9), the area count
would still be 3 even though the total number of cells is 6. You can iterate
the areas by index value. For example...

For N = 1 To myVal.Areas.Count
Debug.Print myVal.Areas(N).Address
Next

Is this what you were looking for?