XL5: Error Returning Formula with Long Sheet and Filename

ID: Q123762

The information in this article applies to:

Microsoft Excel for Windows NT, version 5.0

SYMPTOMS

In Microsoft Excel, if a formula on your worksheet refers to a cell in another workbook, and the combined number of characters in the reference to this cell (combination of worksheet and workbook name) is greater than 108 characters, you may not be able to return the formula in this cell using a macro.

If you use the GET.CELL(6) function in a version 4.0 macro to return the formula in this cell, the #VALUE error value is returned.

If you use the Formula property in a Visual Basic procedure to return the formula in this cell, you receive Error 2015 in the Debug window, or the following error when you run the procedure:

'Run-time error 13:'

Type Mismatch

Additionally, if you use the MsgBox function to display the Formula property for this cell, you receive a "type mismatch" error message. If you set the Formula property for this cell equal to a variable, error 2105 is returned to the variable.

CAUSE

In Microsoft Excel for Windows NT, you can create filenames with up to 255 characters. However, when you reference a cell on another worksheet in Microsoft Excel, if the sum of the number of characters in the worksheet name and twice the number of characters in the workbook name is greater than 247, you cannot use a macro to return the formula that contains this cell reference.

For example, if the workbook name contains 124 characters, and the worksheet name contains 6 characters, you cannot return the formula that contains this cell reference on this worksheet (2*124+6=254).

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows 95, version 7.0.

REFERENCES

For more information about GET.CELL, choose Contents from the Help menu, choose the Reference Information Help topic, and then choose the Microsoft Excel Macro Function Contents. In the Microsoft Excel Macro Functions Help dialog box, choose the Search button and type:

GET.CELL function

Additional query words:

Keywords : xlnt
Version : winnt:5.0
Platform : winnt
Issue type :