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

ID: Q114437

The information in this article applies to:

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

Last Reviewed: February 4, 1998