Excel VBA Case Studies & Practical Application 4

Question 1

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?

Accepted 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

Answer

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

Question 2

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

Accepted Answer

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

Answer

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

Question 3

A macro that applies formatting to 5,000 cells runs slowly. Which two settings should be toggled at the start and end of the macro to improve speed?

Accepted Answer

Application.ScreenUpdating and Application.Calculation

Answer

Setting ScreenUpdating = False prevents screen redraws and setting Calculation = xlCalculationManual prevents formula recalculation during the loop, both significantly reducing runtime.

Question 4

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?

Accepted Answer

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

Answer

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 5

A purchasing team needs a macro to compare two lists of supplier codes and highlight codes in List A that are NOT in List B. Which VBA approach is most efficient for large lists?

Accepted Answer

Load List B into a Scripting.Dictionary for O(1) lookups, then iterate List A and check Dictionary.Exists for each code

Answer

A Dictionary provides O(1) key existence checks, making the comparison of large lists far faster than nested loops which are O(n²).

Excel VBA Practice Test

Excel VBA Practice Test

Excel VBA Case Studies & Practical Application 4