EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Coding VBA Code
Secondary Sidebar
VBA Coding
  • VBA Coding
    • VBA Set Range
    • VBA ByVal
    • VBA Long
    • VBA Data Types
    • VBA Enum
    • VBA Clear Contents
    • VBA Call Sub
    • VBA Exit Sub
    • VBA END
    • VBA Class Module
    • VBA excel programming
    • VBA Code
    • VBA Option Explicit
    • VBA Global Variables
    • VBA Variable Declaration
    • VBA SUB
    • VBA ByRef
    • VBA Variant
    • VBA Public Variable
    • VBA Variable Types
    • VBA Goal Seek
    • VBA Variable Range
    • VBA Class Module
    • VBA XLUP
    • VBA XML
    • VBA ByVal

VBA Code

By Madhuri ThakurMadhuri Thakur

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

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.

Watch our Demo Courses and Videos

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

Visual Basic Window 1

Which will open the blank window as shown below.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

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
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Watch our Demo Courses and Videos

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

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Watch our Demo Courses and Videos

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

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

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

EDUCBA

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

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

Let’s Get Started

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