Using a Macro in a Function

[Jim]

I want to use a macro in an "If then" statement, can this
be done?

 

[Earl Kiosterud]

Jim,

We may be getting into semantics a bit here. Macros are subs or functions.
Subs can do things to worksheets, like clear a cell, copy/paste data, etc.
They're called by an event, like by the click of a button, or an event like
changing a cell in a worksheet. Functions cannot do things to the worksheet,
but can return a value to the place where they're called in a formula in a
worksheet cell (including in an IF function). A sub may not be called in an
IF statement, but a user-defined function can.

 

[Bob Phillips]

Jim,

If the macro changes a part of a worksheet, and the function is used in a
worksheet, you can't do it as worksheet functions may only return a result,
not change formats or other cells.

If the macro just does some common processing, you can call it in your
function, even if that function is used in a worksheet.

If the function is just in your code, the macro can change worksheets as
much as needed.