Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenge Converting Non-Contiguous References
Ive created the following function to convert non-contiguous references into
a 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(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(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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenge Converting Non-Contiguous References
Late on a Friday....
cheat create a new sheet (visible = false) and copy the array onto the new sheet and then get a range reference- do stuff - then delete the new sheet "Brian" wrote: Ive created the following function to convert non-contiguous references into a 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(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(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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Cell References | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Converting to Absolute Cell References - en bloc | Excel Worksheet Functions | |||
XIRR non contiguous references | Excel Discussion (Misc queries) |