TOSA VBA Excel Certification — Questions and Answers

Question 1: A QA sub needs to flag all cells in column B that contain negative numbers. Which loop construct is most appropriate?

For Each cell In Range('B1').CurrentRegion
For Each cell In Range('B:B')
For Each cell In Columns('B').SpecialCells(xlCellTypeConstants, xlNumbers) (Correct answer)
Do While ActiveCell <> ''

Correct answer: For Each cell In Columns('B').SpecialCells(xlCellTypeConstants, xlNumbers)

SpecialCells(xlCellTypeConstants, xlNumbers) restricts the loop to cells with numeric constants, avoiding blank/formula cells and improving performance.

Question 2: What this demonstrates is a function that is used by another function.

Nested Function (Correct answer)
Chain Function
Vlookup Function
Text Function

Correct answer: Nested Function

A "nested function" refers to a function that is used as an argument within another function. This means the inner function's result is passed as input to the outer function. Nesting functions allows for more complex calculations and logical operations to be performed in a single formula, building upon the results of intermediate functions.

Question 3: How do you find the last used row in column A using VBA?

Cells.LastRow(1)
Cells(Rows.Count, 1).End(xlUp).Row (Correct answer)
Range("A1").LastRow
ActiveSheet.UsedRange.Rows

Correct answer: Cells(Rows.Count, 1).End(xlUp).Row

Cells(Rows.Count, 1).End(xlUp).Row navigates up from the bottom of column A to find the last non-empty row.

Question 4: In VBA, which method deletes all content and formatting from a range?

Range.Erase()
Range.Delete()
Range.Clear() (Correct answer)
Range.Reset()

Correct answer: Range.Clear()

Range.Clear() removes both the content and the formatting of all cells in the specified range.

Question 5: Which built-in VBA function splits a string into an array using a delimiter?

Tokenize()
Split() (Correct answer)
Divide()
Slice()

Correct answer: Split()

The Split() function divides a string by a specified delimiter and returns a zero-based string array.

Question 6: Which error handling approach is most appropriate for production VBA code that needs to respond differently to multiple error types?

Display a generic MsgBox for every error regardless of type
Use empty error handlers to silently ignore all errors
Place 'On Error Resume Next' at the start of every procedure
Use a GoTo handler with a Select Case block on Err.Number (Correct answer)

Correct answer: Use a GoTo handler with a Select Case block on Err.Number

A GoTo error handler with a Select Case on Err.Number lets you apply specific logic per error type, resulting in robust and maintainable error handling.

Question 7: Which keyword is used to declare a variable in VBA?

Let
Var
Define
Dim (Correct answer)

Correct answer: Dim

In VBA, the Dim keyword is used to declare variables before they are used.

Question 8: A VBA macro that performs scenario analysis must pause and display interim results to the user before continuing. The best approach is to use:

Application.Wait 5000
MsgBox to show results and wait for user confirmation (Correct answer)
DoEvents in a tight loop
Debug.Print to the Immediate Window

Correct answer: MsgBox to show results and wait for user confirmation

MsgBox halts macro execution and presents results, resuming only after the user dismisses it, making it ideal for interactive scenario review.

Question 9: Your macro displays a MsgBox asking stakeholders to confirm before deleting data. Which MsgBox argument combination shows Yes and No buttons and returns vbYes when Yes is clicked?

MsgBox "Delete?", vbYesNo returns vbYes (Correct answer)
MsgBox "Delete?", vbOKCancel returns vbOK
MsgBox "Delete?", vbYesNoCancel returns vbOK
MsgBox "Delete?", vbRetryCancel returns vbRetry

Correct answer: MsgBox "Delete?", vbYesNo returns vbYes

vbYesNo shows Yes and No buttons, and clicking Yes returns the constant vbYes (value 6).

Question 10: Which keyword is used at the end of an error handler to return execution to the main code flow?

End Error
Exit Handler
Return
Resume (Correct answer)

Correct answer: Resume

The 'Resume' keyword directs execution back to the main code — either re-trying the error line, moving to the next line, or jumping to a specified label.

