Links to Analysis Toolpak disappear?!?

[joelrmonty]

I am creating a worksheet in Microsoft Excel 2004 for Mac that uses
the eomonth() and networkdays() functions from the Analysis Toolpak
add-in. I have had no issues with the worksheet until suddenly last
week as I opened it, Excel told me that there were links to other
worksheets and it gave me the options to Break Links or Edit Links.
Several times, I have been able to "fix" the problem just by
restarting excel/mac and/or removing the add-ins from their folder and
then replacing them. Any ideas on how to permanently fix this error?
Unfortunately, most of the worksheet depends on the values returned
from these functions.

I don't know if this affects anything, but my worksheet has the
following properties:
1. Maximum iteration is set to 1.
2. Several macros are used throughout, but they do not link to the
functions in question.

Thanks in advance!

 

[JE McGimpsey]

I am creating a worksheet in Microsoft Excel 2004 for Mac that uses
the eomonth() and networkdays() functions from the Analysis Toolpak
add-in. I have had no issues with the worksheet until suddenly last
week as I opened it, Excel told me that there were links to other
worksheets and it gave me the options to Break Links or Edit Links.
Several times, I have been able to "fix" the problem just by
restarting excel/mac and/or removing the add-ins from their folder and
then replacing them. Any ideas on how to permanently fix this error?
Unfortunately, most of the worksheet depends on the values returned
from these functions.

I don't know if this affects anything, but my worksheet has the
following properties:
1. Maximum iteration is set to 1.
2. Several macros are used throughout, but they do not link to the
functions in question.

Don't think the problem is due to either 1 or 2, unless a macro unloads
the add-ins.

Are you running any other workbook that would unload the ATP?

Since the add-ins are an application-level setting, it might be due to a
bad preference file. You can safely delete the Excel prefs in the

~:Libraryreferences:Microsoft

folder, though you'll lose existing preferences, obviously. On the other
hand, since you're using macros anyway, you can ensure that the ATP is
loaded by putting something like this in your workbook's ThisWorkbook
module:

Private Sub Workbook_Open()
Application.AddIns("Analysis Toolpak").Installed = True
End Sub

If the ATP is already installed, the code won't do anything, but if it's
not, it will cause the add-in to be loaded and installed.

It's possible that the problem could be corruption in the workbook as
well - copying everything to a blank workbook, while possibly a lot of
work, could take care of that.

 

[Wayne]

My ATP disappears after I load it through the add-in menu, then I receive a funcres error upon starting Excel. I have tried reinstalling Excel with no luck.
If the 'ThisWorkbook module' is a fix, where do I find this option?

 

[JE McGimpsey]

My ATP disappears after I load it through the add-in menu, then I receive a
funcres error upon starting Excel. I have tried reinstalling Excel with no
luck.
If the 'ThisWorkbook module' is a fix, where do I find this option?

You've tacked this on to a message that is so old that it's no longer on
the newsgroup server - so I have no idea what the "ThisWorkbook module'
fix is that you're referring to.

What version of XL are you using?

Can you use the ATP functions in XL after you load the ATP?

What do you mean by "My ATP disappears" - is it still listed in the
Add-ins dialog?

Do you have other add-ins loading? Or do you have event macros that use
the ATP in your Personal Macro Workbook?

 

[Wayne]

I am using Excel 2004, 11.3.6. After I use the tools>Add-ins>Analysis Toolpak, I can use the statistical package for the session. Once I exit out of Excel, the next time I start the Excel program, I get the 'Funcres Not Found' error and Excel fails to start unless I click okay. I can reinstall Excel and repeat the process, but the same error occurs.

I do not have any other add-ins or event macros loading.

In addition, there is a post in this thread that mentions trying the 'This Workbook Module' which I have never heard of.

As always, thanks for your help.

 

[Wayne]

In Case you cannot see the thread, here was your response:
Since the add-ins are an application-level setting, it might be due to a
bad preference file. You can safely delete the Excel prefs in the

~:Libraryreferences:Microsoft

folder, though you'll lose existing preferences, obviously. On the other
hand, since you're using macros anyway, you can ensure that the ATP is
loaded by putting something like this in your workbook's ThisWorkbook
module:

Private Sub Workbook_Open()
Application.AddIns("Analysis Toolpak").Installed = True
End Sub

If the ATP is already installed, the code won't do anything, but if it's
not, it will cause the add-in to be loaded and installed.

 

[JE McGimpsey]

In Case you cannot see the thread, here was your response:

Ah...

First, when you try deleting the preference file, make sure XL is
closed, which forces it to create a new pref file from factory settings.

As far as the VBA fix I suggested, ThisWorkbook is the default name for
the workbook code module:

http://www.mcgimpsey.com/excel/modules.html

It would be most effective to put that code in the Personal Macro
Workbook's startup module, or another file or add-in in your Startup
folder, which by default is

HD:Applications:Microsoft Office 2004:Office:Startup:Excel:

though you can change to an alternate in Preferences/General.