Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Running a macro from an "IF" statement 2
- Thread starter Mark31
- Start date
-
2
- #2
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.
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
Automotive
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
Cheers
Greg Locock
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!
A thought just occured to me, is it possible to run GoalSeek within an IF statement?
Thanks for your help!
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!!
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"
ThenRange("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!!
GregLocock
Automotive
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
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
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.
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.
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!
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" ThenRange("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!
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!
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!
- Thread starter
- #14
- Status
Similar threads
- Locked
- Question
- Locked
- Question