The Everyday VBA Toolkit
Most day-to-day VBA is built from a surprisingly small vocabulary: a handful of data types, a few loop forms, the Range and Cells objects, and a dozen or so methods. This quick reference collects the constructs you reach for repeatedly so you can stop re-deriving them. Keep in mind the mental model behind it all — VBA drives Excel through an object hierarchy (Application → Workbook → Worksheet → Range), and almost everything you do is either navigating that hierarchy or reading and writing properties on the objects you find. Master the small core and the rest is lookup.
Cricket analogy: The core VBA toolkit is like a batsman's bread-and-butter shots — the cover drive, the pull, the defensive block — a small set drilled until automatic, covering ninety percent of an innings.
Types, Loops, and Conditionals
The common data types are Long (whole numbers — prefer it over Integer, which is 16-bit and can overflow), Double (decimals), String, Boolean, Date, Currency, and Variant (anything, but slower). Loops come in a few flavours: For...Next for a known count, For Each...In for iterating a collection like a Range or Worksheets, Do While/Do Until for condition-driven loops, and the block If...ElseIf...Else...End If plus Select Case for branching. Use Exit For or Exit Do to break out early, and remember that iterating a Range with For Each visits one cell at a time, which is convenient but slower than array processing for large blocks.
Cricket analogy: Choosing Long over Integer to avoid overflow is like picking a bat with a bigger sweet spot so a mistimed shot still clears the rope instead of falling short.
Option Explicit
Sub CheatSheet()
' --- Declaring & typing ---
Dim count As Long, price As Double, name As String, ok As Boolean
' --- Reference sheets & ranges ---
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' last used row in col A
' --- For...Next over rows ---
Dim r As Long
For r = 2 To lastRow
If ws.Cells(r, "B").Value > 0 Then
ws.Cells(r, "C").Value = ws.Cells(r, "B").Value * 1.2
End If
Next r
' --- For Each over a range ---
Dim cell As Range
For Each cell In ws.Range("C2:C" & lastRow)
If cell.Value > 1000 Then cell.Interior.Color = vbYellow
Next cell
' --- Do Until with a condition ---
Dim i As Long: i = 2
Do Until IsEmpty(ws.Cells(i, "A").Value)
i = i + 1
Loop
' --- Select Case branching ---
Dim grade As String
Select Case ws.Range("D2").Value
Case Is >= 90: grade = "A"
Case Is >= 75: grade = "B"
Case Else: grade = "C"
End Select
' --- Handy snippets ---
Debug.Print "Rows: " & (lastRow - 1) ' Immediate window
ws.Range("A1").CurrentRegion.Copy ' copy contiguous block
Application.WorksheetFunction.Sum(ws.Range("C2:C" & lastRow)) ' worksheet fn
End SubCells(RowCount, "A").End(xlUp).Row is the idiomatic way to find the last used row and is far more reliable than UsedRange, which can report stale bounds after rows are deleted. Pair it with CurrentRegion to grab a contiguous data block.
Navigating Ranges and Common Objects
Range navigation is where most beginners get stuck. Range("A1") and Cells(1, 1) both address one cell, but Cells takes numeric row/column, which is essential inside loops. Offset(rows, cols) moves relative to a cell, Resize(rows, cols) changes a range's size, and End(xlUp/xlDown/xlToRight) jumps to the edge of a data block just like Ctrl+arrow. Frequently used objects and members include Value, Formula, and NumberFormat on a Range; Interior.Color and Font.Bold for formatting; and Worksheets, Rows, and Columns collections. MsgBox and InputBox handle quick user interaction, while Debug.Print writes to the Immediate window for troubleshooting.
Cricket analogy: Offset and End are like a fielder judging the ball off the bat and sprinting to the boundary edge — relative movement and jumping straight to the rope in one motion.
Integer in VBA is only 16-bit and overflows past 32,767 — enough to break a loop over a large sheet, since worksheets have over a million rows. Always use Long for row counters and any value that could exceed that range.
- Core types: Long (prefer over Integer), Double, String, Boolean, Date, Currency, Variant.
- Loops: For...Next (known count), For Each...In (collections), Do While/Until (conditions); Exit to break early.
- Branching: If...ElseIf...Else...End If and Select Case.
- Address cells with Range("A1") or Cells(row, col); Cells is essential in loops.
- Navigate with Offset, Resize, End(xlUp/xlDown), and CurrentRegion for contiguous blocks.
- Find last row via Cells(Rows.Count, "A").End(xlUp).Row rather than UsedRange.
- Use MsgBox/InputBox for interaction and Debug.Print to the Immediate window for debugging.
Practice what you learned
1. Why prefer Long over Integer for row counters in VBA?
2. Which loop is best for iterating every cell in a Range?
3. What does Cells(Rows.Count, "A").End(xlUp).Row return?
4. Which pair correctly describes Offset and Resize?
5. Where does Debug.Print send its output?
Was this page helpful?
You May Also Like
VBA Best Practices
Practical conventions for writing maintainable, fast, and robust VBA — from Option Explicit and naming to error handling, performance, and avoiding Select/Activate.
VBA Interview Questions
The concepts VBA interviews probe most — variables and scope, error handling, workbook/worksheet references, performance, and events — with model answers.
Building a Report Automation Macro
A step-by-step walkthrough of building a real VBA macro that consolidates raw data, computes summaries, formats output, and exports a monthly report to PDF.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics