Excel VBA Flashcards

Which VBA statement exits a For...Next loop immediately?

Answer: Exit For

Exit For immediately transfers control to the statement following the Next keyword.

What does the UBound function return when called on an array?

Answer: The highest valid index

UBound returns the largest available subscript (upper bound) for the specified dimension of an array.

Which method is used to add a new worksheet to a workbook in VBA?

Answer: Worksheets.Add

Worksheets.Add (or Sheets.Add) creates and returns a new worksheet in the workbook.

What is the result of the VBA expression: 10 Mod 3?

Answer: 1

The Mod operator returns the remainder after division; 10 divided by 3 leaves a remainder of 1.

Which VBA function converts a string to all uppercase letters?

Answer: UCase

UCase(string) returns the string with all alphabetic characters converted to uppercase.

How do you declare a variable that can hold any data type in VBA?

Answer: Dim x As Variant

The Variant data type can store any kind of data including numbers, strings, dates, and objects.

What does the VBA keyword 'Nothing' represent?

Answer: An uninitialized or released object reference

Nothing is used with object variables to indicate the variable holds no valid object reference.

Which VBA event fires when a cell's value is changed by the user on a worksheet?

Answer: Worksheet_Change

The Worksheet_Change event fires whenever a user or external link changes a cell in the worksheet.

What does Application.ScreenUpdating = False do in a VBA macro?

Answer: Prevents the screen from refreshing while the macro runs

Setting ScreenUpdating to False stops the screen from flickering during macro execution, improving speed.

Which function returns the number of characters in a string in VBA?

Answer: Len

Len(string) returns the number of characters contained in the string argument.

In VBA, what does the keyword 'Me' refer to inside a UserForm's code module?

Answer: The UserForm itself

Me refers to the instance of the class (UserForm, worksheet, or workbook) in whose code module it appears.

What VBA construct handles errors that occur during runtime?

Answer: On Error GoTo

VBA uses On Error GoTo [label] to redirect execution to an error-handling block when a runtime error occurs.

Which property of a Range object returns the number of rows in that range?

Answer: Rows.Count

Range.Rows.Count returns an integer representing how many rows are in the specified range.

What is the VBA equivalent of Excel's VLOOKUP function when searching a sorted array?

Answer: Application.WorksheetFunction.VLookup

Application.WorksheetFunction.VLookup lets you call Excel's built-in VLOOKUP from within VBA code.

Which VBA collection object contains all open workbooks in the current Excel instance?

Answer: Workbooks

The Workbooks collection is a property of the Application object and holds all currently open Workbook objects.

What does the MsgBox function return when the user clicks the Cancel button?

Answer: vbCancel (2)

MsgBox returns the VBA constant vbCancel, which has an integer value of 2, when Cancel is clicked.

Which statement is used to define a reusable block of code that does NOT return a value in VBA?

Answer: Sub

A Sub procedure performs actions but does not return a value, unlike a Function which returns a result.

How do you copy a range in VBA without including the clipboard (direct value copy)?

Answer: destRange.Value = sourceRange.Value

Assigning .Value directly between ranges transfers data without using the clipboard or triggering a copy animation.

What does the Cells property return when called as Cells(2, 3) on a worksheet?

Answer: The cell in row 2, column 3 (C2)

Cells(row, column) uses numeric row and column indices, so Cells(2,3) refers to row 2, column C — cell C2.

Which VBA function extracts a substring from the right side of a string?

Answer: Right

Right(string, length) returns the specified number of characters from the rightmost end of the string.