Running a macro from a cell

by Ross Mclean, 27/03/05
revised 05/04/05

Doug Klippert writes on his excel blog:

“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:

=IF(A1>10,Macro1)

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:”

Full article

I don’t know of any built in functions that can do this, but I do know that we can write a UDF (user defined function) to do it, place this code in to a standard modual:

Public Function RMAC(ByVal Macro_Name As String, ByVal Arg1 As Variant)
RMAC = Application.Run(Macro_Name, Arg1)
End Function

Now, all you need to do is use this UDF in a formula, try this:

=IF(A1>2,(RMAC("My_Macro","The value is more than 2, watch out!")),"less than 2")

And of course your macro:

Sub My_Macro(ByVal sMessage As String)
MsgBox sMessage, vbOKOnly, "www.MehtodsInExcel.co.uk"
End Sub

Here the macro that’s called has one argument (sMessage), you can add more by adding more arguments in the Function (i.e. ByVal Arg1 As Variant, ByVal Arg2 As Variant, etc). Obviously you would need to include these in the worksheet formula as well.
 

There is a serious limitation with this method and thats, that you can not update the excel environment - i.e. change cell colour/size etc. - with macros called in this way.  Thanks to Jon Peltier for pointing this out.

So there you go!