VBA Cheat Sheet / Excel Macros Quick Guide

TERM	DESCRIPTION
ARRAY  index starts from 0 by default To amend Redim OR Option Base 1   However when assigning Range to array, array starts from 1. https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm https://bettersolutions.com/vba/arrays/option-base-1.htm
Static Array	Dim arr(0 To 5) As Long (other data types can be used)
Dynamic Array	Dim arr() As Long (other data types can be used)   By default, the array will start at 0. To change, place at top of module: "Option Base 1".   Dim lStudentCount()As Long     lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row       ' Create array of correct size     Dim arr() As Long     ReDim arr(1 To lStudentCount)
Preserve the values of a dynamic  array when it is ReDim	If we use ReDim on an existing array, then the array and its contents will be deleted.   To retain the original values, use Preserve. But the starting index value must be the same.   Dim arr() as String ReDim arr(1 To 2)     arr(1) = "Apple"     arr(2) = "Apple"   'It must start at 1. ReDim Preserve arr(1 To 6)
Fill dynamic array with values	Dim arr () As Variant arr = Array("John", "Hazel", "Fred")   Dim arr ()As Variant arr = Range("A1:D2") --> this will work if there is no reference to a worksheet or workbook   arr = Sheets("sheet1").Range("A1:G311").Value2 --> If there is a reference to a worksheet or workbook, you will need to use "Value" or "Value2"   Differences between Text, Value and Value2 a. Range.Text Property b. Range.Value Property c. Range.Value2 Property     a) Doesn't work with array! Returns the value exactly as it is on your screen. If your column width is too narrow and the value in the cell is shown as ###, that’s exactly what Text will return.   b) Returns the value in whatever format it was in Excel and converts it to the VBA equivalent data type. This is usually a fine way to extract the data, unless your data is formatted as currency or a date. Transmitting as a date may be useful, but the currency can cause some issues. The VBA currency data type only carries 4 decimal places, so your cell value may get truncated when converted to VBA.   c) Returns the value independent of format. Eg, a formatted date will be returned as the serial number date value Excel uses.   Adapted from https://wellsr.com/vba/2017/excel/excel-vba-assign-range-to-array/
2-dimensional array	Dim arr(1 To 5, 1 To 2) As Long   The  above is an array with 5 rows & 2 columns.
Calling out an element of an array	Specify the row and column of an array even if it is a n x 1.   Debug.Print arr(1)  --> Will give an error   Debug.Print arr(2,1)  --> correct     Example:   Dim arr() As Variant arr = ActiveSheet.Range("A1:A5").Value2   Dim c As Variant For Each c In arr     Debug.Print c Next c
WORKBOOK
ActiveWorkbook	Refers to the Workbook which is active (the one you see)
ThisWorkbook	Refers to the Workbook that houses the code
Workbooks(n)	n is a index number being in the order that the open Workbooks were opened
Workbooks("aBook.xls")	Refers to ONLY the workbook of that name
WORKSHEET (ONLY WORKSHEET)
Worksheets(n)	n is the index number of a Worksheet from left to right in a workbook
Worksheets("aSheet")	Refers to a Worksheet tab name
SHEET (INCLUDE WORKSHEET AND CHARTSHEET)
ActiveSheet	Refers to the Sheet which is active (the one you see)
Sheets(n)	n is the index number of a Sheet from left to right in a workbook
Sheets("aSheet")	Refers to a Sheet tab name
Directly use the code name without quotation marks	Refers to a Sheet code name Note: The code name remains the same if you change the Sheet tab name or the order of your sheets. So this is the safest way to reference a Sheet.   The code name can be found in the VBA Editor:     Chart6, Sheet1 are examples of  code names while the tab names are in brackets.     Changing the code name:   Select the Sheet and its Properties will show. Click on the box for (Name) and Name to change its code and tab name, respectively.
CHART
ActiveChart	Refers to the Chart sheet / embedded chart in a worksheet which is active (the one you see). If no chart sheet/embedded chart  is active, code will return an error.
Charts(n)	n is the index number of a Chart sheet from left to right in a workbook
Charts("Chart1")	Refers to a Chart sheet tab name
CELL
Cells ()	Cells (row, column) Column is optional. If only Row index is used & index exceeds the number of columns in the specified range, the reference will wrap to successive rows within the range columns. To refer to "A1": Cells(1)   To refer to "B1": Cells(2)   To refer to "A3": Cells(3,1) is the same as Cells(3, "A")   To select all cells in a worksheet: ActiveSheet.Cells.Select
Range ()	Refers to a single cell, adjacent cells, non-adjacent cells, row(s) or column(s)   Examples Range("A1")   Range("A1, B5")   Range("A3:E5")   Range ("3:3")   Range ("3:5")   Range ("A:A")   Range ("A:C") Range("A3:E5") is same as Range (Cells (3,1),Cells (5,5)) Range("PriceList") --> A named range. It is useful to have a named range when columns/rows/cells are added/deleted.   Range("B" & LastCopyRow2 & ":L" & LastCopyRow2), where LastCopyRow2 is a variable   Range("A2:" & Cells(LastUsedRow(i), LastUsedCol(i)).Address)
Rows()	Refers to a row(s)   To refer to all the rows in a worksheet: Activesheet.Rows   To refer to Row 1-3 in a worksheet: Activesheet .Rows("1:3")   To refer to Row 3 in a worksheet: Activesheet .Rows(3)   To refer to all the rows in a specific range: Activesheet.Range("B2:D4").Rows   To refer to Row 3 in a specific range: Activesheet.Range("B2:D4").Rows(3)
Columns()	Refers to a column(s)   To refer to all the columns in a worksheet: Activesheet.Columns   To refer to Column 3 in a worksheet: Activesheet .Columns(3) is the same as Activesheet .Columns("C")   To refer to Column A-C in a worksheet: Activesheet .Columns("A:C")   To refer to all the columns in a specific range: Activesheet.Range("B2:D4").Columns   To refer to Column 3 in a specific range: Activesheet.Range("B2:D4").Columns(3)
Row	Returns the row number. If more than 1 row is referenced, it will return the 1st row number in the specified range.   Returns "3": Activesheet.Range("F3").row
Column	Returns the column number. If more than 1 column is referenced, it will return the 1st column number in the specified range.     Returns "6": Activesheet.Range("F3:H5").column
THE RANGE.END() METHOD To find last used row/column. End() is akin to pressing the Ctrl+Arrow Key .
End(xlUp)	Example 1: Range.End(xlUp) Method1 LastRow1 = Cells(Rows.Count, 1).End(xlUp).Row Rows.Count is the Row number of the last row in the worksheet. Cells(Rows.Count, 1) will move down to the last row in Column A. Next, End(xlUp) will move up to the last used row in Column A. Finally, Row returns the row number of the last used row in Column A.
End(xlDown)	Example 2:   LastRow2= Cells(1, 1).End(xlDown).Row   From A1, move down to the last used row in Column A.
End(xlToLeft)	Example 3:   LastCol3= Cells(1, Columns.Count).End(xlToLeft).Column   Similar to Example 1, except it is to find last used column.
End(xlToRight)	Example 4:   LastCol4= Cells(1, 1).End(xlToRight).Column Similar to Example 2, except it is to find last used column.
HOW TO FIND THE SPECIFIC ROW & COLUMN NO. OF A USED RANGE?
	Example LastCopyRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row   Explanation ActiveSheet.UsedRange.Rows.Count will return the number of used rows in the UsedRange: 6   ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count) refers to the last row in the UsedRange: 6   ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row returns the row No. of the last row in the UsedRange: 16     LastCopyColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
