Case Studies & Practical Application Flashcards

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?

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.

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

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.

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?

Answer: Application.ScreenUpdating and Application.Calculation

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

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?

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.

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?

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

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

A VBA macro that runs a complex calculation sometimes produces incorrect results due to floating-point precision errors (e.g., 0.1 + 0.2 ≠ 0.3). What is the best mitigation strategy?

Answer: Round intermediate results to a fixed number of decimal places using the Round() function, or use the Currency data type for fixed-decimal arithmetic

Round() can normalize floating-point drift, and the Currency type stores values as fixed-point 64-bit integers, eliminating binary floating-point errors for financial data.

An operations team needs a VBA macro that monitors a folder and automatically processes any new Excel files dropped into it. How should this be implemented?

Answer: Both A and C are viable approaches for different deployment scenarios

VBA's Application.OnTime polling with Dir() works for in-session monitoring, while Task Scheduler triggering a macro via command line suits scheduled batch processing.