Excel VBA 24-Hour Trainer. Tom Urtis

A1 be empty for the next user.

      In any case, comments will be green in color by default, and will not be executed as VBA code.

      Another way you can speed up your macros is to use the With statement when you are performing multiple actions to the same object, such as to a range of cells. Suppose as part of your macro you need to clear a range of cells and format the range for the next user. If you use the Macro Recorder to do this, here is the code you might get:

      Range("A1:D8").Select

      Selection.Clear

      Selection.Locked = False

      Selection.FormulaHidden = False

      Selection.Font.Bold = True

      Selection.Font.Italic = True

      Notice there are five lines of code that all start with the Selection object, which refers to the selected range of A1:D8. If this code were to run as the Macro Recorder produced it, VBA would need to resolve the Selection object for each line of code.

      You can do two key edits to these lines of code by avoiding the Select method altogether and referring to the range object only once at the beginning of a With structure. Between the With and End With statements, every line of code that starts with a dot is evaluated by VBA as belonging to the same range object, meaning the range reference need only be resolved once. Here is the condensed code using a With structure for greater efficiency:

      With Range("A1:D8")

      .Clear

      .Locked = False

      .FormulaHidden = False

      .Font.Bold = True

      .Font.Italic = True

      End With

      Deleting a Macro

      There will be many times when you have recorded or composed a macro that you don't need any more. Instead of having a useless macro hanging around doing no good, it's better to delete it. To delete a macro, you can select its entire code in the Code window (be sure you only select from and including the Sub line to and including the End Sub line) and press the Delete key.

      NOTE You can delete a macro from outside the VBE. While on any worksheet, if you press Alt+F8 to call the Macro dialog box, you can select the macro name in the list and click the Delete button.

      Inserting a Module

      With larger VBA projects, you'll want to distribute your macros among two or more modules. With large projects, you'll be organizing your macros by some kind of theme or purpose. For example, the macros in your company's budget workbook that deal with reports might be placed in their own module. Sometimes you will have no choice in the matter, because modules do have a limit as to how much code they can individually support. To insert a new module, from the VBE menu bar, select InsertModule, as shown in Figure 4.5.