Course contents · Macros and VBA

Variables, conditions and MsgBox

Open the practice in the trainer →

Variables

Dim total As Double declares a variable. Main types: Long (whole numbers), Double (decimals), String (text), Boolean, Range (a range, assigned with Set). Option Explicit at the top of a module forces you to declare every variable — so typos in names show up at once.

Worksheet functions in code

WorksheetFunction.Sum(Range("B2:B8")) — any Excel function: Average, Max, CountIf, VLookup…

Conditions

If score >= 90 Then
    grade = "excellent"
ElseIf score >= 60 Then
    grade = "pass"
Else
    grade = "fail"
End If

With many options Select Case reads better:

Select Case score
    Case Is >= 90: grade = "excellent"
    Case 75 To 89: grade = "good"
    Case Else: grade = "other"
End Select

Messages

MsgBox "Average: " & avg shows a message box (in the trainer — a line in the Output box). The & operator joins text and numbers. Debug.Print writes to the editor's Immediate window — handy for checking values.

Tasks in the VBA editor

  1. Macro Average: calculate the average score (WorksheetFunction.Average) into a variable, write it to G2 and show it with MsgBox.
  2. Using If: C2 gets "pass" if the score in B2 is at least the pass mark in G1, otherwise "fail".
  3. Using Select Case: D2 gets "excellent" from 90, "good" from 75, "satisfactory" from 60, otherwise "poor".
  4. G3 gets the best score (WorksheetFunction.Max), G4 a string like "Best: 95" built with &.

Solve the tasks in the trainer →

Check yourself

  1. Why write Option Explicit?
  2. How do you call the COUNTIF worksheet function from VBA?