VBA training courses London - 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.