Excel VBA Communication & Stakeholder Relations 2 — Questions and Answers
Question 1: A stakeholder requests that your VBA macro send them an automated email summary after each run. Which Outlook object would you use to create and send the email?
- Outlook.MailItem (Correct answer)
- Outlook.Contact
- Outlook.Folder
- Outlook.AddressEntry
Correct answer: Outlook.MailItem
Outlook.MailItem represents an email message and exposes properties like To, Subject, Body, and the Send method.
Question 2: Your manager wants a pop-up notification when a macro finishes processing. Which VBA statement displays a simple message box with just an OK button?
- MsgBox "Done!", vbOKOnly (Correct answer)
- InputBox "Done!"
- MsgBox "Done!", vbYesNo
- Debug.Print "Done!"
Correct answer: MsgBox "Done!", vbOKOnly
MsgBox with vbOKOnly displays a dialog with a single OK button, suitable for a simple completion notification.
Question 3: A stakeholder needs your macro to log activity to a shared status cell so others can monitor progress. Which approach writes text to cell A1 on a sheet named 'Log'?
- Sheets("Log").Range("A1").Value = "Processing..." (Correct answer)
- Sheets("Log").Range("A1").Formula = "Processing..."
- Log.Cells(1,1).Write "Processing..."
- ActiveSheet.Log("A1") = "Processing..."
Correct answer: Sheets("Log").Range("A1").Value = "Processing..."
Setting the .Value property of a Range object writes text directly to the specified cell.
Question 4: You need to present a dynamic dashboard to executives and want your VBA macro to automatically refresh all pivot tables before they open the file. Which method refreshes all pivot tables in a workbook?
- ThisWorkbook.RefreshAll (Correct answer)
- ActiveWorkbook.PivotTables.Update
- Worksheets.RefreshPivots
- Application.CalculateAll
Correct answer: ThisWorkbook.RefreshAll
ThisWorkbook.RefreshAll refreshes all data connections and pivot tables in the workbook at once.
Question 5: A client asks your macro to display a custom dialog box asking for their project name before running. Which VBA function returns user-typed text from a dialog?
- InputBox() (Correct answer)
- MsgBox()
- TextBox()
- UserInput()
Correct answer: InputBox()
InputBox() displays a prompt dialog and returns the string the user types as its return value.
Question 6: When distributing a macro-enabled workbook to stakeholders, which file format preserves VBA code while also being recognized as macro-enabled by Excel?
- .xlsm (Correct answer)
- .xlsx
- .xls
- .xlsb
Correct answer: .xlsm
.xlsm is the Excel Macro-Enabled Workbook format that stores VBA code and clearly signals to users that macros are present.
Question 7: Your stakeholder wants the macro to write a summary report to a separate sheet named 'Report' and then navigate the user to that sheet automatically. Which line of code activates the 'Report' sheet?
- Sheets("Report").Activate (Correct answer)
- Sheets("Report").Select(True)
- Worksheets.GoTo "Report"
- ActiveWorkbook.View("Report")
Correct answer: Sheets("Report").Activate
Sheets("Report").Activate makes the specified sheet the active sheet, bringing it into view for the user.
Excel VBA Communication & Stakeholder Relations 2
A stakeholder requests that your VBA macro send them an automated email summary after each run.
Which Outlook object would you use to create and send the email?