Please Help Running a Macro from a formula

[Vic]

Is there a way to cause a macro to run from a formula?

As an example:

=if(B1<B2,GoToNeg,GoToPos)

GoToNeg and GoToPos are macros which will manipulate B1
and B2.

Thanks in advance!

 

[Bob Phillips]

Vic,

If they really are macros that alter things on a workbook, you can't do it.
If they are functions that return a value, then that is possible.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

Worksheet functions, and UDFs called by worksheet functions can only
return values, not affect other cells' values.

You could use an Event Macro. Put this in the Worksheet code module
(right-click on the worksheet tab, choose View code):

Private Sub Worksheet_Calculate()
If Range("B2").Value > Range("B1").Value Then
GoToPos
Else
GoToNeg
End If
End Sub

This will fire one or the other each time a calculation is done on
the sheet. If you want to fire it only when B1 or B2 are changed by
user entry:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then
If Range("B2").Value > Range("B1").Value Then
GoToPos
Else
GoToNeg
End If
End IF
End Sub

or, if B1 and B2 are calculated:

Private Sub Worksheet_Calculate()
Static oldB1 As Variant
Static oldB2 As Variant
If (oldB1 <> Range("B2").Value) Or _
(oldB2 <> Range("B2").Value) Then
If Range("B2").Value > Range("B1").Value Then
GoToPos
Else
GoToNeg
End If
oldB1 = Range("B1").Value
oldB2 = Range("B2").Value
End If
End Sub