how to run a macro from a formula?

[Guest]

I need to define a formula to run a predefined macro as a result of a logical
comparision. Please help.

 

[Tom Ogilvy]

You can make you macro a function and then use it in your formula. Be aware
that macros used in this fashion can only return a value to the cell
containing the function - they can not format cells, change values in cells
or otherwise alter the Excel environment.

 

[moi]

Add a function and put that in your worksheet IF-statement, like:

Public Function cellFunctionFirst(cellText As String) As String
Call thenModule(cellText)
cellFunctionFirst = cellText
End Function

Private Sub thenModule(cellText As String)
cellText = cellText & " text string added by macro."
End Sub

From the function call the module and do the work there.
Only thing is, that a function always gives back a value and you might not
want that. So then it's better to add a worksheet event that keeps listening
and launches another more macro (or not) after your function changed a cell
with a certain result.