How to use Excel VBA – Excelchat

We can automate actions in Excel by writing and editing custom scripts called Macros.

Excel allows us to complete more complicated tasks and create better code with its Visual Basic for Applications (VBA) function.

Figure 1. of VBA in Excel

The VBA editor, VBE, or VB editor in Excel is essentially an interface for generating scripts.

The programming language that is used to generate these scripts is VBA.

Figure 2. of VBA Code in Excel

VB is a programming language with full features, but we only require to learn a few basic operations to write Excel VBA scripts.  

How to use Excel VBA Editor

          1. Before we begin coding, we must first open Excel’s VBA editor. We do this by heading to the “Developer” tab on top of our worksheet and then click on the “Visual Basic” icon:

Figure 3. of VB Button in Excel

We can also open Excel VBA editor by using Alt + F11 – the keyboard shortcut.

          2. You will discover that the VBA editor in Excel is loaded with different buttons, options, and menus.

Figure 4. of VBA Editor in Excel

We will focus on only the most basic components of the editor.

In the example illustrated above, under the “Project view”  button in the left sided pane, there is a “Modules” folder (highlighted in red).

The “Modules” folder contains VBA modules, which serve as code containers. When we record macros, they are stored in a module.

To add an empty, new module, click on “VBAProject (Book1)”, and then go to Insert > Module.

Figure 5. VBA Module in Excel

If there wasn’t any Modules folder under “VBAProject”, the folder will now be created with our new module inside. This is where we will store our VBA code when we eventually write it.

To run an Excel macro, click on the “Run Macro” icon on our worksheet menu bar:

Figure 6. of Run Macro in Excel

How to use Excel VBA to Get the Value of a Cell

We will now use the following statement structure to get the Excel VBA value of a cell;

myVariable = Cell.ValueOrValue

To get the VBA value of a cell, follow these simple steps;

1. Identify and select the Range object representing those cells whose VBA value we want to get.
2. Determine the cell’s value by using the myVariable = Cell.ValueOrValue statement.
3. Assign the cell.value vba value determined by the statement to a (myVariable =) variable.

Figure 7. of VBA Cell Value in Excel