WHAT IS PASSING ARGUMENTS BY VALUE VS BY REFERENCE?
	Passing Arguments By Value does not change the original variable as only a copy of the variable is passed.   Passing Arguments By Reference changes the original variable as the variable itself is accessed.     A copy of a variable is passed Original variable is changed Ref No Yes Val Yes No   Example for ByRef vs ByVal   Dim i As Long Function Add_ByRef(ByRef i As Long) As Long i = i + 5 Add_ByRef = i End Function   Function Add_ByVal(ByVal i As Long) As Long i = i + 5 Add_ByVal = i End Function   Sub Test_ByRef () i = 2 Debug.Print Add_ByRef(i) ‘Return 7 Debug.Print I ‘Return 2 End Sub   Sub Test_ByVal () i = 2 Debug.Print Add_ByVal(i) ‘Return 7 Debug.Print i ‘Return 2 End Sub

 

 

COMMON ERRORS

1)      Activating a sheet is needed when selecting anything in it. Eg, Sometimes selecting a cell doesn’t work because the sheet has not been activated. At other times, the active sheet could differ from the sheet stated in the code. Hence it is best practice to always activate a sheet. ThisWorkbook.Worksheets("Data").Activate   2)      Ensuring a workbook is opened for formulas to be updated For certain formulas like Indirect and Offset when used to reference another Excel file; that file must be opened for the values to be updated. Offset can usually be substituted with Index which doesn’t require the referenced Excel file to be opened.

 

Download this article at https://1drv.ms/b/s!Aq4mhLQ_aASsaZDGdaKPusJ66BA?e=JV4aIP