Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

User Input in VBA

Learn how to accept input from users, display messages, validate values, and use input in Excel automation with VBA.

1. What Is User Input?

User input allows a VBA program to receive information while it is running. Instead of hardcoding every value, a macro can ask the user to enter a name, number, quantity, or other value.

User Enters value InputBox Accepts input VBA Processes it Output MsgBox

The common VBA functions used for basic interaction are InputBox and MsgBox.

2. InputBox Function

The InputBox function displays a dialog box that asks the user to enter a value. The entered value is returned as a string.

Syntax

InputBox(prompt, title, default)
  • prompt: Message displayed to the user.
  • title: Optional title of the dialog box.
  • default: Optional default input value.

Example 1: Accept a name

Sub GetStudentName()

    Dim studentName As String

    studentName = InputBox( _
        "Enter your name:", _
        "Student Details")

    MsgBox "Welcome, " & studentName & "!"

End Sub

When the macro runs, the user enters a name. VBA stores the returned text in studentName and displays a welcome message.

Note: Clicking Cancel in the basic InputBox returns an empty string, just like submitting an empty input. Use suitable validation when these cases need to be distinguished.

3. MsgBox Function

The MsgBox function displays a message to the user. It can show information, warnings, questions, and buttons.

Syntax

MsgBox(prompt, buttons, title)
Button or Icon Purpose
vbOKOnly Displays an OK button.
vbYesNo Displays Yes and No buttons.
vbYesNoCancel Displays Yes, No, and Cancel buttons.
vbInformation Displays an information icon.
vbExclamation Displays a warning icon.
vbQuestion Displays a question icon.

Example 2: Display a message

Sub ShowMessage()

    MsgBox "Your data has been saved successfully.", _
           vbInformation, _
           "Success"

End Sub

Example 3: Ask for confirmation

Sub ConfirmAction()

    Dim answer As VbMsgBoxResult

    answer = MsgBox( _
        "Do you want to continue?", _
        vbYesNo + vbQuestion, _
        "Confirmation")

    If answer = vbYes Then
        MsgBox "You selected Yes."
    Else
        MsgBox "You selected No."
    End If

End Sub
Important: The return value of MsgBox can be stored in a variable and checked to determine which button the user selected.

4. Accept Numeric Input

Basic InputBox input is text. Before performing calculations, validate the input using IsNumeric and convert it to the appropriate numeric type.

Example 4: Add two numbers

Option Explicit

Sub AddTwoNumbers()

    Dim firstValue As String
    Dim secondValue As String
    Dim firstNumber As Double
    Dim secondNumber As Double

    firstValue = InputBox("Enter the first number:")
    If Len(Trim$(firstValue)) = 0 Then
        MsgBox "First input is empty or cancelled.", vbExclamation
        Exit Sub
    End If

    If Not IsNumeric(firstValue) Then
        MsgBox "Please enter a valid first number.", vbExclamation
        Exit Sub
    End If

    secondValue = InputBox("Enter the second number:")
    If Len(Trim$(secondValue)) = 0 Then
        MsgBox "Second input is empty or cancelled.", vbExclamation
        Exit Sub
    End If

    If Not IsNumeric(secondValue) Then
        MsgBox "Please enter a valid second number.", vbExclamation
        Exit Sub
    End If

    firstNumber = CDbl(firstValue)
    secondNumber = CDbl(secondValue)

    MsgBox "Sum = " & (firstNumber + secondNumber), vbInformation

End Sub

Explanation: The macro checks that both inputs are not empty and are numeric before converting them with CDbl and calculating the sum.

5. Validate User Input

Validation checks whether the input meets the requirements before the program processes it.

Receive input Input valid? Check conditions No Show error message Yes Process the value

Example 5: Validate age

Option Explicit

