referencing active cell works in a sub but not in a custom function

[RITCHI]

Hi

I want to turn the following sub into a custom function but
substituting Sub Choice() with Function Choice() and End Sub with End
Function didn't work.

Any help would be gratefully received

Sub Choice()
Dim RowRand As Range
Set RowRand = ActiveCell.Offset(0, -1)
Select Case RowRand
Case 45 To 50
ActiveCell = ActiveCell.Offset(0, -10)
Case Is > 50
ActiveCell = ActiveCell.Offset(0, -9)
Case Else
ActiveCell = 27.89
End Select
End Sub


Ritchi

 

[Dave Peterson]

This is one way, although I wouldn't recommend it.

Option Explicit
Function Choice()

Application.Volatile

Dim ActCell As Range
Dim RowRand As Range
Dim myVal As Variant

Set ActCell = Application.Caller 'cell with the formula

Set RowRand = ActCell.Offset(0, -1)
Select Case RowRand.Value
Case 45 To 50
myVal = ActCell.Offset(0, -10).Value
Case Is > 50
myVal = ActCell.Offset(0, -9).Value
Case Else
myVal = 27.89
End Select

Choice = myVal

End Function

Since you're not passing any ranges to the function, excel won't know when to
recalculate. You can toss "application.volatile" at the top of the routine, but
that means that excel will recalculate the function when any other calculation
takes place.

And if something changes that doesn't cause a recalc, your function could be out
of date by one calculation cycle.

Instead, I'd pass those ranges as parameters in the function.

Option Explicit
Function Choice(RowRand As Range, rng1 As Range, rng2 As Range)
Dim myVal As Variant

Select Case RowRand.Value
Case 45 To 50
myVal = rng1.Value
Case Is > 50
myVal = rng2.Value
Case Else
myVal = 27.89
End Select

Choice = myVal

End Function

And with the cell with the formula K1, I'd use this formula:
=choice(J1,A1,B1)

And whenever J1, A1, or B1 changes, the function will recalculate.

====
But you could do the same thing with a formula (also in K1):
=IF(AND(J1>=45,J1<=50),A1,IF(J1>50,B1,27.89))

 

[RITCHI]

Thanks Dave

Your recommended solution solves the problem for me; I do have a
formual as you suggest that will do the trick but it is very long and
difficult, I shortened the number of conditions in my question.
I'm using the formula as part of a Monte Carlo type risk simulator.

Ritchi