MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Make a macro a function

Posted by Jennifer on December 15, 2000 7:13 AM

Does anyone know how to transform a macro into a function?
I'm hoping it is as easy as changing the word "sub" to "function", but I suspect it is more than that. Thanks for any help!

Posted by greg on December 15, 2000 7:59 AM

you are correct. You also need end function instead of end sub. You also have to declare any variables in the parenthesis.

Posted by Jennifer on December 19, 2000 10:56 AM

Here is an example of a VERY boring and basic macro:
Sub Macro1()
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
End Sub

To make it to a function, I realize I need to change the word sub to function wherever applicable.
What I don't understand is what needs to be in the parentheses.

Thanks again!

Posted by Celia on December 19, 2000 11:57 AM

This macro cannot be made into a function.
Functions cannot change cells. They can only return results.

Posted by Jennifer on December 21, 2000 6:57 AM

So if I understand your response, Celia, I can only write functions that involve actual calculations?
If I want to change formatting based on values in certain cells (I realize the simple macro above doesn't test for values), I HAVE to use a macro?

Thanks for clarifying!

Posted by Celia on December 21, 2000 1:59 PM

That's right. Have a look also at :-
MS Knowledge Base - article Q213199