XL97: ApplyNames Method May Cause Invalid Page Fault

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q158638

Expand all | Collapse all

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.

Back to the top

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.

Back to the top

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 Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

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 (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

Back to the top

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.

Back to the top

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.

Back to the top

APPLIES TO

Microsoft Excel 97 Standard Edition

Back to the top

kbbug kbdtacode kberrmsg kbfix kbprogramming KB158638

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top