VBA Interview Questions and Answers

by Nithyanandham, on Sep 10, 2022 4:36:10 PM

Q1. What is Visual Basic for Applications(VBA)?

Ans: VBA stands for Visual Basic for Applications; it is an event driven programming language developed by Microsoft. It is predominantly used with Microsoft office applications like MS-word, MS-Access, and MS-Excel.

Q2. What are Data-types?

Ans: Data types helps to declare Variables with specific data, this helps to VBA to know the type of the data and assign the memory based on the DataType of the Variable.

Q3. What are Scope of Variables?

Ans: We can define variable in different levels:

Local Level: Variables which are defined with DIM statement in a procedure or functions
Module Level: Which are defined with DIM statement on top of a module, can be accessed in entire module
Global Level: Which are defined Public statement at top of any module, can be accessed in entire project.

Q4. What is the difference between Msgbox Statement and MsgboxQ function?

Ans: MsgBox is a built in VB function which displays a Message Box and MsgBoxQ is a function defined by the user.

Q5. Where you can write your VBA program for Macro?
Ans: vModule is the place where you can write VBA program for Macro, to insert a Module navigate to Insert -> Module

Q6. What are the four different cursor and locking types in ADO and describe them briefly?

Ans: Coursor types:

Forwardonly
Static
Keyset
Dyanmic

Lock types :

lockpessimistic
lockoptimistic
lockbatchoptimistic
Lockreadonly

Q7. How “reference counting” in VBA is done?

Ans: In VBA, soon a variable goes out of scope, the reference counter on the reference object is decremented. When you assign the object reference to another variable, the reference counter is incremented. While when your reference count reaches to zero it terminates the event.

Q8. Which controls have refresh method, clear method?
Datagrid,listbox,combobox,label,button have refresh methodand textbox has clear methodalmost all controls have refresh method

Q9. Which controls can not be placed in MDI?

Ans: The Controls which do not have Align property can't be placed on MDI Form.
E.g., Picturebox only has Align property in Standard Components of VB other controls dont have align property an hence can't be drawn on MDI Form.

Timer control can also be placed on MDI form.

Q10. How we can declare variable and constant in VBA?

Ans: In VBA, variable can be declared with the keyword “DIM” while constant is declared with keyword “Const.”

Q11. What is insert module and Goal Seek functions present in VBA?

Ans: The chief use of VBA is to make use of its special function which helps in repeated actions. Goal seek function helps to reduce manual entry of the code each and every time. This solves the problem of repeated function entry by automating functions and actions. Sub routines are inserted into the using the VBA editor and command insert module.

Q12. How to format expressions by using VBA?

Ans: Format functions can be used to format many of the expressions such as currency, time, date, percentages and numbers. These functions are much simpler to use in VBA. User defined date, numeric and string formats are present in many of the applications.

Q13. Explain what is ADO, ODBC and OLEDB?

Ans:

ADO: ActiveX Data Objects or ADO is a universal data access framework that encompasses the functionality of DAO
ODBC: Open Database Connectivity or ODBC is a technology that enables a database client application connect to an external database
OLEDB: It is a low-level programming interface designed to access a wide variety of data access object linking and embedding (OLE)

Q14. Mention what are the comments style used in VBA?

Ans: Comments are used to document the program logic and the user information with which other programmers can work seamlessly on the same code in future. There are mainly two methods in VBA to represent comments.

Any statement that begins with a single quote is treated as comment
Or you can use statement REM instead of single quotation mark (‘)

Q15 . Explain how can you pass arguments to VBA functions?

Ans: When arguments are passed to VBA functions, they can be passed in two ways

ByVal: When argument is passed by Value, then it means that only value is passed to the procedure, and any changes that are made to the argument inside the procedure will be lost when the procedure is exited
ByRef: When the argument is passed by reference than the actual address of the argument is passed to the procedure. Any changes that are made to the argument inside the procedure will be recalled when the procedure is exited

Q16. Explain about function pointers in VBA?

Ans: The VBA (Visual Basic Information) have flexible applications, but there is a limitation to a function pointer in VBA. Windows API has inadequate support for function pointers because it has the ability to use the function but not the functional support to call back the function. It has inbuilt support for the call but not for a callback.

