Financial Analysis and Modeling Using Excel and VBA

Appendix B. VBA Quick Reference

Referring to Things in Excel	Â
A workbook	Workbooks(" MyBook.xls")
A worksheet	Worksheets("Sheet 1") or Sheets("Sheet 1")
A cell or range in Al convention	Range("Al")orRangc("Al :C5")
Several cells or ranges	Range("A1,C5,D11")or
Â	Range("Al :C5,F6,D2 :E5")
A range in R1C1 convention	Range(Cells(l,l), Cells(5,10))
A cell in R1C1 convention	Cells(1,5)
A range with a name	Range("RangeName")
An entire column or row in Al convention	Columns("D :D")orRows("6 :6")
An entire column or row in R1C1 convention	Columns(l) or Rows(6)
A number of rows with fixed (that is, known) row numbers	Rows("6 :100")
A number of rows with variable row numbers	Rows(startRow & ";" & endRow)
The 3rd row of a range (C5 :G5 here)	Range("C3 :G15").Rows(3)
All cells in a worksheet	Worksheets("Sheet 1").Cells
A cell relative to a specified cell (CI 6 here)	Range("F15").Offser(l,-3)
A range relative to a specified range (D8 :F13 here)	Range("B5 :D 10").Offset(3,2)
The currently selected object (cell, range, etc.)	Selection
The workbook containing the procedure being executed	ThisWorkbook
The currently active workbook	ActiveWorkbook
The currently active cell (may be part of selected range)	ActiveCell
Using Some Common Properties	Â
To retrieve the value (contents) of a cell or range	cellVal = Cells(l,5).Value
To assign values to a cell or range	Range("Al :C3" ). Value = 123
To assign values to a named cell or range	Range("RangeName"). Value = 123
To retrieve the text in a cell	cellText = Range("AI").Text
To get the column or row ...