MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Starting a macro from a formula?


Posted by Mike Beckage on January 24, 2002 10:36 AM

Can I start a macro from a formula?

EG

=IF(a1=10," ",personal.xls!marco1)

If so what is the proper syntax etc


Posted by Russell Hauf on January 24, 2002 10:54 AM

You can, but what exactly are you trying to accomplish? You can run into some bad stuff if you do it incorrectly...

Posted by mike beckage on January 24, 2002 11:00 AM

EG
I just want to insert a blank row when the value in a sorted column changes from 2001 to 2002 in a sorted table

if (a2<>a1,personal.xls!InsertRow

Posted by Russell Hauf on January 24, 2002 11:11 AM

I can see this causing problems - like when the row is inserted, the 2 cells still won't be equal. I suggest that you just write a macro and attach it to a command button or something.

However, since you asked, you can (at your own peril) call a function from a user-defined function.

Hope this helps,

Russell

Posted by Mike beckage on January 24, 2002 11:28 AM

I understand your concern however I wasn't explicit enough. The formula should read
if (sheet1!a2<>sheet1!a1,personal.xls!InsertRow
where the formula is on sheet2

How do I create a user defined function and call it from a formula. I created the InsertRow macro by recording it
Sub InsertRow()
'
' InsertRow Macro
Application.CutCopyMode = False
Selection.EntireRow.Insert
End Sub I can see this causing problems - like when the row is inserted, the 2 cells still won't be equal. I suggest that you just write a macro and attach it to a command button or something. However, since you asked, you can (at your own peril) call a function from a user-defined function. Hope this helps, Russell

Posted by Gary Bailey on January 24, 2002 12:25 PM

You won't be able to insert a row using a UDF called from a cell. Excel forbids a UDF from altering the structure of the workbook. All they can do is return a value to the cell that called them.

Perhaps you could call your macro from a command button or use the Worksheet_Change event to test whether sheet1!a2<>sheet1!a1 and then call your macro.

Gary I understand your concern however I wasn't explicit enough. The formula should read InsertRow Macro