Adding reference to Solver in Excel messes up the reference path

[obaluse]

Hi,

I am using Excel 2003 SE. So, VBA code is working fine, but adding a reference to SOLVER and running macro starts generating compiler errors:

"Compile error: Can't find project of library" for example to code lines like

x = Ucase("test")

and to get it working one must use

x = Strings.Ucase("test")

Now without changing this kind of stuff everywhere, how to fix this? Or is it simply better coding practise to start using full path (starting from excel or workbook level) function calls everywhere in the code?

BR,
ob

[si_the_geek]

See the "Fixing Common VB Errors" link in my signature for explanations and solutions for this error (and others). It is designed for VB rather than VBA, but most things in it (including this one) are virtually identical.

I suspect it works fine on your machine, but not when you give it to other people, is that correct?

If so, the problem is likely to be the usual cause of the error - a Missing Reference. The way to sort it out is to either use some kind of installation (to make sure they have whatever is Missing), or to use Late-Binding with error handling (so only the code that uses the reference fails).

[obaluse]

See the "Fixing Common VB Errors" link in my signature for explanations and solutions for this error (and others). It is designed for VB rather than VBA, but most things in it (including this one) are virtually identical.

I suspect it works fine on your machine, but not when you give it to other people, is that correct?

If so, the problem is likely to be the usual cause of the error - a Missing Reference. The way to sort it out is to either use some kind of installation (to make sure they have whatever is Missing), or to use Late-Binding with error handling (so only the code that uses the reference fails).

There appears to be no missing references. And it also works on other machines as well. But no machine can use SOLVER -reference and pathless code. I guess its best to just not use SOLVER as a reference when possible.

I was under the impression that binding is only related to variable declarations. Is String-type variable early bound?

[si_the_geek]

I find it odd that there are no Missing References, as that is nearly always the cause of the error (according to Microsoft, the only cause), but there are other possibilities.


Your term "pathless code" is confusing, the technical term is "unqualified", as you aren't qualifying it with the library. That should be OK most of the time, but is not always ideal. The most likely cause of problems is when two References have functions with the same name (in this case, it seems like UCase is in Solver too).

Unfortunately I can't be as useful as I'd like, because I don't have Excel 2003 installed, and don't seem to have Solver in my list of available References.

To check if that is the issue, press F2 to get the Object Browser up, and in the combobox at the top select "Solver" (or whatever name it is given there) and then type Ucase into the other combobox. When you press Enter (or click the binoculars icon) the Search Results list should fill with any matching items.

If there is something shown in the search results, your best bet will probably be to use a Late Bound connection to Solver - as you then don't have to alter any existing code.


I find it odd that you used Strings as the library for Ucase, as I would expect the library for it to be VBA (eg: x = VBA.Ucase("test") ).

I was under the impression that binding is only related to variable declarations. Is String-type variable early bound?

Binding is related to declarations and References - Late Binding means not using a Reference, and declaring the variables with a generic data type like Object.

Data Types like String are not bound at all, as they are not objects, just standard data types.

[obaluse]

I find it odd that there are no Missing References, as that is nearly always the cause of the error (according to Microsoft, the only cause), but there are other possibilities.

Well there really are no missing references.

Your term "pathless code" is confusing, the technical term is "unqualified", as you aren't qualifying it with the library.

Yes, my english skills are limited. What I meant with pathless code is that you don't use it as member of a class which is member of library.

Example of "pathless" code:

x = Ucase("test")

Example of code with path:

x = VBA.Strings.Ucase("test")

That should be OK most of the time, but is not always ideal. The most likely cause of problems is when two References have functions with the same name (in this case, it seems like UCase is in Solver too).

Checked it and it seems that is not the case. Adding the reference to Solver only messes up calls to all members of Strings class in VBA library. Could the problem be that Solver as a reference is not .dll but .xla?

Unfortunately I can't be as useful as I'd like, because I don't have Excel 2003 installed, and don't seem to have Solver in my list of available References.

However your help is most welcome. Solver is a rather old (> 5 years) third party addin and I don't know its origin or source code. Only that it's needed in some applications.

I find it odd that you used Strings as the library for Ucase, as I would expect the library for it to be VBA (eg: x = VBA.Ucase("test") ).

The library is VBA, but it has class called Strings and Ucase is in that class.

[anhn]

With the error message "Compile error: Can't find project of library", for sure, there is a MISSING library.

When any referenced library is missing, VBA will stuff up all its core functions.
Although to qualify all VBA core functions may solve the problem but that is not a good solution.

The best solution is to open Tools>References... to find the MISSING library, if that is used in the project then installed it, if that is NOT used in the project then uncheck the tickbox in front of it.

