2. VBA: Variables and Data Types

A variable is a named place in memory. You declare it with the word Dim and give a type:

• Long holds whole numbers up to about ±2.1 billion. Use it for row numbers rather than Integer (Integer tops out at 32,767, and beyond that you get an Overflow error);
• Double holds fractional numbers; Currency holds money with 4 decimal places and no rounding errors;
• String is text, Boolean is True/False, and Date is a date and time;
• Variant means "any type". A variable with no declared type gets it;
• objects: Range, Worksheet, Workbook. You assign them with Set.

The line Option Explicit at the top of a module forbids undeclared variables. With it, a typo in a name (totl instead of total) causes a compile error instead of a silently wrong result. Turn it on for good: Tools › Options › Require Variable Declaration.

Initial values: numbers are 0, a string is empty "", and a Boolean is False. Operators: / is regular division, \ is integer division, Mod is the remainder, and & joins text.

Option Explicit

Sub Tenure()
    Dim r As Long, m As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    For r = 2 To 6
        m = ws.Cells(r, 2).Value              ' tenure in months from column B
        ws.Cells(r, 3).Value = Int(m / 12)    ' full years
        ws.Cells(r, 4).Value = m Mod 12       ' months left over
        ws.Cells(r, 5).Value = ws.Cells(r, 1).Value & ": " & Int(m / 12) & " yr " & (m Mod 12) & " mo"
    Next r
End Sub

Cells(row, column) is a cell by numbers: Cells(2, 3) is C2. In the tasks, use formulas to get the same results this macro would write.

The simulator doesn't run macros or scripts. Run the code from the lesson in desktop Excel (VBA) or in Excel for the web (Office Scripts). Here you learn to read code and check yourself with formulas and a quiz.