Excel Analysis ToolPak - Bug when installed from Code

[pc]

I have written some code which generally does exactly what I want it
to. However, when a new user runs it for the first time, they get a
400 error with no explanation, just a red cross.

I can repeat their experience by unchecking Analysis ToolPak and
Analysis ToolPak - VBA from Tools > Add-Ins, and then re-opening the
spreadsheet. If I step through the code though, the error does not
occur.

I have removed the majority of my code, to try to establish where the
bug occurs, and I have found that even the few lines below will
reproduce the same error.

Sub Workbook_Open()

AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True

Sheets(1).Select

End Sub

It appears that I cannot select a worksheet after the Analysis ToolPak
is installed by code. I use Excel 2000 / Windows 2000 at home and
Excel 2002 / Windows XP at work, but both set-ups give me the same
problem.

I would be most grateful for any assistance on this. Many thanks in
anticipation.

Philip Clarke

 

[Rob Bovey]

Hi Philip,

Try changing:

Sheets(1).Select
to:
ThisWorkbook.Sheets(1).Select

(or Workbooks("YourBook.xls").Sheets(1).Select is you aren't selecting a
sheet located in the same workbook where the code is running in).

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

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[pc]

Try changing:

Sheets(1).Select
to:
ThisWorkbook.Sheets(1).Select

Rob,

Thanks for your prompt response. Unfortunately, I have tried the
change that you have suggested, but it does not seem to help me.

Were you able to replicate my original problem? Did the suggested
change solve it for you?

Regards,

Philip Clarke

 

[Rob Bovey]

Hi Philip,

My apologies. I remembered this problem and thought I remembered the
solution. I have to do this once in a while to remind myself never to post
an answer in the newsgroups without testing it. The correct fix is to
change the last line to:

ThisWorkbook.Sheets(1).Activate

In some circumstances selecting a sheet fails for some reason, but
activating a sheet always seems to work.

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

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[pc]

Rob,

Yes, that works just fine. Many thanks for taking the time to respond
to me.

On another slightly related issue, I initially thought that my problem
was due to "dirty" code. Therefore, as I have done on a number of
previous occasions, I used your Code Cleaner. Unfortunately in this
case it made no difference to my problem, but as before, I noticed a
slight increase in the file size. I'm just curious to know why this
would be, particularly when errant material is being removed. If I
carry out the same exercise manually, it it usual for the file size to
stay the same or perhaps decrease a little.

Finally, just to say that (almost) everything I know about Excel has
been learnt from browsing the NewsGroups and reading numerous Excel MVP
web-sites. So here's a big thank you to yourself, Chip Pearson, John
Walkenbach, Stephen Bullen and many others for the excellent work. I
guess you really don't know just how many people you have helped over
the years!

Thank you once again.

Philip Clarke

 

[Rob Bovey]

Hi Philip,

The only way to be sure the file sizes you see before and after using
the code cleaner are meaningful is to compare a fully compiled version to a
fully compiled version. What I mean by that is you should choose Debug >
Compile from the VBE menu and then save your project before cleaning it,
then do the same immediately after cleaning it.

If the project doesn't require cleaning it's not unusual for the file
size of the cleaned file to fluctuate a little bit and sometimes even be a
few KB larger than it was previously. I'm not totally sure why this happens
but my best guess is that small changes in the way Excel is storing the same
data are the cause.

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

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[pc]

Rob,

Now that really was a prompt response; just 12 minutes!

Many thanks for all your help on this.

Regards,

Philip Clarke