Is It Possible to Call a Macro from a Function?

[AlphaMax]

I created a macro (Sub Macro6()) that I want to call like a function on my spreadsheet, is it possible to call a macro from a function? I didn't program the macro manually, I used menu options to record it. But I'd like to be able to use it like a function. The macro does exactly what I need it to do when called as a macro, but when I call Macro6 from a function in a separate cell from my data range, all I get returned is "0".

FYI, my macro does this:

1) Copies 2 rows of existing data
2) Pastes the 2 rows of data as values in cells to the right
3) Sorts the two rows of data, using row 2 as the key


Graphically my macro does this:
Existing data: ................ Macro created data:

1..... 2.... 3.. 4... 5.............. 3.....2....4.. 5... 1
500 200 100 350 350 ......... 100 200 350 350 500

(please ignore the dots, they are spacers only)

 

[Jonmo1]

the answer is Yes AND No...

You CAN call a macro from a UDF function in a cell.

However, that macro CANNOT make any changes to ANY cells ANYWHERE in the book. Cannot change values, copy/paste, delete, change formats....nothing...

 

[wsjackman]

You could assign the sub to a button on the tool bar , so all you would have to do is click on it. you might have to modify the macro to use the active cell as the row indicator