VBA Macros Tutorial for Beginners

VBA Macros Tutorial for Beginners: Automate Excel Tasks

Welcome to this in-depth tutorial on VBA (Visual Basic for Applications) Macros in Microsoft Excel! If you’ve ever found yourself doing the same things every day in Excel, VBA macros are the solution. They can automate repetitive tasks, save you time, minimize errors, and ultimately increase your productivity.

This VBA Macros tutorial beginners is for candidates with no programming background. Ready to advance your Excel skills? Before you begin, review our detailed VBA Macros Course Syllabus to see what you’ll learn in our complete course!

What are VBA Macros?

Fundamentally, a VBA macro is a set of instructions in the VBA programming language that Excel can automatically perform. Think of it like taping a series of actions you take in Excel and then being able to play back those actions with one click.

Let’s try an analogy: Suppose you regularly prepare a cup of coffee in the morning. You:

• Pull out the coffee beans.
• Grind the beans.
• Boil the water.
• Place a filter in the coffee maker.
• Add the grounds to the filter.
• Pour hot water over the grounds.

A VBA macro is similar to making a button that automatically does all these actions for you.

Why Should I Learn VBA Macros?

Learning VBA macros has many advantages:

• Automation: Automate routine tasks such as formatting reports, data entry, and calculations.
• Increased Efficiency: Save a lot of time and effort by automating common actions.
• Fewer Mistakes: Prevent the occurrence of manual mistakes in the repetition of tasks.
• Personalized Functionality: Develop personalized functions and commands that suit your needs.
• Excel Mastery: Gain a better grasp of Excel and its functionality.

Starting Point: Enabling the Developer Tab

Prior to working with VBA macros, you must have the Developer tab enabled in your Excel toolbar. This tab offers a key to the VBA editor and other macro tools.

Here’s how to turn on the Developer tab:

• Open File in the Excel ribbon.
• Choose Options from the menu.
• In the Excel Options box, click on Customize Ribbon.
• In the right-hand pane, below the list of Main Tabs, check the box beside Developer.
• Click OK.

You should now have the Developer tab appear in your Excel ribbon.

Introduction to the VBA Editor (VBE)

Visual Basic Editor (VBE) is the window in which you enter, modify, and work with your VBA code. To display the VBE, access the Developer tab and click on Visual Basic.

Alternatively, you can press Alt + F11.

The VBE window typically includes the following main elements:

• Project Explorer: This window, found on the left side, shows all the currently open workbooks and their respective VBA modules, class modules, and user forms.
• Properties Window: Typically below the Project Explorer, this window shows the properties of the selected object (e.g., a worksheet, a module).
• Code Window: This is where you edit and enter your VBA code. Double-clicking on a module or an object in the Project Explorer will open its code window. 
• Immediate Window: You can use this window to run VBA code directly, test code snippets, and debug your macros.
  • You can open it using the Ctrl + G key in the VBE.
• Locals Window: In debugging, this window shows the values of your code’s variables.
  • You can find it in the View menu, then Locals Window.
• Watch Window: Like the Locals Window, but with the added feature of allowing you to watch the values of specific variables or expressions while debugging.
  • Access it through View > Watch Window.

Recommended: VBA Macros Course Online.

Your First Macro: Recording a Simple Action

The simplest way to begin with VBA is to record a macro. This enables Excel to convert your actions automatically into VBA code.

Let’s record a quick macro that formats a cell:

• Choose any cell in your Excel spreadsheet (e.g., A1).
• Go to the Developer tab and click on Record Macro.
• In the Record Macro dialog box:
  • Put in a Macro name (e.g., FormatCell). Do not use spaces and special characters.
  • You can add a Shortcut key (optional). Select a combination that is not currently employed by Excel (e.g., Ctrl + Shift + F).
  • In the Store macro in dropdown, select This Workbook if you would like the macro only in the current workbook, or Personal Macro Workbook if you would like it in all your Excel sessions. For this example, select This Workbook.
  • Add a Description (optional) to describe what the macro accomplishes.
• Click OK to begin recording.
• Then, do the operations you wish to automate. For instance:
  • Choose cell A1.
  • Make the font bold.
  • Make the background color yellow.
