Advanced Excel, Macros and VBA

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

Q.1 What is Microsoft Excel used for?
Microsoft Excel is a spreadsheet software used for data analysis, calculation, reporting, and creating visual representations of data.
Q.2 How do you insert a new worksheet in Excel?
To insert a new worksheet, right-click on an existing sheet tab, and select "Insert." Choose the type of sheet you want to add and click "OK."
Q.3 Explain the difference between a workbook and a worksheet.
A workbook is the entire Excel file, while a worksheet is a single tab within that file where you can enter and manipulate data.
Q.4 What is a cell reference in Excel?
A cell reference is a combination of the column letter and row number that identifies a specific cell, such as "A1" or "B5."
Q.5 How do you freeze panes in Excel?
To freeze panes, go to the "View" tab, click "Freeze Panes," and select options like "Freeze Top Row" or "Freeze First Column."
Q.6 Explain the purpose of the VLOOKUP function.
The VLOOKUP function searches for a value in the first column of a range and returns a corresponding value from another column in the same row.
Q.7 What is conditional formatting in Excel?
Conditional formatting allows you to format cells based on specific criteria, making it easier to visualize and analyze data.
Q.8 How can you protect an Excel workbook or worksheet with a password?
You can protect a workbook or worksheet by going to the "Review" tab, selecting "Protect Sheet" or "Protect Workbook," and setting a password.
Q.9 Explain the purpose of the CONCATENATE function in Excel.
The CONCATENATE function combines multiple text strings into one string. It's often used to join values from different cells.
Q.10 What is the purpose of the "PivotTable" feature in Excel?
PivotTables are used to summarize and analyze large datasets, allowing you to create customized reports and charts.
Q.11 How can you remove duplicates from a list in Excel?
You can remove duplicates by selecting the data, going to the "Data" tab, and clicking "Remove Duplicates."
Q.12 What is the purpose of the IF function in Excel?
The IF function allows you to perform conditional calculations. It returns one value if a condition is true and another if it's false.
Q.13 Explain the difference between absolute and relative cell references.
An absolute reference, like "$A$1," does not change when you copy the formula. A relative reference, like "A1," adjusts relative to the new cell.
Q.14 How do you create a chart in Excel?
To create a chart, select the data you want to include, go to the "Insert" tab, choose the desired chart type, and customize it as needed.
Q.15 What is the purpose of the CONCATENATE function in Excel?
The CONCATENATE function combines multiple text strings into one string. It's often used to join values from different cells.
Q.16 How can you create a named range in Excel?
You can create a named range by selecting the cells, going to the "Formulas" tab, and choosing "Define Name." Give it a name and click "OK."
Q.17 Explain the use of the "IFERROR" function in Excel.
The IFERROR function checks for errors in a formula and returns a specified value if an error is found, making it more robust.
Q.18 How do you add comments to cells in Excel?
To add comments, right-click a cell, choose "Insert Comment," and type your comment. The comment is indicated by a red triangle in the cell.
Q.19 What is the purpose of data validation in Excel?
Data validation restricts the type of data that can be entered in a cell, helping maintain data integrity.
Q.20 What are Excel Macros, and what can they be used for?
Excel Macros are automated scripts that perform repetitive tasks. They can be used to automate data manipulation, formatting, and more.
Q.21 How do you record a macro in Excel?
To record a macro, go to the "View" tab, click "Macros," choose "Record Macro," perform the actions, and stop recording when done.
Q.22 What is the Visual Basic for Applications (VBA) editor?
The VBA editor is a development environment within Excel for writing, editing, and debugging VBA code.
Q.23 What is the purpose of the "Range" object in VBA?
The Range object represents a cell, a group of cells, or a range of data in Excel. It's used to manipulate data within VBA code.
Q.24 How do you write a simple VBA macro to add two numbers in Excel?
Here's an example: Sub AddNumbers() Dim result As Double result = Range("A1").Value + Range("A2").Value Range("A3").Value = result End Sub .
Q.25 How do you run a VBA macro in Excel?
You can run a VBA macro by pressing "Alt" + "F8," selecting the macro from the list, and clicking "Run."
Q.26 What is the purpose of the "For" loop in VBA?
The "For" loop is used to repeat a block of code a specified number of times, making it useful for tasks like iterating through cells.
Q.27 How do you add comments to VBA code?
You can add comments by using an apostrophe (') before a line of text or by enclosing comments with Rem and End statements.
Q.28 Explain the difference between a function and a subroutine in VBA.
A function returns a value to the calling code, while a subroutine (Sub) performs actions but doesn't return a value.
Q.29 What is the purpose of the "If...Then...Else" statement in VBA?
The "If...Then...Else" statement allows you to make decisions in your code. If a condition is true, one block of code is executed; otherwise, another block is executed.
Q.30 How do you declare variables in VBA?
You can declare variables using the "Dim" statement, specifying the variable name and data type. For example, Dim myVar As Integer.
Q.31 What is the purpose of the "Select Case" statement in VBA?
The "Select Case" statement simplifies decision-making by allowing you to test a variable or expression against multiple possible values or conditions.
Q.32 How do you create user-defined functions (UDFs) in VBA?
You can create UDFs by writing custom VBA functions and saving them in a VBA module. They can be used like built-in Excel functions.
Q.33 What is the "Object Browser" in the VBA editor?
The Object Browser is a tool in the VBA editor that allows you to browse and search for objects, properties, and methods available in Excel.
Q.34 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.35 How can you handle errors in VBA code?
You can use error-handling techniques like "On Error Resume Next" to ignore errors or "On Error Goto" to specify error-handling routines.
Q.36 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.37 What is the purpose of the "With" statement in VBA?
The "With" statement simplifies repetitive code by allowing you to perform a series of actions on an object without repeatedly referencing it.
Q.38 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.39 How do you add a new module to the VBA editor in Excel?
To add a new module, go to the "Insert" menu in the VBA editor, select "Module," and start writing VBA code in the new module.
Q.40 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.41 How can you debug VBA code in Excel?
You can use tools like breakpoints, the Immediate window, and the Locals window to debug VBA code in the VBA editor.
Q.42 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.43 What is the purpose of the "Do...Loop" statement in VBA?
The "Do...Loop" statement is used for looping in VBA. You can use different types, like "Do While" or "Do Until," to control loop execution.
Q.44 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.45 How can you automate tasks using VBA and Excel?
You can automate tasks by writing VBA macros that perform actions like data manipulation, report generation, and formatting in Excel.
Q.46 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.47 What is the Excel "Solver" add-in, and how can it be used?
The Solver add-in is used for optimization and what-if analysis. It finds the best values for variables in a model to maximize or minimize a result.
Q.48 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.49 How do you add a custom ribbon tab in Excel using VBA?
You can add a custom ribbon tab by writing VBA code in a workbook and customizing the ribbon XML to define the tab's structure and buttons.
Q.50 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.51 What is the purpose of the "Application" object in VBA?
The "Application" object represents the Excel application itself and provides access to various Excel features and properties.
Q.52 How can you open and manipulate external files using VBA?
You can use VBA to open and manipulate external files, such as text files or other Excel workbooks, using file input/output functions and methods.
Q.53 What is the purpose of the "Workbook" object in VBA?
The "Workbook" object represents an Excel workbook and allows you to perform actions like opening, saving, and modifying workbook properties.
Q.54 What is the purpose of the "Worksheet" object in VBA?
The "Worksheet" object represents a single sheet within an Excel workbook, allowing you to manipulate its contents and properties.
Q.55 How can you protect a VBA project with a password?
To protect a VBA project, open the VBA editor, right-click the project, select "VBAProject Properties," and set a password under the "Protection" tab.
Q.56 What is the purpose of the "Range.Find" method in VBA?
The "Range.Find" method is used to search for specific data within a range of cells and returns the first cell matching the search criteria.
Q.57 How do you create custom Excel functions using VBA?
You can create custom Excel functions (UDFs) by writing VBA code within a VBA module and using the "Function" keyword to define the function.
Q.58 What is a "UserForm" in Excel VBA, and how is it used?
A UserForm is a customizable dialog box in Excel VBA. It's used to create interactive interfaces for data entry or other tasks.
Q.59 How do you add controls (e.g., buttons, text boxes) to a UserForm in Excel VBA?
To add controls to a UserForm, open the UserForm in the VBA editor, go to the "Toolbox" pane, and drag-and-drop controls onto the form.
Q.60 What is the purpose of the "Workbook_Open" event in Excel VBA?
The "Workbook_Open" event is triggered when a workbook is opened and allows you to execute specific code or actions automatically.
Q.61 How do you loop through all worksheets in an Excel workbook using VBA?
You can loop through worksheets by using a "For Each" loop and the "Worksheets" collection in VBA.
Q.62 What is the purpose of the "DoEvents" function in VBA?
The "DoEvents" function allows Excel to process pending events while a macro is running, making the application more responsive.
Q.63 How do you create a custom function to calculate the factorial of a number in Excel VBA?
Here's an example: vba Function Factorial(n As Long) As Long If n = 0 Then Factorial = 1 Else Factorial = n * Factorial(n - 1) End If End Function .
Q.64 How can you use VBA to automate the process of importing data from an external source into Excel?
You can use VBA to establish connections, run queries, and import data from external sources like databases or web services.
Q.65 What is the purpose of the "Worksheet_Change" event in Excel VBA?
The "Worksheet_Change" event is triggered when a cell's value is changed and allows you to execute specific code based on that change.
Q.66 How can you create a custom message box with multiple buttons in Excel VBA?
You can use the "MsgBox" function with the "vbYesNo" or "vbYesNoCancel" argument to create a message box with multiple buttons.
Q.67 What is the purpose of the "Range.Offset" property in VBA?
The "Range.Offset" property allows you to reference a cell or range relative to another cell, which is useful for dynamic data manipulation.
Q.68 How can you use VBA to save a workbook with a specific file name and location?
You can use the "SaveAs" method to specify the file name and path when saving a workbook in VBA.
Q.69 What is the purpose of the "Workbook_BeforeSave" event in Excel VBA?
The "Workbook_BeforeSave" event is triggered just before a workbook is saved, allowing you to execute specific code or validations.
Q.70 How do you create a drop-down list (data validation) in Excel using VBA?
You can use VBA to set the "Validation" properties of a cell or range to create a drop-down list based on specified values.
Q.71 What is the purpose of the "Application.OnTime" method in VBA?
The "Application.OnTime" method schedules a macro to run at a specified time in the future, making it useful for automation tasks.
Q.72 How can you use VBA to export Excel data to a PDF file?
You can use VBA to save a worksheet or workbook as a PDF file using the "ExportAsFixedFormat" method.
Q.73 What is the purpose of the "Worksheet_Activate" event in Excel VBA?
The "Worksheet_Activate" event is triggered when a worksheet is activated (selected) and allows you to execute specific code based on that event.
Q.74 How do you use VBA to protect and unprotect worksheets or workbooks with a password?
You can use the "Protect" and "Unprotect" methods with a password parameter to protect and unprotect worksheets or workbooks in VBA.
Q.75 What is the "Immediate Window" in the VBA editor, and how is it used?
The Immediate Window is a tool for testing and executing VBA code interactively within the VBA editor. It's useful for debugging and exploring code.
Q.76 How do you create custom Excel add-ins using VBA?
You can create custom Excel add-ins by saving your VBA code in a special add-in file format and loading it in Excel.
Q.77 How can you use VBA to apply data filters and extract filtered data in Excel?
VBA can be used to automate the application of filters to a worksheet and then extract and manipulate the filtered data.
Q.78 What is the purpose of the "Workbook_SheetChange" event in Excel VBA?
The "Workbook_SheetChange" event is triggered when any sheet within a workbook is changed and allows you to execute code based on that change.
Q.79 How can you use VBA to create dynamic charts in Excel?
VBA can be used to modify chart data source ranges and chart properties to create dynamic charts that update based on changing data.
Q.80 What is the "Name Manager" in Excel, and how is it used in VBA?
The Name Manager allows you to define and manage named ranges in Excel. In VBA, you can use named ranges to refer to specific data ranges.
Q.81 How do you use VBA to send emails from Excel?
VBA can utilize the Outlook application to send emails from Excel, including attaching workbooks or sheets to the email.
Q.82 What is the "Worksheet_BeforeDoubleClick" event in Excel VBA?
The "Worksheet_BeforeDoubleClick" event is triggered when a cell is double-clicked, allowing you to execute specific code in response to the double-click action.
Q.83 How do you use VBA to sort data in Excel?
VBA can be used to automate the sorting of data in worksheets, specifying sorting criteria and order.
Q.84 What is the purpose of the "Workbook_BeforeClose" event in Excel VBA?
The "Workbook_BeforeClose" event is triggered just before a workbook is closed, allowing you to execute specific code or validations.
Q.85 How can you use VBA to create a dynamic drop-down list in Excel?
VBA can be used to populate drop-down lists dynamically by updating the list source based on conditions or data changes.
Q.86 What is the "Call" statement in VBA, and when is it used?
The "Call" statement is used to call a procedure or subroutine in VBA. It is optional and often omitted when calling a procedure.
Q.87 How can you use VBA to automate data consolidation from multiple worksheets or workbooks in Excel?
VBA can be used to copy and consolidate data from various sources, such as multiple worksheets or workbooks, into a single destination.
Q.88 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.89 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.90 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.91 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.92 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.93 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.94 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.95 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.96 Explain what's COM (Component Object Model) objects in VBA?
COM objects area unit sometimes .dll files, and area unit compiled possible programs.
Q.97 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.98 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.99 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.100 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.101 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.102 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.103 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.104 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.105 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.106 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
 For Support