Advanced Excel, Macros and VBA

Here are some important questions and answers for advanced excel professionals.

Q.1 Explain the security vulnerabilities for VBA?
The Microsoft Visual basic Application (VBA) has its host of problems one of them being macros. Macros has been created that can built a havoc for a programmer with good intentions. Together with the security issue rests in the hands of the user and not the programmer as such. Such that a user can manipulate the VBA document.
Q.2 What do you understand by Line Option Explicit?
The line explicit function that makes the compiler to identify all the variables that are not specified by the dim statement. Such that this command significantly reduces the problem of type errors. This is used extensively since VBA deals with information rich applications in which the type errors are common.
Q.3 How can we format Expressions by using VBA?
Primarily, the format functions are used to format many of the expressions such as currency, time, date, percentages and numbers. Such that these functions are very simpler to use in VBA. Also user defined date, numeric and string formats are present in most of the applications.
Q.4 What forms are recommended for complex user input?
The userforms should be used for complex user input. Therefore when an application input has non-text as an input, it is suggested to use Userforms rather than the input boxes. When we are using Userforms a user can give input any number of times that is not the case when we use input boxes. This is accessible from the insert menu in your VBA editor after which you can insert your code.
Q.5 How should we use data validation function in VBA?
Data validation is one of the most important concept in VBA. He application procedure and a custom dialog box is used to correct input errors. In which case we can assign an application procedure to the frame of a dialog box. Such that error message or custom dialog box can be highlighted with the first field with errors.
Q.6 What do you understand by option explicit and option base?
Option Explicit generally makes the declaration of Variables Mandatory while Option Base is used at module level to declare the default lower bound for array subscripts. For instance Option Base 1 will make the array lower bound as 1 instead of 0.
Q.7 Differentiate between Activeworkbook and Thisworkbook.
ThisWorkbook indicates workbook where code is being written while ActiveWorkbook indicates to the workbook which is in active state with active window. But in case of only one workbook open, ActiveWorkbook is same as ThisWorkbook.
Q.8 Differentiate between Functions and Subroutines?
The subroutines never return a value but functions does return values. On the other hand a function could not change the values of actual arguments whereas a subroutine could change them.
Q.9 What kind of Workbook Protection are available?
Excel primarily provides three ways to protect a workbook -
1. It requires a password to open the workbook.
2. It prevents users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets.
3. It prevents users from changing the size or position of windows.
Q.10 What is VBA or Visual Basic for Applications?
VBA: it stands for Visual Basic for Applications; it's an event driven programming language developed by Microsoft. Primarily it's used with Microsoft office applications like MS-word, MS-Access, and MS-Excel.
Q.11 Mention where you can write your VBA program for Macro?
Module is the place where VBA program can be written for Macro, to insert a Module navigate to Insert -> Module
Q.12 State what are the comments style used in VBA?
Comments are used to document the program logic and the user information with which other programmers can work seamlessly on the same code in the future. There are predominantly two methods in VBA to represent comments.
1.) Any statement that begins with a single quote is treated as comment
2.) Or you can use statement REM instead of single quotation mark (‘)
Q.13 How can declare variable and constant be proclaimed in the VBA?
In VBA, variable can be proclaimed with the keyword “DIM” while constant is declared with keyword “Const.”
Q.14 Explain what is ADO, ODBC and OLEDB?
ADO: ActiveX Data Objects or ADO: It's a universal data access framework that encompasses the functionality of DAO
ODBC: Open Database Connectivity or ODBC: It's a technology that enables a database client application connect to an external database
OLEDB: It's a low-level programming interface designed to access a wide variety of data access object linking and embedding (OLE).
Q.15 Explain about operating pointers in VBA?
The VBA (Visual Basic Information) have versatile applications, but there is a limitation to a function pointer in VBA. Windows API has inadequate support for operate pointers as a result of it's the power to use the operate however not the useful support to decision back the operate. it's built-in support for the decision however not for a asking.
Q.16 In VBA how “reference counting” is done?
In VBA, presently a variable goes out of scope, the reference counter on the reference object is decremented. once you assign the article regard to another variable, the reference counter is incremented. While once your reference count reaches to zero it terminates the event.
Q.17 How are you able to decrease the reference counter explicitly?
To decrease the reference counter expressly, you would like to line a variable to “Nothing”.
Q.18 Explain what's COM (Component Object Model) objects in VBA?
COM objects area unit sometimes .dll files, and area unit compiled possible programs.
Q.19 Explain how are you able to dial a number through VBA?
In order to dial a number through VBA, you would like to follow the subsequent steps
Shell command in VBA will be wont to begin the dialler gift in Windows O.S
To connect to your electronic equipment, number will be used
With the assistance of send keys and shell command, you'll dial to your user
Send key dictate the windows to dial in line with the keystrokes of the applying whereas Shell activates the Windows application
A macro will be wont to initiate the cardboard file program that triggers the automobile dialler feature.
Q.20 Explain what's the meaning of “Option Explicit”? And where should it to used?
“Option Explicit” makes the declaration of variables necessary. Line specific operate makes the compiler to work out all the variables that aren't declared by the dim statement. This command diminishes the matter of sort errors considerably. it's employed in VBA as a result of it deals with info made application within which sort errors area unit common. Before initiating any, sub-procedures it will be used inside a module.
Q.21 Explain how are you able to pass arguments to VBA functions?
When arguments area unit passed to VBA functions, they will be passed in 2 ways:
ByVal: once argument is gone by worth, then it implies that solely worth is passed to the procedure, and any changes that area unit created to the argument within the procedure are lost once the procedure is exited.
Q.22 Mention the tactic that area unit referred to as from the Object Context object to apprise MTS that the dealing was unsuccessful or successful?
Setabort and set complete technique area unit referred to as from the Object Context object to apprise MTS that the dealing was unsuccessful or unsuccessful.
Q.23 What is the code to search out a final used Row during a column or last used Column of a row?
To find the last row during a column, the command used is End(xlUp) and to search out last column during a row, the command used is End(xlToLeft).
Q.24 Mention the distinction between the Subroutines and Functions in VBA?
The distinction between the Subroutines and Functions is that
Subroutines ne'er come a worth, however functions will come values
Subroutines might amendment {the worth the worth}s of the particular arguments whereas a operate couldn't amendment the value of actual arguments
Q.25 Explain what's the distinction between Current Region properties and Used Range?
Current Region: this region could be a vary finite by any combination of blank columns and rows
Used Range: This property is employed to pick out the vary of used cells on a worksheet. It returns {a vary variety a spread} object that represents the used range on the precise worksheet.
Q.26 Explain the way to correct a VBA code?
To correct a VBA code, follow the steps:
Using Breakpoints (F9)
Step by step execution (F8)
Print & Immediate Window and Watch the window
Q.27 How are you able to stop VBA script once it goes into the infinite loop?
By pressing Cntrl+ Pause Break key one will stop VBA script once it goes into infinite loop.
Q.28 Mention that property of menu that cannot be set at runtime?
Name property of menu can not be set, at the runtime.
Get Govt. Certified Take Test