Q17. What does Option Explicit refer to?

Ans: Option Explicit makes the declaration of Variables Mandatory

Q18. What is the meaning of “Option Explicit”? Where it should be used?

Ans: “Option Explicit” makes the declaration of variables mandatory. Line explicit function makes the compiler to determine all the variables that are not declared by the dim statement. This command diminishes the problem of type errors significantly. It is used in VBA because it deals with information rich application in which type errors are common. Before starting any, sub-procedures it can be used inside a module.

Q19 . How can you decrease the reference counter explicitly?

Ans: To decrease the reference counter explicitly, you need to set a variable to “Nothing”.

Q20. What is line option explicit?

Ans: Line explicit function makes the compiler to identify all the variables which are not specified by the dim statement. This command significantly reduces the problem of type errors. This is used extensively because VBA deals with information rich applications in which type errors are common.

Q21. Explain what is COM (Component Object Model) objects in VBA?

Ans: COM objects are usually .dll files, and are compiled executable programs.

Q22. Explain the difference between visual basic, VB script and visual basic applications?

Ans: Visual basic is useful if you are planning to develop your programs from scratch. This language helps you in developing Active x controls, exe files, etc.
Visual script is a powerful tool, through which you can create small scale applications on web pages, automation applications, etc. Integrated development environment is not present for VB script.
Visual basic applications are very useful in automating your existing application. VB application is useful for developing already existing applications.

Q23. Which Property is used to compress a image in image control?

Ans: Stretch

Q24. Benefit of wrapping database calls into MTS transactions?

Ans: Benefit of wrapping database calls into MTS transa...
If database calls are made within the context of a transaction, aborting the transaction will undo and changes that occur within that transaction. This removes the possibility of stranded or partial data.

Q25. What do ByVal and ByRef mean and which is the default?

Ans: If you pass an argument by reference when calling a procedure, the procedure access to the actual variable in memory. As a result, the variable's value can be changed by the procedure.
If you pass an argument by value when calling a procedure, the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.

Passing by reference is the default in VBA. If you do not explicitly specify to pass an argurment by value, VBA will pass it by reference.

Argument Passing ByVal
Describes passing arguments by value, which means the procedure cannot modify the variable itself.

Argument Passing ByRef
Describes passing arguments by reference, which means the procedure can modify the variable itself.

Q26. Mention the method that are called from the ObjectContext object to notify MTS that the transaction was unsuccessful or successful?

Ans: Setabort and setcomplete method are called from the ObjectContext object to notify MTS that the transaction was unsuccessful or unsuccessful

Q27. Explain Which property of textbox cannot be changed at runtime and what?s the maximum size of a textbox?

Ans: MultiLine Property
No limits

Q28.  What is the code to find a last used Row in a column or last used Column of a row?
Ans: To find the last row in a column, the command used is End(xlUp) and to find last column in a row, the command used is End(xlToLeft).

Q29. How to use data validation function in VBA?

Ans: Data validation is an important concept in VBA. Application procedure and a custom dialog box can be used to correct input errors. You can assign an application procedure to the frame of a dialog box. Error message or custom dialog box can be highlighted with the first field with errors.

Q30. Explain how can you dial a phone number through VBA?

Ans: In order to dial a phone number through VBA, you need to follow the following steps

Shell command in VBA can be used to start the dialer present in Windows O.S
To connect to your modem, phone number can be used
With the help of send keys and shell command, you can dial to your user
Sendkey dictate the windows to dial according to the keystrokes of the application while Shell activates the Windows application
A macro can be used to initiate the card file program which triggers the auto dialer feature

Q31. Explain Type Library and what is its purpose?

Ans: Type libraries are files that explicitly describe some or all of the contents of components. This includes information about the methods, properties, constants, and other members exposed by the component. Development tools such as Visual Basic make use of the information contained in the type library to help you, as a developer, access and use the component. In addition, type libraries provide a convenient way to include a simple level of descriptive documentation for component members.

Q32. Mention the difference between the Subroutines and Functions in VBA?

Ans: The difference between the Subroutines and Functions is that

Subroutines never return a value, but functions does return values
Subroutines could change the values of the actual arguments while a function could not change the value of actual arguments