• Return to the Developer tab and click on Stop Recording.

Now, what you did was captured as VBA code. To see the code, go to VBE (Alt + F11), locate your workbook in the Project Explorer, and double-click on Module1 (or a similarly named module) under the Modules folder. You should find code like this:

Sub FormatCell()

‘ FormatCell Macro

‘ Shortcut Key: Ctrl+Shift+F

    Range(“A1”).Select

    With Selection.Font

.Bold = True

        .Italic = False

        .Size = 11

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

.OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ColorIndex = xlAutomatic

        .TintAndShade = 0

        .ThemeFont = xlThemeFontNone

    End With

With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent6

        .TintAndShade = 0.799981688894314

        .PatternTintAndShade = 0

End With

End Sub

This code is the sequence you captured. You can execute this macro by pressing the shortcut key you designated (if you did so), by accessing the Developer tab -> Macros, choosing “FormatCell”, and clicking Run, or by placing a button or shape on your sheet and linking the macro to it.

Understanding VBA Fundamentals: Objects, Properties, and Methods

VBA is an object-oriented programming (OOP) language. That is, VBA code works with objects. In Excel VBA, typical objects are:

• Application: Refers to the entire Excel program.
• Workbook: Refers to an Excel workbook file.
• Worksheet: Refers to a sheet in a workbook.
• Range: Refers to a range of cells or a cell.
• Cell: Refers to an individual cell.
• Chart: Refers to a chart in a worksheet.
• Shape: Signifies a drawing element, as an arrow or a rectangle.

Objects possess properties, which are characteristics that detail the object (e.g., the Name property of a worksheet or the Value property of a cell). You can alter an object’s properties to alter its appearance or its behavior.

Objects also possess methods, which are things you can do to the object (e.g., the Save method of a workbook or the Select method of a range).

The syntax for referring to a property or method of an object is:

ObjectName.Property

ObjectName.Method

Examples:

• Application.Version returns the version of Excel.
• ThisWorkbook.Name returns the title of the active workbook.
• Worksheets(“Sheet1”).Activate makes the worksheet “Sheet1” active.
• Range(“B5”).Value = “Hello” sets the content of cell B5 to “Hello”.
• Range(“A1:C10”).ClearContents clears the range A1 to C10.

Working with Variables

Variables are labeled memory areas in your computer’s memory where you can store data. In VBA, it’s advisable to declare your variables before doing anything with them. This saves you from making mistakes and makes your code more readable.

You declare variables with the Dim statement, followed by the name of the variable and its type:

Dim variableName As DataType

Some VBA data types are:

• Integer: Whole numbers (like -10, 0, 5).
• Long: Whole numbers that are larger.
• Single: Single-precision floating-point numbers (decimal numbers).
• Double: Double-precision floating-point numbers (more accurate decimals).
• String: Text (e.g., “Hello”, “Excel”).
• Boolean: True or False values.
• Date: Date and time.
• Object: Used to represent an object (such as a worksheet or a range).
• Variant: May contain any data type (use sparingly as it can create unexpected behavior at times).

Some examples of variable declarations are:

Dim counter As Integer

Dim employeeName As String

Dim totalSales As Double

Dim currentDate As Date

Dim mySheet As Worksheet

You can initialize a variable with a value using the assignment operator (=):

counter = 1

employeeName = “John Doe”

totalSales = 1250.75

Set mySheet = ThisWorkbook.Worksheets(“Sheet2”) ‘ Use the Set keyword for object variables

Related: Data Analytics Course Online.

Controlling the Flow of Your Code

VBA includes several control flow statements to enable you to determine the sequence in which your code runs under specific conditions.

If.Then.Else Statements

The If.Then.Else statement enables you to run alternative blocks of code based on whether a condition is true or false.

If condition Then

    ‘ Code to run if the condition is true

Else

    ‘ Code to run if the condition is false

End If

You can also use ElseIf to test several conditions:

If condition1 Then

    ‘ Code to run if condition1 is true

ElseIf condition2 Then

    ‘ Code to run if condition2 is True

Else

    ‘ Code to run if both conditions are False

End If

Example:

