Data Analyst and Technical Consultant

VBA Starter Kit

VBA is a programming language used in Excel to record routine tasks and procedures.  By creating a short script you can automate adding new data to a sheet, performing calculations and create custom functions.

Creating a VBA module

Add the Developer tab to your ribbon:

  1. Click the File tab > Options > Customise Ribbon
  2. Select the Developer checkbox
  3. Click the Visual Basic button (or Alt+F11) to open the VBA workspace
  4. Create a new Module by inserting it into your workbook

vba-tips-tricks-introduction

Writing a subroutine

The code is written in code blocks between Sub (subroutine) and End Sub.  VBA is not case-sensitive like other languages and lines do not terminate with semi-colons.

Sub NameofSub()
... code goes here
End Sub

 

Calling a subroutine

To call a subroutine:

  1. Use the Developer tab to switch to Design mode.
  2. You can then allocate the script to run on the click of a button

 
vba-tips-tricks-instructions

Selecting cells

VBA uses the keyword ‘Range’ and cell references to select a cell or block of cells.  Dot notation is used to separate the object from the method

Sub SelectCell()
Cells(3, 5).Select
End Sub

Selects the cell in the third row and fifth column, E3, using a cell reference

Sub SelectBlock()
Range("A1:A11").Select
End Sub

Selects all cells in column A from 1 to 11

Sub SelectColumns()
 Columns("A:Z").Select
End Sub

Selects columns A to Z

Sub SelectRows()
Rows("1:5").Select
End Sub

Selects rows 1 to 5

 

Assigning a variable

Declare your variable and data type with Dim (like Var in JavaScript)

Sub NameofSub()
Dim Num As Integer
Num = 1
MsgBox " We are number " & Num
End Sub

 

If Statement

To create an IF statement that has one path:

Sub PerformanceTier()
Dim Tier As Range
Set Tier = Worksheets("Sheet1").Range("A1")
If Tier() >0 Then
MsgBox "You're part of the programme!"
End If
End Sub

To create an IF statement that has more than one path:

Sub PerfTier()
Dim Tier As Range
Set Tier = Worksheets("Sheet1").Range("A1")
If Tier >= 1000 Then
MsgBox "You are a Platinum partner"
ElseIf Tier < 1000 And Tier >= 300 Then
MsgBox "You are a Gold partner"
ElseIf Tier < 300 And Tier >= 75 Then
MsgBox "You are a Silver partner"
ElseIf Tier < 75 And Tier >= 25 Then
MsgBox "You are a Bronze partner"
ElseIf Tier < 25 And Tier >= 1 Then
MsgBox "You are a Starter partner"
Else
MsgBox "You are a partner"
End If
End Sub