XL97: Macro That Inserts Formula Returns #NAME?

• Microsoft Excel 97 for Windows

SYMPTOMS

When you run a macro that inserts a formula into a cell, the formula returns the #NAME? error value.

CAUSE

This problem occurs when you do all of the following:

1. You programmatically insert a formula into a cell.




-and-

2. The formula refers to cells using both a named cell reference and an A1 style reference, where the named cell reference begins with a letter followed by a number, for example: P4D or S1Test.




-and-

3. The formula uses the Lotus 1-2-3 formula entry method of using an absolute reference with the named cell reference.
   
   For example, the following macro causes the #NAME? error:

   
      Sub makeFormula()
           With ActiveSheet
              .TransitionFormEntry = True
              .Range("A1").Value = 1
              .Range("A2").Value = 2
              .Range("A2").Name = "A2Range"
              .Range("A3").Formula = "=sum(A1,$A2Range)"
              .TransitionFormEntry = False
         End With
      End Sub 

WORKAROUND

To work around this behavior, use either A1 style references or named references exclusively.

The following examples illustrate how to insert a formula into a cell without receiving a #NAME? error value.

Example Using A1 Style Reference

   Sub makeFormulaA1Ref()
      With ActiveSheet
         .TransitionFormEntry = True
         .Range("A1").Value = 1
         .Range("A2").Value = 2
         .Range("A3").Formula = "=sum(A1,A2)"
         .TransitionFormEntry = False
      End With
   End Sub 

Example Using a Named Reference

   Sub makeFormulaNamedRef()
       With ActiveSheet
           .TransitionFormEntry = True
           .Range("A1").Value = 1
           .Range("A1").Name = "P3D"
           .Range("A2").Value = 2
           .Range("A2").Name = "P4D"
           .Range("A3").Formula = "=sum($P3d,$P4D)"
           .TransitionFormEntry = False
       End With
   End Sub 

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

Additional query words: XL97

Keywords : kbdta kbdtacode xlformula
Version : WINDOWS:
Platform : WINDOWS
Issue type : kbbug