I’ve been working with the FORECAST worksheet function a little lately and it’s been a struggle. First a quick look at the function
The known x’s and known y’s form a trendline based on linear regression, and the function returns the y coordinate on that line for the provided x.
The worksheet function seems to handle dates in the worksheet just fine. In the top example, my x values are dates and my y values are integers. I supply an x date right in between the two dates and should get a result right in between the two integers. And I do. The bottom example does the exact same thing except I’ve formatted the dates as General. That works too.
So what’s the problem, you ask. The problem is in VBA. I supplied an array of dates for my known x’s and it barfed:
Unable to get the Forecast property of the WorksheetFunction class
I tried making my arrays zero-based instead of 1-based. I tried making my arrays two dimensional (you know, like rows and columns). No dice. Finally, I tried converting the dates to Doubles. Success!
Dim dResult As Double
dResult = Application.WorksheetFunction.Forecast(Range(“c3”).Value2, Range(“B4:B5”).Value2, Range(“A4:a5”).Value2)
On Error Resume Next
dResult = Application.WorksheetFunction.Forecast(Range(“c3”).Value, Range(“B4:B5”).Value, Range(“A4:a5”).Value)
If you’re pulling the dates from Excel cells like I am above, you can use the Value2 property to “convert” then to Doubles. The above code produces
I wasn’t pulling from Excel cells, so I had to use the
CDbl function on my dates as I filled my knownx arrays. I find it odd that it works via the worksheet but not in code. I’d be interested to see what’s going on under the hood to make it act like that. The array arguments, according to intellisense, are un-typed. That make sense because you can supply ranges or arrays. But what causes the conversion? We may never know.