A macro, in Office applications, is a synonym for VBA code. In Excel, we can record almost any action we perform (such as mouse clicks and typing), which in turn is registered as VBA code. This can come in handy when we need to discover properties and methods related to an object. Let us now have a look at some ways you can record a macro in Excel. There are two options:

Option 1 – Recording a macro from the status bar

From the status bar, click on the Record Macro button. If the button is not visible, right-click on the status bar and from the pop-up menu, choose the Macro Recording option, as shown in the following screenshot:

Once you have recorded your macro, if you have added a shortcut to it, then you will be able to run the code by using this keyboard combination.

However, if you are coding directly into the container object (a user form, standard module, class module, and so on), then you may have to use different methods in case there is no shortcut to your procedures.

Here are some methods you can use to execute your code (all of them assume you have the Visual Basic Editor open):

Before you get too excited with coding in VBA, be aware that Excel has specific file formats which are appropriate for specific tasks. The default file format does not allow you to save embedded macros in it. This format ends with the extension .xlsx. Any macros placed in such a file will be wiped out.

When it is time to save your Excel workbook, you must select the Excel Macro-Enabled Workbook (*.xlsm) type (which ends with the extension xlsm). The open format (xlsm) is the preferred format. However, you can also use the binary format (xlsb) or, to ensure the code can be run in older versions of Excel, you can use the xls format:

You can easily distinguish the files by their desktop icons. The macro-enabled workbook has an exclamation mark, while the macro-free version does not.

You add code to the code window of any object. These objects can be the workbook itself (also called ThisWorkbook; see the screenshot in the Immediate window section), the worksheet object, user forms, modules, and class modules. Your choice will depend on the usage of the code. If the code needs to be public, then you should add it to a module.

In order to add a module, follow these steps:

The Object Browser is a very important tool that you can use to check for the properties and methods of an object as well as any other information related to that object.

In order to access the Object Browser, follow these steps:

Once the Object Browser is open, you will be presented with the window shown in the following screenshot. From the first drop-down list you can choose which library you want to browse to. You can browse all libraries in one go or you can browse a specific library. In the following example, the active library is Excel. Below it, there is a list with all the classes (objects) available in the Excel object library. Currently, the active class is Range and to its right you have all the properties and methods that are members of this class:

The methods are represented by a small "running brick" whereas the properties are represented by a hand holding a card.

Methods are named using verbs while properties are named using nouns. Methods represent procedures, that is, actions to be performed or functions. Therefore, the Activate method is a procedure that activates the object Range, which has been specified in the code. Similarly, properties refer to the qualities of the object. For example, the Name property can be used to retrieve the name of a worksheet as well as to rename it. Properties can be read-only, write-only, or read-write.

If you find that confusing, you can think of it in terms of your own body. For example, Height is a property that tells how tall you are, whereas Grow is a method (think of this method as a growth hormone) that instructs your body to grow.

Finally, you can search for properties and methods while in this window. Simply enter what you are looking for in the box right under the Excel box, as shown in the following screenshot. The Object Browser will show you all the matching results with the corresponding class and its membership:

One important aspect of VBA programming lies in declaring your variables. Variables, as the name suggests, are things that vary or change over a period of time. Therefore, a variable could be specific such as a text string, a number (such as integer and long), or an object. But it can also be a variant, which means it takes no specific shape to begin with, but it will take whatever shape it is set to later on in your code.

It is not a prerequisite that you declare your variables (unless the container has the key phrase Option Explicit placed on the first line of the code window). In this scenario we have what we call implicit declaration , that is, you don't declare any variables and let VBA automatically create a variant type whenever a variable is needed.

Forcing explicit variable declaration is a good practice as it improves code performance, it makes reading your code easier (for others and yourself), and it also avoids ambiguity in your code. If you want the compiler to force variable declarations, follow these steps to switch on explicit variable declaration:

Variables can be declared as:

The preceding declarations relate to accessibility of the variables you declare. However, there are other keywords you can use in the declaration:

The following code snippet illustrates the usage of such declarations and combinations. Explanations are embedded in the code:

'Variable which is only accessible within this module
Private myInteger           As Integer

'Variable accessible from anywhere within this VBA Project
Public myExcelRange         As Excel.Range

'Constant accessible from anywhere within this VBA Project
Public Const myString       As String = "This text will not change."

'Declaring variables within a producedure
Sub DeclaringVariables()
'   Static variable will retain its previously
'   assigned value across the same session
    Static MyStaticCounter  As Long

'   Early binding of an object
    Dim myAppExcel         As Excel.Application
End Sub

The Immediate window allows you to display information related to the debugging of your code. Debugging refers to the process of finding and mitigating "bugs". Bugs are coding mistakes that cause your program to deviate from its original intended use. The Immediate window will also execute commands that you type directly into it. It appears, by default, at the bottom of Visual Basic Editor (VBE) window:

To display the Immediate window (if it is not active), follow these steps:

The Immediate window has many uses, some of which we will look at now. To demonstrate the possibilities, ensure that the Immediate window is active and replicate the following code snippets in a standard module; then, execute it:

In this section, you have learned some basic stuff about VBA. These included macro recording, adding modules, and browsing objects.

You also learned how to use the immediate window to debug your code. This feature is very important because it allows you to carry out many critical debugging tests.

With these tools mastered, you are now able to move on to the next step of your VBA quest.