Excel VBA Part 1: Key Concepts

• These questions cover key concepts from Part 1 of a VBA course, including the Excel object model, referencing ranges, variables, and loops
• There are additional questions to broaden preparation

Objects in Excel VBA

• An object in Excel VBA an entity in Excel, such as a Workbook, Worksheet, or Range, that has properties and methods

Modifying Cell Values

• Range("A1").Value = 100 places the value 100 into cell A1

Range Object Methods

• IsEmpty is a VBA function, not a direct Range method
• Common methods of a Range object in Excel VBA are Select, Cut, and ClearContents

Offset Method

• Range("B2").Offset(3,2).Select selects the cell 3 rows below and 2 columns to the right of B2, which is D5

Array Indexing

• Without specifying Option Base 1, arrays default to 0-based
• For example, Dim MyArr(5) As Integer starts at index 0

Select Method

• Select highlights a cell or range in the worksheet’s user interface

Select vs Activate

• Select can select multiple cells
• Activate works on only one cell at a time

Code Example

• Code:

Sub Example()
    Dim x As Long
    For x = 1 To 3
        Range("A" & x).Value = x * 2
    Next x
End Sub

• Cells A1, A2, and A3 will contain the values 2, 4, and 6, respectively, after running this code

VBA Data Types

• "Fraction" is not a built-in VBA data type

Dynamic Arrays

• To declare a dynamic array in VBA, use Dim MyArray() As Double then ReDim MyArray(10)

Moving Cell Content

• Range("A1").Cut Destination:=Range("B2") moves the content of cell A1 to cell B2

Checking Cell Values

• If Range("A1").Value > 50 Then... Else... End If is best to check if a single cell’s value is greater or less than 50 and run different code based on the result

Changing Cell Background Color

• Modify the Interior.Color property of a Range object to change the background color of cells

Explicit Variable Declaration

• Use the Option Explicit statement to force VBA to require variable declarations at the top of a module

Loops

• For... Next loop executes a block of code a specific number of times based on a counter

Variant Data Type

• The Variant keyword declares a variable that can store any data type

Exiting Loops

• Exit For statement allows you to exit a For... Next loop prematurely if a certain condition is met

Object Variable Type

• The Object variable type refers to a reference that can point to any Excel object like Range or Workbook

Assigning a Worksheet Range

• If MyRange is declared as Dim MyRange As Range, the correct way to assign a worksheet’s range is by using Set MyRange = Range("A1:A5")

Cell Selection Size

• If you have Range("B2:D4") selected, there are 9 cells in the selection

Iterating Over Worksheets

• For Each ws In Worksheets... Next ws loop type in VBA is best suited for iterating over each Worksheet in a Workbook

Assigning Values to Multiple Cells

• If you see the code Range("A1:F1").Value = 10, cells A1 through F1 all become 10

Checking for Formulas

• Check the .Formula property of a Range object to see if it contains a formula

Selection Object

• In Excel VBA, Selection is an object representing the currently selected cell(s) or shape(s)

Selecting Columns

• The statement Columns("B").Select will select column 2 in the active worksheet

Invalid Loop Syntax

• Repeat 10 Times is not a valid way to start a loop in VBA

Two-Dimensional Arrays

• Dim MyArr(10,5) As Variant
• Dim MyArr(1 To 10, 1 To 5)
• Dim MyArr() As Variant : ReDim MyArr(10,5)
• These can all be correct for declaring a two-dimensional array that can hold 10 rows and 5 columns

Option Explicit Statement

• The Option Explicit statement forces variable declaration before use

Excel VBA Project

• PivotTables inside code modules cannot be part of an Excel VBA project

End(xlDown) Purpose

• In Range("B2").End(xlDown).Select, End(xlDown) finds the last cell in column B that has content before an empty cell

Static Variable Declaration

• If i is declared as a Static variable inside a Sub procedure, it retains its value between calls to that procedure

Storing Formulas in Cells

• Range("A1").Formula = "=SUM(B1:B5)" will store a formula in cell A1 using standard “A1” references in Excel

Custom Function Syntax

Function ComputeValue(x As Double) As Double
    ComputeValue = x^2
End Function

• This will create a custom function that returns a numeric result in VBA

Running Subs from Other Modules

• To run a Sub named TestMacro located in Module1 from another Sub named MainMacro in Module2:
• Use: Call Module1.TestMacro

Property Procedure Use

• A Property procedure defining or retrieving a custom property within a Class Module

With...End With Blocks

• With...End With blocks shorten references and improve readability/performance when dealing with the same object repeatedly

Variant Type Truth

• The Variant type can handle any data type but takes more memory

Activating Worksheets

• The statement Worksheets(3).Activate will activate the third visible worksheet in the active workbook

Public Variables

• A good reason to use Public MyVar As Long in the Declarations section of a module to make MyVar accessible to every procedure in every module

On Error GoTo Correctness

• On Error GoTo directs the program flow to a label where your error-handling code resides

ActiveCell.Offset Function

• If the active cell is currently C5:
• ActiveCell.Offset(1, 0).Select selects cell C6.

Determining Array Elements

• UBound() for arrays & .Count for collections return the number of elements in a collection or array in VBA

Macro Recorder Results

• Running the Macro Recorder, inserting a row in a worksheet, and then stopping recording will generate code that usually references the exact rows or columns selected, with direct references in a Sub routine

Checking Cell Content Type

• To check if the content of cell A1 is text or numeric in code:
• IsNumeric(Range("A1").Value) is useful

Class Module

• A class module in VBA is a module where you define new objects with their own properties and methods

MsgBox Function

• MsgBox "Hello" displays a message box

Prompting User Input

• Use InputBox("Enter a value:") to prompt the user to enter a value into your VBA procedure

Exit Sub Keyword Function

• The Exit Sub keyword stops the current procedure immediately and returns to the caller (if any)

Parent Object

• In the statement Workbooks("Data.xlsx").Worksheets("Sheet1").Range("A1"), the parent of Range("A1") is Worksheets("Sheet1")

Protecting Variables

• To protect a variable from being modified by other procedures, declare it with Private or local Dim in the procedure

ActiveWorkbook.Worksheets Function

• Set ws = ActiveWorkbook.Worksheets(1) assigns a reference to the first worksheet of the active workbook to the object variable ws

Single Line If... Then

• Writing If x = 5 Then x = 7 Else x = 10 all on one line is valid & sets x=7 if x=5, otherwise sets x=10

Interior.Color vs Font.Color

• .Interior.Color sets the fill color
• .Font.Color sets the text color for a Range

Do Until Loop

• Structure a “Do Until” loop that repeats until i is greater than 10:

Do Until i > 10
    i = i + 1
Loop

Resizing Range

• To resize an existing Range("A1:B2") to make it 3 rows by 4 columns:
• Use Range("A1:B2").Resize(3,4)

Double Storage

• Declaring "result" as Double if:

Dim result As Double
result = 10 / 3

• 3.333333... is stored in result

Referencing Cell A1

• Workbooks("MyBook.xlsx").A1("Sheet1") is not a valid method for referencing cell A1 on “Sheet1”

Omitting Data Type

• Variables are created as Variant if you omit a data type in your variable declaration and Option Explicit is turned off

Select Case Statement

• About Select Case statements:
• They allow multiple ranges or values in each Case clause

ReDim Preserve

• ReDim Preserve MyArr(...) in VBA when wanting to keep current contents of a dynamic array while resizing it