0

I am trying to generate a simplistic Excel VBA function to calculate different types of interpolation. One difficulty I have is that, I don't seem to be able to re-assign the Y-range of values within the function.

When my VBA function reaches the following line, it doesn't seem to be able to change/re-calculate the YRange value according to the formula:

 YRange(rowCount) = -Log(YRange(rowCount)) / XRange(rowCount)

Can anyone advise/help?

Public Function Kian_CurveInterp(XRange As Range, YRange As Range, XFrac As Double, InterpMode As Integer)
    Select Case InterpMode
        Case 1 'step-straight
            Kian_CurveInterp = Linterp2(XRange, YRange, XFrac)

        Case 2 'zero-rate interp
            Dim rowCount As Integer
            For rowCount = 1 To XRange.Count
                YRange(rowCount) = -Log(YRange(rowCount)) / XRange(rowCount) 'convert discountFactors to zero-rates
            Next rowCount
            Kian_CurveInterp = Linterp2(XRange, YRange, XFrac)
            Kian_CurveInterp = Exp(-Kian_CurveInterp * XFrac)
    End Select
End Function
Community
  • 1
  • 1
Kiann
  • 531
  • 1
  • 6
  • 20
  • 3
    Are you calling this as a UDF ( ie from as formula in a cell)? If so, you can't change range values. Better approach is to copy values to variant arrays and process those – chris neilsen Feb 28 '18 at 00:15
  • 1
    Note that you should not use `Integer` for row counting because Excel has more rows than `Integer` can handle. I recommend always to use `Long` instead of `Integer` as there is no benefit in using `Integer` at all. – Pᴇʜ Feb 28 '18 at 08:34
  • Hi Chris, yes. The values pulled in are from formulae; and I am using an UDF. So, you mean I have to pull in the values, and re-copy the values into another range internally within the function? – Kiann Feb 28 '18 at 18:50
  • hi Peh, I thought for 'For/next' loop, the values have to be integer.. but let me try 'Long'. – Kiann Feb 28 '18 at 18:50
  • @Kiann `Long` is a kind of integer, but a "long integer". `Integer` can handle numbers from `-32768` to `32767` but Excel has `1048576` rows. Therefore you need to use `Long` (which is also an integer type) that can handle numbers from `-2147483648` to `2147483647`. We can conclude that we can [always use `Long` instead of `Integer` in VBA](https://stackoverflow.com/a/26409520/3219613). There is no benefit in using `Integer` in VBA at all. – Pᴇʜ Mar 01 '18 at 07:37
  • @Kiann What Chris said, if you use `Kian_CurveInterp` as a UDF in your sheet like `=Kian_CurveInterp(…)` you can only read from cells but cannot write to them, because this is not allowed in UDFs. If you need to do change values of cells you need to do that anywhere but NOT in a UDF. So you will eg. need another procedure to change the values (which eg. can be called by a button). – Pᴇʜ Mar 01 '18 at 07:41

0 Answers0