Sub ValidateAge()

    Dim ageText As String
    Dim age As Long

    ageText = InputBox("Enter your age:")

    If Len(Trim$(ageText)) = 0 Then
        MsgBox "Age was not entered.", vbExclamation
        Exit Sub
    End If

    If Not IsNumeric(ageText) Then
        MsgBox "Enter a numeric age.", vbExclamation
        Exit Sub
    End If

    If CDbl(ageText) <> Fix(CDbl(ageText)) Then
        MsgBox "Age must be a whole number.", vbExclamation
        Exit Sub
    End If

    If CDbl(ageText) < 0 Or CDbl(ageText) > 120 Then
        MsgBox "Enter an age between 0 and 120.", vbExclamation
        Exit Sub
    End If

    age = CLng(ageText)

    MsgBox "Entered age: " & age, vbInformation

End Sub

This example checks for empty input, non-numeric values, decimal values, and ages outside the allowed range.

6. Accept Input and Write to Excel Cells

VBA can take input from a user and place the result directly into a worksheet cell.

Example 6: Enter an employee name

Option Explicit

Sub EnterEmployeeName()

    Dim employeeName As String

    employeeName = InputBox("Enter employee name:")

    If Len(Trim$(employeeName)) = 0 Then
        MsgBox "Employee name was not entered.", vbExclamation
        Exit Sub
    End If

    ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = employeeName

    MsgBox "Employee name saved in cell A2.", vbInformation

End Sub
Cell Result Example
A2 Rahul Patil

Ensure that a worksheet named Sheet1 exists in the workbook containing the macro.

7. InputBox vs Application.InputBox

VBA provides a basic InputBox function. Excel also provides Application.InputBox, which can restrict the expected input type.

Input Method Typical Use
InputBox Accepting simple text input.
Application.InputBox Accepting values such as numbers or selecting a range.

Example 7: Accept a number

Option Explicit

Sub GetNumberUsingApplicationInputBox()

    Dim result As Variant

    result = Application.InputBox( _
        Prompt:="Enter a number:", _
        Title:="Numeric Input", _
        Type:=1)

    If VarType(result) = vbBoolean Then
        If result = False Then
            MsgBox "Input was cancelled.", vbInformation
            Exit Sub
        End If
    End If

    MsgBox "You entered: " & result, vbInformation

End Sub
Note: With Application.InputBox, pressing Cancel returns Boolean False. The Variant variable allows the result to be checked before it is used.

8. Common Mistakes to Avoid

  • Assuming input is numeric: validate using IsNumeric before conversion.
  • Ignoring empty input: check Len(Trim$(value)) where blank input is not allowed.
  • Confusing Cancel and blank input: the basic InputBox returns an empty string for both cases.
  • Using the wrong data type: convert values to an appropriate type before calculations.
  • Using a missing worksheet: verify worksheet names before writing to cells.

9. Practice Exercises

Write VBA macros to solve the following tasks.

  1. Accept a student's name and display a welcome message.
  2. Accept two numbers and display their sum.
  3. Accept a product price and quantity and calculate the total.
  4. Accept a student's marks and validate that they are between 0 and 100.
  5. Ask the user to confirm an operation using Yes and No buttons.
  6. Accept an employee name and save it in cell A2.
  7. Accept a number and display whether it is positive, negative, or zero.
  8. Use Application.InputBox to accept a numeric value.

Important Points to Remember

  • InputBox accepts basic user input as text.
  • MsgBox displays information and can return a button selection.
  • Use IsNumeric to validate numeric input.
  • Use CDbl or another suitable conversion function before calculations.
  • Handle empty input and cancellation appropriately.
  • Use Application.InputBox when typed input or range selection is needed.

Summary

User input makes Excel VBA macros interactive. The InputBox function collects values, while MsgBox displays messages and collects simple button choices. Validate input before using it in calculations, handle empty values and cancellation, and use worksheet references to transfer data between users and Excel.

These concepts prepare you to create more useful macros with conditional statements, loops, and dynamic ranges.