VBA Interview Questions

Thought of sharing this to the people who go for interview.

Question: What is an Object In Excel?
Answer: The term Object refers to the entities that make up a collection of objects, properties, methods, and events. This involves working in an hierarchy level.
    Properties: Properties are the characteristics of an Objects which can be measured and quantified.
    Methods: Methods are the actions that can be performed by an Object or on an Object.
    Events: An Event happens when certain actions are performed by the user or VBA.

Question: What is a Sub Procedure?
Answer: A set of commands to perform a specific task is placed into a procedure with or without Arguements. The Sub Procedure will not return a result.

Question: What is a Function?
Answer: A set of commands to perform a specific task is placed into a procedure with or without Arguements. The Function can return a result.

Question: How do you call an Excel Function in VBA? Give an Example.
Answer: Use Application.WorksheetFunction.
    Example: Application.WorksheetFunction.VLookup(123, Range(“A1:C100”), 3, FALSE)

Question: How do you hide a Worksheet using VBA?
Answer:

Sheets("<Sheet Name>").Visible = <Visibility Type>

    Visibility Type:
    xlSheetVisible (or -1) – Displays the Worksheet.
    xlSheetHidden (or 0) – Hides the Worksheet which the user can unhide via Menu or VBA.
    xlSheetVeryHidden (or 2) – Hides the Worksheet so that the only way for the user to make it visible again is by setting this property to Visible (the user cannot make the object visible either via Menu or VBA).

Question: Can you call Function in a Sub Procedure and vice versa?
Answer: Yes you can. Below is an example:

Sub sCheck001()
    MsgBox "In Sub sCheck001"
    fCheck001 'Calling Function from Sub
End Sub

Sub sCheck002()
    MsgBox "In Sub sCheck002"
End Sub

Function fCheck001()
    MsgBox "In Function fCheck001"
End Function

Function fCheck002()
    MsgBox "In Function fCheck002"
    sCheck002 'Calling Sub from Function
End Function

Sub CheckThis()
    sCheck001
    fCheck002
End Sub

Question: How can you protect/unprotect a Worksheet using VBA?
Answer:
Protect:

Sheets("<Sheet Name>").Protect "<Password>"

Unprotect:

Sheets("<Sheet Name>").Unprotect "<Password>"

Question: What is FSO?
Answer: FileSystemObject (FSO) is used to access drives, folders and files with VBA. In order to use FSO, the user should select Microsoft Scripting Runtime Reference from Tools > References… in the Visual Basic Editor Menu.

Question: What is InputBox and MsgBox?
Answer:
    InputBox: InputBox function is used to prompt the user to enter a value.
    MsgBox: MsgBox function is used to provide information to the users.

Question: What are the different types of Error Handling In VBA?
Answer:There are four types of Error Handling methods in VBA.
    1. On Error Goto 0: When error occurs, the code stops and displays the error.
    2. On Error Resume Next: Ignores the error and continues to run the code.
    3. On Error Goto <Label Name>: Goes to a specific label when error occurs.
    4. On Error Goto -1: Clears the error.

Question: What are the types of errors in VBA?
Answer: There are three types of errors.
    1. Syntax Error: Syntax Error occurs when the user types a line and hits Enter key, VBA will evaluate the line (syntax) and if it is not correct (in other words, if the keywords are missing), it will display an error message. For example, an error is displayed if the user types If statement and forgets to type Then (keyword) and hits Enter key.
    2. Compilation Error: Compilation Error occurs when the syntax is correct on on one code line but is incorrect when all the code in the project is taken into consideration.
Examples of Compilation Errors are:
        » If statement without End If.
        » For without Next.
        » Select without End Select.
        » Call a Sub Procedure or Function that does not exist.
        » Call a Sub Procedure or Function with the wrong/less/more parameters than required.
        » Giving a Sub or Function the same name as a Module/Sub Procedure/Function that is already existing in the same Module.
        » Variables that are not defined (Option Explicit is a must at the beginning of the Module).
    3. Runtime Error: Runtime Error occurs when the code is running. They are normally outside of user control but can be caused by errors in the code. Examples of Runtime Errors are:
        » Database is not available.
        » Invalid Data Entry by the user.
        » A text instead of a number in a cell.

Question: What is ByVal and ByRef?
Answer: These are used to let the Sub Procedure or Function know how the arguement is passed to them.
    ByVal: The argument is passed by Value.
    ByRef: The argument is passed by Reference.

Question: What is Optional Arguement?
Answer: The arguments that the user can pass in a Procedure if wanted. If the arguement is omitted, the Procedure will assign a default value to them.

Question: What is an Array?
Answer: An array is a type of variable used to store lists of data of the same type.

Advertisements