Question 11: What does the LBound() function return for an array?

The first valid index (Correct answer)
The total number of elements
The data type of elements
The last valid index

Correct answer: The first valid index

LBound() returns the lowest subscript (first valid index) of the specified array dimension.

Question 12: What is 'error propagation' in VBA?

Automatically correcting formula errors in a worksheet
Logging errors to an external database or file
Copying error messages between open workbooks
An unhandled error bubbling up the call stack to the calling procedure (Correct answer)

Correct answer: An unhandled error bubbling up the call stack to the calling procedure

Error propagation occurs when a procedure has no error handler (or re-raises the error), causing the error to move up the call stack to the calling procedure.

Question 13: A macro that imports CSV files stops working when a user's CSV has a semicolon delimiter instead of a comma. What is the most robust fix?

Hardcode comma as the delimiter and ask users to resave their files
Always open CSVs with Workbooks.Open and let Excel auto-detect the delimiter
Use Split(line, ',') and ignore delimiter differences
Detect the delimiter by reading the first line of the file and testing which character appears most frequently between known positions, or prompt the user to specify it (Correct answer)

Correct answer: Detect the delimiter by reading the first line of the file and testing which character appears most frequently between known positions, or prompt the user to specify it

Auto-detecting the delimiter by inspecting the file's first row, or prompting the user, makes the import robust to regional delimiter differences.

Question 14: A sales team's workbook uses a UserForm with a ListBox to let users select multiple products. How should the VBA code retrieve all selected items from a multi-select ListBox?

Read ListBox.Value, which returns a comma-separated string of selections
Use ListBox.MultiSelect property to get the selections directly
Call ListBox.GetSelected() method
Loop through ListBox.ListCount and check ListBox.Selected(i) for each index (Correct answer)

Correct answer: Loop through ListBox.ListCount and check ListBox.Selected(i) for each index

For multi-select ListBoxes, you must iterate through each item index and test the Selected property to determine which items are chosen.

Question 15: In VBA, which operator is used for string concatenation?

+
*
#
& (Correct answer)

Correct answer: &

The ampersand (&) operator is the standard VBA operator for joining strings together.

Question 16: Which VBA function returns the position of a substring within a string?

Find()
Search()
Locate()
InStr() (Correct answer)

Correct answer: InStr()

InStr() searches for the first occurrence of one string within another and returns its position as an integer.

Question 17: A VBA procedure must document its analysis steps in a log sheet. Which approach writes text to the next available row automatically?

Cells(1,1).Value = log
Cells(Rows.Count,1).End(xlUp).Offset(1,0).Value = log (Correct answer)
Range("A:A").Last.Value = log
ActiveCell.Value = log

Correct answer: Cells(Rows.Count,1).End(xlUp).Offset(1,0).Value = log

Cells(Rows.Count,1).End(xlUp).Offset(1,0) navigates to the last filled cell and offsets one row down to append a new log entry.

Question 18: To validate that all entries in a research dataset column are numeric before analysis, which VBA function should you use?

IsNull
IsNumeric (Correct answer)
IsEmpty
IsDate

Correct answer: IsNumeric

IsNumeric returns True if the argument can be evaluated as a number, allowing pre-analysis data validation.

Question 19: What does the Range.AutoFilter method do in VBA?

Groups rows by value
Applies or removes dropdown filter controls on a range (Correct answer)
Fills missing values automatically
Sorts the range alphabetically

Correct answer: Applies or removes dropdown filter controls on a range

Range.AutoFilter applies or toggles the AutoFilter dropdowns on the specified range, allowing row filtering.

Question 20: You are automating data entry into a web form using VBA and Internet Explorer Automation. The form has a dropdown menu. How do you select a specific option by its visible text?

Use SendKeys to type the option text into the dropdown
Set the element's .innerText property directly
Set the element's .value property to the option text
Find the SELECT element, iterate its .options collection, and set .selectedIndex when .text matches the target (Correct answer)

Correct answer: Find the SELECT element, iterate its .options collection, and set .selectedIndex when .text matches the target