Sub CheckCellValue()

    Dim cellValue As Integer

    cellValue = Range(“C5”).Value

    If cellValue > 10 Then

        MsgBox “The value in cell C5 is greater than 10.”

ElseIf cellValue = 10 Then

        MsgBox “The value in cell C5 is equal to 10.”

    Else

        MsgBox “The value in cell C5 is less than 10.”

    End If

End Sub

Select Case Statements

The Select Case statement is more organized for responding to several conditions depending on the value of one expression.

Select Case expression

    Case value1

        ‘ Code to execute if expression = value1

    Case value2

        ‘ Code to execute if expression = value2

Case value3 To value4

        ‘ Code to execute if expression is between value3 and value4 (inclusive)

    Case Is > value5

        ‘ Code to execute if expression is greater than value5

    Case Else

‘ Code to run if none of the above cases are true

End Select

Example:

Sub CheckGrade()

    Dim grade As String

    grade = Range(“D1”).Value

    Select Case grade

        Case “A”

            MsgBox “Excellent!”

Case “B”

            MsgBox “Good job.”

        Case “C”

            MsgBox “Satisfactory.”

        Case “D”, “E”, “F”

MsgBox “Needs improvement.”

        Case Else

            MsgBox “Invalid grade.”

    End Select

End Sub

For.Next Loops

The For.Next loop allows you to execute a block of code a specific number of times.

For counter = start To end [Step increment]

‘ Code to run in each loop

Next [counter]

Example:

Sub FillCells()

    For i = 1 To 5

        Cells(i, 1).Value = “Row ” & i ‘ Fill cells A1 to A5

    Next i

End Sub

For Each.Next Loops

The For Each.Next loop cycles through all the members of a collection (e.g., all the worksheets in a workbook, all the cells in a range).

For Each element In collection

    ‘ Code to run for each element

Next element

Example:

Sub HideAllWorksheetsExceptActive()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        If ws.Name <> ActiveSheet.Name Then

ws.Visible = xlSheetHidden

    End If

Next ws

End Sub

Do.While and Do.Until Loops

Do.While loops repeat a set of code as long as a given condition is true.

Do While condition

    ‘ Code to run while the condition is true

Loop

Do.Until loops run a set of statements until a given condition is met.

Do Until condition

    ‘ Statements to run until the condition is met

Loop

Example:

Sub IncrementCounter()

    Dim count As Integer

    count = 0

    Do While count < 10

        count = count + 1

Debug.Print “Count is: ” & count ‘ Print to the Immediate Window

    Loop

End Sub

Related: VB Dot Net Course Online.

Handling Ranges and Cells

The Range object is used to handle cells and ranges of cells in Excel VBA. You can address ranges in several ways:

• By address: Range(“A1”), Range(“B2:D5”), Range(“A1,C3,E7:F9”)
• Using Cells property: Cells(row, column) (i.e., Cells(1, 1) = Range(“A1”))
• Using named ranges: Range(“MyNamedRange”)
• Using Offset property: Range(“A1”).Offset(1, 2) represents the cell two columns to the right and one row down from A1 (i.e., C2).
• Using Resize property: Range(“A1”).Resize(3, 2) represents the range A1:B3.

There are numerous things you may do by experimenting with the features and methods of Range objects:

• Get or set values: Range(“A1”).Value, Range(“B3”).Value = 100
• Format cells: Range(“C1:C10”).Font.Bold = True, Range(“D5”).Interior.Color = RGB(255, 0, 0)
• Clear contents or formatting: Range(“E1”).ClearContents, Range(“F1:F5”).ClearFormats
• Select or activate cells: Range(“G2”).Select, Worksheets(“Sheet3”).Activate

Example:

Sub FormatSpecificRange()

    With Worksheets(“Data”).Range(“A2:C10”)

        .Font.Name = “Arial”

        .Font.Size = 12

        .Borders.LineStyle = xlContinuous

        .Interior.Color = RGB(200, 200, 200)

    End With

End Sub

Interacting with Worksheets and Workbooks

You can use VBA to handle entire workbooks and worksheets.

Working with Worksheets:

