VBA TUTOR NEWSLETTER ~ TUTORIAL ONE: Using the 'Macro Recorder' AND 'Office Assistant' to start a VBA project

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 2

VBA TUTOR NEWSLETTER ~ TUTORIAL ONE:
Using the 'Macro Recorder' AND 'Office Assistant' to start a VBA project.

In this project we want to create a macro that will highlight all occurrences of some text in the document.

Enter some text that has the word "vba" entered several times in it somewhere.
Start the macro recorder by selecting "Tools|Macro|Record New Macro". Give the macro the name "highlightText".
First set the default Highlight color to red.
Then open "Edit ... Replace" to open the Replace dialog box.
Enter "vba" as the text to search for, ensuring that the no formatting button is greyed. If it is not greyed, then click on the "No Formatting" button to clear formatting.
place the cursor in the "Replace with" text box, but enter no text. Then click on the "Format" button and select "highlight".
Finally click on the "Find Next" button to highlight all of the selected text.
When all the "vba" text has been highlighted, close the search dialog box.
Stop the macro recorder.
Go to "Tools|Macro|Macro", and select the newly recorded "highlight" macro, then click on the "Edit" button.
In the VB Editor, you can see the code for the recorded macro. It should look like ...

Sub highlightText()
Options.DefaultHighlightColorIndex = wdRed
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.highlight = True
With Selection.Find
.Text = "vba"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

That is all it takes!

NOW ... we want to be able to use this macro by first getting the user to enter the text. How do we do that?
While still in the VB Editor, open the "Office Assistant", and type in the text "get user input". You will be given a list of possible answers to your query. After selecting one of the suggestions, you will be led to getting user input by the "InputBox" function. Look at the example given ...

Dim Message, Title, Default, MyValue

' Set prompt.
Message = "Enter a value between 1 and 3"

' Set title.
Title = "InputBox Demo"

' Set default.
Default = "1"

' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

You can use this example to alter the above macro so that it should look like ....

Sub highlightText()
Dim Message, Title, Default, userText
Message = "Enter the text to be highlighted."
Title = "Using the Macro Recorder!"
Default = "text"
userText = InputBox(Message, Title, Default)
Options.DefaultHighlightColorIndex = wdRed
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.highlight = True
With Selection.Find
.Text = userText
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

This macro can be run be opening "Tools|Macro|Macro", then selecting the macro and clicking on the "Run" button.

YOU HAVE NOW LEARNT HOW TO USE THE 'MACRO RECORDER' TO CREATE A MACRO AND HOW TO USE 'HELP' TO ALTER THAT MACRO TO SUIT.

IN THE NEXT TUTORIAL WE WILL LOOK AT IMPROVING A STANDARD WORD MENU ITEM BY CREATING A NEW MACRO USING THE ABOVE METHOD.

HOPEFULLY, THESE CODE EXAMPLES WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!