Posted on Leave a comment

Visual Basic Editor

The Visual Basic Editor is the tool used to create, modify and maintain Visual Basic for Applications procedures in Excel. The easiest way to access VBE is to press Alt + F11. When you press this combination, a VBE window will open.

VBE has a lot of options or windows that can be accessed from the View menu at the VBA editor window. Each one is explained below.

The Project window

The Project window opens on the left of the Editor window. It provides you with an index of all the VB files that are linked to open Excel workbooks. It shows you files linked to each worksheet as well as additional files (also called modules), which contain user-defined code. Modules are not specifically linked to any worksheet.

If you want a macro to be linked to a specific worksheet then you should choose the file option, but if you want your macro to be general then it will be better if you create a new module. You can create a new module by clicking on the option “Insert” and choosing the “Module” option.

The Code window

The Code window shows the VBA code for each individual worksheet or module. Once you open a module or worksheet, you can enter your code directly in the Code window and the VBA editor will help you create valid code.

The Properties window

This window lists the various properties of the object selected in the Project window. These properties may vary depending on the type of object selected. An object can be a worksheet or a workbook or a module.

The Immediate window

You can open the Immediate window either by selecting it in the View menu of the VBE or by pressing Ctrl + G. The Immediate window helps with the debugging of the code, either by acting as the output for the debug statement or by allowing the user to evaluate expression of lines of code.

The Locals window

The Locals window can be viewed by selecting it from the View menu in the VBE window. This window will display all the local variables that are declared. It is separated into columns, which show the name, value and type of variable. It also updates these values automatically as the program continues to run.

The Watch window

The Watch window can be accessed by selecting it from the View menu in the VBE window. The Watch window is useful in debugging VBA code. It shows the value, type and context of watch expressions defined by the user.

Posted on Leave a comment

Excel Macro VBA. How to create a macro!

Excel Macro VBA. How to create a macro.

What is a Excel VBA Macro?

A macro is a tool in Microsoft Excel that allows you to perform a number of operations just by opening a workbook, clicking a button or changing a cell value. A macro is basically a recording of a series of tasks in Excel. It increases the efficiency of Excel by reducing manual work and saving time.

In Excel, you have the option to record or write a macro.

How to record a excel VBA macro

You can record a macro by following these steps:

  1. Open a new workbook.
  2. Go the “Developer” tab and click on the “Record Macro” button that is found in the Menu bar.
  3. In the Record Macro dialog box that opens, enter the name for this macro. You can also add a shortcut for this particular macro so that it will run when you press the shortcut.
  4. You can also enter the location where you want to store the macro.
  5. Then click OK and perform some operations and functions on the worksheet.
  6. After you are done, click on the “Stop” button, which will be in the same place where you clicked “Record Macro”.

To view this macro, right click on the sheet name and select View Code. In the pane, under VBA Project, expand the Module and you will see all the recorded macros for this worksheet with their respective names.

How to run a macro

There are two ways to run a recorded macro in Excel. The first method is by pressing the Run button and the other is by pressing Alt + F8.

When you click on the Run button or press Alt + F8, a dialog box will open. In this box you have to choose the relevant macro, select it and click Run. Then the recorded macro will play. Once the macro finishes running, you will see that any formatting that was deleted after the recording will be redone.