Excel VBA Introduction Courses UK Wide

Mastering Simple VBA Automation

Excel VBA (Visual Basic for Applications) is a programming language developed by Microsoft that allows you to automate tasks and create custom functions in Excel. VBA can be used to write macros, which are sequences of instructions that Excel can execute to perform repetitive tasks, manipulate data, and interact with other applications. 

Examples of Excel VBA 

Example 1: Displaying a Message Box 

Open the VBA Editor: 

Press Alt + F11 to open the VBA editor. 

Insert a new module by clicking Insert > Module. 

Write the Code: 

Sub ShowMessage() 

MsgBox 'Hello, World!' 

End Sub 

Run the Macro: 

Press F5 or go back to Excel, press Alt + F8, select ShowMessage, and click Run. 

Example 2: Automating Data Entry 

Sub EnterValue() 

Range('A1').Value = 'Automated Entry' 

End Sub

Example 3: Looping Through a Range 

Sub LoopThroughRange() 

Dim cell As Range 

For Each cell In Range('A1:A10') 

cell.Value = 'Processed' 

Next cell 

End Sub 

Building Logic With Decision Structures

Let’s dive into the decision-making structures in Excel VBA: IF and SELECT CASE. 

IF Statement 

Sub CheckValue() 

Dim score As Integer 

score = 75 

If score >= 90 Then 

MsgBox 'Grade: A' 

ElseIf score >= 80 Then 

MsgBox 'Grade: B' 

ElseIf score >= 70 Then 

MsgBox 'Grade: C' 

Else 

MsgBox 'Grade: F' 

End If 

End Sub 

SELECT CASE Statement 

Sub CheckDay() 

Dim dayOfWeek As String 

dayOfWeek = 'Wednesday' 

Select Case dayOfWeek 

Case 'Monday' 

MsgBox 'Start of the work week!' 

Case 'Wednesday' 

MsgBox 'Midweek already!' 

Case 'Friday' 

MsgBox 'Almost the weekend!' 

Case Else 

MsgBox 'Just another day.' 

End Select 

End Sub 

Understanding Looping Techniques

Loop statement in VBA is used to repeat a block of code while a condition is true or until a condition becomes true. 

Loop Example 

Sub BasicDoLoop() 

counter = 0 

Do While counter < 10 

counter = counter + 1 

Debug.Print counter 

Loop 

End Sub 

Loop with If Statement 

Sub DoLoopWithIf() 

counter = 0 

Do 

counter = counter + 1 

If counter Mod 2 == 0 Then 

Debug.Print counter & ' is even' 

Else 

Debug.Print counter & ' is odd' 

End If 

Loop While counter < 10 

End Sub 

Loop with Exit Do 

Sub DoLoopWithExit() 

counter = 0 

Do 

counter = counter + 1 

If counter == 5 Then 

Exit Do 

End If 

Debug.Print counter 

Loop While counter < 10 

End Sub 

Essential Debugging Tools for VBA

Breakpoints 

Purpose: Halt the execution of your code at a specific line. 

Step Into (F8) 

Purpose: Execute your code line by line. 

Step Over (Shift+F8) 

Purpose: Execute the current line and move to the next one. 

Immediate Window 

Purpose: Test code snippets and evaluate expressions on the fly. 

Watch Window 

Purpose: Monitor the values of variables.