Microsoft Excel Tutorial

Excel VBA Tutorial

By IncludeHelp Last updated : November 17, 2024

Table of Contents

What is (VBA) Visual Basic for Application?

VBA (Visual Basic for Applications) is a programming language developed by Microsoft. Since, it’s developed by Microsoft so it facilitates its users for automation and customisation in Microsoft Office applications such as Excel, Word, Access, and PowerPoint. It is a subset of the Visual Basic programming language designed to work with the Office suite's features. VBA follows Visual Basic programming language features. It enables users to build macros which includes set of Programming statements that execute operations and automate task

VBA enables users to create custom functions, change user interfaces, and control Office program workflows to meet unique requirements. For example, with Excel, VBA may automate data entry, formatting, and calculations, as well as sophisticated procedures like data integration from different sources. VBA is especially useful for people who lack extensive programming skills since it gives an easy way to increase productivity by exploiting the built-in tools of Office products.

How to Open VBA in Excel?

VBA is integrated with MS Excel; to open first of all open MS Excel and then press Alt + F11 or Fn + Alt + F11. It will look like this:

Editor: The Visual Basic Editor (VBE) can be accessed from within Office programs (for example, by pressing Alt + F11 in Excel).
Macros: Small scripts or programs written in VBA that perform tasks when triggered.
Object-Oriented: VBA relies on manipulating objects, such as workbooks, worksheets, cells, documents, and slides.

How to Insert a Module in VBA?

Click on Insert Menu
Click on Module

Module added, now you can start to Write VBA Code like:

Click on Play Green Button to Run the Program:

Output

Key Characteristics of VBA

Automation and Macros: Macros can communicate with program objects like cells in Excel or text formatting in Word. Users can automate repetitive tasks, such as formatting data, generating reports, or processing information.
Integration with Microsoft Office Applications: VBA is specifically designed to integrate with Microsoft Office applications like Excel, Word, Access, and PowerPoint.
Custom Solutions: VBA allows users to construct macros, user-defined functions, and interactive forms that are suited to their individual requirements.
Event-Driven Programming: VBA enables event-driven programming, which allows users to write code that runs in response to specified events like opening a workbook, clicking a button, or changing a cell value.
Extensibility: Beyond Office, VBA can interact with other external applications or databases, making it a versatile tool for broader automation. Hence, using APIs and libraries, VBA may communicate with databases, external files, and other programs.

Advantages of VBA

Built into Microsoft Office, requiring no additional installation.
Automation of Repetitive Tasks.
User friendly even if users are non-programmers due to its simplicity.
Provides the ability to create customized solutions.
Ideal for small to medium-sized businesses.
Reduces manual effort, enabling faster completion of projects.
Automates data handling, including importing, cleaning, analyzing, and reporting.
Very Useful for building complex models, such as financial models in Excel.
Allows for Macro Recording - allows users to record tasks and convert them into code.
Works effectively with large datasets in Excel or Access.

Disadvantages of VBA

Restricted to windows systems and Microsoft apps.
Slower than modern programming languages
Not feasible for heavy computations.

List of VBA Programs

1. Write a VBA Program to Print a message

Code:

Sub msg()
    MsgBox "This is my first VBA Program, Thanks https://www.includehelp.com/ to taught easily "
End Sub

Code Window:

Insert the code in the VBA editor under a module.

Output:

A message box will pop up displaying: "This is my first VBA Program, Thanks https://www.includehelp.com/ to taught easily".

2. Write a VBA Program to Add Three Integer Numbers

Code:

Sub addition()
    Dim num1, num2, num3, result As Integer
    num1 = CInt(InputBox("input first number:"))
    num2 = CInt(InputBox("input second number:"))
    num3 = CInt(InputBox("input third number:"))
    result = num1 + num2 + num3
    MsgBox "Addition of inputted numbers is: " & result
End Sub

Code Window:

Insert the code in the VBA editor under a module.

Output:

A message box will display the sum of the inputted numbers.

3. Create a User Form in VBA, Input Three Numbers and Find Greater One

Code:

Private Sub CommandButton1_Click()
    Dim n1, n2, n3 As Integer
    n1 = CInt(TextBox1.Text)
    n2 = CInt(TextBox2.Text)
    n3 = CInt(TextBox3.Text)
    If TextBox1.Text > TextBox2.Text And TextBox1.Text > TextBox3.Text Then
        TextBox4.Text = TextBox1.Text
    ElseIf TextBox2.Text > TextBox3.Text Then
        TextBox4.Text = TextBox2.Text
    Else
        TextBox4.Text = TextBox3.Text
    End If
End Sub

Code Window:

Insert the code in the VBA editor, and create a UserForm with TextBoxes and a CommandButton.

Output:

The largest of the three inputted numbers will be displayed in TextBox4.

4. Create a User Form Using VBA to Check if a Number is Positive, Negative, or Zero

Code:

Private Sub CommandButton1_Click()
    Dim n1 As Integer
    n1 = CInt(TextBox1.Text)
    If n1 > 0 Then
        TextBox2.Text = "Positive"
    ElseIf n1 < 0 Then
        TextBox2.Text = "Negative"
    Else
        TextBox2.Text = "Zero"
    End If
End Sub

Code Window:

Insert the code in the VBA editor, create a UserForm with TextBoxes and a CommandButton.

Output:

The appropriate message ("Positive", "Negative", or "Zero") will be displayed in TextBox2 based on the user input.

5. A VBA Program to Determine Discount Based on Purchase Amount

Code:

Sub discount()
    Dim spentamnt As Double
    Dim discper As Double
    Dim amnt As String
    amnt = InputBox("Please input spent amount:")
    spentamnt = CDbl(amnt)
    Select Case spentamnt
        Case Is > 1000
            discper = 10
        Case 500 To 1000
            discper = 5
        Case Else
            discper = 0
    End Select
    MsgBox "You are eligible for a " & discper & "% discount.", vbInformation, "Discount Percentage"
End Sub

Code Window:

Insert the code in the VBA editor under a module.

Output:

A message box will display the discount percentage based on the entered amount.

Excel - Rotating Text in a Cell

Excel Formulas Cheat Sheet

Comments and Discussions!

Load comments ↻