 |
|
|
|
The Macro Recorder
The Macro recorder in Excel is a great tool to use as a VBA learning aid. It will record any steps taken while in Excel and write the code so as they can be performed again. By studying the code written by the Macro recorder we can better understand the VBA language as applied to Excel. Having said this though it is important to realize that it does not write very efficient code. This is because it is only recording the steps you have taken to perform a particular task. Most of these steps are not necessary when writing efficient VBA code. For example you might record a macro going to all Worksheets within your Workbook and changing the Format Properties of the cells. To do this you would need to activate each Worksheet and then scroll to the required cell(s), select them and then format them as required. You would find that the code generated by the Macro recorder would be quite lengthy and most of it would be superfluous. This is because the Macro recorder cannot really write VBA code as it should be written, it can only write the code for ALL the steps you have taken. As a general rule with recorded code you can remove all the words like: Select, Activate, Scroll etc. This is because we very rarely need to actually Select, Activate or Scroll to a Worksheet or Range to perform any action on it. You will see what I mean by this soon!
While it is true that the Macro recorder is a good method for learning VBA there is also another reason why we should use it. This is because it can eliminate typos and save time. Although I can usually write the code for a particular task in Excel, I often record a macro doing it and then go into the VBE and edit it so that it becomes much more efficient.
To activate the Macro recorder go back to Excel (Alt+F11), go to Tools>Macro>Record New Macro. This would display the Record macro dialog box. It is here we can give our macro a one word meaningful name. There is also a drop down arrow asking where we want to store the macro, for all purposes unless stated otherwise, we will use "ThisWorkbook."
Exercise
1. Start recording a macro (call it ChangeRangeProperty)
2. Select Cell A1
3. Go to Format > Cells > Number > Currency
4. Select any one of the Currency Formats
5. Click OK
6. Stop recording the macro
Open the VBE (Alt + F11). In the Project Explorer, you should now see under Microsoft Excel Objects another folder (Object). In this case the Modules Object, and within that you will see Module1, which again is another Object. Modules is a collection of Objects which in this case are the ModuleObject. Double click on Module1 to open the Module. You should see some code as shown below.
Sub ChangeRangeProperty()
Range ("A1").Select
Selection.NumberFormat = <your format chosen>
End Sub
Let's go through each bit of this and define what each word means.
Sub
The word Sub is simply letting Excel know that there is a Procedure contained within it. A Procedure is a series of statements giving Excel instructions on what you wish to do. Basically a Procedure is Marco.
()
After each Procedure name is a pair of empty parenthesis. This is reserved in case wish to add arguments to the Procedure. These can be similar to the arguments that are often used in Worksheet formulas. Do not worry about the arguments at this stage as it will only add confusion and we can easily get by without them.
Range
A range as we have discussed above is member of the WorksheetObject. You will notice that the recorded macro does not include the WorksheetObject (the sheet we changed number format of cell A1). This is because the default for the RangeObject on it's own is always the active sheet. So unless the range we are referring to is on a different sheet, there is no need to use the WorksheetObject.
"A1"
This is simply the cell reference address that we selected. It must be enclosed in quotations as Excel sees cell addresses as text or strings (chain of characters that represent the characters themselves rather than their numeric values)
.Select
Select (in this case) becomes a method of the RangeObject. A method is a Procedure that acts on an Object. If we had selected a Worksheet it would be a WorksheetObject, a Chart a ChartObject etc.
Selection
Selection is as it states, simply returning to Excel the Object that has been selected. In this case, the RangeObject. The key word Selection is very generic as it can refer to nearly all Objects within Excel, but as the line Range ("A1").Select is the line of code immediately before it, Excel knows that in this case Selection is referring to a RangeObject only.
.NumberFormat
NumberFormat is a Property of the RangeObject.
= <your format chosen>
Is simply the format type that you have chosen.
End Sub
End Sub tells Excel that the Procedure has finished.
While we can certainly find out a lot from recording a macro and studying it's code, you will find as time goes on that the recorded code will include many lines of code, Properties, Methods etc., that are not needed.
As an example we could easily shorten the above-recorded macro to read:
Sub
Range ("A1").NumberFormat = <your format chosen>
End Sub
The reason we would do this is
It means less typing (good!!)
It means faster execution of code (although barely noticeable in this small example)
It means there is no need for the active cell to change. So this means that if you ran the Macro immediately above, while any cell except A1 is your active cell, the code will run without the user knowing.
What this means in a nutshell is that in MOST instances there is no need to select or activate an Object to change any one of it's Properties.
Exercise
Record three macro's as follows:
Selecting any cell, typing a number in and pushing ENTER.
Selecting a different Worksheet and highlighting the range A1:D10 change the background colour to yellow.
Selecting any cell, typing a number in and pushing ENTER. Copy the number you just typed and paste it to another cell
When you have recorded these three macros, I would like you to try and modify the code on all of them so that the words "Select" or "Selection" does not appear within the Sub.
Make use of the VBE Help. When you have modified as much as you can so that they still work the same, e:mail them to me. If you get stuck, please do not hesitate to contact me.
I fully realise that a lot of the Object, Property, terms etc. used in VBA can seem daunting, but please don't be deterred at all if you do not understand or grasp the concept. Take my word for it, there are many programmers out there who quite successfully use VBA without fully understanding the concept of what we have described here.
Any questions at all relating to this lesson, let me know. Please don't feel that any question is a stupid question. In fact, stupid is to not ask the question if you are unsure.
I look forward to hearing from you soon. When the next lesson commences is entirely up to you. Just keep in mind that it is important to understand what we have discussed here before moving on.