VBA code not waiting on Solver (Office 2003)

[mikeJW]

I am running an Excel VBA programme where the VBA code calls the Solver and moves on immediately without waiting on the Solver. In fact the Solver does not run! :-(
When running in the development environment it works!
When starting the code via an event such as command button press, it fails!
I now understand the problem and have a solution so I have published the information for anyone else who encounters this.

Sequence:
VBA code starts running in Sheet1.
Sheet1 calls a procedure in Sheet2
Sheet2 procedure ends by calling the Solver.
Code returns to Sheet1 and continues without the Solver starting.

It is not an error in initializing the Solver but it is an anomaly in the VBA.
Moving the Solver call and the cells associated with the Solver to Sheet1 runs OK!
So it seems that code started in one sheet will not recognise the Solver if it is called from another sheet.

Solution1
Make sure when starting any code which eventually calls the Solver, that the Solver call and associated cells are on the starting sheet.
Solution2
Write your own ‘Solver’ it is a simple iteration of successive estimates between constraints.
I could have/should have gone this way but I wanted to know why the Solver was not running.

For your interest my Solver initialisation: 'Needs Project reference 'Solver'
Public Sub UseSolverQ()
SolverReset
SolverOptions Precision:=0.001
SolverAdd CellRef:="DesVar", Relation:=3, FormulaText:="Con1"
SolverAdd CellRef:="DesVar", Relation:=1, FormulaText:="Con2"
SolverOk SetCell:="TargetF", MaxMinVal:=3, ValueOf:="0", ByChange:="DesVar"
SolverSolve True 'True omits dialog
End Sub

DesVar is the design variable = in this case a range name
Relation relationship between the left/right sides of the equations
1 <= 2 = 3 >= 4 cell reference is an integer
FormulaText is the range name to go with the relationship
SolverAdd CellRef:="DesVar", Relation:=3, FormulaText:="Con1"
means the value in ‘DesVar’ >= the value in Con1
TargetF is the equation I am solving
MaxMinVal 1 = solve for minimum, 2 = solve for maximum, 3 = solve for specific value
Value of 0 = I am trying to solve for the equation = 0
ByChange by changing the value in ‘DesVar’

SolverOk SetCell:="TargetF", MaxMinVal:=3, ValueOf:="0", ByChange:="DesVar" means
the equation in 'DesVar' is specifically equal to the value 0

 

[JTBorton]

mikeJW

Have you tried using Application.Wait?

Sometimes your code will run faster than Excel can keep up with. In those cases it is necessary to make the code wait a second or two (usually only one is needed) to load such functions. A classic example is when using a status bar to update the user with macro status. The application.wait meathod is needed to give your status form time to load and appear to the user.

Dim Min                 As Integer      'Minute holder for time serial
Dim Hr                  As Integer      'Hour holder for time serial
Dim Sec                 As Integer      'Second holder for time serial
Dim WaitTime            As Date         'Used to allow frmProgress to change the caption
Dim objProgress         As ProgressBar  'Link to the progress bar
Dim objProgressLabel    As Object       'Link to the progress label

    Application.ScreenUpdating = False
    [COLOR=green]'Load progress form, set links to controls[/color]
    frmProgress.Show 0
    Set objProgress = frmProgress.pgbProgress
    Set objProgressLabel = frmProgress.lblProgress
    objProgressLabel.Caption = "Creating Error Table..."
    objProgress.Value = 0
    [COLOR=green]'Make the application wait 1 second.  This way the label change is visible to the user.[/color]
    Hr = Hour(Now())
    Min = Minute(Now())
    Sec = Second(Now()) + 1
    WaitTime = TimeSerial(Hr, Min, Sec)
    [COLOR=darkblue]Application.Wait WaitTime[/color]

[...do calculations...]

Another application of the application.wait function is when using SendKeys to access items from the excel menu. It is necessary to wait a moment for menus to appear or load excel applications (such as the solver)