- Tips on how to use Microsoft Excel. Although you can use the =UPPER() function or formula to lowercase all the text in a cell, there are times when you may want to.
- A TMDL or Total Maximum Daily Load is a calculation of the maximum amount of a pollutant that a waterbody can receive and still meet water quality standards. Computer.
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 you're looking for a job in MIS/Automation/Dashboard creation etc. Business Analyst, Senior Analyst, Associate Analyst, etc involving MS Excel, MS Access, VBA, SQL, Cognos, ASP.
I know VBA in Excel isn't the quickest of things - but I need the most efficient (i.e. quickest) way to loop through a large sample of rows. Currently I have: For.
NET etc then Click here on 'Excel VBA Job Postings'Ques 0. What is the difference between By. Val 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 variable's value can be changed by the procedure. By. Val: 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. 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.
Set the Calculation mode to xlCalculationManual so that no calculations are carried out within the Excel Workbook until the Calculation mode is changed back to.
Read more about it here> > Ques 0. What is the meaning of Option Explicit and Option Base? Solution: 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.
![Excel Vba Manual Calculation On Open Access Excel Vba Manual Calculation On Open Access](https://i.stack.imgur.com/vzx1d.png)
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, you're working over 3.
Long as a data type. The Long data type is a 3.
The Single data type is a 3. 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 range?
Solution: Last Row in a column can be find using End(xl. Up) and Last Column in a row can be find using End(xl. To. Left). For e. Range("A1. 04. 85.
End(xl. 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.
Worksheets("Sheet. Please change the sheet name. Method #1: By Finding Last used cell in the worksheet. Row = wks. Range("A1"). Special. Cells(xl. Cell. Type. Last. Cell). Row. 'Method #2: Using Table Range.
Row = wks. List. Objects("Table. Range. Rows. Count. Method #3 : Manual way of selecting last Row : Ctrl + Shift + End. Row = wks. Cells(wks. Rows. Count, "A").
End(xl. Up). Row. Method #4: By using Used. Range. wks. Used. Range 'Refresh Used. Range. lst. Row = wks.
Used. Range. Rows(wks. Used. Range. Rows. Count). Row. 'Method #5: Using Named Range. Row = wks. Range("My. Named. Range"). Rows.
Count. 'Method #6: Ctrl + Shift + Down (Range should be the first cell in data set). Row = wks. Range("A1"). Current. Region. Rows. Count. 'Method #7: Using Range. Find method. lst.
Row = wks. Range("A: 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 aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/9. 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 b/w 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 Breakpoints(F9), 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. 0%2. 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 cat's 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 Sheet's visible property and set it to xl. Sheet. Very. Hidden . For eg. Sheets(1). 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 = Union(rng. Ques 1. 3. Which are the 2 macro languages and which do you use? Solution: XLM (used in Excel 9. VBA(used for 2. 00.
Obviously, VBA is in use these days. 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. Ques 1. 5. How can you add a drop- down list to a cell so the user can choose a value from the list?
Solution: Using 'Data Validation'. Ques 1. 6. How can you increase the number of rows in a worksheet? Solution: No one can't. They're fixed as 6. Excel 2. 00. 3 or before and 1. Excel 2. 00. 7 & Excel 2. Ques 1. 7. How can you increase the number of columns in a worksheet?
Solution: No one can't. They're fixed as 2. Excel 2. 00. 3 or before and 1. Excel 2. 00. 7 & Excel 2. Ques. 1. 8. How will you distribute a workbook such that it can't be copied using macro or anything? Solution: 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)Ques 1.
Your colleague created a dashboard and when you enter a value, it appears with two decimal places. For example, when you enter 2.
What's wrong? Solution: By chance Excel's fixed- decimal mode was turned on. To return to normal,Excel 2. Click Tools and then Options to display the Options dialog box.