Excel VBA Communication & Stakeholder Relations 4 — Questions and Answers
Question 1: A stakeholder wants your macro to export the summary sheet as a PDF for distribution. Which method saves a worksheet as a PDF in VBA?
- Sheets("Summary").ExportAsFixedFormat xlTypePDF, "C:\report.pdf" (Correct answer)
- Sheets("Summary").SaveAs "C:\report.pdf", xlPDF
- Sheets("Summary").Print.PDF "C:\report.pdf"
- Workbook.ExportPDF Sheets("Summary"), "C:\report.pdf"
Correct answer: Sheets("Summary").ExportAsFixedFormat xlTypePDF, "C:\report.pdf"
ExportAsFixedFormat with xlTypePDF exports the sheet as a PDF to the specified file path.
Question 2: Your macro displays a MsgBox asking stakeholders to confirm before deleting data. Which MsgBox argument combination shows Yes and No buttons and returns vbYes when Yes is clicked?
- MsgBox "Delete?", vbYesNo returns vbYes (Correct answer)
- MsgBox "Delete?", vbOKCancel returns vbOK
- MsgBox "Delete?", vbRetryCancel returns vbRetry
- MsgBox "Delete?", vbYesNoCancel returns vbOK
Correct answer: MsgBox "Delete?", vbYesNo returns vbYes
vbYesNo shows Yes and No buttons, and clicking Yes returns the constant vbYes (value 6).
Question 3: Stakeholders complain that when your macro runs, Excel shows the formula bar and status bar changing rapidly, which is distracting. Besides ScreenUpdating, which property hides status bar messages from your macro?
- Application.DisplayStatusBar = False
- Application.StatusBar = False (Correct answer)
- Application.StatusBar = ""
- Application.HideStatusBar = True
Correct answer: Application.StatusBar = False
Setting Application.StatusBar = False restores the default status bar text, while setting it to False explicitly clears any macro-set text.
Question 4: A client wants to know when your macro last ran. Which approach stamps the current date and time into a cell each time the macro executes?
- Range("A1").Value = Now() (Correct answer)
- Range("A1").Value = Date()
- Range("A1").Formula = "=NOW()"
- Range("A1").Timestamp = Now()
Correct answer: Range("A1").Value = Now()
Now() returns the current date and time as a VBA value; assigning it to .Value stores it as a static timestamp.
Question 5: Your stakeholder needs a macro that prompts for a password before granting access to the reporting sheet. Which VBA approach hides typed characters in an InputBox?
- VBA's standard InputBox does not mask input; use a custom UserForm with a TextBox set to PasswordChar = "*" (Correct answer)
- InputBox("Password:", PasswordChar:="*")
- MsgBox InputBox("Enter password:", , , , , , 1)
- Application.InputBox("Password:", Type:=8, Hidden:=True)
Correct answer: VBA's standard InputBox does not mask input; use a custom UserForm with a TextBox set to PasswordChar = "*"
Standard VBA InputBox cannot mask characters; a UserForm TextBox with PasswordChar = "*" is the correct way to accept hidden input.
Question 6: You want your VBA report macro to CC the CFO automatically on all generated emails. Which Outlook MailItem property sets the CC recipients?
- .CC = "cfo@company.com" (Correct answer)
- .CarbonCopy = "cfo@company.com"
- .Recipients.AddCC "cfo@company.com"
- .AddCC = "cfo@company.com"
Correct answer: .CC = "cfo@company.com"
The .CC property on a MailItem accepts a semicolon-delimited string of email addresses for carbon-copy recipients.
Question 7: A stakeholder finds your macro hard to use because it offers no progress indication during a long loop. Which technique updates a status message in the Excel status bar during processing?
- Application.StatusBar = "Processing row " & i & " of " & total (Correct answer)
- MsgBox "Processing row " & i
- Debug.Print "Row " & i
- Cells(1,1).Value = "Row " & i
Correct answer: Application.StatusBar = "Processing row " & i & " of " & total
Setting Application.StatusBar to a string displays it in Excel's status bar without interrupting macro execution.
Excel VBA Communication & Stakeholder Relations 4
A stakeholder wants your macro to export the summary sheet as a PDF for distribution.
Which method saves a worksheet as a PDF in VBA?