XL97: ApplyNames Method May Cause Invalid Page Fault (158638)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
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:

http://www.microsoft.com/partner/referral/

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.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbbug kbdtacode kberrmsg kbfix kbProgramming KB158638
©2004 Microsoft Corporation. All rights reserved.