Example

We would like to be able to record a macro that applies some shading to the cells that are currently selected.
It is always worth rehearsing the steps before you use the Macro Recorder. If you make any unnecessary steps it will create more code than is actually needed.
Lets rehearse the steps first. Highlight the cells "B2:E6". Select (Format > Cells)(Patterns tab).
Select the colour you would like to apply to the current selection.
Press OK and the cells will be shaded.

Select the cells "B2:E6"

You are now ready to record the actual macro, so lets start by inserting a new workbook.
Select (File > New) and create a new workbook.


Record the Macro

Select (Tools > Macro > Record New Macro) to display the Record Macro dialog box. Give the macro the name "ApplyShading".
Make sure that the "store macro in" drop-down list has "This Workbook" selected. This should be the default.
Press OK to start recording. The Stop Recording dialog box should be displayed.

(Tools > Macro > Record New Macro) dialog box

Select (Format > Cells)(Patterns tab).
Select the colour you would like to apply to the current selection.
Press OK and the cells will be shaded. Press the stop recording button on the left hand side of the Stop Recording toolbar.
The toolbar will then disappear.


Locating the Macro

Once you have recorded the macro viewing and editing the code is very easy. This is done using the Visual Basic Editor.
You can edit a macro by selecting (Tools > Macro > Macros). Select the name of your macro and press the "Edit" button. This will open the Visual Basic Editor in another window.
Using this method will locate the macro for you and will display the code module where the macro can be found.

(Tools > Macro > Macros) dialog box

Another application window should open and you should see the following lines of code.


Editing the Code

Notice that the default description is placed as a comment at the top of the macro.

Sub ApplyShading() 
'
' ApplyShading Macro
' Macro recorded #date# by Russell Proctor
'

'
   With Selection.Interior
      .ColorIndex = 50
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
   End With
End Sub

Highlight the cells G2:J6 and run this macro. This same shading will be applied to these cells.
The Macro Recorder will always include some lines of code that can be removed.
The above macro could be simplified significantly and the result would be exactly the same.


Writing Efficient Code

The first thing to remove is the comments. These are lines that start with a " ' " and are displayed in green in the Visual Basic Editor.
The following macro performs exactly the same action as the macro that was recorded, although this macro is significantly more efficient and will therefore run a lot faster.

Sub ApplyShading() 
    Selection.Interior.ColorIndex = 50
End Sub

For small macros speed is not that important. Both the above macros will only take a split second to run so you will probably not even notice a difference.
For larger, more complicated macros, speed becomes more of an issue. It is very easy to write a macro that works, but it requires a lot of practice to write a macro that is efficient.
Running
Being able to write macros directly into the code window will take some practice but the Macro Recorder is the best place to start if you want to learn more about the code.


Important

The AppActivate statement will appear a lot in your recorded macros. This statement just changes the focus to the named application or window but does not affect whether the window is maximised or minimised. These lines can be deleted with no consequence.
It is also important to remember that you do not need to select the cells, sheet, object, etc in order to be able to manipulate or format it. This can save a lot of time.
You can press (Tools > Macro > Visual Basic Editor) at any time to open the Visual Basic Editor directly.
If you have recorded a macro that takes a few seconds to run and there appears to be a lot of flickering on the screen, it is possible to turn screenupdating off. Use the following line of VBA code to switch it off Application.ScreenUpdating = False and then always remember to switch it back to True at the end.


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext