Knowledge Base

Drag to Replace in Recorded Macro Returns Invalid Code

Article ID: 110709

Article Last Modified on 10/10/2006

APPLIES TO

Microsoft Excel 5.0 Standard Edition

This article was previously published under Q110709

SUMMARY

In Microsoft Excel version 5.0 for Windows, when you are recording a Visual Basic subroutine and you drag a cell or range of cells to another cell or range of cells to replace the original information, the recorded subroutine may contain syntax errors that prevent it from functioning correctly.

WORKAROUND

To work around this problem, you may need to modify the recorded code. The modified code should resemble the following:

   Sub Macro1()
      Range("A1").Select
      Selection.Cut (Range("B1"))
      Range("B1").Select
   End Sub

The third line, which originally contained invalid code, has been replaced by a line of code that performs the correct action.

MORE INFORMATION

Steps to Reproduce Problem

In a new workbook, create a new worksheet and enter the following values:

        A1: Alpha   B1: Bravo

From the Tools menu, choose Record Macro, and then choose Record New Macro.
In the Record New Macro dialog box, choose the Options button. In the Expanded Record New Macro dialog box, Under Language, verify that the Visual Basic option is selected. Choose OK to begin recording.
Select the contents of cell A1 and drag it to cell B1.
When you are prompted with the "Replace contents of destination cells?" message, choose OK.
To stop the Macro Recorder, choose Record Macro from the Tools menu, and then choose Stop Recording.
Switch to the new Visual Basic module.

The following subroutine should be contained in the module:

   Sub Macro1()
      Range("A1").Select
   ExecuteExcel4Macro "CUT(Range("A1"),Range("B1"))"
      Range("B1").Select
   End Sub

The third line (which starts with the ExecuteExcel4Macro method) will appear in red, indicating that the line contains a syntax error.

Switch to the worksheet.
In cell A1, type Charlie.
With cell A1 selected, choose Macro from the Tools menu.
From the list of macros, select Macro1 and choose the Run button.

You will receive the error message "Syntax error" and the third line of the subroutine will be selected.

Additional query words: XL5

Keywords: kbprogramming KB110709

Microsoft KB Archive/110709

Contents