VBA Code

Excel VBA Code

VBA code can be defined as the code that inputs in the visual basic window to perform a set of instructions or actions in excel and provide results. With the help of VBA code, we can reduce the time to perform a repetitive task, there will not be required much human intervention except to run the program. Now we will see how to open a visual basic window, how to write code in it and how to run and how to save it.

How to Open a Visual Basic Window?

If you want to write the visual basic code, we should write it in a visual basic window. The question here is, where is the visual basic window in excel and how to open it. The answer is to select the Developer menu and under the developer menu, choose Visual Basic as shown in the below image.

Which will open the blank window as shown below.

Easy and looks cool right. But incase if excel does not have Developer tab. We need to add the developer tab with the below steps.

Step 1: Click on File Option.

Step 2: Drop-down appears, click on Options tab.

Step 3: Once you click on “Options”, a dialog box appears as shown below and click on the Customize Ribbon option.

Step 4: When we drag down in the customize ribbon options we will find an option for Developer (Custom) we need to check that box which will enable us to use VBA in excel.

Another easy way to open the Visual basic window is by pressing Alt+F11

Where to Write Code in Excel VBA?

By default, there will be two modules available to write your code, one is sheet1 module and workbook module.

If we add multiple sheets in our workbook it will allow different sheet modules here.

Sheet Module Code: Whatever the event code we write in the sheet module it will apply for that sheet alone.

You may be in confusion what is an event code. VBA comes with multiple events shown in the above screenshot, events like change, deactivate and many more. Whenever that event happened the macro will run.

ThisWorkbook Module

Whatever the code written in this module will apply for all the sheets in the workbook. Suppose if we write an event code for the workbook then whenever that event happened in any of the sheets of the workbook the macro will run. Suppose if we write an event for “New sheet” then whenever we created a new sheet the macro will run for that workbook.

Module: Normally we will write code in modules in VBA. We need to insert the module by clicking on the Insert menu and select the module option to insert a module.

Similarly, we can insert a class module and user form also. These are the modules where we can write our code. Depend on the type of requirement we should choose the module and write the code in it.

Examples to Write and Run Code in Excel VBA

Below are the different examples to write and run the code using VBA Code in Excel.

Example #1 – Macro Code using a Normal Module

Now we will see a sample macro code using a normal module. When we insert a new module, the empty window will look like below.

Follow the below steps to write code in excel VBA.

Step 1: Any program in VBA starts with sub keyword and program name with open and close brackets as below. When we enter after the brackets automatically “End Sub” will appear.

Code:

Sub sample()

End Sub

Step 2: In between, we should write our code.

Code:

Sub sample()

Cells(1, 1).Value = "Name"
Cells(1, 2).Value = "Age"
Cells(1, 3).Value = "Address"

End Sub

In the above macro, I wrote a sample code like when I run the macro first three cells should fill with Name, Age and Address. Cells(1,1).value represents the cell of row1 and column1 value.

Step 3: We can run the code by pressing F5 or click on the run button at the top which is marked in Black colored box.

Step 4: Once we click on run, a pop up will ask for a run as below. If multiple macros are available need to choose the correct macro and click on the run button.

Step 5: Go to excel and check whether the results are appearing or not as below.

Example #2 – Attaching a Macro to a Shape

If we want to run the macro from the worksheet itself with the help of a shape, we can do that. Follow the below steps to assign the macro to shapes.

Step 1: Go to Insert, and Select the shape as per your wish.

Step 2: After selecting the shape, draw this on your worksheet.

Step 3: Now, we can write a text like “click here” or “run Macro” in that shape. For adding the text just right click on a shape and select Edit Text

Step 4: Add the word as per your wish. I have added the word as “Run Macro”.

Step 5: Now, we need to assign the macro to that shape, for that select the shape and right click and choose  “Assign Macro” option.

Step 6: A pop-up box will come asking for macro selection. Select the macro name and click on the Ok button.

Step 7: Now, if we click on the shape the macro will run and give the result as below.

How to Save the Macro Workbook?

We all know how to save a workbook. Saving macro workbook is also same but one small change will be there. We have two different ways to save our macro files.

1. Macro enabled workbook
2. Macro enabled template

When you are saving the workbook, we need to choose the file type from the list of file type options as below.

By default, we can use the “Excel macro-enabled workbook”. In case if you want to use as standard file as a starting point for other files then use “Excel macro-enabled template” format.

Things to Remember

• Add the developer tab in case if it is not available in the menu ribbon.
• If want to create a simple basic macro, try to use recording a macro as it does not need any coding not even need to go to the visual basic screen. Click on record macro option from developer tab and give macro name perform required steps. It will record the steps you are performing, once it is over, stops recording and can run the macro now as to how we run the macro in the above steps.
• Choose the module type depending on the requirement, by default we can write in a normal module.
• Do not forget to save the file in the macro-enabled format then only the workbook will be a macro-enabled workbook.

This is a guide to VBA Code in Excel. Here we learn how to Copy, Insert & Run the VBA Code in Excel along with simple and practical examples. Below are some useful excel articles related to VBA –

1. VBA UBound Function
2. Excel CODE Function
3. Using the VBA END Function
4. COUNTA Function in Excel