***
The SOLVER Add-In package has 2 files: SOLVER.XLA and Solver32.DLL

This is VBA_Functions module in SOLVER.XLA

Option Explicit

' this module contains all VBA functions the users can use in their macros.
' This is the place where localization should take place.
' NOTE: For localization to work with recording macros, the function names
'       here must be the same as the names on the Language sheet, starting
'       at A197, including parameters
' On the Excel4Functions sheet, the user functions are outlined in blue, and
' the first cell in each of these must be renamed for localization for
' backwards compatibility.
' The 2 dialogs on the Excel4Functions should also be localized.
'
Function SolverAdd(CellRef As Variant, Relation As Integer, Optional FormulaText As Variant) As Variant
    SolverAdd = SolvAdd(CellRef, Relation, FormulaText)
End Function

Function SolverChange(CellRef As Variant, Relation As Integer, Optional FormulaText As Variant) As Variant
    SolverChange = SolvChange(CellRef, Relation, FormulaText)
End Function

Function SolverDelete(CellRef As Variant, Relation As Integer, Optional FormulaText As Variant) As Variant
    SolverDelete = SolvDelete(CellRef, Relation, FormulaText)
End Function

Function SolverFinish(Optional KeepFinal As Variant, Optional reportArray As Variant) As Variant
    SolverFinish = SolvFinish(KeepFinal, reportArray)
End Function

Function SolverFinishDialog(Optional KeepFinal As Variant, Optional reportArray As Variant) As Variant
    SolverFinishDialog = SolvFinishDialog(KeepFinal, reportArray)
End Function

Function SolverGet(TypeNum As Integer, Optional SheetName As Variant) As Variant
    SolverGet = SolvGet(TypeNum, SheetName)
End Function

Function SolverLoad(LoadArea As Variant) As Variant
    SolverLoad = SolvLoad(LoadArea)
End Function

Function SolverOk(Optional SetCell As Variant, Optional MaxMinVal As Variant, Optional ValueOf As Variant, Optional ByChange As Variant) As Variant
    SolverOk = SolvOk(SetCell, MaxMinVal, ValueOf, ByChange)
End Function

Function SolverOkDialog(Optional SetCell As Variant, Optional MaxMinVal As Variant, Optional ValueOf As Variant, Optional ByChange As Variant) As Variant
    SolverOkDialog = SolvOkDialog(SetCell, MaxMinVal, ValueOf, ByChange)
End Function

Function SolverOptions(Optional MaxTime As Variant, Optional Iterations As Variant, Optional Precision As Variant, Optional AssumeLinear As Variant, Optional StepThru As Variant, Optional Estimates As Variant, Optional Derivatives As Variant, Optional SearchOption As Variant, Optional IntTolerance As Variant, Optional Scaling As Variant, Optional Convergence As Variant, Optional AssumeNonNeg As Variant) As Variant
    SolverOptions = SolvOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, SearchOption, IntTolerance, Scaling, Convergence, AssumeNonNeg)
End Function

Function SolverReset() As Variant
    SolverReset = SolvReset
End Function

Function SolverSave(SaveArea As Variant) As Variant
    SolverSave = SolvSave(SaveArea)
End Function

Function SolverSolve(Optional UserFinish As Variant, Optional ShowRef As Variant) As Variant
    SolverSolve = SolvSolve(UserFinish, ShowRef)
End Function

[obaluse]

With the error message "Compile error: Can't find project of library", for sure, there is a MISSING library.

The best solution is to open Tools>References... to find the MISSING library, if that is used in the project then installed it, if that is NOT used in the project then uncheck the tickbox in front of it.

Ok, just to get this clear. When I open Tools->References and References window pops up there should be a text MISSING before the library name?

[si_the_geek]

That's right.


However, I think in this case the problem might be something else, highlighted by this comment:

The SOLVER Add-In package has 2 files: SOLVER.XLA and Solver32.DLL

If you haven't included/referenced both files, that could possibly be the cause of the error.

[obaluse]

That's right.

There is no missing text. Microsoft support is not right on this one.

However, I think in this case the problem might be something else, highlighted by this comment:
If you haven't included/referenced both files, that could possibly be the cause of the error.

Apparently I can only add the .xla as reference. It doesn't seem to accept the .dll.

[pointer27]

In the vba editor:

Tools>references
click on "browse" allow the browser window to show ALL files
go to: C:\Program Files\Microsoft Office\Office12\Library\SOLVER

add a reference to "SOLVER.XLAM"

This solved it for me, the problem seems to be Excel 2007. For some reason (as previously mentioned) you can't add SOLVER32.DLL