Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Analysis ToolPak - Bug when installed from Code
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Analysis ToolPak - Bug when installed from Code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Analysis ToolPak - Bug when installed from Code
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" wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Analysis ToolPak - Bug when installed from Code
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Analysis ToolPak - Bug when installed from Code
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" wrote in message ups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Analysis ToolPak - Bug when installed from Code
Rob,
Now that really was a prompt response; just 12 minutes! Many thanks for all your help on this. Regards, Philip Clarke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Analysis ToolPak Regression Analysis Help Requested | Excel Worksheet Functions | |||
installed analysis toolpak in xl2007, not visible | Excel Discussion (Misc queries) | |||
Can't get CUMPRINC to work. Have Installed Analysis Toolpak | Excel Worksheet Functions | |||
NETWORKDAYS gives #NAME even though Analysis Toolpak is installed | Excel Worksheet Functions | |||
Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) |