Cannot assign value of calculated range cell to a variable

[StreetMike]

This is driving me bonkers. I don't think I've ever had this issue before but...

I have a function that takes several ranges from a spreadsheet and calculates a spline curve from them. The mechanics aren't important. Because this thing is having trouble in a REALLY WEIRD WAY. The function is below. The ranges "CFDts", "Prices", and "weights" are all non-calculated values entered into cells on the sheet. The range "CFs" is a range of calculated values. They all have the same number of cells.

The problem is in the for...next loop. The first three lines work fine to assign the range values into the requisite array. The fourth, identical, line does not work. Stepping through it and putting a watch on the variables, CFs(i).Value has a value for each i. However, after each and every assignment CF(i) has a value of 0 for every i. No error is thrown. It simply takes a value (say, 103.8987744) and calls it 0 for CF(i).

I've tried value, value2; I've tried wrapping it in a CDBL(), I've tried changing the type...the only thing which seems to work is that if I hard-code the numbers in that column (rather than having them calculate), then the assignment happens. But that's not useful!

There must be something odd that I'm doing. Suggestions?

Thanks!
Mike


Public Function ThisFunction(curveTag As String, evalDate, settledate, CFDts As Range, CFs As Range, Prices As Range, weights As Range, Knots As Range) As String

Dim P() As Double
Dim w() As Double
Dim k() As Double
Dim CF() As Double
Dim CFDates() As Double
Dim i As Integer
Dim Alphas As Variant
Dim e_d As Double
Dim s_d As Double

e_d = CDbl(CDate(evalDate))
s_d = CDbl(CDate(settledate))

ReDim P(1 To Prices.Count, 1 To 1)
ReDim w(1 To weights.Count, 1 To 1)
ReDim CFDates(1 To CFDts.Count, 1 To 1)
ReDim CF(1 To CFs.Count, 1 To 1)

For i = 1 To Prices.Count
P(i, 1) = Prices(i).Value
w(i, 1) = weights(i).Value
CFDates(i, 1) = CFDts(i).Value
CF(i, 1) = CFs(i).Value 'wth***********
Next i

...more code

 

[mikerickson]

You have dimensioned CFs as a range, not an array. Try

CF(i,1) = CFs.Cells(i, 1).Value

Or (if its not a column wise range) perhaps

CF(i,1) = CFs.Item(i).Value

 

[StreetMike]

I actually tried that. Although again, the first three variables in that assignment are all ALSO ranges, and the assignments work! But here's what I get when I try assigning it using the code you suggest. From the immediate window, with i=1:
? CF(i,1)
0
? CFs.Cells(i, 1).Value
101.887058322359

 

[mikerickson]

Does setting CF to an array of Variants work? Don't see why Double doesn't, but ...

 

[StreetMike]

The only thing that changes is that then CF(i,1) is "Empty" rather than 0.

Weird, right?

 

[StreetMike]

OK, I discovered something that helps diagnose the problem but gets me no closer to solving it I am afraid. When I turn off auto-calc...it works. And if I hard-code CFs to values, it works.

So there's something about the fact that my function is using values that are themselves derived. But I don't know how to solve that since the whole point of the sheet is to take changing values and calculate numbers that changes based on those values. Is there a trick here?

 

[mikerickson]

Have you tried leaving autocalc to Manual and forcing calculation with Application.Calculate?

 

[StreetMike]

Continuously? The problem is that the incoming data are financial prices, and the whole point of this sheet is to calculate a live (or as-live) curve. Making it so the user needs to push a button to bring in live data and recalculate the curve each time kinda defeats the purpose!