Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing active cell works in a sub but not in a custom function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing active cell works in a sub but not in a custom function
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(J150,B1,27.89)) RITCHI wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing active cell works in a sub but not in a custom function
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 Dave Peterson wrote: 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(J150,B1,27.89)) RITCHI wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
copy name from active sheet to cell - using macro or function | Excel Worksheet Functions | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
HYPERLINK IF referencing the cell of the function.. | Excel Worksheet Functions |