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

VBA code not waiting on Solver (Office 2003)

VBA code not waiting on Solver (Office 2003)

VBA code not waiting on Solver (Office 2003)

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! sad
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.

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 smile
Make sure when starting any code which eventually calls the Solver, that the Solver call and associated cells are on the starting sheet.
Solution2 neutral
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()
    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

RE: VBA code not waiting on Solver (Office 2003)


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
    'Load progress form, set links to controls
    frmProgress.Show 0
    Set objProgress = frmProgress.pgbProgress
    Set objProgressLabel = frmProgress.lblProgress
    objProgressLabel.Caption = "Creating Error Table..."
    objProgress.Value = 0
    'Make the application wait 1 second.  This way the label change is visible to the user.
    Hr = Hour(Now())
    Min = Minute(Now())
    Sec = Second(Now()) + 1
    WaitTime = TimeSerial(Hr, Min, Sec)
    Application.Wait WaitTime

[...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)


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


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