XL97: ApplyNames Method May Cause Invalid Page Fault

• Microsoft Excel 97 for Windows

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.

   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 examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

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 this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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"

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")