Excel VBA Technology & Digital Applications 2 — Questions and Answers
Question 1: Which VBA method is used to send an HTTP GET request using the MSXML2.XMLHTTP object?
- Open (Correct answer)
- Send
- Get
- Fetch
Correct answer: Open
The Open method initializes a request on MSXML2.XMLHTTP, specifying the method (GET/POST) and URL before calling Send.
Question 2: When automating a web browser with VBA using Internet Explorer automation, which library reference must be enabled?
- Microsoft Internet Controls (Correct answer)
- Microsoft Web Browser
- Microsoft HTML Object Library
- Microsoft Shell Controls
Correct answer: Microsoft Internet Controls
Microsoft Internet Controls (shdocvw.dll) provides the InternetExplorer object used for IE automation in VBA.
Question 3: In VBA, which function converts a JSON-like string value retrieved from a web API into a usable number?
- CDbl (Correct answer)
- Val
- CStr
- Asc
Correct answer: CDbl
CDbl converts a string representation of a number to a Double data type, suitable for numeric calculations after parsing API responses.
Question 4: What VBA statement is used to create a new instance of a late-bound COM object without a reference set?
- CreateObject("ProgID") (Correct answer)
- New Object("ProgID")
- Set Obj = ProgID()
- GetObject("ProgID")
Correct answer: CreateObject("ProgID")
CreateObject creates a new instance of a COM object using its ProgID string, enabling late binding without requiring a library reference.
Question 5: Which Excel VBA object model class allows you to interact with Power Query (Get & Transform) connections programmatically?
- WorkbookQuery (Correct answer)
- QueryTable
- PowerQuery
- ListObject
Correct answer: WorkbookQuery
The WorkbookQuery object (accessed via Workbook.Queries collection) represents a Power Query query defined in the workbook.
Question 6: What is the correct VBA syntax to add a new worksheet and position it after the last existing sheet?
- Sheets.Add After:=Sheets(Sheets.Count) (Correct answer)
- Sheets.Add(Last:=True)
- Worksheets.AddNew(Position:=Last)
- Sheets.Insert(After:=Sheets.Last)
Correct answer: Sheets.Add After:=Sheets(Sheets.Count)
Sheets.Add with the After parameter set to Sheets(Sheets.Count) inserts the new sheet after the last sheet in the workbook.
Question 7: When using VBA to write data to a CSV file, which statement opens a file in output mode and assigns it a file number?
- Open filePath For Output As #1 (Correct answer)
- File.Open(filePath, Output, 1)
- OpenFile filePath, #1, Output
- Set f = OpenText(filePath)
Correct answer: Open filePath For Output As #1
The VBA Open statement with 'For Output As #n' opens a file for sequential writing, assigning it the specified file number for subsequent Print or Write statements.
Excel VBA Technology & Digital Applications 2
Which VBA method is used to send an HTTP GET request using the MSXML2.XMLHTTP object?