Microsoft KB Archive/158638

= XL97: ApplyNames Method May Cause Invalid Page Fault =

Article ID: 158638

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q158638





SYMPTOMS
When you run a Visual Basic for Applications macro in Microsoft Excel 97, you may receive the following error message:

This program has performed an illegal operation and will be shut down.

If the problem persists, contact the program vendor.

If you click Details, you receive an error message similar to the following:

EXCEL caused an invalid page fault in module EXCEL.EXE at

014f:3026787c.



CAUSE
This problem occurs when the following conditions are true:


 * You use the ApplyNames method to apply names to a range of cells that contains formulas.

-and-
 * None of the names you apply exists in the active workbook.



RESOLUTION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

To prevent this problem from occurring, do not apply names that do not exist in the active workbook. One method for doing this in a Visual Basic macro is to loop through the array of defined names before you use the ApplyNames method. Then, verify that at least one of the names exists. To do this, create the following macro: Sub CheckArrayofNames

'Set the range to which you want to apply names. Set Range1 = Range("B1:B5")

'Assume that none of the names exist. OneNameExists = False

'Set the array of names you want to apply. MyArray = Array("Alpha", "Bravo", "Charlie")

'Prevent the macro from stopping if a name doesn't exist. On Error Resume Next

'For each name we want to apply... For Each xItem In MyArray

'For each defined name in the workbook... For Each yName In ActiveWorkbook.Names

'If a match exists, then... If xItem = yName.Name Then

'A name that you are applying exists, so exit 'the loop. OneNameExists = True Exit For End If          Next yName If OneNameExists = True Then Exit For Next xItem

'Re-enable normal error handling. On Error GoTo 0

'If one of the names you are applying exists, then... If OneNameExists = True Then

'...apply names now. Range1.ApplyNames MyArray End If

End Sub



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.



MORE INFORMATION
In a Visual Basic for Applications macro, you can use the ApplyNames method to apply names to a range of cells that contain formulas. For example, if the defined name "Alpha" (without the quotation marks) refers to cell A1 in a worksheet, and cell B5 contains the formula "=$A$1" (without the quotation marks), you can use the ApplyNames method to automatically change the formula to "=Alpha" (without the quotation marks). For example, use the following sample line of code: Range("B5").ApplyNames "Alpha" Any reference to cell A1 is replaced by a reference to the defined name "Alpha".

You can apply more than one name at a time by creating an array of defined names. For example, use the following sample line of code to create an array: Range("B5").ApplyNames Array("Alpha", "Bravo", "Charlie") If you create an array, and none of the names specified in the array exist in the active workbook, you will receive an invalid page fault and Microsoft Excel will stop responding. To prevent this behavior from occurring, verify that at least one of the names specified in the array actually exists in the active workbook.

Additional query words: XL97 crash hang EXCEL EXE

Keywords: kbbug kbdtacode kberrmsg kbfix kbprogramming KB158638

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.