You must iterate the options collection of the SELECT element and set selectedIndex to the matching option's index to programmatically choose a value.

Question 21: A stakeholder needs your macro to log activity to a shared status cell so others can monitor progress. Which approach writes text to cell A1 on a sheet named 'Log'?

Sheets("Log").Range("A1").Formula = "Processing..."
ActiveSheet.Log("A1") = "Processing..."
Sheets("Log").Range("A1").Value = "Processing..." (Correct answer)
Log.Cells(1,1).Write "Processing..."

Correct answer: Sheets("Log").Range("A1").Value = "Processing..."

Setting the .Value property of a Range object writes text directly to the specified cell.

Question 22: A VBA macro that processes a large dataset crashes with 'Out of Memory'. Which refactoring strategy is most likely to resolve the issue?

Add more error handlers with On Error Resume Next
Break the dataset into smaller chunks, process each chunk, and release object references with Set obj = Nothing (Correct answer)
Increase Excel's undo history stack size
Replace all integer variables with Long data type

Correct answer: Break the dataset into smaller chunks, process each chunk, and release object references with Set obj = Nothing

Processing data in chunks and explicitly releasing object references with Set obj = Nothing frees memory incrementally and prevents exhaustion.

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

Dim x As Dynamic
Dim x As Any
Dim x As Object
Dim x As Variant (Correct answer)

Correct answer: Dim x As Variant

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

Question 24: Which range of error numbers is reserved for user-defined errors raised with Err.Raise in VBA?

200 to 400
1 to 100
512 to 65535 (Correct answer)
1000 to 9999

Correct answer: 512 to 65535

VBA reserves error numbers 512 through 65535 for user-defined errors; numbers below 512 are reserved for VBA's built-in runtime errors.

Question 25: What is the effect of using 'On Error GoTo 0' in a VBA procedure?

Enables the system-default VBA error handler
Jumps to line number 0 of the procedure when an error occurs
Disables any currently active error handler in the procedure (Correct answer)
Resets the error counter variable to zero

Correct answer: Disables any currently active error handler in the procedure

'On Error GoTo 0' disables the currently active error handler, causing VBA to revert to its default unhandled-error behavior for any subsequent errors.

Question 26: What is the purpose of the 'Option Explicit' statement at the top of a VBA module?

Speeds up code execution
Enables early binding for objects
Forces all variables to be declared before use (Correct answer)
Prevents the module from being exported

Correct answer: Forces all variables to be declared before use

Option Explicit requires every variable to be declared with Dim, preventing typos from creating unintended new variables.

Question 27: What does 'On Error Resume Next' do in VBA?

Restarts the procedure from the beginning
Allows execution to continue with the statement following the error (Correct answer)
Jumps to the next module in the project
Stops execution immediately when an error occurs

Correct answer: Allows execution to continue with the statement following the error

'On Error Resume Next' instructs VBA to continue executing with the statement immediately following the one that caused the error.

Question 28: A QA macro must report rows where the value in column C does not match the pattern 'AA-####' (two letters, hyphen, four digits). Which VBA tool handles this?

cell.Value Like '[A-Z][A-Z]-####' (Correct answer)
InStr(cell.Value, '-')
IsNumeric(Mid(cell.Value, 4, 4))
cell.Value = 'AA-####'

Correct answer: cell.Value Like '[A-Z][A-Z]-####'

The Like operator with the pattern '[A-Z][A-Z]-####' matches exactly two uppercase letters, a hyphen, and four digits.

Question 29: What is the correct syntax to raise a custom user-defined error in VBA?

Error.Raise("Custom", 1000)
Err.Raise Number, Source, Description (Correct answer)
Err.Create "Custom error message"
Throw New Error("message")

Correct answer: Err.Raise Number, Source, Description

Err.Raise allows you to generate a custom error by specifying an error number, source, and description, enabling custom error signaling within procedures.

Question 30: Which VBA debugging feature lets you monitor a specific variable or expression and optionally pause execution when its value changes?

Trace Variables
Watch Expressions (Correct answer)
Step Over
Breakpoints

Correct answer: Watch Expressions

