TOSA VBA Excel Certification — Questions and Answers

Question 1: When building a VBA solution for a FINRA-regulated broker-dealer, which record retention practice is most compliant?

Allowing traders to overwrite historical trade records directly in Excel
Deleting workbook versions older than 30 days to save disk space
Using VBA to enforce read-only status on finalized reports and log any export or print action (Correct answer)
Storing trade confirmations only in the workbook's Comment fields

Correct answer: Using VBA to enforce read-only status on finalized reports and log any export or print action

FINRA Rule 4511 requires broker-dealers to preserve records in a non-alterable format; enforcing read-only status and logging access supports this requirement.

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

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

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 3: 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 4: Which VBA statement correctly catches a division-by-zero error that may occur when calculating a risk ratio?

Resume Next Err.Clear
On Error GoTo ErrHandler (Correct answer)
Try Catch Finally
If Err.Number <> 0 Then

Correct answer: On Error GoTo ErrHandler

On Error GoTo ErrHandler redirects execution to a labeled error handler where division-by-zero (Err.Number 11) can be addressed.

Question 5: Which Excel VBA function returns the standard deviation of a range of simulated risk outcomes stored in an array?

WorksheetFunction.StDev(arr) (Correct answer)
Math.StdDev(arr)
VBA.StdDev(arr)
Application.StDevP(arr)

Correct answer: WorksheetFunction.StDev(arr)

WorksheetFunction.StDev exposes Excel's STDEV function to VBA, making it straightforward to compute sample standard deviation on an array or range.

Question 6: What does the Range.Value2 property return compared to Range.Value?

Formatted text instead of raw data
A copy of the cell value as a string
Raw underlying numeric values without date/currency conversion (Correct answer)
The formula string of the cell

Correct answer: Raw underlying numeric values without date/currency conversion

Value2 returns the raw underlying value without applying date serial or currency formatting conversions.

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

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

Correct answer: Join()

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

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

Len() (Correct answer)
Size()
Count()
Length()

Correct answer: Len()

Len() is the VBA function that returns the number of characters contained in a string.

Question 9: A VBA developer needs to call a REST API that returns JSON data and parse the result. Since VBA has no native JSON parser, what is the recommended approach?

Use MSXML2.XMLHTTP or WinHttp to fetch the response, then parse with a third-party JSON library like VBA-JSON or use ScriptControl with JavaScript's JSON.parse (Correct answer)
Use Application.WorksheetFunction.FilterXML on the JSON string
Use the Split function to tokenize the JSON string manually
Convert JSON to XML first using a formula, then use MSXML DOM

Correct answer: Use MSXML2.XMLHTTP or WinHttp to fetch the response, then parse with a third-party JSON library like VBA-JSON or use ScriptControl with JavaScript's JSON.parse

MSXML2.XMLHTTP handles the HTTP request, and a library like VBA-JSON (or ScriptControl with JSON.parse) provides reliable JSON deserialization.

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

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

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 11: How do you clear a variant array completely in VBA?

Set arr = Nothing
arr = Null
Erase arr (Correct answer)
ReDim arr(0)

Correct answer: Erase arr

The Erase statement frees the memory of a dynamic array or resets all elements of a static array.

Question 12: How do you protect a worksheet using VBA so users cannot edit cells?

Worksheet.Lock(Password)
Worksheet.Protect Password:="pass" (Correct answer)
Sheet.SetProtection("pass")
ActiveSheet.EnableProtection(True)

Correct answer: Worksheet.Protect Password:="pass"

Worksheet.Protect with an optional password argument enables protection to prevent unauthorized cell edits.

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

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

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 14: A macro needs to import data from a closed workbook without opening it in the Excel UI. What is the most reliable VBA technique?

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
Use a formula like ='C:\path\[file.xlsx]Sheet1'!A1 inserted by VBA
Both B and C are reliable approaches (Correct answer)

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 15: In VBA, what happens if a runtime error occurs inside an active error handler?

VBA jumps to a nested error handler
The current error handler is disabled and the error propagates to the caller (Correct answer)
The error is silently ignored
VBA automatically retries the failing statement

Correct answer: The current error handler is disabled and the error propagates to the caller

If an error occurs within an error handler, VBA disables that handler and passes the error up to the calling procedure's error handler.

Question 16: What is the output of expression in VBA? (1O<>0 OR 0<>0) True/False

True (Correct answer)
False
100
0.0

Correct answer: True

In VBA, the `OR` operator returns `True` if at least one of its operands is `True`. The first part of the expression, `10 <> 0`, evaluates to `True` because 10 is indeed not equal to 0. The second part, `0 <> 0`, evaluates to `False`. Since `True OR False` results in `True`, the overall expression evaluates to `True`.

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

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

Correct answer: InStr()

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

Question 18: 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?

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)
Always open CSVs with Workbooks.Open and let Excel auto-detect the delimiter
Use Split(line, ',') and ignore delimiter differences
Hardcode comma as the delimiter and ask users to resave their files

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 19: Which built-in VBA function splits a string into an array using a delimiter?

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

Correct answer: Split()

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

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

