Excel VBA Excel VBA Automation & Macros 2 — Questions and Answers
Question 1: What is the purpose of the On Error GoTo statement in VBA?
- Skips the next line of code
- Redirects execution to an error-handling label when a runtime error occurs (Correct answer)
- Logs errors to a file automatically
- Converts errors to warnings
Correct answer: Redirects execution to an error-handling label when a runtime error occurs
On Error GoTo Label redirects the execution flow to the specified label when a runtime error is encountered.
Question 2: Which VBA object represents the currently active worksheet?
- ActiveSheet (Correct answer)
- CurrentSheet
- ThisSheet
- FocusedSheet
Correct answer: ActiveSheet
ActiveSheet is the VBA property that references whichever worksheet currently has focus in the workbook.
Question 3: How do you open a file dialog box in VBA to let the user select a file?
- FileOpen.Show()
- Application.GetOpenFilename() (Correct answer)
- Shell.OpenFileDialog()
- Dialogs.ShowOpen()
Correct answer: Application.GetOpenFilename()
Application.GetOpenFilename() displays the standard Windows Open dialog and returns the selected file path.
Question 4: What is the VBA method to close a workbook without saving changes?
- Workbook.Exit(False)
- Workbook.Close SaveChanges:=False (Correct answer)
- Workbook.Quit(NoSave)
- Workbook.Discard()
Correct answer: Workbook.Close SaveChanges:=False
Workbook.Close SaveChanges:=False closes the workbook and discards any unsaved modifications.
Question 5: Which VBA statement pauses a macro for a specified number of seconds?
- Application.Sleep()
- Wait.Pause()
- Application.Wait Now + TimeValue("00:00:05") (Correct answer)
- Delay(5)
Correct answer: Application.Wait Now + TimeValue("00:00:05")
Application.Wait pauses execution until the specified time, and adding a TimeValue offset creates an effective delay.
Question 6: How do you protect a worksheet using VBA so users cannot edit cells?
- Worksheet.Lock(Password)
- Worksheet.Protect Password:="pass" (Correct answer)
- ActiveSheet.EnableProtection(True)
- Sheet.SetProtection("pass")
Correct answer: Worksheet.Protect Password:="pass"
Worksheet.Protect with an optional password argument enables protection to prevent unauthorized cell edits.
Excel VBA Excel VBA Automation & Macros 2
What is the purpose of the On Error GoTo statement in VBA?