Adding reference to Solver in Excel messes up the reference path
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
and to get it working one must use
Code:
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
Re: Adding reference to Solver in Excel messes up the reference path
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).
Re: Adding reference to Solver in Excel messes up the reference path
Quote:
Originally Posted by 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).
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?
Re: Adding reference to Solver in Excel messes up the reference path
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") ).
Quote:
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.
Re: Adding reference to Solver in Excel messes up the reference path
Quote:
Originally Posted by 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.
Well there really are no missing references.
Quote:
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:
Example of code with path:
Code:
x = VBA.Strings.Ucase("test")
Quote:
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?
Quote:
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.
Quote:
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.
Re: Adding reference to Solver in Excel messes up the reference path
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
Code:
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
Re: Adding reference to Solver in Excel messes up the reference path
Quote:
Originally Posted by anhn
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?
Re: Adding reference to Solver in Excel messes up the reference path
That's right.
However, I think in this case the problem might be something else, highlighted by this comment:
Quote:
Originally Posted by anhn
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.
Re: Adding reference to Solver in Excel messes up the reference path
Quote:
Originally Posted by si_the_geek
That's right.
There is no missing text. Microsoft support is not right on this one.
Quote:
Originally Posted by si_the_geek
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.
Re: Adding reference to Solver in Excel messes up the reference path
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
