How to do the impossible (almost)
There are times when it might be nice to run a macro from a cell function.
Something like : if a cell has a certain value, a macro will run:
You can not initiate a macro from a worksheet cell function. However, you can use the worksheet's Change event to do something like this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("A1")) Is Nothing Then If Target.Value > 10 Then MsgBox "Put your macro code here in place of the MsgBox line" End If End If End Sub
When A1 is changed to a value greater than 10, the macro code will run.
To get to the Worksheet Event code, right-click the sheet tab and choose View Code.
How to Run a Macro When Certain Cells Change
After posting this, Ross Mclean came up with a great work around using a User Defined Function.
Public Function RMAC(ByVal Macro_Name As String, _
ByVal Arg1 As Variant)
RMAC = Application.Run(Macro_Name, Arg1)
Keep in mind that some commands will be ignored. A macro run from the worksheet like this will not change the Excel environment.
For example (watch line wrap), this VBA code:
Public Function RMAC _ (ByVal Macro_Name As String, _ ByVal Arg1 As Variant) RMAC = Application.Run _ (Macro_Name, Arg1) End Function Sub MyMacro(arg As String) ActiveCell.Interior.ColorIndex _ = 3 Beep End Sub
when invoked by this worksheet formula:
runs the sub MyMacro with some modification. The Beep is executed, the cell color change is not.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
See all Topics