EasyExcelVBA.com

Excel VBA Tutorial Chapter 2: How to write a macro

Goal of this chapter

In this chapter we will see how to write a macro from scratch. It is useful, but not indispensible to first have gone through Chapter 1: How to record a macro. In this chapter we will discuss the following Excel VBA elements:

VBA editing window

Open a new Excel workbook and access the VBA editing window by pressing Alt+F11 on a Windows computer or Option+F11 on Mac. Alternatively, you can go to the “Developer” ribbon and choose “Visual Basic”. The VBA editing window shows empty, or as below with a module open from FUNCRES.XLAM. If so, you can close that module‘s window, we will not use it. If you have any issue accessing the VBA editing window then have a look at the Preparations section in the Introduction.

Image of the VBA editing window on Mac
Figure 1: VBA editing window on Mac in Office 365

Modules

To start working on a new macro we first need to create a module. A module is a sheet in which you can type the programming code. First, select any line within ”VBAProject(Book1)“ in the Project Explorer window on the left-hand side. Thereby the module we will create will be located within this new “Book1” Workbook. Then create a new module by clicking the “Insert -> Module”. A clean sheet now appears in the main window. Depending on your settings it possibly states “Option Explicit” at the top. To rename the module press F4 (F6 for Mac users) or go to "View -> Properties Window". Let’s name the module “PractiseWritingVBA”. If that all went well the VBA editing window should look as below.

Image of created module called PractiseWritingVBA
Figure 2: VBA editing window with PractiseWritingVBA module created in Book1.

Option Explicit

Option Explicit is a module-level statement, which means it holds for all the programming you do in this module. Writing Option Explicit at the top of a module means you have to define (“declare”) all variables used in the module. This is good programming practice as it prevents possible mistakes in your macros, for example if you inadvertenty misspell the name of a variable (more about variables later in this chapter).

If Option Explicit doesn't show up automatically at the top of your new module then you can type it yourself on the first line of the empty sheet. If typed correctly it turns blue (may be rather dark color of blue). All reserved keywords recognised by the editor turn blue. Also the first letter of each reserved keyword should automatically turn to a capital letter if you initially typed all in lower case letters. The term “reserved keyword” means that Option Explicit is something that has a predefined function in Excel VBA and can only be used for the predefined purpose. So in case you wanted to use a reserved keyword the colour and capitalising let you know you have spelled it correctly. Or, if you didn’t want to use a reserved keyword, it indicates you would need to use other words.

If you want Option Explicit automatically in your newly created modules (recommended), then go in the VBA editor to “Tools -> Options -> Editor” (on Mac: “Excel -> Preferences... -> Editor”) and tick “Require variable declaration”.

Subprocedure

In the module you have to write the programming within different groups of code. Generally the objective of a group of code is either to execute an action or to return a value. The type of code that executes an action is a “Subprocedure”, while a “Function” returns a value (like the functions in the Excel worksheet). When writing a macro the main basis will be the subprocedure which we will see first. How to use functions is discussed in Chapter 7.

To start a new subprocedure type “sub” (anywhere below Option Explicit) followed by a space and the name of the subprocedure. In this case type “sub WrittenMacro” and hit enter. The result on your screen should be the following:

Image module after creating the sub calle writtenmacro
Figure 3: Sub WrittenMacro

The VBA editor immediately created the “End Sub” line when you hit enter. In case of WrittenMacro it deliminates the programming code you want to execute when running the WrittenMacro routine: anything written between the “Sub” and “End Sub” lines. Imagine you would have several subprocedures in your VBA Project then it enables you to execute only a specific part of your code. In contrast to "End Sub" appearing automatically here, in subsequent chapters you will see that for most statements in Excel VBA you not only need to write where it starts but also where it ends.

Copy & Paste

Let’s write in this subprocedure the same action that we recorded in Chapter 1. What we want to do is that the value from cell B3 is copied to the range B6:F14. Instead of recording we will now write the following code anywhere between the “Sub” and “End Sub” lines:

Range("B6:F14") = Range("B3")

And that’s it! You have just written the whole macro! What is essential to recognise in this one programming line is that we use an equal sign and assign the value from right to left. Whatever is on the right-hand side will be assigned to the contents of what is on the left-hand side.

To test if it works we need to write something in cell B3. For example write “Good morning”. To run the macro there are several options.

Do any of the two above and the result on your Excel worksheet should be:

Image of result of written macro
Figure 4: Result afer running WrittenMacro

Common errors when compiling or executing

Should something go wrong while compiling or executing the VBA code in this tutorial then either one of these events is likely to happen:

Comparison to recorded macro

In Chapter 1: How to record a macro we have recorded a macro that performs exactly the same action that we have programmed here.  We called it “RecordedMacro”. It is instructive to see the difference between the recorded macro and the written macro. The two codes are:

Image comparing the written and recorded code
Figure 5: Comparison of recorded and written VBA code on Windows Office 2013.

Whereas the “Recordedmacro” includes each and every action we performed manually for the copy pasting, we tried to write as little as possible in “WrittenMacro”. Whereas “RecordedMacro” literally states copy & paste lines, in “WrittenMacro” we have just assigned the value of B3 to B6:F14. In general, written macros are more efficient as they need a lot less lines and are therefore quicker to execute, which will become relevant when you make large VBA routines with many iterations or repetitions.

Variables

