Most Asked Excel VBA Interview Questions and Answers
How can you prevent a user for adding or deleting sheets?
You need to protect the workbook’s structure.
Excel 2003 –>
Select Tools – Protection – Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked.
Excel 2007/2010 –>
Go to Review –> Click ‘Protect Workbook’ –> Click ‘Protect Structure and Windows’
If you specify a password, that password will be required to unprotect the workbook. When a workbook’s structure is protected, the user may not:
* Add/Delete a sheet
* Hide/Unhide a sheet
* Rename a sheet
* Move a sheet
What types of workbook protection are available?
Excel provides three ways to protect a workbook:
* Require a password to open the workbook
* Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
* Prevent users from changing the size or position of windows
The Font dialog box allows you to select different Fonts, their style, their size, and some other special effects. How do you bring up this Font dialog box?
Use Application.Dialogs(xlDialogFont).Show or Application.Dialogs(xlDialogFormatFont).Show to load font dialog box from Excel VBA.
What is ADO, OLEDB & ODBC?
ADO : ActiveX Data Objects is universal data access framework that encompasses the functionality of DAO.
ODBC : Open Database Connectivity(ODBC) is a windows technology that lets a database client application connect to a external database.
OLEDB : Low level programming interface designed to access a wide variety of data access Object Linking and Embedding (OLE).
How to set the custom paper size in Excel Object through VB?
Activesheet.PageSetup.PaperSize = xlPaperLetter (Similarly xlPaperA4 or xlPaperLegal etc.)
What is the method for returning more than one values from a function in VB?
Any of the three methods can be used:
i) Create a class with the properties you require to return and then return the object of the class from the function.
ii) Using ByRef for the values.
iii) Return an array of the values.
Does VBA supports OOP principles?
Yes because VBA is VB6.0 based which is an Object Based Programming Language and is also known as ‘Event Driven Programming’ and it supports Polymorphism, Encapsulation and partially Inheritance.
To set the command button for ESC, Which property needs to be changed?
Set Cancel property of Button to True on the Form.
What is Type Library and its purpose?
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. You can use them through ‘Tools –> References’ in VBE.
How do you use the Getsetting and Savesetting functions to read and write registry settings?
A computer registry can be used to store configuration settings and application initialization. We can use Getsetting function to read registry settings and save settings function to write registry settings. Application name, section, key, setting, and default are to be specified for registry modifying. It is advisable to know about your computer settings before modifying registry settings.
What is a Variant, what the pros and cons of its use?
Variant data type is able to hold any other data type, including numbers, strings, dates, and object references. A Variant’s descriptor is only 16 bytes long (4 short words for the type, and 2 long words for the data, or data pointer).
Pros: You cannot use Null with any variable type other than Variant.
You don’t need to worry about what you have declared a variable as.
When a Variant has been declared but not assigned a value, it contains the special value Empty.
Cons: A developer may not remember and misuse a variable assigning any value to it which will be type-casted without errors.
Give technical reasons which made Microsoft withdraw its support for VBA in Mac?
The reasons which made Microsoft drop its support to VBA are as follows, Microsoft visual basic relies heavily on machine code which was written for Power PC architecture. Also it would take another two years for developing VBA support for its architecture. It also states that Microsoft will incorporate VBA in the next script of office release for Mac.
What is a volatile function?
Volatile functions are a type of function that will always recalculate. That means whenever Excel needs to calculate any part of the worksheet, those cells containing volatile functions will also calculate.
Give some examples of Volatile function.
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()
Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()
Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.
A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()
and CELL(“Filename”) IS volatile although a MSKBN article says its not.
One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:
=SUMIF(A1:A4,”>0″,B1) is volatile whereas =SUMIF(A1:A4,”>0″,B1:B4) is not volatile.
How do you make a UDF volatile?
By adding Application.Volatile statement to it. It must be the first line of your User Defined Function.
Is it possible to apply ‘Application.Volatile(False)’ to a volatile public function like INDEX and make it not volatile?
Actually INDEX is not a volatile function, even though some MicroSoft documentation says it is. Anyway no its not possible to apply Application.Volatile(False) to a built-in Excel function except by duplicating what the built-in function does inside a UDF.