The UserForm itself (Correct answer)
The active workbook
The parent worksheet
The calling procedure

Correct answer: The UserForm itself

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

Question 21: In VBA, which scope modifier makes a variable visible throughout the entire module?

Static
Dim
Private (Correct answer)
Public

Correct answer: Private

A variable declared with Private at module level is accessible to all procedures within that module only.

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

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

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 23: What does 'Resume Next' do when placed inside a VBA error handler?

Exits the current subroutine immediately
Re-runs the entire procedure from the start
Continues execution with the statement after the one that caused the error (Correct answer)
Jumps to the next procedure in the module

Correct answer: Continues execution with the statement after the one that caused the error

'Resume Next' directs VBA to continue execution with the statement immediately following the line that caused the error, effectively skipping it.

Question 24: When building a risk scoring matrix in VBA, a nested Select Case structure is preferred over nested If/ElseIf because it:

Executes faster at runtime
Automatically sorts the cases numerically
Improves readability for multiple discrete score bands (Correct answer)
Supports string comparisons only

Correct answer: Improves readability for multiple discrete score bands

Select Case clearly maps discrete score ranges to outcomes, making the risk banding logic easier to read and audit than deeply nested If/ElseIf.

Question 25: Which VBA approach is best for iterating through an unknown number of data rows in a research spreadsheet?

For i = 1 To 1000
For Each cell In Range("A:A")
Do While Cells(i,1) <> "" (Correct answer)
While i < 500

Correct answer: Do While Cells(i,1) <> ""

Do While Cells(i,1) <> "" dynamically stops at the last row of data without hardcoding row counts.

Question 26: A risk register macro accidentally deletes rows instead of hiding them. To add an undo checkpoint before the destructive operation, a developer should call:

Application.EnableEvents = False
ActiveWorkbook.SaveCopyAs before deleting
CommandBars.ExecuteMso "Undo"
Application.OnUndo (Correct answer)

Correct answer: Application.OnUndo

Application.OnUndo registers a custom procedure with the Undo stack so the user can reverse the macro's destructive action.

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

Use empty error handlers to silently ignore all errors
Display a generic MsgBox for every error regardless of type
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 28: What is the result of the VBA expression: 7 Mod 3?

2
3
4
1 (Correct answer)

Correct answer: 1

The Mod operator returns the remainder of integer division, so 7 divided by 3 leaves a remainder of 1.

Question 29: 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 Open filename For Output As #1, which appends by default
Use the FileSystemObject's OpenTextFile with ForWriting mode

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.

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

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

Correct answer: ReDim Preserve arr(20)

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

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

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

Correct answer: Dim x As Variant

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

Question 32: Which VBA statement outputs values and messages to the Immediate Window during code execution for logging purposes?

Print.Debug
Console.WriteLine
Debug.Print (Correct answer)
Debug.Write

Correct answer: Debug.Print

'Debug.Print' writes values or messages to the Immediate Window at runtime, which is useful for tracing variable values without stopping execution.

Question 33: Which VBA debugging action executes code one line at a time, including stepping into called procedures?

Step Into (Correct answer)
Watch Window
Breakpoints
Immediate Window

Correct answer: Step Into

'Step Into' (F8) executes code one line at a time and enters any called Sub or Function to debug it line by line.

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

DoEvents in a tight loop
Application.Wait 5000
MsgBox to show results and wait for user confirmation (Correct answer)
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 35: What is the benefit of standardized digital reporting in Excel VBA practice?

It limits creativity
It ensures consistency, enables comparison, and facilitates regulatory compliance (Correct answer)
It replaces professional communication
It is only needed for government work

Correct answer: It ensures consistency, enables comparison, and facilitates regulatory compliance

This is fundamental to Excel VBA practice. It ensures consistency, enables comparison, and facilitates regulatory compliance represents the professional standard for technology in the Excel VBA certification framework.

Question 36: 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 37: A developer needs to create a VBA class module that models a 'Product' with properties (Name, Price, Stock) and a method (ApplyDiscount). Which statement is correct about this implementation?

Properties are created with Property Get/Let procedures and methods are standard Sub or Function procedures inside the class module (Correct answer)
VBA classes require an Interface keyword to define properties
VBA class modules cannot have methods, only properties
All properties in a VBA class must be Public variables, not Property procedures

Correct answer: Properties are created with Property Get/Let procedures and methods are standard Sub or Function procedures inside the class module

VBA class modules use Property Get/Let/Set for encapsulated properties and standard Sub/Function procedures for methods, fully supporting OOP encapsulation.

Question 38: In VBA, which method adds a new worksheet to a workbook?

Sheets.New()
Workbook.AddSheet()
Workbook.Insert()
Worksheets.Add() (Correct answer)

Correct answer: Worksheets.Add()

Worksheets.Add() inserts a new worksheet into the workbook and returns a reference to the new sheet.

Question 39: Why is documentation important in Excel VBA risk management?

It slows down operations
It creates an audit trail, supports decision-making, and demonstrates due diligence (Correct answer)
It only benefits legal teams
It is optional paperwork

