×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Running a macro from an "IF" statement
2

Running a macro from an "IF" statement

Running a macro from an "IF" statement

(OP)
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....

RE: Running a macro from an "IF" statement

2
should be just:

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

assuming no parameters passed

TTFN

RE: Running a macro from an "IF" statement

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

RE: Running a macro from an "IF" statement

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.


RE: Running a macro from an "IF" statement

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

TTFN

RE: Running a macro from an "IF" statement

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.

RE: Running a macro from an "IF" statement

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

RE: Running a macro from an "IF" statement

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!

RE: Running a macro from an "IF" statement

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") Then
    Range("B2").GoalSeek Goal:=12, ChangingCell:=Range("B1")
    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!!

RE: Running a macro from an "IF" statement

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").goalseek Goal:=100, ChangingCell:=Range("H13")
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

RE: Running a macro from an "IF" statement

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.

RE: Running a macro from an "IF" statement

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")

In the code sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Range("A1") = "ok" Then
  Range("B2").GoalSeek Goal:=12, ChangingCell:=Range("B1")
  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!

RE: Running a macro from an "IF" statement

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!

RE: Running a macro from an "IF" statement

(OP)
Hey....STTTTOOOPPPPPP

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close