EXCEL VBA

Excel VBA Is Microsoft's Programming Language For Excel. Excel Is Defined By Cells And All The Other Microsoft Office Programs, Like Word And PowerPoint.

1.Explain what is VBA or Visual Basic for Applications?

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.

2.Mention where you can write your VBA program for Macro?

Module is the place where you can write VBA program for Macro, to insert a Module navigate to

Insert -> Module

3.Mention 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 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 (‘)

4.In VBA how we can declare variable and constant?

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

5.What are the data types in VBA?

Data types in VBA are classified into two categories

Numeric Data type                                   Non-numeric Data type      


Byte                                                          String (fixed length) 


 

Integer                                                     String (variable length)


 

Long                                                         Date


 

Single                                                       Boolean


 

Double                                                      Object


 

Currency                                                   Variant (numeric)               


Decimal                                                     Variant (text)


 

6.Explain what is ADO, ODBC and OLEDB?

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)

7.Explain about function pointers in VBA?

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.

8.Explain how “reference counting” in VBA is done?

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.

9.How can you decrease the reference counter explicitly?

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

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

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

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

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
  • Send key 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

12.Explain what is the meaning of “Option Explicit”? Where it should be used?

“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.

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

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

  • By Val: 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

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

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

15.What is the code to find a last used Row in a column or last used Column of a row?

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).

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

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

17.Explain what is the difference between CurrentRegion properties and UsedRange?

  • CurrentRegion: The current region is a range bounded by any combination of blank columns and rows
  • UsedRange: This property is used to select the range of used cells on a worksheet. It returns a Range object that represents the used range on the specific worksheet

18.Explain how to debug a VBA code?

To debug a VBA code, follow the steps

  • Using Breakpoints (F9)
  • Step by step execution (F8)
  • Print & Immediate Window and Watch the window

19.How can you stop VBA script when it goes into the infinite loop?

By pressing Cntrl+ Pause Break key one can stop VBA script when it goes into infinite loop.

20.Mention which property of menu cannot be set at runtime?

At runtime Name property of menu cannot be set.

21.Mention whether VBA have dictionary structure?

Yes, VBA does have a dictionary structure. You can set a reference to dictionary like

  • Set dict = CreateObject(“Scripting.Dictionary”)
  • OR Dim dict as New Scripting.Dictionary

22.Mention how can you comment and uncomment blocks of code in the office VBA editor?

In office VBA editor, you can comment and uncomment blocks of code by following steps

  • In the VBA editor go to View ->Toolbars -> Customize… OR right click on the tool bar and select Customize
  • Select the edit menu on the left, under the commands tab
  • When you scroll down you can see two icons, “Comment Block” and “Uncomment ”
  • When you drag and drop these onto your toolbar, you will have an easy access to highlight a block of code and do action on it

23.What is the difference between ByVal and ByRef and which is default ?

ByRef: 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.

ByVal: 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.

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

24.What is the meaning of Option Explicit and Option Base?

Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to
declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower
bound as 1 instead of 0.

25.Difference between ActiveWorkbook and ThisWorkbook.

ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the
workbook which is in active state with active window. In case of only one workbook open,
ActiveWorkbook is same as ThisWorkbook.

26.Code to find a Last used Row in a column or Last used column of a Row.

Last Row in a column can be find using End(xlUp) and Last Column in a row can be find using
End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.

27.Difference between ActiveX and Form Controls.

i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within
embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on
worksheets. ActiveX controls do not work in MacExcel.
ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were
used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d
be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities.
Both can link to cells and ranges in the worksheet.

28.What is the difference b/w Functions and Subroutines?

i) Subroutines never return a value but functions does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.

29.How to debug a VBA code?

Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch
window.

30.Draw basic Excel Object Model.

Application --> Workbooks --> Worksheets --> Range / Chart

31.How to hide a worksheet so that a user cannot unhide it?

Use Sheet's visible property and set it to xlSheetVeryHidden . For eg. Sheets(1).Visible =
xlSheetVeryHidden will super hide the first worksheet of the workbook.

32.Union is used for _____________ ?

To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For
eg. Set nrange = Union(rng1,rng2)

33.Which are the 2 macro languages and which do you use ?

XLM (used in Excel 97 or before) and VBA(used for 2000 and after). Obviously, VBA is in use these
days.

34.Can you lock cells such that only specific users can modify them?

There is a option "Allow users to edit ranges" can be used for this purpose.

35.How can you add a drop-down list to a cell so the user can choose a value from the list?

Using 'Data Validation'.

36.How can you increase the number of rows in a worksheet?

No one can't. They're fixed as 65536(2^16) in Excel 2003 or before and 1048576(2^20) in Excel 2007 &
Excel 2010.

37.How can you increase the number of columns in a worksheet?

No one can't. They're fixed as 256(2^8) in Excel 2003 or before and 16384(2^14) in Excel 2007 & Excel
2010.

38.How will you distribute a workbook such that it can't be copied using macro or anything?

We can create a workbook which cannot be modified but can not create a workbook which can't be
copied.(It depends on system security, it has nothing to do with Excel or VBA)

39.Your colleague created a dashboard and when you enter a value, it appears with two decimal places. For example, when you enter 265 it shows up as 2.65. What's wrong?

By chance Excel's fixed-decimal mode was turned on. To return to normal,

Excel 2003 -->
Click Tools and then Options to display the Options dialog box.
Then click the Edit tab and remove the check mark from the "Fixed decimal " option.
Excel 2007 -->
Click Office button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Excel 2010 -->
Click File button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to
keep the fixed-decimal mode turned off.

40.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

41.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

42.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.

43.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).

44.How to set the custom paper size in Excel Object through VB?

Activesheet.PageSetup.PaperSize = xlPaperLetter (Similarly xlPaperA4 or xlPaperLegal etc.)

45.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.

46.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.

47.To set the command button for ESC, Which property needs to be changed?

Set Cancel property of Button to True on the Form.

48.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.

49.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.

50.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.

51.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.

52.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.

53.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.

54.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.

55.What does Range.Dirty used for ?

To add the specified cells to the list of cells requiring calculation at the next recalculation.

56.How do you check the Calculation state ?

Pretty simply, using the Application.CalculationState property which tells if calculation has completed (
xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ).

57.How do you define Excel Calculation Interruption Key to stop the calculation?

Using Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.
Remember using XlNokey, calculation cannot be interrupted.