Excel VBA Case Studies & Practical Application 4 — Questions and Answers
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?
- Use the Split function to tokenize the JSON string manually
- 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)
- Convert JSON to XML first using a formula, then use MSXML DOM
- 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 2: 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)
- Use DDE (Dynamic Data Exchange) to push data to SQL Server
- Copy data to the clipboard and paste into SQL Server Management Studio
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 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?
- Application.EnableEvents and Application.DisplayAlerts
- Application.ScreenUpdating and Application.Calculation (Correct answer)
- Application.Interactive and Application.AutomationSecurity
- Workbook.AutoSaveOn and Application.EnableCancelKey
Correct 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.
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?
- The users have a different version of Windows
- 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'
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 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?
- Use nested For loops comparing each item in List A against every item in List B
- Load List B into a Scripting.Dictionary for O(1) lookups, then iterate List A and check Dictionary.Exists for each code (Correct answer)
- Use the worksheet's Remove Duplicates feature via VBA
- Sort both lists and use a VLOOKUP formula inserted by VBA
Correct 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²).
Question 6: 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?
- Use Integer variables instead of Double for all calculations
- Round intermediate results to a fixed number of decimal places using the Round() function, or use the Currency data type for fixed-decimal arithmetic (Correct answer)
- Multiply all values by 1000 and work in integers only
- Set Application.Precision = 15 before the calculation
Correct 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.
Question 7: 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?
- Run a Do...Loop with Dir() on a timer using Application.OnTime to poll the folder at regular intervals
- Use the FileSystemObject's WatchFolder event directly in VBA
- Use Windows Task Scheduler to run a batch file that triggers the macro
- Both A and C are viable approaches for different deployment scenarios (Correct answer)
Correct 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.
Excel VBA Case Studies & Practical Application 4
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?