Example

We would like to record a macro that changes the style of the text that is currently selected and inserts a heading above it.
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.
Select (File > New) and create a new document.
Press the Enter key a couple of times so there are few blank lines above your text.
Enter the following text and highlight it.

microsoft excel docs

Record the Macro

Select (Tools > Macro > Record New Macro) to display the Record Macro dialog box. Give the macro the name "FormattingText".
You don't have to worry about changing the "Store macro in" drop-down list as recorded macros are always placed in the the Normal.dot template.
Press OK to start recording. The Stop recording toolbar should be displayed.

microsoft excel docs

Select the Formatting toolbar and select the style "Heading 3" from the style drop-down box.
Press the Up Arrow key once and type the following "Be More Productive"
Hold down Shift and press the Home key to select the whole line of text.
Select the Formatting toolbar and select the style "Heading 1" from the style drop-down box.
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 the code is very easy. This is done in the Visual Basic Editor.
You can edit a macro by selecting (Tools > Macro > Macros). Select the name of the 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.

microsoft excel docs

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


Editing the Code

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

Sub FormattingText() 
'
' FormattingText Macro
' Macro recorded #date# by Russell Proctor
'
   Selection.Style = ActiveDocument.Styles("Heading 3")
   Selection.MoveUp Unit:=wdLine, Count:=1
   Selection.TypeText Text:="Be More Productive"
   Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
   Selection.Style = ActiveDocument.Styles("Heading 1")
   Selection.MoveLeft Unit:=wdCharacter, Count:=1
End Sub

Enter some more text and run this macro. The text will be changed to Heading 3 and the title "Be More Productive" will be added above it.
The Macro Recorder will always include some lines of code that can be removed.
The above macro could be simplified and the result would be exactly the same.


Writing Efficient Code

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

Sub FormattingText() 
   With Selection
      .Style = ActiveDocument.Styles("Heading 3")
      .MoveUp Unit:=wdLine
      .TypeText Text:="Be More Productive"
      .Style = ActiveDocument.Styles("Heading 1")
      .HomeKey Unit:=wdLine
   End With
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.
You could have typed the above code directly into the code window if you were familiar with VBA code.
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

It is very important to remember that you do not need to select the actual text to format it, if no particular text is selected then any formatting is applied to the whole paragraph.
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.


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