VBA is an object-oriented programming language. VBA stands for Visual Basic For Applications. It is an integration of Visual Basic with Microsoft Office applications (MS Excel, MS PowerPoint, MS Access, MS Word and MS Outlook).
Running VBA (Visual Basic for Applications) in Microsoft Office applications allows you to automate repetitive tasks. VBA Excel methods and objects are centered around Excel objects to manipulate, such as cells or ranges, as well as rows and columns of data.
An object has properties and methods. A property allows you to assign a value or characteristic to the object, while a method enables you to perform an action with the object. For example, if you imagine an object representing a cell in VBA Excel, one of its properties could be used to change the font or color of the cell (a characteristic), and a method could be used to delete the cell (an action). To call a property or method of a VBA object, you separate the object from the property or method with a dot in the code: Object.Property = value.
As you may know, when you record an automatic macro with Excel, it records your actions for later playback with a simple button click. These actions are actually recorded in VBA Excel code. What we propose is to create simple macros to view and understand the code generated. Through this process, we will explore fundamental objects and learn essential properties and methods to get started in the best way possible.
Activating the Developer tab :
Let’s start by enabling the Developer tab:
1. Go to the “File” menu.
2. Select “Options.”
3. Choose “Customize Ribbon.”
4. You’ll find the “Developer” tab, which is likely unchecked. Check the box next to it.
5. Click “OK.”
Now, you should see the Developer tab in Excel, which allows you to access VBA and create macros.
How do I create an automatic macro?
We are going to create our first macro. The goal is to see the generated code. With a cell selected, typically A1 by default:
1. Go to the Developer tab on the ribbon.
2. Click on the “Record Macro” button.
3. In the dialog box, name the macro, for example, “test_mef.”
4. Keep “This Workbook” selected from the list to save the macro in this workbook.
5. Click “OK” to start recording.
From this point, we need to perform only the strictly necessary actions, as Excel is recording them.
1. Click on the “B” button in the Home tab to make the text bold.
2. Choose a red fill color using the Home tab.
3. Then, set the font color to white.
These actions will be recorded by Excel in the macro.
We can now stop the recording.
Click on the “Stop Recording” button.
For now, the macro only exists as VBA code. We will create a button for it.
1. Click on the “File” menu and choose “Options” from the list.
2. In the dialog box, select the “Quick Access Toolbar” category.
3. In the center, choose the “Macros” category.
4. Then, select the “test_mef” macro from the list below and click “Add.”
5. Click on “Modify” at the bottom to choose an image for the button.
6. Click “OK” twice to confirm both dialog boxes.
Now, you should have a button on the Quick Access Toolbar that runs the “test_mef” macro.
The button now appears in the Quick Access Toolbar above the ribbon.
1. Enter some text in a cell and press CTRL + Enter to keep it active.
2. Click on the new button in the Quick Access Toolbar.
This should execute the “test_mef” macro, which applies formatting to the selected cell.
As you can see, the formatting properties defined by the macro are applied instantly to the selected cell.
How do I create a macro using VBA code?
We will write the macro and give it a new name. At this stage, its behavior will be similar to the previous macro; we will only change certain values.
In the code editor, copy the entire macro (from Sub to End Sub).
Paste it below, on the module page.
Change its name after Sub to: mef_bleue.
We now have a new macro with a new name, but for now, it performs exactly the same actions as the previous one. We want this new code to make the cell blue with dark gray text initially. So, we just need to focus on the values of the RGB() function components.
Secondly, we would like this macro to be able to apply a border to the selection. Light blue can be obtained with the components: 40, 180, 255. Similarly, dark gray can be obtained with the components: 76, 76, 76. (We have this information thanks to the Paint tool).
Modify the color components of the RGB() functions in the code of this new macro like this:
The code editor allows us to test it:
First, select a cell containing text on the worksheet, and then go back to the code editor and click anywhere between the boundaries of the procedure of the second macro to place the insertion point there. This is how the software will know which code to execute when asked.
Finally, click the button represented by a green arrow in the code editor’s toolbar.
In appearance, nothing happens. In fact, it happens very quickly.
Display the worksheet,
You notice that the preselected cell has been affected. We have just executed a macro. Now we want to define a thick border for the selection using code. To do this, we could go through the object explorer and the properties window in the code editor. Both of these are accessible through the View menu. However, it’s even simpler to create an automatic macro and retrieve the code.
Here’s what you need to do:
1. Select a cell in the Excel workbook’s sheet.
2. In the Developer tab, click the “Record Macro” button and name it “Bordure.” Then, click OK to start recording.
3. Using the Borders button in the Home tab, set an outer thick border, and then click the “Stop Recording” button in the Developer tab.
4. Return to the code editor.
Under the `mef_bleue()` macro, you’ll see a new procedure. This is the `bordure()` macro that we just created. Its code may seem complicated, but it’s actually a systematic repetition of the same properties set in the same way. Excel treats borders independently. First, it sets the left border: `Selection.Borders(xlEdgeLeft)`, then the top border: `Selection.Borders(xlEdgeTop)`, and so on. However, it’s sufficient not to specify any parameters for the `Borders` property of the Selection object for VBA Excel to understand that we’re referring to the outer border.
To achieve this, follow these steps:
1. Select and copy the first `With` block.
2. Paste it into the `mef_bleue()` macro right below the line `Selection.Interior.Color = RGB(40, 180, 255),`.
3. Delete the parameters within the parentheses of the `Borders` property as well as the parentheses themselves.
This modification will apply a thick outer border to the selected cell within the `mef_bleue()` macro.
By making these changes, we specify the entire border of the selection rather than just one of its sides.
Here are the modifications you need to make:
1. Delete the line `.TintAndShade = 0`, as it’s unnecessary, as we’ve seen before.
2. Replace the line `.ColorIndex = 0` with `.Color = RGB(76, 76, 76)`.
These modifications will ensure that the selected cell has a thick outer border and a text color of dark gray within the `mef_bleue()` macro.
`.ColorIndex = 0` sets the border color to black. We prefer the `.Color` property, as before, which allows us to define a specific border color using the `RGB()` function. In this case, we are setting the border color to be the same as the text color.
Select a cell on the worksheet, return to the code editor, place the insertion point within the bounds of the `mef_bleue()` procedure, and click the play arrow to execute the code.
The cell now appears with the previous formatting attributes plus the border.
Back on the worksheet, the button appears next to the previous one. If you select cells and click on it, you will execute the macro in the same way as the previous one. Now, let’s clean up the previous work.
- Return to the code editor.
- Select all lines of code within the `bordure()` procedure, from `Sub` to `End Sub`.
- Delete them by pressing the Delete key on your keyboard.
With its code deleted, the “bordure” macro will no longer be available in the list of macros, especially for creating buttons.
Range and Cells objects: how to use them?
What’s better than an automatic macro to find out which object allows us to select and manipulate a specific cell? Because so far, the Selection object implied that we were acting on a preselected range. And if we want to act on specific cells, referenced by their coordinates, what do we do? This is what we will answer by asking a macro to record us while we select a specific cell.
- Activate the Developer tab from the Excel workbook sheet,
- Click on the Record a Macro button,
- Name it, for example, “cellules,” and click OK to start,
Select, for example, cell G10 with the mouse, - Click the Stop Recording button on the Developer tab,
- Display the code editor by clicking the Visual Basic button on the Developer tab.
If you don’t see your new macro in the code editor, it means it was written in a new module.
Double-click on the Module2 section from the Project window on the left.
The excessively simple code of the macro appears. You can see the use of the Range object with the cell references enclosed in quotes as they are identified in Excel. Range originally refers to a range of cells, but if only a single cell is indicated, it points to that individual cell.
Associated with the Range object is the Select method. Remember, a method performs an action and doesn’t return a value to a property. Here, the action is to select the specified cell or range.
The Range object has properties and methods that are simple to explore for customizing and configuring cells.
In the editor, under the first line of code, type Range(‘G10’).
When you type the dot (.) after the Range object, you’re calling a property or method of the object. That’s why a small dropdown list appears as you type. It provides you with all the methods of the object (preceded by a green icon) as well as all its properties (preceded by a gray icon). If you type the first letters of the property, for example, the list will position you on properties starting with those letters.
Type or select the Font property and then type another dot (.),
This way, you’re accessing properties derived from the Font property.
Type or select the Size property for the font size,
Assign it the value 12 (=12).
As we mentioned, the Range object is initially used to designate a range of cells. To do this, much like in Excel functions, you simply specify the references separated by a colon (:) for a range of contiguous cells.
Finally, there is the Cells object, which allows you to designate a cell based on its row and column numbers. This object expects two parameters: the row number and the column number. So, for cell C10, you would pass the values 10 for the row and 3 for the column. Since this object deals with cells, it has the same properties and methods as the Selection and Range objects.
We hope you enjoyed this article. Feel free to subscribe to our newsletter to stay updated on the latest tech, data, and AI news.
