Running a macro from an "IF" statement 2

[Mark31]

I would be grateful if someone could let me know how to run a macro from an "IF" statment. I guess I am really after the syntax....
=if(a1>b1,run macro1,run macro2)

what do I need to replace "run macro" with to make it work?

thanks....

 

[IRstuff]

should be just:

=if(a1>b1,macro1(),macro2())

assuming no parameters passed
TTFN

 

[melone]

Or just put the if statement in the macro...

 

[gb156]

IRstuff, I tried using your suggestion, but kept receiving the following error: "That name is not valid." The macro name is only one word and was spelled correctly. Any idea? I'm using Excel 2002.

 

[IRstuff]

Sorry, I think I steered you wrong. Melone's suggestion is probably the only way with this version of Excel

TTFN

 

[Mutt]

Are you trying to return a value back to the spreadsheet cell via the if statement? 'cause then a macro written as a function will work as you propose - =if(a>b, macrofunction1(variables), macrofunction2(variables)).

If you want the macro do to certain 'things' to the workbook - eg build a graph or whatever, then have to put if statement within the visual basic environment.

Can help if you could describe more detailed what you wish to do with the macros.

 

[GregLocock]

I think one problem with this structure "=if(a1>b1,macro1(),macro2())" is that it would be called every time the sheet recalculated. That would be a very big overhead in some cases. It would also be very neat.



Cheers

Greg Locock

 

[gb156]

What I'm trying to do is run a macro that runs GoalSeek when an IF statement is true. The macro already works great; I just want it to run automatically. I'm not sure if having an IF statement within the macro would accomplish this. Would writing the macro as a function work here?

A thought just occured to me, is it possible to run GoalSeek within an IF statement?

Thanks for your help!

 

[Mutt]

I have done similar with a complex solver function to resolve a fairly sophisticated model of a synthesis gas loop with tail gas blending. Have just tried simple sample with goalseek and also works.

OK here goes :
Lets say in cell "A1" you have the result of the IF test - ie true or false - not a value in this code.
In "B1" you have the cell you wish to vary such that cell "B2" equals some specific target, say 12 here.

In VBasic mode, enable the project view and double click on the sheet in which the goalseek is to operate. In the code sheet view, write :

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1&quot Then
Range("B2&quot.GoalSeek Goal:=12, ChangingCell:=Range("B1&quot
End If
End Sub

Now what happens is every time anything on that sheet changes, it checks if A1 is true and if so executes the goalseek.

But beware - if lot of calcs on the go, especially when iterating, can get very slow or some strange hangups occurring. As usual make sure you have a master copy somewhere before adding in this function!!

 

[GregLocock]

If you can write the macros as functions then IRstuff's approach works. Goalseek won't run inside a function, when I tried.

That is

Function GregFun3(x1, x2, x3)
GregFun3 = x1 * x2 * x3
End Function

Function GregFun4()
Range("H15&quot.goalseek Goal:=100, ChangingCell:=Range("H13&quot
GregFun4 = 999
End Function



=IF(A1>B1,GregFun3(D3,D4,D5),gregfun4())

Will execute gregfun3 correctly, but gregfun 4 merely returns the value 999, goalseek doesn't run.



Cheers

Greg Locock

 

[Mutt]

To Greg above : Yep, afraid you can't operate any code within a function that changes cells back in the worksheet as general rule of thumb. Can read in values from the worksheet while executing the code but no outputing except to the function calling cell.

A tip : What is useful about functions is also that you can return a whole array of results - say you define a reactor or compressor model within a function, you usually want a lot of results that are carried out during the calculation procedure - just put all results into an array and return the whole lot back to the function.
eg
results(0,0) = result1
results(1,0) = result2
results(2,0) = result3
Gregfun = results

On the worksheet side, when you enter the function, highlight a range of cells large enough for the returned array, type in the function, press ctrl-shift-enter and you will be able to readily access all the returned values - each in its own cell.

 

[gb156]

Mutt,
Thank you for all of your help! Your suggestion worked, with one exception. Here's what I did:

In cell A1: =If(A2>A3,"ok","no good&quot

In the code sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1&quot = "ok" Then
Range("B2&quot.GoalSeek Goal:=12, ChangingCell:=Range("B1&quot
End If
End Sub


I had to put: = "ok" otherwise I got an error. Now, if A2>A3, then goal seek runs!

Thanks again for your help!

 

[Mutt]

Could try in cell A1 to rather have just =(A2>A3) - then cell contents will be true or false - but as your way is working leave it be!

Just a warning though - this method has some surprising results and spectacular crashes when the workbook gets large (> 2 or 3MB) or lots of defined functions are called!

 

[Mark31]

Hey....STTTTOOOPPPPPP

I figured it out about 2 months ago...thanks to everyone for your comments