• Referring to worksheets: ThisWorkbook.Worksheets(“Sheet1”), ActiveWorkbook.Sheets(2).
• Adding new worksheets: Worksheets.Add
• Deleting worksheets: Worksheets(“ToDelete”).Delete
• Renaming worksheets: Worksheets(“OldName”).Name = “NewName”
• Hiding/Unhiding worksheets: Worksheets(“Secret”).Visible = xlSheetHidden, Worksheets(“Secret”).Visible = xlSheetVisible
• Activating worksheets: Worksheets(“Summary”).Activate

Working with Workbooks:

• Referring to current workbook: ActiveWorkbook
• Referring to the workbook where the code is hosted: ThisWorkbook
• Opening workbooks: Workbooks.Open Filename:=”C:\\MyFiles\\Data.xlsx”
• Saving workbooks: ThisWorkbook.Save, ActiveWorkbook.SaveAs Filename:=”C:\\Backup\\Data_” & Format(Date, “YYYYMMDD”) & “.xlsx”
• Closing workbooks: Workbooks(“AnotherBook.xlsx”).Close SaveChanges:=True

Example:

Sub CreateAndFormatNewSheet()

    Dim newSheet As Worksheet

SetnewSheet=ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

    newSheet.Name = “New Data Sheet”

    newSheet.Cells(1, 1).Value = “Important Information”

    newSheet.Cells(1, 1).Font.Bold = True

End Sub

Displaying Messages and Getting Input

VBA enables you to interact with the user by using message boxes and input boxes.

MsgBox Function

The MsgBox function shows a message to the user and can also return a value stating which button was clicked by the user.

MsgBox(prompt[, buttons][, title][, helpfile, context])

• prompt: The message to be shown.
• buttons: Optional. Controls the buttons and icon to be shown.
• title: Optional. The text to be shown in the title bar of the message box.

Example:

Sub DisplayMessage()

    Dim result As VbMsgBoxResult

result = MsgBox(“Are you sure you want to proceed?”, vbYesNo + vbQuestion, “Confirmation”)

    If result = vbYes Then

        MsgBox “Proceeding with the action.”, vbInformation

        ‘ Your code to proceed here

    Else

        MsgBox “Action cancelled.”, vbExclamation

End If

End Sub

InputBox Function

The InputBox function makes a dialog box appear that invites the user to input something and returns what the user enters.

InputBox(prompt[, title][, default][, xpos, ypos][, helpfile, context])

• prompt: The text to show in the input box.
• title: Optional. The text to show in the title bar.
• default: Optional. The default text to show in the input area.

Sub GetUserName()

    Dim userName As String

userName = InputBox(“Please type in your name:”, “User Information”, “Guest”)

    If userName <> “” Then

        MsgBox “Hello, ” & userName & “,”, vbInformation

    Else

        MsgBox “No name entered.”, vbExclamation

    End If

End Sub

Recommended: Dot Net Training Online.

Error Handling in VBA Macros

Errors can occur in your VBA code. It’s important to implement error handling to prevent your macros from crashing and to provide a more user-friendly experience. The On Error GoTo statement is commonly used for basic error handling.

Sub ExampleWithErrorHandling()

    On Error GoTo ErrorHandler

    ‘ Your code that might cause an error

    Dim result As Integer

result = 10 / 0 ‘ This will create a division by zero error

MsgBox “Result: ” & result ‘ This line will never be executed if there is an error

ExitSub:

    Exit Sub

ErrorHandler:

    MsgBox “An error occurred: ” & Err.Description, vbCritical

    Resume ExitSub ‘ Exit the sub after handling the error

End Sub

In this instance, if a division by zero error is produced, the code will branch to the ErrorHandler label, print an error message, and then end the subroutine.

Explore: All Software Training Courses.

Conclusion

We have discovered the basics of VBA, from recording macros to navigating the VBE, objects, variables, control flow statements, ranges, worksheets, workbooks, user input, and simple error handling in this VBA Macros tutorial. This is only the tip of the iceberg for your VBA adventure. There are so many possibilities when automating and making Excel do your bidding. Practice, try new code, and play around with the various objects, properties, and methods within VBA.

Ready to learn more and become a VBA power user? Sign up for our in-depth VBA Macros Course in Chennai today! Take the next step towards learning Excel like a master and unlocking its full potential!