XL: Copy of Recorded Macro Code Doesn't Work Correctly

 Last reviewed: February 3, 1998
Article ID: Q114437
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a

SYMPTOMS

In the versions of Microsoft Excel listed above, when you copy and paste code in a Visual Basic procedure that was created with the Macro Recorder, the pasted code may not behave the way that you expect.

For example, when you record a Visual Basic procedure and you type text into a cell or text box and you manually break the line (press ALT+ENTER in a cell, or press CTRL+ENTER or ENTER in a Text Box), the line break character is displayed as a "box" in the recorded procedure.

If you copy the line that contains this character to another location in the module or to another module, the line is pasted as multiple lines, breaking the text at the "box" character. This break causes either a syntax error or the "Sub or function not defined" error message to appear when you run the procedure. The error message you receive depends on where the line break character is located in the string.

CAUSE

In some cases, the Visual Basic code recorded by the Macro Recorder contains an extended ASCII character (such as a carriage return or line break). When you copy and paste this code to another location in a module, the character is pasted with the normal functionality.

The nonprinting character used in Microsoft Excel to create the line break is not supported by the text format in a Visual Basic module.

WORKAROUND

To work around this behavior, edit the recorded procedure and replace the symbol with a Chr(n) function, where "n" is the ASCII character code for the extended character. For example, if the recorded macro appears as follows in the module 

   ActiveCell.FormulaR1C1 = "good||guy"
where || is the "box" character, edit the line as follows: 

   ActiveCell.FormulaR1C1 = "good" & Chr(10) & "guy"
NOTE: The value 10 is the ASCII character code for a carriage return.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

In Microsoft Excel, if you enter the text "good" and then press ALT+ENTER and then type the text "guy" in a cell on a worksheet while the macro recorder is running, the code appears as follows in the recorded procedure 

   ActiveCell.FormulaR1C1 = "good||guy"
where || is a "box" character.

If you copy this line, and paste it to another location in the procedure or module, it appears as follows: 

   ActiveCell.FormulaR1C1 = "good"
   guy ""
When you run this procedure, you receive the following error message: 

   Sub or function not defined
If the recorded procedure appears as follows 

   ActiveCell.FormulaR1C1 = "goodguy||"
where || is a "box" character, then pasting this text to another location results in the following: 

   ActiveCell.FormulaR1C1 = "good guy"
You receive a syntax error when you run the procedure that contains this line.

For more information about character codes, such as the ANSI or ASCII Character Set, or the Character Set (0-127), choose the Search button in the Visual Basic Reference and type: 

    character codes
For more information about the Chr Function, choose the Search button in the Visual Basic Help and type: 

    Chr

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a macro paste chart XL5
XL7
Keywords : kbcode kbprg PgmOthr SynGnrl
Version : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbug
Solution Type : kbfix

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.