Excel Analysis ToolPak - Bug when installed from Code

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

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" <[email protected]> wrote in message
news:[email protected]...
>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 wrote:
>
> 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

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" <[email protected]> wrote in message
news:[email protected]...
>
> Rob Bovey wrote:
>>
>> 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,

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>