Treating noncontiguous cells as a single range used as argument of a UDF

Question

I'm trying to construct a yield curve by interpolation using two data sets: a range of calendar days until maturity and another range of interest rates.

I have a UDF on VBA which interpolates the interest rates. It uses as arguments two ranges of cells selected by the user on a excel worksheet.

I'm aware that Excel requires those ranges to be composed of contiguous cells. What I'm trying to do is to select noncontiguous cells in the worksheet and use its values as ranges for the UDF arguments.

More specifically, I have two columns of data which I use as ranges. But sometimes I need to skip one value on each of those columns and use the remaining values as the ranges for my UDF.

I tried to include two more range arguments in my UDF and use the union method to merge two ranges into one, so to use the resulting range in my code. It didn't work.

****edit

Chris, Thanks for pointing out the watch and immediate Windows. After many attempts the code finally works as I intended, but only with separate loops for DC_1 and taxas_1 ranges. Strangely, it won't work if I remove the "If k > 1 Then" statement from inside the loop. So I needed to keep it and make it do nothing.

I noticed that the indirect function won't work with an argument like (A1:A3,C2:C5), so I cant use indirect((A1:A3,C2:C5)) as an argument for the UDF. However, that's a minor problem.

In the case someone reaches this post with a similiar problem, here's the code I'm using.

Public Function Interplin_union(ByVal taxas_1 As Range, ByVal DC_1 As Range, ByVal dias As Integer) As Double
Dim tam1 As Long
Dim taxa1 As Double, taxa2 As Double, alfa As Double, d1 As Double, d2 As Double
Dim k As Long
Dim taxas As Variant
Dim DC As Variant

tam1 = taxas_1.Cells.Count
ReDim taxas(1 To tam1)
ReDim DC(1 To tam1)
Interplin_union = -1
Dim c As Range

    k = 1
For Each c In DC_1
    'taxas(k) = taxas_1(k)
    DC(k) = c
    If k > 1 Then
        'Debug.Print DC(k)
        If DC(k - 1) > DC(k) Then
            Interplin_union = CVErr(xlErrNA)
            Exit Function
        End If
    End If
    k = k + 1
Next

    k = 1
For Each c In taxas_1
    taxas(k) = c
        If k > 1 Then
          'Debug.Print DC(k), taxas(k)
        End If
    k = k + 1
Next


For k = 1 To (tam1 - 1)
    If ((DC(k) < dias) And (DC(k + 1) >= dias)) Then
        taxa1 = taxas(k)
        taxa2 = taxas(k + 1)
        alfa = (taxa2 - taxa1) / (DC(k + 1) - DC(k))
        Interplin_union = taxa1 + (alfa * (dias - DC(k)))
    End If
Next k

If (dias <= DC(1)) Then
    Interplin_union = taxas(1)
    ElseIf dias > DC(tam1) Then
    Interplin_union = taxas(tam1)
End If
End Function

Answer 1

You can actually loop over cells a discontiguous range.

That said, many Range properties, when applied to a discontiguous range , return the property value of the first contiguous sub-range.

Demo:

Dim cl as Range, SomeDiscontiguousRange as Range
Dim Rpt as String
Set SomeDiscontiguousRange = [A1:A3, C2:C5]
For each cl in SomeDiscontiguousRange
    'Do something with cl, eg
    Rpt = Rpt & "," & cl.Address
Next
Debug.Print Rpt 'returns the 7 cell addresses
Debug.Print SomeDiscontiguousRange.Rows.Count 'returns 3, the rows in the first sub-range

So, how to apply that to your situation? I'd suggest two things:

1. Don't create extra parameters for your UDF, rather use the power of Excel's formula Union operator - enclose the discontiguous ranges in brackets in the formula.
2. Loop the discontiguous range as shown above to map the data into a Variant Array. Then loop that array to apply your logic

Demo of 1

Function Demo(r as range) as Variant
    Demo = r.Address
End Function

Use in a cell like this: =Demo((A1:A3,C2:C5)) Note the double brackets, this tells Excel to treat (A1:A3,C2:C5) as a single parameter.

Your code refactored to apply these methods (and a few other optimisations)

Public Function Interplin_union(ByVal taxas_1 As Range, ByVal DC_1 As Range, ByVal dias As Integer) As Double
    Dim tam1 As Long
    Dim taxa1 As Double, taxa2 As Double, alfa As Double, d1 As Double, d2 As Double
    Dim k As Long
    Dim taxas As Variant
    Dim DC As Variant

    tam1 = taxas_1.Cells.Count
    ReDim taxas(1 To tam1)
    ReDim DC(1 To tam1)
    Interplin_union = -1
    Dim c As Range

    k = 1
    For Each c In DC_1
        taxas(k) = taxas_1(k)
        DC(k) = c
        If k > 1 Then
            Debug.Print DC(k - 1), DC(k)
            If DC(k - 1) > DC(k) Then
                Interplin_union = CVErr(xlErrNA)
                Exit Function
            End If
        End If
        k = k + 1
    Next

    For k = 1 To (tam1 - 1)
        If ((DC(k) < dias) And (DC(k + 1) >= dias)) Then
            taxa1 = taxas(k)
            taxa2 = taxas(k + 1)
            alfa = (taxa2 - taxa1) / (DC(k + 1) - DC(k))
            Interplin_union = taxa1 + (alfa * (dias - DC(k)))
        End If
    Next k

    If (dias <= DC(1)) Then
        Interplin_union = taxas(1)
        ElseIf dias > DC(tam1) Then
        Interplin_union = taxas(tam1)
    End If
End Function

---

Side notes:

• I make no comment on the logic of the function you apply to the data, I haven't analysed that

• To access properties of each range of a discontiguous range, Loop over Areas first, then Cells of each Area. Eg

  Dim Arr as Range, Cl as Range 
  For Each Arr in SomeDiscontiguousRange.Areas
      Debug.Print Arr.Rows.Count
      For Each Cl in Arr.Cells
          Debug.Print Cl.Address
      Next
  Next
  

• Excels Formula Union operator is , and Intersection operator is (Space).
  Try =demo((C4:D7 C2:C12)) to see what I mean. See this - "Under Reference operators" heading

• You'll want to add some checks to validate the range shapes, incase something unexpected is passed to the UDF