Correct answer: It creates an audit trail, supports decision-making, and demonstrates due diligence

This is fundamental to Excel VBA practice. It creates an audit trail, supports decision-making, and demonstrates due diligence represents the professional standard for risk management in the Excel VBA certification framework.

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

Jumps to line number 0 of the procedure when an error occurs
Disables any currently active error handler in the procedure (Correct answer)
Enables the system-default VBA error handler
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 41: What does the Err.Number property return when no error has occurred in VBA?

0 (Correct answer)
Null
Nothing
-1

Correct answer: 0

When no error has occurred, Err.Number returns 0, which is why 'If Err.Number <> 0' is the standard pattern for detecting whether an error happened.

Question 42: Which window in the VBA IDE allows you to type and execute VBA statements interactively without running a full procedure?

Watch Window
Locals Window
Immediate Window (Correct answer)
Output Window

Correct answer: Immediate Window

The Immediate Window (Ctrl+G) lets developers type and run VBA expressions instantly, making it ideal for testing snippets and inspecting values.

Question 43: A VBA solution must write data to a SQL Server database. Which approach is standard for this task?

Export data to CSV and email it to a DBA for manual import
Use ADO (ADODB.Connection and ADODB.Command) to open a connection and execute parameterized INSERT statements (Correct answer)
Copy data to the clipboard and paste into SQL Server Management Studio
Use DDE (Dynamic Data Exchange) to push data to SQL Server

Correct answer: Use ADO (ADODB.Connection and ADODB.Command) to open a connection and execute parameterized INSERT statements

ADO is the standard VBA library for database connectivity; ADODB.Connection manages the connection and ADODB.Command executes parameterized SQL.

Question 44: A VBA workbook is part of a system undergoing FedRAMP authorization. Which security control directly applies to the workbook's data handling?

Using Excel's default file format (.xlsx) without additional controls
Ensuring data at rest is encrypted and access is limited to users with the appropriate FedRAMP-defined clearance level (Correct answer)
Disabling macro security to ensure smooth operation in the cloud
Storing data in unprotected CSV files for interoperability

Correct answer: Ensuring data at rest is encrypted and access is limited to users with the appropriate FedRAMP-defined clearance level

FedRAMP requires federal cloud services to meet NIST 800-53 controls including data encryption at rest and strict access controls aligned with authorization levels.

Question 45: How should an Excel VBA professional approach a novel situation not covered by standard procedures?

Apply foundational principles, assess risks, consult resources, and document the rationale for decisions (Correct answer)
Follow the closest standard procedure exactly
Improvise without documentation
Refuse to proceed

Correct answer: Apply foundational principles, assess risks, consult resources, and document the rationale for decisions

This is fundamental to Excel VBA practice. Apply foundational principles, assess risks, consult resources, and document the rationale for decisions represents the professional standard for practical in the Excel VBA certification framework.

Question 46: In VBA, how do you declare a two-dimensional array with 3 rows and 4 columns?

Dim arr(3, 4) As Integer
Dim arr[3][4] As Integer
Dim arr(3)(4) As Integer
Dim arr(2, 3) As Integer (Correct answer)

Correct answer: Dim arr(2, 3) As Integer

Since VBA arrays are zero-based by default, Dim arr(2, 3) creates indices 0–2 and 0–3, giving 3 rows and 4 columns.

Question 47: Which VBA loop construct checks its condition BEFORE executing the loop body?

Do...Loop Until
For Each...Next
Do Until...Loop (Correct answer)
Do...Loop While

Correct answer: Do Until...Loop

Do Until...Loop evaluates the condition before each iteration and stops when the condition is True.

Question 48: If a formula in a cell relates to its own cell directly or indirectly, what is it called?

Circular reference (Correct answer)
Relative reference
Absolute reference
Physical reference

Correct answer: Circular reference

A circular reference occurs when a formula in a cell refers directly or indirectly to the cell containing the formula itself. This creates a loop where the cell's value depends on its own calculation, which can lead to incorrect results or infinite recalculations if not handled properly. Excel typically warns users when a circular reference is detected.

Question 49: When running a VBA analysis macro on a large research dataset, which property should be set to False to improve performance?

Application.ScreenUpdating (Correct answer)
Application.EnableEvents
Application.Calculation
Application.DisplayAlerts

Correct answer: Application.ScreenUpdating

Setting Application.ScreenUpdating = False prevents Excel from refreshing the display during the macro, significantly speeding up execution.

Question 50: During the calculation process, rounding errors may occur.

Multiplication (Correct answer)
Addition
None of the above
Subtraction

Correct answer: Multiplication

Rounding errors are more prominent in multiplication because intermediate results can have many decimal places, which are then rounded. When these rounded intermediate results are used in further calculations, the small inaccuracies accumulate, leading to a noticeable error in the final product. While addition and subtraction can also involve rounding, multiplication often exacerbates the issue due to the scaling effect.

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

30 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

These practice questions were written with AI tools. They are not real exam questions. We have not yet matched this exam's facts to an official source, so confirm fees, dates and rules with the exam body. Not reviewed by a subject-matter expert. Editorial policy