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)
Debug.Print dResult
On Error Resume Next
dResult = Application.WorksheetFunction.Forecast(Range(“c3”).Value, Range(“B4:B5”).Value, Range(“A4:a5”).Value)
Debug.Print Err.Description
End Sub
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.
.Value converts cells formatted as currency to VB Currency data type, and cells formatted as dates to VB Date data type. .Value2 does not do these conversions (and its faster too!)
So most of the time you want to use .Value2 rather than .Value (but .Value is unfortunately the default and can’t be changed because somebody’s code would be bound to break).
The only exception I have found to the always use .Value2 rule is if you want to use VB’s ISDATE() because it recognises VB Date datatype but not a Double.