EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home VBA VBA Resources VBA Coding VBA Code

VBA Code

Madhuri Thakur
Article byMadhuri Thakur

Updated January 19, 2023

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.

VBA Code

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.

Visual Basic Window 1

Which will open the blank window as shown below.

Visual Basic Window 2

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.

VBA Code Step 1

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

VBA Code Step 2

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

VBA Code Step 3

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.

VBA Code Step 4

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.

vba code 1

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.

Sheet Module

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.

ThisWorkbook Module

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.

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.

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.

VBA Code Example 1-1

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

VBA Code Example 1-2

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

VBA Code Example 1-3

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.

VBA Code Example 1-4

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.

VBA Code Example 1-5

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

VBA Code Example 1-6

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.

Select the shape

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

VBA Code Example 1-8

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

Edit Text

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

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.

Assign Macro

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

VBA Code Example 1-12

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

VBA Code Example 1-13

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.

Excel macro-enabled workbook

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 the developer tab and give macro name to perform the 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.

Recommended Articles

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
  2. VBA Get Cell Value
  3. VBA END
  4. VBA RGB
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Code Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW