Compile error in hidden module - in custom VBA add-in...

[pbastian]

This problem has me stumped. I personally feel this is poor planning
on MS part, but I am looking for a workaround.

I have a VBA add-in that I distribute that has a number of UDFs and
other functionalities. My most recent addition involves the SOLVER.XLA
add-in as a reference in the project. Depending on what version of
Office you are using, the location of this add-in will be in a
different place (THANK YOU, MS! BRILLIANT MOVE!). When I compile the
add-in with the correct path to Solver.xla and give it to someone that
has a different version of Office, I get this error message. I am
fully aware of late-binding and ensure that I use it as much as
possible to avoid just this sort of problem, but this issue with the
Solver add-in doesn't seem to have a solution a-la "late binding".

My application is password protected, so when an end-user opens Excel,
and the add-in tries to load, and Solver.xla is "missing", the end user
will first get the "Compile error in hidden module", and the add-in
stops loading.

How can I create an add-in that at least traps this error, and allows
corrective action? Ideally, I can control access to certain features
in the menus of the add-in based on the availability of the references,
and let the rest of the add-in work correctly.

From scanning all the posts on this subject, there does not appear to

be a good workaround.

 

[Gary Keramidas]

just curious, have you tried:

Select Case Val(Application.Version)
Case 11
MsgBox "office 11" ' load from here
Case 10
MsgBox "office 10" ' load from here
Case Else
MsgBox "not office 10 or 11"
End Select

 

[pbastian]

That might work if I can get past the compile error that pops up when
you try to load the add-in.

From what I can tell, if I successfully compile it on one machine, and

copy it to another that has a different directory structure for the
Office installation, then it no longer "compiles" cleanly, and
generates the error.

 

[NickHK]

I would imagine it is more a case that you develop on a later version of
Excel than your users have installed.
If so, COM will not "downgrade" references.
If you develop on the oldest supported version, COM will "upgrade" the
references.

To me though, an add-in could be located anywhere on the file system, or
possibly not present.
If Excel knows about the add-in, it will be listed in the AddIns collection,
although not necessarily loaded.
You can iterate this collection, checking for the addin and load it.
Using late binding, if the above succeeds, you can then set your references.

NickHK