[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

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

pbastian

12/12/2006 3:09:00 PM

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.
3 Answers

Gary Keramidas

12/12/2006 3:21:00 PM

0

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


--


Gary


<pbastian@bastianconsulting.com> wrote in message
news:1165936111.903669.104450@l12g2000cwl.googlegroups.com...
> 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.
>


pbastian

12/12/2006 3:30:00 PM

0

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.

Gary Keramidas wrote:
> 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
>
>
> --
>
>
> Gary
>
>
> <pbastian@bastianconsulting.com> wrote in message
> news:1165936111.903669.104450@l12g2000cwl.googlegroups.com...
> > 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.
> >

NickHK

12/13/2006 3:09:00 AM

0

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

<pbastian@bastianconsulting.com> wrote in message
news:1165936111.903669.104450@l12g2000cwl.googlegroups.com...
> 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.
>