Trending September 2023 # How To Write And Run Code In Excel Vba? # Suggested October 2023 # Top 11 Popular

You are reading the article How To Write And Run Code In Excel Vba? updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Write And Run Code In Excel Vba?

Excel VBA Code Programming

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, how to run, and how to save it.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 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.

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.

You can download this VBA Code Excel Template here – VBA Code Excel Template

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.

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 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 4: Add the word as per your wish. I have added the word as “Run Macro”.

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.

Macro enabled workbook

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.

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.