Microsoft KB Archive/114437

= Copy of Recorded Macro Code Doesn't Work Correctly =

Article ID: 114437

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q114437



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.00a 5.00c 7.00a macro paste chart XL5 XL7 XL

Keywords: kbbug kbcode kbfix kbprogramming KB114437

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.