Variables are another vital element of programming in Excel VBA that we treat in this chapter. A variable is a named storage location that has one particular value. You could think of it as a drawer that has a name on it. For example, in my desk I have a drawer with “Greeting” on it. Inside the drawer is a note with one particular greeting, e.g. “Hello”, “Good morning”, “Good evening”, etc.

Variables are a very convenient way to transport data through a macro. The variable (the drawer) remains the same and can be reused endlessly while we can keep changing its contents.

In our example we can use a variable as an intermediate step when copying the value of cell B3 to B6:F14. We will first put the value of B3 in a variable before assigning it to the range. Therefore we first need to determine what type of variable it is. The type determines what kind of value it represents e.g.: an integer or decimal number, a word of TRUE/FALSE (Boolean). Some of the most commonly used data types are:

If you look for “Data Type Summary” in the VBA Help you will find a complete list of available data types (to access the help, press F1 while in the VBA editing window or click the question mark symbol in the menu). Always try to use data types that define your variables as narrowly as possible so you will have fewer problems to detect errors in your programs and your programs will run faster if you use data types that use less storage. Use the Variant data type therefore as little as possible.

Declaring a variable

Once we know the type of variable we want to create we can define the variable, called “declaring” in Visual Basic. There are three statements that can be used to declare a variable:

As with the data types discussed in the previous section, it is good practice to declare the scope of variables as narrowly as possible to avoid issues later on. Here we will declare the variable in the subprocedure with the Dim statement. To declare the variable you start with "Dim", give it a name (“Greeting” in this case) and add the data type. So, write between the “Sub” line and the only code line the following “dim Greeting as string” (without quotes) press enter and the result should look like:

Image of example code showing declaration of variable Greeting
Figure 6: Declare the variable Greeting.

Remember that reserved keywords show up in blue and capitalised to indicate you have written them correctly. In this case the words “Dim”, “As” and “String” should show they are keywords.
A variable always needs to be declared in a line preceding (by any amount of lines) the line where you first use it. So, now we defined the variable we can use it in the Sub. Let’s pass the value from cell B3 first to the variable “Greeting” and then to range B6:F14. In order to do so rewrite the subprocedure as follows:

Image of example code using the variable as an intermediate step
Figure 7: Assign a value to variable Greeting from cell B3 and then paste in B6:F14.

Again, to assign a value you have to read from right to left. To see if it works, change the value in cell B3 to “Good evening” (without quotes) and run the macro either by pressing F5 while in the subprocedure in the VBA editor or, when in the Excel worksheet view, go to the Macro window by pressing ALT+F8 (Windows) or Option+F8 (Mac). The cells in the range B6:F14 should now have changed to “Good evening”.

Instead of using cell “B3” as input for Greeting we can also write in the Sub what value we want to assign to it. If we want to assign “Hello” than you can replace Range(“B3”) with that value in the code:

Image of example code assigning Hello to the variable greeting
Figure 8: Assign the value Hello to the variable Greeting.

Note that I have written in this case “Hello” with quotation marks in the code as it is a String. The double quotation marks differ strings from variables. As “Greeting” is a variable it is not written with quotes in the code. Numbers are never written in the code with quotation marks as a number can never be a variable (and a number written between double quotation marks will be seen as a string). Run the macro again to see that it works. You will observe that cell B3 has not changed as we now have only changed B6:F14 to “Hello” without doing anything with cell B3.

Naming Excel Ranges

Though the naming of Excel ranges might not be a VBA feature directly, it is undoubtedly important enough for well-organised programming to dedicate the last paragraph of the chapter to it.

In the Excel worksheet the user can give cells other names than the initial letter & number format (“A1”, “C23”, etc.), in order to make them easy to reference in Excel formulas, but also in VBA. Personally, if a cell contains the value of a variable to be used in VBA, then I like to give the same name to both the variable and the cell in the worksheet. For our example that means I would call cell B3 “Greeting”. If a range is not one cell, but a set of adjacent cells, it can be named in the same manner.

To name cells in the Excel worksheet, first select the cell or range of cells you want to name and then go to the “Name Box” on the left in the “Formula Bar”, the tool bar right above the worksheet. When you click the name box you can fill in another name. In our example, if we first select cell B3, then click in the name box, we see we have the text “B3” selected and can overwrite it. Write “Greeting” (without quotes) in the name box and press enter. Now you can see selecting cell B3 that it is called “Greeting” while the cells around it still have the letter & number format.

Figure 9: Rename cell B3 to Greeting.

To name a cell directly in the name box is only recommendable for unnamed cells. If you have already named a cell and you want to delete the name, change it, or use it somewhere else then go to “Formulas -> Define Name” (or something similar depending on the Excel version you are using), because entering a name in the name box only adds a name, it does not delete the previously assigned name.

Continuing our example after this aside, we can now use in “WrittenMacro” the reference to Range(“ Greeting”) instead of Range(“B3”). This keeps the code more readable, especially if you have many variables where you obtain the value from the Excel sheet and you want to ensure each variable points to the right range in the worksheet. The macro using the named range to assign a value to the variable “Greeting” becomes:

Image of code using a named range
Figure 10: Use a named range as input for variable Greeting and return to different range.

One last advantage I will mention on using named ranges: when you move a named cell in the worksheet (e.g. cut & paste a cell or insert/delete a row or column before a referenced cell) the VBA reference goes with it. If you do not give a specific name to the cell you are referencing, and the cell moves, the macro needs to be updated manually to change the reference to the new location (not efficient and error prone).

Next: Chapter 3: Condition statements

Easy Excel VBA Tutorial: Contents