Challenge Converting Non-Contiguous References

[Guest]

*** REPOSTING FROM FRIDAY NIGHT ***

I’ve created the following function to convert non-contiguous references
into contiguous reference:

Function Contiguous(ParamArray ArgList() As Variant)
Dim x As Integer
Dim Argument
Dim Cell As Range
Dim ReturnArray()
For Each Argument In ArgList
If TypeName(Argument) = "Range" Then
ReDim Preserve ReturnArray(0 To x + Argument.Count)
For Each Cell In Argument
ReturnArray(x) = Cell
x = x + 1
Next Cell
Else
ReDim Preserve ReturnArray(0 To x)
ReturnArray(x) = Argument
x = x + 1
End If
Next Argument
Contiguous = ReturnArray
End Function

This function is very handy in conjunction with a number of formulas, such
as IRR, NPV, etc. [ e.g. =IRR(contiguous(-A1,C1:C10,E2+E10)) or
=NPV(10%,contiguous(A1,C1:C10,E10)) ]

However, the function does not work with selected functions such as COUNTIF
or SUMIF [ e.g. =SUMIF(contiguous(A1,C1:C10,E10),â€>1000â€) ]. The Contiguous
function does not work with selected user-defined formulas as well such as
the ReverseIRR function below [e.g.
=ReverseIRR(10%,contiguous(-A1,C1:C10,E2)) ]:

Function ReverseIRR(ByVal InterestRate As Double, CashFlows)
Dim x As Integer
For x = 1 To CashFlows.Count
ReverseIRR = ReverseIRR - CashFlows(x) * (1 + InterestRate) ^
(CashFlows.Count - x)
Next
End Function

Interesting enough, the ReverseIRR function works if I make as slight
modification as follows [e.g. =ReverseIRRv2(10%,contiguous(-A1,C1:C10,E2)) ]:

Function ReverseIRRv2(ByVal InterestRate As Double, CashFlows)
Dim x As Integer
Dim MaxRecords As Integer
If TypeName(CashFlows) = "Range" Then
MaxRecords = CashFlows.Count
Else
MaxRecords = UBound(CashFlows) - 1
End If
For x = 1 To MaxRecords
ReverseIRRv2 = ReverseIRRv2 - CashFlows(x) * (1 + InterestRate) ^
(MaxRecords - x)
Next
End Function

I presume (perhaps erroneously) that the problem results from the fact that
the Contiguous function returns an array not a range. If this is in fact the
problem, how do I get the above function to return a range? Any thoughts?

Thanks a million!

 

[Guest]

You need to write the aray to a range and then reference the range,
The problem you will run into is that as a function you need to figure out
where you can send the new range.

Depending on the usage adding a hidden sheet can be problematic and adding a
temporary file also seems invasive. Delivering the function as an addin is
the only clean way, then you can write the range to a sheet in the addin.