Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing:SOLVER (XL2002 SP1)

    Hi,

    I seem to be experiencing erratic problems with the Excel Solver add-in (again!!) with an application that I am writing.

    When I call the code that uses the Solver.xla functions, I check first to see whether the add-in is loaded. If not, I load it. I also have a reference set to SOLVER in my VBA project. For some reason, every now and then, when I open the spreadsheet application, the reference has failed and in VBA under Tools/References, SOLVER is listed as missing. Any ideas on how to get around this ? I can go and browse to C:Program FilesMicrosoft OfficeOffice10LibrarySolver again, but it refuses to mark the reference as valid. At the moment I have to deselect the Add-In at which time I get a warning that Solver is referenced and cannot be unloaded, then I quit Excel. I then reload Excel, select the Add-In again and all is well until the next time.

    Any ideas please,

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    Are you developing this application on one PC, or on different ones (for exampla at work and at home)?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    Hi Hans,

    I am working on both a work and a home PC.

    After I posted my original message, the whole .xls file got corrupted - I had a feeling that something was "going wrong" with the file. After Excel "recovered" the file, all my modules, class modules and user forms have gone ! I'll have to save the modules from a backup copy and import them to recreate the file !

    What did you have in mind for my original problem when you asked if I was working on multiple PC's ?

    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    I was wondering if a) you were using different versions of Excel on the work and home PC, and/or [img]/forums/images/smilies/cool.gif[/img] the Solver add-in was installed in different folders on the work and home PC.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    You may find that de-referencing the solver add-in, then selecting the solver addin from Excel's Tools, add-ins and then referencing it again works.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    Thanks, that is more or less what I do to get going again, but I plan to distribute this application to a few people and I don't want this sort of erratic problem to keep arising. I suspect Hans is onto something about me using different PC's with different versions of Excel.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    Two PC's, two versions of XL :

    Home : Excel 2003 (11.5612.5606), solver in C:Program FilesMicrosoft OfficeOFFICE11LibrarySOLVER

    Work : Excel 2002 (10.2614.3501) SP-1, solver in C:Program FilesMicrosoft OfficeOffice10LibrarySolver

    The function to check whether Solver is loaded as an add-in and referenced is shown below - I don't know who wrote it, although I have edited it a bit.

    Private Function SolverInstalledOK() As Boolean
    'Function to check that solver is installed, loaded and referenced
    'Function returns TRUE if the above conditions are satisfied

    On Error GoTo ErrorHandler

    Dim strTemp As String
    Dim adn As AddIn
    Dim blnReferenced As Boolean
    Dim obj As Object

    SolverInstalledOK = False

    'See if Solver.xla exists in the library directory
    strTemp = Application.LibraryPath & "SolverSolver.xla"
    If Dir(strTemp) = "" Then
    MsgBox "Solver

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    I have always found trying to manage references in code very tricky. (In Access, the application I work most with, the problem is that standard VBA code stops working if there is a missing reference)

    Perhaps your best bet is to create and distribute a separate version of the workbook for each version of Excel you need to support. Finalize each version on a PC with the target version of Excel.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    Hi Hans,

    With your help I think I've finally nailed this problem and I'll describe what I've found to maybe help others . . . If you see anything wrong with my "findings", please shout.

    The problem was never "random" as I thought, it is quite reproducible.

    You are quite correct that the version of Excel matters as I have been trying it out on two PC's at home. Saving a workbook with a reference to Solver in it using XL2000, and then opening that same workbook in a later version does not cause problems and the reference to Solver is automatically resolved by the newer version of Excel. Going to an earlier version however gives the error every time. I used the code below to list the references when opening the file on the different PC's :

    <font color=red>For i = 1 To ThisWorkbook.VBProject.References.Count
    With ThisWorkbook.VBProject.References(i)
    ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).O ffset(1, 0) = .Name
    ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).O ffset(0, 1) = .FullPath
    ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).O ffset(0, 2) = .GUID
    End With
    Next i</font color=red>

    I noticed that running this after first saving the file in XL2003 and then opening it in XL2000 that the solver "Name" was listed as "C:Program FilesMicrosoft OfficeOFFICE11LibrarySOLVERSOLVER.XLA" and not as SOLVER. Looking at the references (on the XL2000 PC) obviously showed Solver as missing because there is no "Office11" folder hierarchy on the XL2000 PC. Strangely enough, the code to list the references worked despite the missing solver reference. I would then manually deselect the missing Solver reference and then, because it was no longer listed as an available reference, instead of browsing for it, I used the code below to add it again :

    <font color=red>ThisWorkbook.VBProject.References.AddFro mFile Application.LibraryPath & "SOLVERSOLVER.XLA"</font color=red>

    There is never a GUID listed for solver under either XL2000 or XL2003, so I cannot use the .AddFromGUID method to load Solver. I wonder why it doesn't have a GUID ?

    I have decided the best solution is to save the Workbook without a Solver reference - I think I'll try putting some code in the Workbook_BeforeClose or probably better in the Workbook_BeforeSave event to strip out the Solver reference. I'll then put some code in the Workbook_Open event procedure to reference Solver. Hopefully this'll do the trick as the correct Office version of Solver will then be added to the references when the workbook is opened. Does it matter that my workbook will always be saved and hence opened in an "uncompiled" state ? If so, is there a way to compile it from code ?

    Regards,

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing:SOLVER (XL2002 SP1)

    Hello Adrian,

    Your observation is correct that references are adjusted automatically when going from an older version to a newer one, but not when going from a newer version to an older one.

    I don't think you can compile a project from code, but that is not serious, for VBA will code the first time it is run; the only disadvantage is that the code will run slightly slower the first time, but with today's PCs that is hardly noticeable, if at all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •