TOSA VBA Excel Certification — Questions and Answers

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

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

Correct answer: InStr()

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

Question 2: Which error-handling structure ensures cleanup code runs regardless of whether an error occurred in a VBA QA routine?

On Error GoTo 0 with a standard Exit Sub
On Error GoTo ErrHandler with a Cleanup label called before both normal exit and the error branch (Correct answer)
A single On Error Resume Next block
Wrapping all code in an If Err.Number = 0 block

Correct answer: On Error GoTo ErrHandler with a Cleanup label called before both normal exit and the error branch

Routing both the normal exit and the error branch through a shared Cleanup label guarantees resources are released in all scenarios.

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

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

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 4: What does 'On Error Resume Next' do in VBA?

Allows execution to continue with the statement following the error (Correct answer)
Jumps to the next module in the project
Restarts the procedure from the beginning
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 5: What is the professional reason for using 'Option Explicit' at the top of every VBA module?

It forces all variables to be declared, preventing typo-based bugs (Correct answer)
It enables advanced debugging tools in the VBE
It speeds up macro execution significantly
It locks the module from being edited by others

Correct answer: It forces all variables to be declared, preventing typo-based bugs

Option Explicit requires explicit variable declaration, catching misspelled variable names at compile time rather than at runtime.

Question 6: What is the default lower bound for VBA arrays?

-1
It depends on the array type
0 (Correct answer)
1

Correct answer: 0

VBA arrays are zero-based by default, meaning index 0 is the first element unless Option Base 1 is set.

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

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

Correct answer: Split()

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

Question 8: What does 'Resume Next' do when placed inside a VBA error handler?

Jumps to the next procedure in the module
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)

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 9: Which range of error numbers is reserved for user-defined errors raised with Err.Raise in VBA?

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

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

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 11: 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?

Convert JSON to XML first using a formula, then use MSXML DOM
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 the Split function to tokenize the JSON string manually
Use Application.WorksheetFunction.FilterXML on the JSON string

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 12: How do you protect a worksheet using VBA so users cannot edit cells?

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

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

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

Question 13: Which VBA method copies a range of cells to another location?

Range.Copy() (Correct answer)
Range.Move()
Range.Clone()
Range.Transfer()

Correct answer: Range.Copy()

Range.Copy() copies the specified range to the clipboard or directly to a destination range.

Question 14: A macro loops through 50,000 rows and runs slowly. Which professional optimization technique should be applied first?

Use a MsgBox inside the loop to track progress
Turn off ScreenUpdating and set Calculation to Manual before the loop (Correct answer)
Increase the computer's RAM
Break the macro into multiple smaller macros and run them manually

Correct answer: Turn off ScreenUpdating and set Calculation to Manual before the loop

Disabling screen updates and automatic recalculation dramatically reduces overhead in large data loops.

Question 15: A finance team's end-of-month macro takes 45 minutes to run. Profiling shows most time is spent in a nested loop that applies cell-by-cell formatting. What is the single most impactful refactor?

Replace the loop with a single call to Range.AutoFormat
Use DoEvents inside the loop to free up the processor
Split the macro into two smaller macros run sequentially
Replace cell-by-cell formatting with a single conditional formatting rule applied to the entire range using VBA, or apply formatting to the full range object in one call (Correct answer)

Correct answer: Replace cell-by-cell formatting with a single conditional formatting rule applied to the entire range using VBA, or apply formatting to the full range object in one call

Applying formatting to an entire Range object in one call (e.g., Range.Interior.Color = ...) is orders of magnitude faster than setting properties on individual cells in a loop.

Question 16: A workbook macro needs to run only when a specific named range value changes. Which event-driven approach should be used?

Application.OnTime called every second
Worksheet_Change event that checks if the Target intersects the named range (Correct answer)
Workbook_BeforeSave event
Workbook_Open event with a loop that polls the cell value

Correct answer: Worksheet_Change event that checks if the Target intersects the named range

The Worksheet_Change event fires on cell edits; using Intersect(Target, Range('NamedRange')) checks whether the changed cell is the one of interest.

Question 17: Which principle guides a professional VBA developer when choosing between a recorded macro and hand-written code?

Recorded macros are a starting point; hand-written code provides flexibility, error handling, and reusability (Correct answer)
Hand-written code is always preferred regardless of the task
Recorded macros cannot be edited, so hand-written code must always be used
Recorded macros are always preferred because they are faster to produce

Correct answer: Recorded macros are a starting point; hand-written code provides flexibility, error handling, and reusability

Recorded macros offer a quick baseline but lack error handling and adaptability; professionals refine them with hand-written logic.

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

Locals Window
Output Window
Immediate Window (Correct answer)
Watch 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 19: Which VBA practice aligns with the SEC's Rule 17a-4 requirement for electronic record preservation?

Archiving records to a password-protected zip file
Allowing broker-dealers to delete records after internal review
Writing finalized trade records to a WORM (Write Once Read Many) compliant output and preventing any VBA routine from modifying archived data (Correct answer)
Storing records in editable Excel format in a shared folder

Correct answer: Writing finalized trade records to a WORM (Write Once Read Many) compliant output and preventing any VBA routine from modifying archived data

SEC Rule 17a-4 requires broker-dealers to preserve electronic records in a non-rewritable, non-erasable format; VBA must enforce read-only status on archived records.

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

Worksheet_Change (Correct answer)
Worksheet_Edit
Worksheet_Modify
Worksheet_Update

Correct answer: Worksheet_Change

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

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

Resize arr(20)
ReDim arr(20)
Expand 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 22: How has digital technology transformed Excel VBA practice?

It only affects large organizations
It has replaced all traditional methods
It has had no impact
It has enhanced data collection, analysis, communication, and operational efficiency (Correct answer)

Correct answer: It has enhanced data collection, analysis, communication, and operational efficiency

This is fundamental to Excel VBA practice. It has enhanced data collection, analysis, communication, and operational efficiency represents the professional standard for technology in the Excel VBA certification framework.

Question 23: When importing external risk data via VBA using ADODB, which step is critical before releasing the connection to prevent memory leaks?

Use On Error Resume Next to suppress errors
Set rs = Nothing and conn.Close (Correct answer)
Call Application.Quit
Set conn = New ADODB.Connection again

Correct answer: Set rs = Nothing and conn.Close

Closing the recordset and connection, then setting both to Nothing, releases COM object references and prevents memory leaks.

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

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

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 25: You need to build a VBA solution that reads configuration settings (database connections, thresholds) that change periodically without modifying the VBA code. What is the best design pattern?

Store settings in a dedicated 'Config' worksheet or a separate INI/XML file and read them at runtime (Correct answer)
Hardcode all settings as Public constants in the module
Embed settings in the macro's comment block for the user to edit
Use Application.InputBox each time the macro runs

Correct answer: Store settings in a dedicated 'Config' worksheet or a separate INI/XML file and read them at runtime

Externalizing configuration to a worksheet or file decouples settings from code, allowing updates without opening the VBA editor.

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

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

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 27: How do Excel VBA professionals build trust with clients or stakeholders?

Through competitive pricing only
Through consistent competence, transparency, reliability, and ethical behavior (Correct answer)
By always agreeing with clients
Through marketing only

Correct answer: Through consistent competence, transparency, reliability, and ethical behavior

This is fundamental to Excel VBA practice. Through consistent competence, transparency, reliability, and ethical behavior represents the professional standard for communication in the Excel VBA certification framework.

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

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

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

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

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 30: What does the Transpose function do when used with VBA Range data?

Removes duplicate values
Converts rows to columns and columns to rows (Correct answer)
Sorts data in reverse order
Formats data as a table

Correct answer: Converts rows to columns and columns to rows

Transpose switches the orientation of an array or range, turning rows into columns and vice versa.

Question 31: How do you run another macro from within a VBA procedure?

Exec "MacroName"
Call MacroName or just MacroName (Correct answer)
Run.Macro "MacroName"
Application.RunMacro "MacroName"

Correct answer: Call MacroName or just MacroName

You can invoke another subroutine using the Call keyword followed by the name, or simply write the name with arguments.

Question 32: How do you declare a dynamic array in VBA?

Dim arr As Array
Dim arr(10) As Integer
Dim arr() As Integer (Correct answer)
Dim arr[10] As Integer

Correct answer: Dim arr() As Integer

A dynamic array is declared with empty parentheses (Dim arr() As Integer) and resized later with ReDim.

Question 33: What is reflective practice in Excel VBA professional development?

Only reflecting on successes
Avoiding past mistakes
Systematically examining experiences to gain insight and improve future practice (Correct answer)
Writing personal diaries

Correct answer: Systematically examining experiences to gain insight and improve future practice

This is fundamental to Excel VBA practice. Systematically examining experiences to gain insight and improve future practice represents the professional standard for practical in the Excel VBA certification framework.

Question 34: To ensure a risk scoring formula recalculates only when relevant inputs change—not on every worksheet recalculation—a VBA UDF should avoid:

Using WorksheetFunction calls inside the UDF
Calling Application.Volatile without necessity (Correct answer)
Returning a Double data type
Declaring the function as Public

Correct answer: Calling Application.Volatile without necessity

Application.Volatile marks a UDF to recalculate on every worksheet change, adding overhead; omitting it allows Excel to recalculate only when the function's direct inputs change.

Question 35: When scraping web-based research data into Excel via VBA, which object is used to make HTTP requests?

Scripting.FileSystemObject
MSXML2.XMLHTTP (Correct answer)
WScript.Shell
InternetExplorer.Application

Correct answer: MSXML2.XMLHTTP

MSXML2.XMLHTTP (or MSXML2.ServerXMLHTTP) sends HTTP GET/POST requests to retrieve web data programmatically within VBA.

Question 36: What is the key difference between 'Step Into' (F8) and 'Step Over' (Shift+F8) during VBA debugging?

Step Into handles errors automatically; Step Over skips them
Step Into enters called procedures line by line; Step Over executes them as a single step (Correct answer)
Step Into runs all code at once; Step Over runs one line at a time
Step Over is faster than Step Into in all scenarios

Correct answer: Step Into enters called procedures line by line; Step Over executes them as a single step

'Step Into' descends into any called Sub or Function to debug it, while 'Step Over' executes the called procedure entirely without entering it.

Question 37: In VBA, what keyword prevents a variable declared in a standard module from being accessed by other modules?

Dim
Local
Friend
Private (Correct answer)

Correct answer: Private

Private restricts the variable's scope to the module in which it is declared, hiding it from all other modules.

Question 38: A VBA solution supports COSO Internal Control framework documentation. Which feature best supports the Control Activities component?

Implementing segregation of duties by restricting data entry, review, and approval actions to separate user roles enforced in VBA (Correct answer)
Removing all input validation to speed up data entry
Using a single shared login for all finance team members
Allowing all users to override calculated values with manual entries

Correct answer: Implementing segregation of duties by restricting data entry, review, and approval actions to separate user roles enforced in VBA

COSO's Control Activities component includes segregation of duties; VBA user-role enforcement ensures that no single person can initiate, record, and approve the same transaction.

Question 39: Which statement is used to enable error handling and redirect execution to a labeled section in Excel VBA?

Catch Error
On Error GoTo (Correct answer)
Try...Catch
Error Handle

Correct answer: On Error GoTo

'On Error GoTo' is the VBA statement that redirects execution to a specified error handler label when a runtime error occurs.

Question 40: In VBA, what isn't a decision statement?

if.. Else statement
if.. Elseif..Else statement
None of the above (Correct answer)
if statement

Correct answer: None of the above

VBA includes several decision statements to control program flow based on conditions. These include the `If...Then` statement, `If...Then...Else` statement, and `If...Then...ElseIf...Else` statement. All the options A, B, and C are valid forms of decision statements in VBA. Therefore, "None of the above" is the correct answer, implying all listed options *are* decision statements.

Question 41: A VBA procedure writes risk assessment results to a closed workbook using early binding to Excel.Application. What must be true for this to work reliably?

A reference to the Microsoft Excel Object Library must be set in the VBA project (Correct answer)
The target workbook must be password-protected
The workbook must be stored on a network drive
The Excel version on both machines must match exactly

Correct answer: A reference to the Microsoft Excel Object Library must be set in the VBA project

Early binding requires a reference to the target type library so VBA can resolve object types at compile time, not just runtime.

Question 42: Which VBA statement exits a For...Next loop immediately?

Stop
Exit For (Correct answer)
End For
Break

Correct answer: Exit For

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

Question 43: Which approach correctly prevents an Excel Add-in macro from appearing in the user-facing Macro dialog box?

Prefix the Sub name with an underscore
Declare the Sub as Private (Correct answer)
Use the Hidden attribute in the VBA project
Store it in a Class Module

Correct answer: Declare the Sub as Private

Private procedures are not listed in the Macro dialog, effectively hiding internal helper routines from end users.

Question 44: 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 45: Which VBA property disables automatic recalculation during a macro for performance?

Application.Calculation = xlManual (Correct answer)
Application.AutoCalc = False
Application.FormulaUpdate = False
Application.RecalcMode = xlOff

Correct answer: Application.Calculation = xlManual

Setting Application.Calculation to xlManual prevents Excel from recalculating formulas after every cell change.

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

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

Correct answer: The first valid index

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

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

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

Correct answer: Whether worksheet and workbook event procedures fire automatically

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

Question 48: A GDPR compliance officer asks you to implement a 'right to erasure' workflow in Excel VBA. What should the macro do?

Clear only the Name column for the requested subject
Hide rows containing the data subject's information
Replace the subject's name with 'DELETED' as a placeholder
Locate all rows referencing the data subject's ID and permanently delete their personal data across all worksheets (Correct answer)

Correct answer: Locate all rows referencing the data subject's ID and permanently delete their personal data across all worksheets

GDPR's right to erasure (Article 17) requires complete removal of a data subject's personal data from all locations, not partial deletion or obfuscation.

Question 49: When distributing a macro-enabled workbook to end users, which security step is considered professional best practice?

Saving the file as .xlsx to hide the macros
Embedding the macro password in a worksheet cell
Removing all error handling so errors are visible
Digitally signing the VBA project with a trusted certificate (Correct answer)

Correct answer: Digitally signing the VBA project with a trusted certificate

Digitally signing VBA projects allows users to verify the code's origin and enables macro trust without disabling security entirely.

Question 50: A client wants a VBA macro that protects all sheets in a workbook with one password but still allows the macro itself to make edits programmatically. What is the correct approach?

In the macro, call Sheet.Unprotect with the password before edits and Sheet.Protect with the password after edits (Correct answer)
Use AllowFiltering:=True on each sheet
Use WorksheetFunction.Protect to bypass the restriction
Store the password in a public variable and check it at runtime

Correct answer: In the macro, call Sheet.Unprotect with the password before edits and Sheet.Protect with the password after edits

VBA can call Unprotect and Protect with the password string to temporarily lift and restore sheet protection around programmatic edits.

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