What is Excel VBA?


Excel stands for Visual Basic for VBA Applications. A built-in language for Microsoft office applications. The purpose of this application is to automate a task that requires manual effort while working in Excel. Using Excel VBA Code will automate this process and make it easier and quicker.

We will create a Macro and then run that macro in the Visual Basic Editor is what Visual Basic does for the application. So let’s see what the Visual Basic Editor looks like? How do you create a macro in Excel? And run those marks in the Visual Basic Editor.

Excel VBA - VBA Stands for Visual Basic Application

Create Excel Macro – Excel VBA

When you have to do tasks that require repetition, you need to create a Macro. This will ultimately help you save your time and effort. Once you develop Macro, you can save Macro and use it multiple times.

What is a macro? A macro is an action that is performed to run action as many times as you want.

This tutorial will explain how you can create Macro in Excel. Even non-programmers will be able to create Macro by following the steps below. But before we look at the steps, remember these few essential tips:

1. Always keep Macro names short compared to identification, it becomes very easy.

2. Macro names must begin with a letter and cannot contain space, symbols, and punctuation marks.

3. The maximum length of the name can be 80 characters.

4. Use relative cell addresses

5. Always start your macro at a home position which means cell A1

6. Always navigate through the directional keys from the keyboard as navigation becomes easier, avoid using the mouse for navigation to select and click menu options.

7. Always keep the Macro as small as possible. The larger the macros, the longer it will take to run the macro.

8. Small Macro can help you detect failure easily taken by Big macro.

The steps to create Macro in Excel are divided into 4 parts. The first part tells you to turn on the Developer Tab. The second part tells you to place the Command Button. The third part is to assign to Macro. The fourth view is to open the Visual Basic Editor.

1st part: Enable Developer Tab – Excel VBA in Hindi

There are two ways to turn on the Developer Tab. Both ways to start Developer Tab are described below.

Step 1: Right-click anywhere on the ribbon and click Customize Ribbon

Excel VBA - Customize the Ribbon
Excel VBA - Customize Ribbon

Step 2: Insert a check on the developer’s box to the right of the Excel Options dialog box in the main tab options. And click OK.

Excel VBA - Excel options
Excel VBA - Excel options

Result: After doing either way you will see the Developer tab to the right of the View tab.

2nd Part: Place a Command Button in the Worksheet. – Excel VBA in Hindi

Now, we insert the command button on the worksheet. The steps to place a command button in a worksheet are as follows:

Step 1: Click on the Developer tab.

Step 2: Click Insert in the Controls group.

Step 3: From the drop-down menu of Insert, click the command button in the active X control

Step 4: Drag the mouse into the worksheet. The larger the command button drag, the larger it takes. In short, you can customize the size of your command button.

Result: A command button 1 appears at the location where you drew your mouse. We have changed the caption of the command button on ClickMe.

Excel VBA - ActiveX Controls
Excel VBA - Command button

3rd Part: Assign a Macro to the Command Button 1 – Excel VBA in Hindi

After inserting the Command Button we have to Assign the Code to run the Command Button. For this, we need to create a macro and assign the command button. The steps to create and assign a macro are as follows:

Step 1: Click the command button and activate the design mode in the control groups of the Developer tab.

Step 2: Right-click on the command button and click on View Code.

Excel VBA - Click ME
Excel VBA - ClickMe Button

Step 3: Visual Basic Editor opens.

Step 4: Place the cursor between the Private Sub CommandButton1_Click() and End Sub.

Step 5: Add the Code line or Lines you want to run. In our example, we enter the line Range(“A1:A5”). Value = “Become an Excel Superstar”

Excel VBA - VBA interface
Excel VBA - VBA Interface

Step 6: Close the Visual Basic Editor and now click on Command Button 1 which is a spreadsheet.

Excel VBA - Design Mode
Excel VBA - Design Mode

Result: Woo!!! You have created a macro in Excel. Wasn’t it easy?

4th Part: Open Visual Basic Editor – Excel VBA in Hindi

The Visual Basic Editor is a part of Excel VBA where you will insert the code to run the command. The steps to open Visual Basic Editor in Excel are as follows:

Step 1: Go to the Developers Tab > Click on the Visual Basic in the Code Group.

Excel VBA - VBE
Excel VBA - Visual Basic

Step 2: Press the shortcut ALT + F11 to open the Visual Basic Editor.

Excel VBA Shortcuts
Excel VBA Shortcuts

Result: Visual Basic Editor pops up.

Excel VBA Editor
Excel VBA Editor

Result: Woo!!! You have created a macro in Excel. Wasn’t it easy?

Now let us see the details of the Visual Basic Editor

Visual Basic Editor has three windows. The first is the project window. The second is the code window and the third is the properties window.

1st Part: The projector window is located to the left of the Visual Basic Editor dialog box. This is a place where all open workbooks are located. The VBA project window is divided into 3 different types of modules. The module is where all the code will be stored. CTRL + R is the shortcut to navigate in the projector window with the help of the keyboard.

2nd Part: The Code Window view is the white area to the right of the Basic Editor. This is where 90% of VBA work is done. We can test, develop, and modify macros.

3rd Part: The property window is located below the projector window. This window shows you the properties of the selected components in the project window. F4 is a shortcut to open Properties windows and edit and move in Properties windows.

Excel VBA - VBA interface part 2
Excel VBA Interface

Record a Macro

Macro Recorder allows you to record tasks performed in Excel VBA Code. Even if you do not have the code and you record a process that creates a series of actions in the macro recorder.

Macro recording is easy. The steps to record a macro are as follows:

Step 1: Go to the developer’s tab> click on record macros in the code group

Excel VBA - Record macro
Excel VBA - Record Macro

Step 2: Name the macro and give a shortcut of your choice. (The name should be such that you can easily remember in case of shortcut)

Excel VBA - Record macro Dialog Box
Excel VBA Record Macro popup

In the example, we wrote the name as the brand name, and the shortcut is used Ctrl + E.

Step 3: Click OK and continue your action

Here we have written our brand name i.e. Excel Superstar for the category A8: A12.

Step 4: Click Stop Recording after you are done with your tasks.

Excel VBA - Stop Recording
Excel VBA - Stop Recording

Run a Macro

After recording the macro while looking in the record macro. Now you can run the recorded macro. We will run the same macro that we recorded in the record macro. We will follow the steps to run the macro:

Step 1: Go to the Developer tab> click Macros.

Excel VBA - macro
Excel VBA - Macros

Step 2: The Macro dialog box opens where you can see the name of the recorded macro. Click on the macro name, and then click Run.

Excel VBA - macro run
Excel VBA - Run Macros

Result: You will see that the macro you entered earlier is now seen in another worksheet. We previously recorded our brand name Excel Superstar. So, when I move to the next sheet in this workbook and complete the above two steps I get the result that appears below:

Excel VBA - Excel Superstar
Excel VBA - Macro Result

This tutorial explained to you how you can create a macro, run the macro created in the same workbook. This is basic knowledge, to begin with learning Excel VBA. Read more blogs to gain deeper knowledge and start your coding.