{"id":48153,"date":"2018-01-31T06:22:17","date_gmt":"2018-01-31T00:52:17","guid":{"rendered":"http:\/\/blog.odango.com\/?p=48153"},"modified":"2023-02-03T17:42:55","modified_gmt":"2023-02-03T12:12:55","slug":"microsoft-excel-interview-questions-and-answers","status":"publish","type":"post","link":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/","title":{"rendered":"Microsoft Excel Interview Questions and Answers"},"content":{"rendered":"\n[et_pb_section bb_built=”1″ next_background_color=”#000000″][et_pb_row][et_pb_column type=”4_4″][et_pb_post_title _builder_version=”3.0.106″ title=”on” meta=”off” author=”on” date=”on” categories=”on” comments=”on” featured_image=”off” featured_placement=”below” text_color=”dark” text_background=”off” title_font=”Titillium Web||||||||” title_font_size=”47″ title_font_size_tablet=”40″ title_font_size_phone=”35″ title_font_size_last_edited=”on|desktop” title_text_color=”rgba(0,0,0,0.8)” title_text_align=”left” text_shadow_horizontal_length=”0.08em” text_shadow_vertical_length=”0.08em” text_shadow_blur_strength=”0.08em” title_text_shadow_horizontal_length=”0.08em” title_text_shadow_vertical_length=”0.08em” custom_margin=”|||10%” \/][\/et_pb_column][\/et_pb_row][et_pb_row][et_pb_column type=”4_4″][et_pb_text _builder_version=”3.0.106″ background_layout=”light” text_font=”Titillium Web|300|||||||” text_font_size=”20″ text_font_size_last_edited=”on|tablet” text_text_color=”rgba(0,0,0,0.8)” text_line_height=”1.6em” max_width=”800px” custom_margin=”|||10%” text_line_height_last_edited=”off|phone” max_width_last_edited=”off|phone” custom_margin_last_edited=”off|desktop”]\r\n\r\n1)\u00a0What is the difference between ByVal and ByRef and which is default ?<\/strong>\r\n\r\nAns:\u00a0<\/strong>ByRef:<\/em>\u00a0If 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.\r\nByVal:<\/em>\u00a0If 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.\r\nByRef is default:<\/em>\u00a0Passing 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.\r\n\r\n
\r\n\r\n2)\u00a0What is the meaning of Option Explicit and Option Base?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Option 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.\r\n\r\n
\r\n\r\n3)\u00a0What are various data type and their size?<\/strong>\r\n\r\nAns:<\/strong>\r\ni) The\u00a0Boolean\u00a0data type has only two states, True and False. These types of variables are stored as 16-bit (2 Byte) numbers, and are usually used for flags.\r\nii) The\u00a0Byte\u00a0data type is an 8-bit variable which can store value from 0 to 255.\r\niii) The\u00a0Double\u00a0data type is a 64-bit floating point number used when high accuracy is needed.\r\niv) The\u00a0Integer\u00a0data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers. In case, you’re working over 32767 rows use Long as data type.\r\nv) The\u00a0Long\u00a0data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.\r\nvi) The\u00a0Single\u00a0data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.\r\nvii) The\u00a0String\u00a0data type is usually used as a variable-length type of variable. A variable-length string can contain up to approximately 2 billion characters. Each character has a value ranging from 0 to 255 based on the ASCII character set.\r\n\r\n
\r\n\r\n4)\u00a0Difference between ActiveWorkbook and ThisWorkbook.<\/strong>\r\n\r\nAns:<\/strong>\u00a0ThisWorkbook 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.\r\n\r\n
\r\n\r\n5)\u00a0Code to find a Last used Row in a column or Last used column of a Row.<\/strong>\r\n\r\nAns:\u00a0<\/strong>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.\r\n\r\n
\r\n\r\n6)\u00a0Difference between ActiveX and Form Controls.<\/strong>\r\n\r\nAns:<\/strong>\r\ni) 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.\r\nii) The Forms controls aren\u2019t very complicated, and they have been part of Excel for longer (they were used in Excel 5\/95\u2019s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they\u2019d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.\r\n\r\n
\r\n\r\n7)\u00a0What is the difference b\/w Functions and Subroutines?<\/strong>\r\n\r\nAns:<\/strong>\r\ni) Subroutines never return a value but functions does return values.\r\nii) A function could not change the values of actual arguments whereas a subroutine could change them.\r\n\r\n
\r\n\r\n8)\u00a0How to debug a VBA code?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch window.\r\n\r\n
\r\n\r\n9) Draw basic Excel Object Model.<\/strong>\r\n\r\nAns:<\/strong>\u00a0 Application –> Workbooks –> Worksheets –> Range \/ Chart\r\n\r\n
\r\n\r\n10)\u00a0What are properties, methods, events and objects?<\/strong>\r\n\r\nAns:<\/strong>\r\nAll the controls in the ToolBox except the Pointer are\u00a0objects\u00a0in Visual Basic. These objects have associated properties, methods and events.\r\nA\u00a0property\u00a0is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.\r\n\r\nA\u00a0method\u00a0is an action that can be performed on objects. For example, a cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities.\r\n\r\nTherefore, a cat’s methods might be move, jump, play, breath etc.Visual Basic programs are built around\u00a0events.\r\n\r\nEvents are various things that can happen in a program. Let us consider a TextBox control and a few of its associated events to understand the concept of event driven programming.\r\n\r\nThe TextBox control supports various events such as Change, Click, MouseMove and many more that will be listed in the Properties dropdown list in the code window for the TextBox control. We will look into a few of them as given below.\r\n* The code entered in the Change event fires when there is a change in the contents of the TextBox\r\n* The Click event fires when the TextBox control is clicked.\r\n* The MouseMove event fires when the mouse is moved over the TextBox\r\n\r\n
\r\n\r\n11)\u00a0How to hide a worksheet so that a user cannot unhide it?<\/strong>\r\n\r\nAns:\u00a0<\/strong>Use Sheet’s visible property and set it to\u00a0xlSheetVeryHidden\u00a0. For eg. Sheets(1).Visible = xlSheetVeryHidden will super hide the first worksheet of the workbook.\r\n\r\n
\r\n\r\n12)\u00a0Union is used for __?<\/strong>\r\n\r\nAns:<\/strong>\u00a0To 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)\r\n\r\n
\r\n\r\n13) Which are the 2 macro languages and which do you use ?<\/strong>\r\n\r\nAns:<\/strong>\u00a0 XLM (used in Excel 97 or before) and VBA(used for 2000 and after). Obviously, VBA is in use these days.\r\n\r\n
\r\n\r\n14)\u00a0Can you lock cells such that only specific users can modify them?<\/strong>\r\n\r\nAns:<\/strong>\u00a0There is a option “Allow users to edit ranges” can be used for this purpose.\r\n\r\n
\r\n\r\n15)\u00a0How can you add a drop-down list to a cell so the user can choose a value from the list?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Using ‘Data Validation’.\r\n\r\n
\r\n\r\n16)\u00a0How can you increase the number of rows in a worksheet?<\/strong>\r\n\r\nAns:<\/strong>\u00a0No one can’t. They’re fixed as 65536(2^16) in Excel 2003 or before and 1048576(2^20) in Excel 2007 & Excel 2010.\r\n\r\n
\r\n\r\n17)\u00a0How can you increase the number of columns in a worksheet?<\/strong>\r\n\r\nAns:\u00a0<\/strong>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.\r\n\r\n
\r\n\r\n18. How will you distribute a workbook such that it can’t be copied using macro or anything?<\/strong>\r\n\r\nAns:<\/strong>\u00a0We 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)\r\n\r\n
\r\n\r\n19)\u00a0Your 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?<\/strong>\r\n\r\nAns:\u00a0<\/strong>\u00a0By chance Excel’s fixed-decimal mode was turned on. To return to normal,\r\nExcel 2003 –>\r\nClick Tools and then Options to display the Options dialog box.\r\nThen click the Edit tab and remove the check mark from the “Fixed decimal ” option.\r\nExcel 2007 –>\r\nClick Office button on Top-Left corner and click ‘Excel Options’.\r\nGo to Advanced and Uncheck ‘Automatically insert a decimal point’ option.\r\nExcel 2010 –>\r\nClick File button on Top-Left corner and click ‘Excel Options’.\r\nGo to Advanced and Uncheck ‘Automatically insert a decimal point’ option.\r\nOf 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.\r\n\r\n
\r\n\r\n20. How can you prevent a user for adding or deleting sheets?<\/strong>\r\n\r\nAns:<\/strong>\u00a0You need to protect the workbook’s structure.\r\nExcel 2003 –>\r\nSelect Tools – Protection – Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked.\r\nExcel 2007\/2010 –>\r\nGo to Review –> Click ‘Protect Workbook’ –> Click ‘Protect Structure and Windows’\r\nIf you specify a password, that password will be required to unprotect the workbook. When a workbook’s structure is protected, the user may not:\r\n* Add\/Delete a sheet\r\n* Hide\/Unhide a sheet\r\n* Rename a sheet\r\n* Move a sheet\r\n\r\n
\r\n\r\n21)\u00a0What types of workbook protection are available?<\/strong>\r\n\r\nAns:<\/strong>\r\n\r\nExcel provides three ways to protect a workbook:\r\n* Require a password to open the workbook\r\n* Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets\r\n* Prevent users from changing the size or position of windows\r\n\r\n
\r\n\r\n22)\u00a0The 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?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Use Application.Dialogs(xlDialogFont).Show or Application.Dialogs(xlDialogFormatFont).Show to load font dialog box from Excel VBA.\r\n\r\n
\r\n\r\n23. What is ADO, OLEDB & ODBC?<\/strong>\r\n\r\nAns:\u00a0<\/strong>\r\nADO\u00a0 \u00a0 \u00a0 \u00a0: ActiveX Data Objects is universal data access framework that encompasses the functionality of DAO.\r\nODBC\u00a0 \u00a0 : Open Database Connectivity(ODBC) is a windows technology that lets a database client application connect to a external database.\r\nOLEDB\u00a0 : Low level programming interface designed to access a wide variety of data access Object Linking and Embedding (OLE).\r\n\r\n
\r\n\r\n24. How to set the custom paper size in Excel Object through VB?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Activesheet.PageSetup.PaperSize = xlPaperLetter (Similarly xlPaperA4 or xlPaperLegal etc.)\r\n\r\n
\r\n\r\n25)\u00a0What is the method for returning more than one values from a function in VB?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Any of the three methods can be used:\r\ni) Create a class with the properties you require to return and then return the object of the class from the function.\r\nii) Using ByRef for the values.\r\niii) Return an array of the values.\r\n\r\n
\r\n\r\n26)\u00a0Does VBA supports OOP principles?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Yes 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.\r\n\r\n
\r\n\r\n27)\u00a0To set the command button for ESC, Which property needs to be changed?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Set Cancel property of Button to True on the Form.\r\n\r\n
\r\n\r\n28. What is Type Library and its purpose?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Type 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.\r\n\r\nIn 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.\r\n\r\n
\r\n\r\n29)\u00a0How do you use the Getsetting and Savesetting functions to read and write registry settings?<\/strong>\r\n\r\nAns:<\/strong>\u00a0A 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.\r\n\r\nIt is advisable to know about your computer settings before modifying registry settings.\r\n\r\n
\r\n\r\n30)\u00a0What is a Variant, what the pros and cons of its use?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Variant 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).\r\nPros:<\/em>\u00a0You cannot use Null with any variable type other than Variant.\r\nYou don’t need to worry about what you have declared a variable as.\r\nWhen a Variant has been declared but not assigned a value, it contains the special value Empty.\r\nCons:<\/em>\u00a0A developer may not remember and misuse a variable assigning any value to it which will be type-casted without errors.\r\n\r\n
\r\n\r\n31. Give technical reasons which made Microsoft withdraw its support for VBA in Mac?<\/strong>\r\n\r\nAns:<\/strong>\u00a0The 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.\r\n\r\n
\r\n\r\n32. What is a volatile function?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Volatile 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.\r\n\r\n
\r\n\r\n33. Give some examples of Volatile function.<\/strong>\r\n\r\nAns:<\/strong>\r\nSome of Excel\u2019s functions are obviously volatile: RAND(), NOW(), TODAY()\r\nOthers are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()\r\nSome are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.\r\nA number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:\r\nINDEX(), ROWS(), COLUMNS(), AREAS()\r\nand CELL(“Filename”) IS volatile although a MSKBN article says its not.\r\nOne particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:\r\n=SUMIF(A1:A4,”>0″,B1) is volatile whereas =SUMIF(A1:A4,”>0″,B1:B4) is not volatile.\r\n\r\n
\r\n\r\n34. How do you make a UDF volatile?<\/strong>\r\n\r\nAns:<\/strong>\u00a0By adding Application.Volatile statement to it. It must be the first line of your User Defined Function.\r\n\r\n
\r\n\r\n35)\u00a0Is it possible to apply ‘Application.Volatile(False)’ to a volatile public function like INDEX and make it not volatile?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Actually 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.\r\n\r\n
\r\n\r\n36)\u00a0What is Excel dependency tree?<\/strong>\r\n\r\nAns:<\/strong>\r\n\r\nDependency trees are excel way of minimizing the calculation by tracking what has changed since last calculation. It allows Excel to recalculate only:\r\n* Formulae\/Names that have changed.\r\n* Formulae containing Volatile Functions\r\n* Formulae dependent on changed or volatile formulae or cells or names.\r\n\r\nExcel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function.\r\nDependency trees are immediately updated whenever a formula is entered or changed.\r\nTo force the dependency trees to be rebuilt and all formulae calculation use CTRL+ALT+SHIFT+F9.\r\n\r\n
\r\n\r\n37)\u00a0What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?<\/strong>\r\n\r\nAns:\u00a0<\/strong>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Shortcut Combination<\/td>\r\nVBA Equivalent<\/td>\r\nMeaning<\/td>\r\n<\/tr>\r\n
F9<\/td>\r\nApplication.Calculate<\/td>\r\nRecalculate<\/td>\r\n<\/tr>\r\n
Ctrl+Alt+F9<\/td>\r\nApplication.CalculateFull<\/td>\r\nFull Calculation<\/td>\r\n<\/tr>\r\n
Ctrl+Alt+Shift+F9<\/td>\r\nApplication.CalculateFullRebuild<\/td>\r\nRebuild Excel Dependency Tree\r\nand Full Calculation<\/td>\r\n<\/tr>\r\n
Shift+F9<\/td>\r\nSheets(1).Calculate<\/td>\r\nCalculate Worksheet<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n\r\n
\r\n\r\n38)\u00a0What does Range.Dirty used for ?<\/strong>\r\n\r\nAns:\u00a0<\/strong>To add the specified cells to the list of cells requiring calculation at the next recalculation.\r\n\r\n
\r\n\r\n39)\u00a0How do you check the Calculation state ?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Pretty simply, using the Application.CalculationState property which tells if calculation has completed ( xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ).\r\n\r\n
\r\n\r\n40)\u00a0How do you define Excel Calculation Interruption Key to stop the calculation?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Using Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.\r\nRemember using XlNokey, calculation cannot be interrupted.\r\n\r\n
\r\n\r\n41)\u00a0What does ‘Workbook.ForceFullCalculation’ do?<\/strong>\r\n\r\nAns:<\/strong>\u00a0When this property is set to True, dependencies are not loaded at open, the dependency dirty chain is not updated, and every calculation of the workbook is a full calculation rather than a recalculation.\r\nIf you have a workbook that has so many complex dependencies that loading the dependencies at workbook open takes a long time or recalculation takes longer than full calculation, you can use this property to force Excel to skip loading the dependencies and always use full calculation. Also if making a change to the workbook takes a long time in manual mode because of the time taken to dirty all the dependencies of the cell being changed, then setting Workbook.ForceFullCalculation to True will eliminate the delay.\r\n
    \r\n \t
  • \u00a0Although this is a workbook property the effect is at Application level rather than workbook level.<\/li>\r\n \t
  • \u00a0In Excel 2007 setting the property back to False once it has been set to True has no effect on the current Excel session.<\/li>\r\n \t
  • The property is saved and restored with the workbook.<\/li>\r\n \t
  • Once this property has been set to True ‘Calculate’ shows in the status bar and cannot be removed by using calculation keys such as F9.<\/li>\r\n<\/ul>\r\n\r\n
    \r\n\r\n42)\u00a0What are excel dependency tree limits?<\/strong>\r\n\r\nAns:<\/strong>\r\n
      \r\n \t
    • There are two limits to the number of dependencies that Excel versions prior to Excel 2007 can track before it must do full calculations instead of recalculations.<\/li>\r\n \t
    • The number of different areas in a sheet that may have dependencies is limited to 65,536.<\/li>\r\n \t
    • The number of cells that may depend on a single area is limited to 8K.<\/li>\r\n \t
    • After the workbook has passed these limits, Excel no longer attempts to recalculate only changed cells. Instead, it recalculates all cells at each calculation.<\/li>\r\n<\/ul>\r\n\r\n
      \r\n\r\n43)\u00a0Mention those conditions when status bar shows CALCULATE.<\/strong>\r\n\r\nAns:<\/strong>\u00a0There are five known conditions in which the status bar will show CALCULATE:\r\n
        \r\n \t
      • The Calculation Option has been set to Manual and the workbook contains uncalculated formulae. Try setting calculation to Automatic (Tools–>Options–>Calculate). Note that Excel sets the calculation mode from the first workbook\u00a0opened in a session: when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened.<\/li>\r\n \t
      • The Iteration Option is turned on and the workbook contains circular references. Check that turning off Iteration (Tools–>Options–>Calculation) and pressing F9 shows “Circular Reference” in the statusbar.<\/li>\r\n \t
      • You are using Excel 2000 without the SR1 update and have a user-defined function that attempts to define a name and depends on a volatile function: see MSKB Q248179<\/li>\r\n \t
      • You have hit one of Excels limits for tracking dependencies.<\/li>\r\n \t
      • You are using Excel 2007 and have set Workbook.ForceFullCalculation to True<\/li>\r\n<\/ul>\r\n\r\n
        \r\n\r\n44)\u00a0What do you know about multi threaded calculation?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Excel 2007 can split calculation across multiple processors or cores. When Excel 2007 loads a workbook, it determines from the operating system how many processors are available and then creates a separate calculation thread for each processor. These threads can then run in parallel. The beauty of this system is that it scales extremely well with the number of processors.\r\nMost workbooks show a significant improvement in calculation speed on a system with multiple cores. The degree of improvement depends on how many independent calculation trees the workbook contains. If you make a workbook that contains one continuous chain of formulas, it will not show any multithreaded calculation (MTC) performance gain, whereas a workbook that contains several independent chains of formulas will show gains close to the number of processors available.\r\n\r\n
        \r\n\r\n45)\u00a0How can we dial a phone number?<\/strong>\r\n\r\nAns:\u00a0<\/strong>Shell command present in VBA can be used to start the dialer present in windows operating system. Phone number can be used to connect to your modem. With the use of shell and sendkeys you can dial to your user. Shell starts windows application and sendkeys inform the window to dial according to the keystrokes of the application. A macro can be used to start the cardfile program which activates the auto dialer feature.\r\n\r\n
        \r\n\r\n46)\u00a0What do you know about the interpretation features of VBA?<\/strong>\r\n\r\nAns:\u00a0<\/strong>VBA is licensed to Microsoft and this compatible with and only Microsoft products. Code written is compiled by an intermediate language called P-code and this is stored in hosting applications such as Excel, Word and Access. The intermediate code is interpreted by a virtual machine. This code and intermediate language is the exclusive right of Microsoft.\r\n\r\n
        \r\n\r\n47)\u00a0Explain about insert module and Goal Seek functions present in VBA?<\/strong>\r\n\r\nAns:<\/strong>\u00a0The 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.\r\n\r\n
        \r\n\r\n48)\u00a0State the difference between Visual Basic, VB Script and Visual Basic for Applications?<\/strong>\r\n\r\nAns:<\/strong>\u00a0Visual 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.\r\nVB 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.\r\nVisual Basic for Applications are very useful in automating your existing application. VB application is useful for developing already existing applications.\r\n\r\n
        \r\n\r\n49)\u00a0Write a macro to select all the non-blank cells of Activesheet ?<\/strong>\r\n\r\nAns:<\/strong>\r\nSub\u00a0NonBlankCells()\r\nOn Error Resume Next\r\nUnion(Cells.SpecialCells(xlCellTypeFormulas, 23), Cells.SpecialCells(xlCellTypeConstants, 23)).Select\r\nIf\u00a0Err.Number <> 0\u00a0Then\r\nCells.SpecialCells(xlCellTypeFormulas, 23).Select\r\nElse\r\nExit Sub\r\nEnd If\r\nIf\u00a0Err.Number <> 0\u00a0Then\r\nCells.SpecialCells(xlCellTypeConstants, 23).Select\r\nElse\r\nExit Sub\r\nEnd If\r\nOn Error GoTo 0\r\nEnd Sub\r\n\r\n
        \r\n\r\n50)\u00a0What is the difference between UsedRange and CurrentRegion properties ?\r\nAns:<\/strong>\r\ni)\u00a0The current region is a range bounded by any combination of blank rows and blank columns.\r\nThis property is useful for many operations that automatically expand the selection to include the entire current region, such as the AutoFormat method. This property cannot be used on a protected worksheet.\r\nThe UsedRange 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 specified worksheet.\r\nii) Every non-blank cell got its CurrentRegion and its keyboard shortcut is Ctrl+Shift+Spacebar.\r\niii) There can be many current regions but there is only one used range in a worksheet.\r\n\r\n
        \r\n\r\n51) I have a User Form which needs to take a specified size depending upon the data source. Data Source is in Range DataS1 and DataS2. The following range (named as “Dimensions”) shows the dimensions which the User Form needs to have when ever we use DataS1 or DataS2.\r\nAns:<\/strong>\r\n\r\nUserFormSpec \u00a0 \u00a0DataS1 \u00a0 \u00a0 \u00a0DataS2\r\nFormWidth \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 50 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0100\r\nFormHeight \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a040 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 80\r\n\r\nCell A1 of Sheet1 contains a dropdown containing the names of the datasources, i.e., DataS1 and DataS2. The following code is used to show the UserForm.\r\n\r\nPrivate Sub MyForm_Click()\r\nUserForm.Show\r\nEnd Sub\r\n\r\n
        \r\n\r\n52) What is wish is that when I select DataS1 or DataS2 from Cell A1, the UserForm width and height changes according to the numbers in range “Dimensions”.\r\nAns:<\/strong>\r\nBrief Description: Solution requires use of Event Macros, VBA, Forms, Object Hierarchy. Even if you don’t know refer below to solve such problems in a very easy manner.\r\nAs you want to re-size the form whenever value in Cell A1 of Sheet1 changes, you mustuse event macro to achieve this. Since event macros are automatically fired in response to associated events. Following are some examples of excel events for which event macros are already in the MS Excel applications:\r\n\u00a0Name of Event\u00a0\u00a0\u00a0<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Event Macro<\/strong>\r\n1. Opening of Workbook\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Workbook_Open()\r\n2. Double click a cell\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Worksheet_BeforeDoubleClick\r\n3. Activate a Sheet\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Worksheet_Activate()\r\n4. Pivot Table Update\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Worksheet_PivotTableUpdate\r\n5. Change Cell Value\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Worksheet_SelectionChange\r\netc etc etc\r\n\r\nSince, each event macro is associated with an event, it is also associated with a particular object. Like, if defined then a workbook event macro is associated to the workbook where it is defined. So if you’ve written a workbook event macro in Workbook A then it won’t affect Workbook B. Same for Sheets, pivot tables etc.\r\n\r\nFull Solution\u00a0to the Query:\r\n1. Open the desired Excel file.\r\n2. Go to VBE by pressing Alt+F11.\r\n3. Under Microsoft Excel Objects for this workbook, Double Click on Sheet1 to open it’s code window.\r\n4. Paste the Following Code in it. (Change the User form name from UserForm1 to your form name)\r\n\r\nPrivate Sub\u00a0Worksheet_SelectionChange(ByVal\u00a0Target\u00a0As\u00a0Range)\r\nIf\u00a0Target.Address(0, 0) = “A1”\u00a0Then\r\nIf\u00a0UCase(Target.Value) = “DATAS1”\u00a0Then\r\nUserForm1.Height = 40\r\nUserForm1.Width = 50\r\nElseIf\u00a0UCase(Target.Value) = “DATAS2″\u00a0Then\r\nUserForm1.Height = 80\r\nUserForm1.Width = 100\r\nEnd If\r\nEnd If\r\nEnd Sub\r\n\r\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section bb_built=”1″ prev_background_color=”#000000″][et_pb_row][et_pb_column type=”4_4″][et_pb_team_member _builder_version=”3.0.106″ name=”Chandanakatta” position=”Author” facebook_url=”” twitter_url=”” google_url=”” linkedin_url=”” background_layout=”light” image_url=”https:\/\/asha24.net\/blog\/\/wp-content\/uploads\/2018\/03\/portraits-circle-small_1.png” header_font=”Titillium Web|700|||||||” body_font=”Titillium Web||||||||” body_font_size=”16″ body_font_size_last_edited=”on|desktop” disabled=”off” disabled_on=”off|off|”]\r\n\r\nHey there! I shoot some hoops when I\u2019m not drowned in the books, sitting by the side of brooks.\r\n\r\n[\/et_pb_team_member][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":4,"featured_media":48154,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[57],"tags":[58,60],"class_list":["post-48153","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-interview-questions","tag-interview-questions","tag-resume"],"yoast_head":"\nMicrosoft Excel Interview Questions and Answers - Asha24 Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Microsoft Excel Interview Questions and Answers - Asha24 Blog\" \/>\n<meta property=\"og:url\" content=\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/\" \/>\n<meta property=\"og:site_name\" content=\"Asha24 Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-31T00:52:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-03T12:12:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"459\" \/>\n\t<meta property=\"og:image:height\" content=\"306\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Mahesh\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"18 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/asha24.net\/blog\/#website\",\"url\":\"https:\/\/asha24.net\/blog\/\",\"name\":\"Asha24 Blog\",\"description\":\"Dedication Towards Learning\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/asha24.net\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg\",\"contentUrl\":\"https:\/\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg\",\"width\":459,\"height\":306},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#webpage\",\"url\":\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/\",\"name\":\"Microsoft Excel Interview Questions and Answers - Asha24 Blog\",\"isPartOf\":{\"@id\":\"https:\/\/asha24.net\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#primaryimage\"},\"datePublished\":\"2018-01-31T00:52:17+00:00\",\"dateModified\":\"2023-02-03T12:12:55+00:00\",\"author\":{\"@id\":\"https:\/\/asha24.net\/blog\/#\/schema\/person\/f6167b78bbaddfc399ae1154cd5b6759\"},\"breadcrumb\":{\"@id\":\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/asha24.net\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Microsoft Excel Interview Questions and Answers\"}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/asha24.net\/blog\/#\/schema\/person\/f6167b78bbaddfc399ae1154cd5b6759\",\"name\":\"Mahesh\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/asha24.net\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/085c03e75ffb51af2509c1cfad9c7d78b30236d43a008db2e46f96e2f40c67fc?s=96&d=wavatar&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/085c03e75ffb51af2509c1cfad9c7d78b30236d43a008db2e46f96e2f40c67fc?s=96&d=wavatar&r=g\",\"caption\":\"Mahesh\"},\"url\":\"https:\/\/asha24.net\/blog\/author\/mahesh\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Microsoft Excel Interview Questions and Answers - Asha24 Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/","og_locale":"en_US","og_type":"article","og_title":"Microsoft Excel Interview Questions and Answers - Asha24 Blog","og_url":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/","og_site_name":"Asha24 Blog","article_published_time":"2018-01-31T00:52:17+00:00","article_modified_time":"2023-02-03T12:12:55+00:00","og_image":[{"width":459,"height":306,"url":"https:\/\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg","path":"\/home\/reviews981\/public_html\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg","size":"full","id":48154,"alt":"","pixels":140454,"type":"image\/jpeg"}],"twitter_card":"summary_large_image","twitter_misc":{"Written by":"Mahesh","Est. reading time":"18 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebSite","@id":"https:\/\/asha24.net\/blog\/#website","url":"https:\/\/asha24.net\/blog\/","name":"Asha24 Blog","description":"Dedication Towards Learning","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/asha24.net\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#primaryimage","inLanguage":"en-US","url":"https:\/\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg","contentUrl":"https:\/\/asha24.net\/blog\/wp-content\/uploads\/2018\/03\/microsoft-excel-interview-questions-and-answers-1.jpg","width":459,"height":306},{"@type":"WebPage","@id":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#webpage","url":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/","name":"Microsoft Excel Interview Questions and Answers - Asha24 Blog","isPartOf":{"@id":"https:\/\/asha24.net\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#primaryimage"},"datePublished":"2018-01-31T00:52:17+00:00","dateModified":"2023-02-03T12:12:55+00:00","author":{"@id":"https:\/\/asha24.net\/blog\/#\/schema\/person\/f6167b78bbaddfc399ae1154cd5b6759"},"breadcrumb":{"@id":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/asha24.net\/blog\/microsoft-excel-interview-questions-and-answers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/asha24.net\/blog\/"},{"@type":"ListItem","position":2,"name":"Microsoft Excel Interview Questions and Answers"}]},{"@type":"Person","@id":"https:\/\/asha24.net\/blog\/#\/schema\/person\/f6167b78bbaddfc399ae1154cd5b6759","name":"Mahesh","image":{"@type":"ImageObject","@id":"https:\/\/asha24.net\/blog\/#personlogo","inLanguage":"en-US","url":"https:\/\/secure.gravatar.com\/avatar\/085c03e75ffb51af2509c1cfad9c7d78b30236d43a008db2e46f96e2f40c67fc?s=96&d=wavatar&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/085c03e75ffb51af2509c1cfad9c7d78b30236d43a008db2e46f96e2f40c67fc?s=96&d=wavatar&r=g","caption":"Mahesh"},"url":"https:\/\/asha24.net\/blog\/author\/mahesh\/"}]}},"_links":{"self":[{"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/posts\/48153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/comments?post=48153"}],"version-history":[{"count":5,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/posts\/48153\/revisions"}],"predecessor-version":[{"id":52128,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/posts\/48153\/revisions\/52128"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/media\/48154"}],"wp:attachment":[{"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/media?parent=48153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/categories?post=48153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/asha24.net\/blog\/wp-json\/wp\/v2\/tags?post=48153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}