Excel VBA Oral Interview Questions. These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is aware of terminologies. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA. If youre looking for a job in MISAutomationDashboard creation etc. Business Analyst, Senior Analyst, Associate Analyst, etc involving MS Excel, MS Access, VBA, SQL, Cognos, ASP. NET etc then Click here on Excel VBA Job PostingsQues 0. What is the difference between By. Excel Vba Get Active File Names Too Long To CopyVal and By. Ref and which is the default Solution By. Ref If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result, the variables value can be changed by the procedure. By. Val If you pass an argument by value when calling a procedure the variables value can be changed with in the procedure only outside the actual value of the variable is retained. By. Ref 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. Read more about it here Ques 0. What is the meaning of Option Explicit and Option BaseSolution 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. Ques 0. 3. What are various data type and their size Solution i The Boolean data type has only two states, True and False. These types of variables are stored as 1. VBA.Create.PDF.and.Email.It.Anim.gif' alt='Excel Vba Get Active File Names' title='Excel Vba Get Active File Names' />Byte numbers and are usually used for flags. The Byte data type is an 8 bit variable which can store a value from 0 to 2. The Double data type is a 6. The Integer data type is a 1. Integers should be used when you are working with values that can not contain fractional numbers. In case, youre working over 3. Summary Introduces Visual Basic for Applications VBA in Excel 2010 to the Excel power user who is not yet a programmer. This article includes an. Learn how to use Excel worksheet functions in Excel VBA code. In this article, Ive shown you how to use INDEX and MATCH Functions in your VBA Code. Paper AS11 Using VBA and BASE SAS to Get Data from SAS to Excel Without Data Integrity Issues Timothy Adlington, AstraZeneca UK Ltd, Manchester, UK. I want to search through existing Excel files with a macro, but I dont want to display those files when theyre opened by the code. Is there a way to have them open. Long as a data type. The Long data type is a 3. The Single data type is a 3. Excel Vba Get Active File Names Too Long To Delete' title='Excel Vba Get Active File Names Too Long To Delete' />When you need fractional numbers within this range, this is the data type to use. The String data 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 2. ASCII character set. Ques 0. 4. What is the difference between Active. Workbook and This. Workbook Solution This. Workbook refers to the workbook where the code is being written while Active. Workbook refers to the workbook which is in the active state with active window. In the case of only one workbook open, Active. Workbook is same as This. Workbook. Ques 0. How will you find the last used row or column in a rangeSolution Last Row in a column can be find using Endxl. Up and Last Column in a row can be find using Endxl. To. Left. For e. RangeA1. Endxl. Up. Row gives last used row of Column A. Sub Finding. Last. Row. PURPOSE Various ways to find the last row in a column or a range. Dim wks As Worksheet. Dim lst. Row As Long. Set wks This. Workbook. WorksheetsSheet. Please change the sheet name. Method 1 By Finding Last used cell in the worksheet. Row wks. RangeA1. Special. Cellsxl. Cell. Type. Last. Cell. Row. Method 2 Using Table Range. Row wks. List. ObjectsTable. Range. Rows. Count. Method 3 Manual way of selecting last Row Ctrl Shift End. Row wks. Cellswks. Rows. Count, A. Endxl. Up. Row. Method 4 By using Used. Range. wks. Used. Range Refresh Used. Range. lst. Row wks. Used. Range. Rowswks. Used. Range. Rows. Count. Row. Method 5 Using Named Range. Row wks. RangeMy. Named. Range. Rows. Count. Method 6 Ctrl Shift Down Range should be the first cell in data set. Row wks. RangeA1. Current. Region. Rows. Count. Method 7 Using Range. Find method. lst. Row wks. RangeA A. Cells. Find, Search. Order xl. By. Rows, Look. In xl. Values, Search. Direction xl. Previous. Row. Ques 0. 6. What is the difference between Active. X and Form Controls Solution 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 2. Active. X controls can only be used on worksheets. Active. X controls do not work in Mac. Excel. ii The Forms controls arent very complicated, and they have been part of Excel for longer they were used in Excel 59. Controls Toolbox Excel 9. Being newer, the Active. X controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet. Ques 0. 7. What is the difference bw Functions and Subroutines Solution i Subroutines never return a value but functions does return values. A function could not change the values of actual arguments whereas a subroutine could change them. Ques 0. 8. How to debug a VBA code Solution Using BreakpointsF9, Step by step execution F8, Debug. Print Immediate Window and Watch window. Ques 0. 9. Draw basic Excel Object Model. Solution Application Workbooks Worksheets Range Chart. Ques 1. 0. What are properties, methods, events and objects Solution For details click here http msdn. VS. 8. 02. 9. aspx. All the controls in the Tool. Box except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events. A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. A method is 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 the cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cats methods might be move, jump, play, breath etc. Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a Text. Box control and a few of its associated events to understand the concept of event driven programming. The Text. Box control supports various events such as Change, Click, Mouse. Move and many more that will be listed in the Properties dropdown list in the code window for the Text. Box control. We will look into a few of them as given below. The code entered in the Change event fires when there is a change in the contents of the Text. Box. The Click event fires when the Text. Box control is clicked. The Mouse. Move event fires when the mouse is moved over the Text. Box. Ques 1. 1. How to hide a worksheet so that a user cannot unhide it Solution Use Sheets visible property and set it to xl. Sheet. Very. Hidden. For eg. Sheets1. Visible xl. Sheet. Very. Hidden will super hide the first worksheet of the workbook. Ques 1. 2. Union is used for Solution To unite the different ranges depending on the logic. It is similar to set union, here range works as the set. For eg. Set nrange Unionrng. Ques 1. 3. Which are the 2 macro languages and which do you use Solution XLM used in Excel 9. VBAused for 2. 00. Obviously, VBA is in use these days. Free Download Serial Number Test Drive Unlimited 2 Youtube there. Ques 1. 4. Can you lock cells such that only specific users can modify them Solution There is a option Allow users to edit ranges can be used for this purpose.