Copy of Recorded Macro Code Doesn't Work Correctly (114437)
The information in this article applies to:
- Microsoft Excel for Windows 95 7.0a
- Microsoft Excel for Windows 5.0c
- Microsoft Excel for Windows NT 5.0
- Microsoft Excel for the Macintosh 5.0a
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:
For more information about the Chr Function, choose the Search button in
the Visual Basic Help and type:
| Modification Type: | Minor | Last Reviewed: | 10/10/2006 |
|---|
| Keywords: | kbbug kbcode kbfix kbProgramming KB114437 |
|---|
|