running a macro from IF statement

[David]

Trying to execute a recorded macro from an IF function. I
have tried CALL statement but doesn't seem to work.

eg. IF (A1=1,call macro1(),2)

Is this the way to do it?

regards,
David D

 

[Don Guillett]

How?

=IF(A1=1, macro1(),2) should work.

-Dave

 

[Don Guillett]

right click on the sheet tab>view code>insert this>save workbook.
Now when a change is made to a1 the macro will run

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Target = 1 Then macro1
End Sub

 

[Dave Smith]

Errr.. of course, it is slightly more complicated than that. I only
addressed the issue of how to correctly compose the formula. To get Excel
to allow you to actually enter this formula requires that you change your
macro from a sub to a function. The number of things you can actually *do*
in a macro called like this is limited to say the least, but it is possible.
I, myself, would do something along the lines of what you suggested in
another post for that reason and many other reasons as well.

Regards,
Dave

 

[Don Guillett]

Yes,
You would have to do it this way with teh function in a REGULAR module:

=IF(F4=2,tryit(),"")

Function tryit()
MsgBox "works"
tryit = activecell * 8
End Function
========

 

[Dave Smith]

Perhaps I'm misunderstanding something but wouldn't this run macro1 every
time the sheet is calculated even if the value of A1 doesn't change? Am I
incorrect in thinking that for a formula such as this:

=if(A1=1, Foo(),"")

that foo will only be called when a change in A1( or it's precedents) occurs
and A1=1? This is very different behavior. Given that the original
question concerned an attempt to call a macro in a formula such as the
above, I'm attempting to construct a solution that will behave as much like
this formula as possible (it's possible that this is more restrictive than
is really needed but it's a worthwhile exercise nonetheless, IMO).

Now that I think of it, it will be necessary to test if foo() should be
called in the open event as well since the formula above would also be
recalculated when the book is opened. There's probably other times too but
I can't think of any off the top of my head.

Is there a way that one can tell in the Calculate event if a given cell is
being recalculated by Excel?

Regards,
Dave

 

[J.E. McGimpsey]

nope - the event is triggered after the calculation of the
sheet/book occurs.

 

[Harlan Grove]

...
...

Then it seems to me that the change event is probably a better place to put
this sort of thing since one can tell if the cell in question was affected
by the change. Is there any reason not to do this along the lines of the
code I posted previously?

Formula value changes due to recalculation don't trigger Change event handlers.
Only actual entries into cells trigger Change event handlers. So Change envent
handlers won't run when some formula changes value.

Enter =RAND() in cell A1 of some worksheet. Then add the following code to that
worksheet's VBA class module.

Private Sub Worksheet_Calculate()
MsgBox "Calc Trigger"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
MsgBox "Change Trigger - " & Target.Address(0, 0, xlA1, 1)
End Sub

Go back to the worksheet and press [F9]. Which event handler fires?

 

[Tushar Mehta]

Ahem!

I know that you only posted an example and I suspect you already know
this, but as a general rule having a UDF access XL-dependent data that
is not part of the UDF's argument list (ActiveCell in this case) is a
prescription for potential problems.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions