Web Development

General discussion

Gravatar
Locked

Excel UDF Help - "Select Case"

By rford2112 ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

Two possible problems

by bschaettle In reply to Excel UDF Help - "Select ...

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

Also, add an error handler:

On Error GoTo ErrHandler

Back to Web Development Forum

Start or search

Related Discussions

Related Forums