Excel VBA Communication & Stakeholder Relations 3 — Questions and Answers
Question 1: A project manager wants your VBA tool to automatically add comments to cells flagging issues so reviewers can see them. Which method adds a comment to a cell in VBA?
- Range("A1").AddComment "Review this value" (Correct answer)
- Range("A1").Comment = "Review this value"
- Range("A1").Insert.Comment "Review this value"
- Cells(1,1).NewComment "Review this value"
Correct answer: Range("A1").AddComment "Review this value"
The AddComment method on a Range object inserts a new comment with the specified text.
Question 2: You are building a VBA tool for non-technical stakeholders. To prevent accidental macro modification, you should protect the VBA project with a password. Where is this setting found in the VBA IDE?
- Tools > VBAProject Properties > Protection tab (Correct answer)
- File > Project Security > Set Password
- Debug > Project Lock > Password
- Insert > Module > Protect
Correct answer: Tools > VBAProject Properties > Protection tab
In the VBA IDE, Tools > VBAProject Properties > Protection tab lets you lock the project and set a password.
Question 3: A stakeholder wants to receive a weekly Excel report generated by your macro via email with the workbook attached. Which code snippet correctly attaches the active workbook to an Outlook email?
- oMail.Attachments.Add ThisWorkbook.FullName (Correct answer)
- oMail.Attach = ThisWorkbook.Path
- oMail.File = ActiveWorkbook.Name
- oMail.AddFile ThisWorkbook.FullName
Correct answer: oMail.Attachments.Add ThisWorkbook.FullName
The Attachments.Add method accepts a file path, and ThisWorkbook.FullName provides the complete path including filename.
Question 4: To make your macro's status updates visible in real time while it runs (instead of only after it finishes), which Application method should you call periodically?
- Application.DoEvents (Correct answer)
- Application.Refresh
- Application.Update
- Application.ScreenUpdate True
Correct answer: Application.DoEvents
Application.DoEvents yields control to the Windows event loop, allowing the screen to refresh and the UI to respond during long macro runs.
Question 5: Your team lead asks why cell updates in your macro aren't visible until the macro ends. You explain you disabled screen updating for performance. Which line re-enables it?
- Application.ScreenUpdating = True (Correct answer)
- Application.Screen.Update = True
- Excel.RefreshScreen = True
- Application.Display = True
Correct answer: Application.ScreenUpdating = True
Application.ScreenUpdating = True re-enables live screen refresh after it was turned off for performance.
Question 6: A stakeholder wants the VBA macro to create a hyperlink in a cell that, when clicked, opens their team's SharePoint site. Which method inserts a hyperlink in VBA?
- ActiveSheet.Hyperlinks.Add Range("A1"), "https://sharepoint.com/team" (Correct answer)
- Range("A1").Link = "https://sharepoint.com/team"
- Range("A1").Insert.Hyperlink "https://sharepoint.com/team"
- Hyperlink.New Range("A1"), "https://sharepoint.com/team"
Correct answer: ActiveSheet.Hyperlinks.Add Range("A1"), "https://sharepoint.com/team"
ActiveSheet.Hyperlinks.Add takes an Anchor range and an Address string to insert a clickable hyperlink.
Question 7: You want your macro to notify stakeholders of an error without crashing the tool. Which VBA construct lets you handle runtime errors gracefully and show a user-friendly message?
- On Error GoTo ErrorHandler with a labeled error routine (Correct answer)
- On Error Resume Next with no additional handling
- Try...Catch block
- If Err Then MsgBox Err.Number
Correct answer: On Error GoTo ErrorHandler with a labeled error routine
On Error GoTo with a labeled handler block lets you catch errors and display a meaningful message before exiting cleanly.
Excel VBA Communication & Stakeholder Relations 3
A project manager wants your VBA tool to automatically add comments to cells flagging issues so reviewers can see them.
Which method adds a comment to a cell in VBA?