Article ID: 817578
Article Last Modified on 4/7/2006
APPLIES TO
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition
SYMPTOMS
When you try to find and to change the link source of a Microsoft Excel Add-In (*.xla) that is referenced by a workbook, and the existing link source contains a long path, you may receive the following error message:
CAUSE
When you open an Excel workbook, the external add-in reference is resolved. The add-in is loaded by using the last-saved path of the add-in. If the add-in cannot be found, Excel prompts you for the add-in location. The add-in may not be found if the workbook was transferred to a different computer or to a different mapped drive. If you later select a location or try to update links, you may receive the error message that is mentioned in the "Symptoms" section. This problem occurs if one or more cells contains formulas that are large, and the cells use one or more of the add-in functions.
This problem occurs when an add-in reference is either missing or changed. Excel appends the full path of the add-in name to the formula for qualification. If a formula is either large or contains multiple calls to add-in functions, and the path that is appended is long, the total length of the formula may be greater than the internal limit of 1024 characters. This is the cause of the error. If the error occurs, the link path cannot be changed, and the workbook cannot use the add-in from the new location.
If you use the following formula in a cell, and one of the following conditions is true, the problem occurs:
- The add-in for MyProduct and for MySum is already loaded.
- The add-in for MyProduct and for MySum exists at the same location as where the workbook was last saved.
The formula appears in the workbook as follows:
=MyProduct(MySum(A1,B1,C1,D1) + MySum(A2,B2,C2,D2) + MySum(A3,B3,C3,D3)) + MyProduct(MySum(E1,F1,G1,H1) + MySum(E2,F2,G2,H2) + MySum(E3,F3,G3,H3)) + MyProduct(MySum(I1,J1,K1,L1) + MySum(I2,J2,K2,L2) + MySum(I3,J3,K3,L3)) + MyProduct(MySum(M1,N1,O1,P1) + MySum(M2,N2,O2,P2) + MySum(M3,N3,O3,P3))
However, if the add-in is not loaded and is missing from the original path, the add-in name is fully qualified with the last known path and is appended to each add-in function that is used in the formula. The result may be a formula that is greater than 1024 characters. The following is an example:
='C:\Program Files\My Application\MyXLAddin.xla'!MyProduct( 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(A1,B1,C1,D1) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(A2,B2,C2,D2) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(A3,B3,C3,D3)) + 'C:\Program Files\My Application\MyXLAddin.xla'!MyProduct( 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(E1,F1,G1,H1) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(E2,F2,G2,H2) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(E3,F3,G3,H3)) + 'C:\Program Files\My Application\MyXLAddin.xla'!MyProduct( 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(I1,J1,K1,L1) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(I2,J2,K2,L2) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(I3,J3,K3,L3)) + 'C:\Program Files\My Application\MyXLAddin.xla'!MyProduct( 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(M1,N1,O1,P1) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(M2,N2,O2,P2) + 'C:\Program Files\My Application\MyXLAddin.xla'!MySum(M3,N3,O3,P3))
The qualified function name in the formula cannot be changed because the length limit prevents editing of the cell without potential truncation of the formula and loss of data. As a result, Excel prevents the action and you may receive the error message that is mentioned in the "Symptoms" section.
WORKAROUND
If you receive this error message, Microsoft recommends that you close the workbook without saving. Then, return the workbook to the original owner to change the add-in location to a shared folder location, such as the library folder, that permits you to share the workbook. Or, the large formula can be changed by dividing the formula into two or more sub-formulas. After the workbook is saved and then reopened, the problem does not occur.
Alternatively, if you have the add-in, but it is in a different path, you can copy the add-in to the path that is specified by the formula. Load the add-in from that path and then reopen the workbook. This avoids the qualification of the function names. You can then change the link to a shorter path, or you can remove the long formula.
This problem occurs only for workbooks that have missing links or broken links.
STATUS
This behavior is by design.
MORE INFORMATION
The formula length is limited to 1024 Unicode characters by the internal workings of Excel. For additional information about these limits, click the following article number to view the article in the Microsoft Knowledge Base:
264626 Microsoft Excel 2000 specifications
Keywords: kbqfe kbprb kbhotfixserver KB817578
