Recording a macro
Even though a lot of your work in VBA both can and will be made using only programming, it is highly recommended to get to know the Record Macro feature as well. When using Record Macro, VBA will track and translate every action you perform to code. This can help you in several ways:
- It is always easier to start a task with some existing code instead of a blank sheet.
- Use the fact that VBA translates all your actions into code to your advantage. Instead of searching online for e.i. how to mark a range of cells, try recording a macro doing it and then look at the code created.
Our first task will be to duplicate a sheet using the Record Macro feature.
Expand |
---|
title | Duplicate sheet tutorial, text |
---|
|
- Make a simple sheet to duplicate.
![](/wiki/download/attachments/151453808/image2019-8-2_13-2-45.png?version=1&modificationDate=1564743763000&api=v2) - Locate the Developer tab, and click on Record Macro.
![](/wiki/download/attachments/151453808/image2019-8-2_13-5-10.png?version=1&modificationDate=1564743908000&api=v2) - A new window should then open. You always want to name your macros something meaningful, making it easy to understand what macro does by just the name. This applies especially when the number of macros increase. Click then OK.
![](/wiki/download/attachments/151453808/image2019-8-2_13-9-37.png?version=1&modificationDate=1564744175000&api=v2) - VBA is now recording our actions.
![](/wiki/download/attachments/151453808/image2019-8-2_13-10-41.png?version=1&modificationDate=1564744239000&api=v2) - To duplicate a sheet in Excel, right-click on the sheet name, and click on Move or Copy...
![](/wiki/download/attachments/151453808/image2019-8-2_13-11-48.png?version=1&modificationDate=1564744306000&api=v2) - In the window appearing, mark off for (move to end) on the location, and tick off for Create a copy. Click then OK.
![](/wiki/download/attachments/151453808/image2019-8-2_13-13-31.png?version=1&modificationDate=1564744409000&api=v2) - A duplicate has now been made.
![](/wiki/download/attachments/151453808/image2019-8-2_13-14-29.png?version=1&modificationDate=1564744467000&api=v2) - Time to stop the recording. This is done by clicking on Stop Recording, located the same place as the Record Macro was.
![](/wiki/download/attachments/151453808/image2019-8-2_13-15-51.png?version=1&modificationDate=1564744549000&api=v2) - To look at the code created by VBA, open the Visual Basic Editor.
![](/wiki/download/attachments/151453808/image2019-8-2_13-20-54.png?version=1&modificationDate=1564744852000&api=v2) - Open Module1, and the code is displayed.
![](/wiki/download/attachments/151453808/image2019-8-2_13-21-45.png?version=1&modificationDate=1564744902000&api=v2) - If we want to run the code again, we can click somewhere inside the subroutine DuplicateSheet() and then click on the play icon.
![](/wiki/download/attachments/151453808/image2019-8-2_13-26-3.png?version=1&modificationDate=1564745160000&api=v2) - Our Sheet1 is then duplicated again.
![](/wiki/download/attachments/151453808/image2019-8-2_13-27-55.png?version=1&modificationDate=1564745273000&api=v2) - If we wanted, we could also have made e.i. a button and attached our macro to it, just as in the Hello World tutorial.
|
Explicit vs implicit variable declaration
When writing larger pieces of code, knowing the difference between explicit and implicit variable declaration may save you several hours of debugging.
Expand |
---|
title | Explicit vs implicit variable declaration |
---|
|
Before we look at the different variable declarations, we need to establish the difference between the words explicit and implicit.
- explicit is defined as: "stated clearly and in detail, leaving no room for confusion or doubt."
- implicit is defined as: "implied, rather than expressly stated"
When coding in Visual Basic, you are by default not obligated to declare your variables before using them. This an example of an implicit variable declaration.
Code Block |
---|
|
MyVariable = 10 |
When doing so, VBA will automatically declare it as a variant type, provided that the variable has not been declared before. This is a nice feature, but may cause trouble when writing larger scripts. Let's look at an example:
We have already written a lot of code using the variable "MyVariable". If we were to misspell "MyVariable" with "MyVaiable" once, VBA will think of it as a new variable declared implicitly, and declare it automatically, thus not informing us of our misspell.
Code Block |
---|
|
MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10
'... lots of code
'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed |
To avoid this problem, we can use the Option Explicit Statement. It ensures that all variables must be explicitly declared by using the Dim, Private, Public, ReDim or Static statements. Otherwise, it will display an error at compile time.
If used, the "Option Explicit" statement must appear in a module before any procedures.
Code Block |
---|
|
Option Explicit
Sub Example()
'Explicitly declaring MyVariable as an integer
Dim MyVariable As Integer
MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10 ' We will encounter an error at this line.
'... lots of code
'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed
End Sub |
Compiling the code above will result in the following error message.
Image Removed
If you want to make all your modules have the "Option Explicit" statement without having to remember to write it yourself, follow these instructions:
In your VBA editor, go to "Tools" → "Options".
Image Removed
Tick the "Require Variable Declaration" box. Click "OK".
Image Removed