Watch Expressions (added via Debug > Add Watch) monitor specific variables or expressions and can pause execution when the watched value changes or meets a condition.

Question 31: A client asks your macro to display a custom dialog box asking for their project name before running. Which VBA function returns user-typed text from a dialog?

MsgBox()
UserInput()
InputBox() (Correct answer)
TextBox()

Correct answer: InputBox()

InputBox() displays a prompt dialog and returns the string the user types as its return value.

Question 32: You distribute a macro-enabled workbook to 50 users. Some users get a 'Compile error: Can't find project or library' when opening it. What is the most likely cause?

The workbook's VBA project references a library (e.g., Microsoft Scripting Runtime) that is not registered on those machines (Correct answer)
The workbook was saved in .xlsx format instead of .xlsm
Users have macro security set to 'Disable all macros with notification'
The users have a different version of Windows

Correct answer: The workbook's VBA project references a library (e.g., Microsoft Scripting Runtime) that is not registered on those machines

A missing or broken library reference causes compile errors; the referenced library DLL must be present and registered on the end user's machine.

Question 33: How should an Excel VBA professional handle an outcome that differs from expectations?

Analyze contributing factors, document findings, and adjust approach based on lessons learned (Correct answer)
Blame external factors
Repeat the same approach
Ignore the discrepancy

Correct answer: Analyze contributing factors, document findings, and adjust approach based on lessons learned

This is fundamental to Excel VBA practice. Analyze contributing factors, document findings, and adjust approach based on lessons learned represents the professional standard for practical in the Excel VBA certification framework.

Question 34: What is the purpose of the Err.Clear method in VBA?

Resets the Err object's properties to their default zero/empty values (Correct answer)
Removes error-handling code from the procedure
Clears the VBA error log file
Deletes all error history from the workbook

Correct answer: Resets the Err object's properties to their default zero/empty values

Err.Clear resets the Err object's Number, Description, and Source properties to their default values (0 and empty strings).

Question 35: What information does the Locals Window display during VBA debugging?

The complete call stack of procedure invocations
All variables declared across the entire project
All variables in the current procedure scope with their current values and types (Correct answer)
A list of all VBA error numbers and descriptions

Correct answer: All variables in the current procedure scope with their current values and types

The Locals Window automatically displays all variables in the currently executing procedure's scope, showing their names, types, and current values.

Question 36: Which VBA event fires automatically when a workbook is first opened?

Workbook_Start
Workbook_Activate
Workbook_Open (Correct answer)
Workbook_Load

Correct answer: Workbook_Open

The Workbook_Open event procedure runs automatically each time the workbook is opened.

Question 37: When automating Outlook from Excel VBA, which object is the correct entry point to the Outlook object model?

Outlook.Explorer
Outlook.NameSpace
Outlook.Session
Outlook.Application (Correct answer)

Correct answer: Outlook.Application

Outlook.Application is the top-level object that provides access to all other Outlook objects such as NameSpace, Folders, and MailItem.

Question 38: A macro needs to import data from a closed workbook without opening it in the Excel UI. What is the most reliable VBA technique?

Use a formula like ='C:\path\[file.xlsx]Sheet1'!A1 inserted by VBA
Both B and C are reliable approaches (Correct answer)
Open the workbook with Workbooks.Open, read the data, then close it with SaveChanges:=False
Use ADO (ActiveX Data Objects) to query the file as a database

Correct answer: Both B and C are reliable approaches

Workbooks.Open is straightforward and reliable, while ADO can read data without a visible open—both are valid and commonly used.

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

vbCancel (2) (Correct answer)
False
0
vbNo (7)

Correct answer: vbCancel (2)

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

Question 40: How do Excel VBA professionals ensure compliance in daily practice?

By hiring a compliance officer
Compliance is checked only annually
By integrating compliance requirements into standard operating procedures and regular audits (Correct answer)
By memorizing all regulations

Correct answer: By integrating compliance requirements into standard operating procedures and regular audits

This is fundamental to Excel VBA practice. By integrating compliance requirements into standard operating procedures and regular audits represents the professional standard for regulatory in the Excel VBA certification framework.

