Well, this isn't very "robust" code. It needs to be able to gracefully handle whatever gets thrown at it and not crash and die on the runway, so to speak.
[1] You're not setting a default value for the function. You should always do this as the first executable line in any function, to be sure it returns a value.
So, add a line that says: rForecast = -999
[2] You aren't checking for a divide-by-zero condition. When dividing, you should always check that the divisor is not zero. In your case,
If WorksheetFunction.Count(fRange)=1 Then your function will probably die miserably. End If
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Excel UDF Help - "Select Case"
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