I’m trying to write a function to forecast a value based on historical data. The UDF has three arguments: range of cells (must be within a
single row or column), method (1-4), and an optional rate (-1 <= rate <= 1). The four methods are: 1) last value in range, 2) last value minus
first value divided by number of intervals, 3) same as (1) but multiplied by 1 plus rate, and 4) same as (2) but multiplied by rate. I am trying
to use "Select Case" vs. multiple If...Then...Else. When I use the formulas between the "Case" sections, they work fine. But when I tie them all
together using "Select Case," I get the #VALUE! error.
Please help me find the error in my ways. Thank you in advance!
Rob
Public Function rForecast(fRange As Range, _
fType As Integer, _
Optional fRate As Double) As Double
Application.Volatile True
' Declare variables
Dim CtRows As Long
Dim CtCols As Long
Dim RngChk As Integer
RngChk = 0
Dim fRangeFirstRow As Integer
Dim fRangeFirstCol As Integer
Dim fRangeLastRow As Integer
Dim fRangeLastCol As Integer
' Check for valid arguments
CtRows = fRange.Rows.Count
CtCols = fRange.Columns.Count
If CtRows > 1 Then RngChk = RngChk + 1 ‘ Allow ranges in one row or
If CtCols > 1 Then RngChk = RngChk + 1 ‘ in one column only
If fRate = “” Then fRate = 0 ‘ Assume rate is 0 if left blank
‘ Get values of first and last rows and columns in range
fRangeFirstRow = fRange.Row
fRangeFirstCol = fRange.Column
fRangeLastRow = fRange.Row + fRange.Rows.Count – 1
fRangeLastCol = fRange.Column + fRange.Columns.Count – 1
‘ Calculate function
If RngChk <= 1 Then Select Case fType Case 1 rForecast = Cells(fRangeLastRow, fRangeLastCol).Value Case 2 rForecast = Cells(fRangeLastRow, fRangeLastCol).Value + _ (Cells(fRangeLastRow, fRangeLastCol).Value - Cells(fRangeFirstRow, fRangeFirstCol).Value) _ / (WorksheetFunction.Count(fRange) - 1) Case 3 rForecast = Cells(fRangeLastRow, fRangeLastCol).Value * (1 + fRate) Case 4 rForecast = fRate * (Cells(fRangeLastRow, fRangeLastCol).Value + _ (Cells(fRangeLastRow, fRangeLastCol).Value - Cells(fRangeFirstRow, fRangeFirstCol).Value) _ / (WorksheetFunction.Count(fRange) - 1)) Case Else rForecast = CVErr(xlErrNum) End Select End If End Function