Question 41: Which VBA property of the Application object returns the version number of Excel currently running?

Application.Release
Application.Edition
Application.Version (Correct answer)
Application.Build

Correct answer: Application.Version

Application.Version returns a string like "16.0" representing the Excel version, useful for writing version-conditional code.

Question 42: Which VBA function converts a string to all uppercase letters?

ToUpper
UCase (Correct answer)
Upper
StrUpper

Correct answer: UCase

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

Question 43: Which VBA function joins array elements into a single string?

Join() (Correct answer)
Combine()
Merge()
Concat()

Correct answer: Join()

Join() concatenates all elements of a one-dimensional array into a single string, optionally using a delimiter.

Question 44: What is the purpose of the UsedRange property in VBA?

Returns only cells with formulas
Lists all named ranges
Filters out blank rows
Returns the rectangular range that encompasses all non-empty cells on the sheet (Correct answer)

Correct answer: Returns the rectangular range that encompasses all non-empty cells on the sheet

UsedRange returns a Range object covering all cells that have ever had content or formatting on the worksheet.

Question 45: A VBA macro must generate reports for FDA 21 CFR Part 11 compliance. Which feature is mandatory?

Saving the file in CSV format for simplicity
Using Excel's built-in spell checker before saving
Enabling AutoSave on OneDrive
Capturing an electronic signature with signer identity and timestamp before finalizing the report (Correct answer)

Correct answer: Capturing an electronic signature with signer identity and timestamp before finalizing the report

FDA 21 CFR Part 11 requires electronic records to include electronic signatures with the signer's identity and the date/time of signing.

Question 46: In VBA, what data type is used to store decimal values?

Long
lnteger
Double (Correct answer)
Byte

Correct answer: Double

The `Double` data type in VBA is specifically designed to store floating-point numbers, which are numbers with decimal values. It provides high precision and a wide range for both positive and negative numbers. While `Single` also stores decimal values, `Double` offers greater precision and is generally preferred for most calculations involving non-integer numbers.

Question 47: Which VBA statement resizes a dynamic array while preserving existing data?

ReDim Preserve arr(20) (Correct answer)
ReDim arr(20)
Resize arr(20)
Expand arr(20)

Correct answer: ReDim Preserve arr(20)

ReDim Preserve resizes the array to the new size while keeping all previously stored values intact.

Question 48: What is a breakpoint in the context of VBA debugging?

A line of code that automatically throws an error
A marker that pauses execution when that line is reached (Correct answer)
A special comment that stops code from running
An error that permanently breaks the code flow

Correct answer: A marker that pauses execution when that line is reached

A breakpoint is a marker set on a specific line that causes VBA to pause execution when reached, allowing inspection of variables and program state.

Question 49: What does the Application.EnableEvents property control?

Whether keyboard shortcuts work
Whether add-ins can run code
Whether the macro recorder is active
Whether worksheet and workbook event procedures fire automatically (Correct answer)

Correct answer: Whether worksheet and workbook event procedures fire automatically

Setting EnableEvents to False prevents VBA event procedures from triggering during programmatic changes.

Question 50: A VBA solution needs to log errors with timestamps to a text file for troubleshooting. What is the correct approach to append log entries without overwriting previous ones?

Use Open filename For Append As #1 to open in append mode and write entries with Print #1 (Correct answer)
Use FileCopy to duplicate the log before each write
Use the FileSystemObject's OpenTextFile with ForWriting mode
Use Open filename For Output As #1, which appends by default

Correct answer: Use Open filename For Append As #1 to open in append mode and write entries with Print #1

Opening a file For Append positions the write pointer at the end, so new entries are added without overwriting existing content.

TOSA VBA Excel Certification

The TOSA VBA Excel certification assesses proficiency in Excel VBA programming, covering macro automation, Excel object manipulation, error handling and debugging, and practical application of VBA in real-world spreadsheet scenarios. It is an adaptive exam used by employers to validate candidates' VBA development skills.

Questions

60 min

Time Limit

55%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds