Calling procedures in .xla projects

S

Steve

I have several functions/routines that are located in
library .xla files (particularly solver.xla and
atpvbaen.xla), and when working on other systems I often
get "macro not found" errors (when I say "often", I mean
in a seemingly random fashion). I have the references
set in my project accordingly.

Does anyone have any advice on systematically avoiding
these types of errors, and also detecting if the .xla
files are missing in order to fail gracefully?

Any advice would be appreciated,
Steve
 
R

Rob Bovey

Hi Steve,

I've copied some code below that handles Solver, which is the more
complicated case. It's more complicated because it's a demand-loaded add-in,
so even if the user has it checked in the Tools/Add-Ins menu it isn't
necessarily open. The last two lines are a trick I use to force it open.

Dim szSolverName As String
''' Determine if the Solver is installed on this computer.
On Error Resume Next
szSolverName = Application.AddIns("Solver Add-in").Name
On Error GoTo 0
''' If Solver was not located, error out.
If Len(szSolverName) = 0 Then
MsgBox "Solver not installed!"
Exit Sub
End If
''' If Solver was located, ensure that Solver.xla is open.
Application.AddIns("Solver Add-in").Installed = False
Application.AddIns("Solver Add-in").Installed = True

For non-demand loaded add-ins (almost everything else I've come across),
the logic is exactly the same, but you don't need the second to last line,
because these will always physically open when you install them.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
C

Chris

Hi Rob,

I was wondering if I could pick your brain a bit more on
the solver...

I'm experiencing some solver errors that are making me
pull my hair out, even after using your suggestions. I
see
"Solver: An unexpected internal error occured has
occured, or available memory was exhausted."
on a semi-random basis. I find that when I toggle the
add-in.installed property (not at run time), I'm able to
get the solver to work correctly. However when I try to
code the property change, I get the same error. Do you
have any knowledge about the solver that would help me??

Thanks a lot!
Chris
 
R

Rob Bovey

Hi Chris,

In the only project where I personally experienced this error I was able
to solve it by calling the Solver Auto_Open procedure prior to doing
anything with Solver. I have had some feedback from other people that this
works for them and some that it doesn't, so there's clearly more than one
thing that can cause this problem. You might try this and see if you get
luck, though.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Top