Most of us already know that you can put Excel into a state where the formulas show – instead of values. This function is called Show Formulas (on the Ribbon: Formulas tab -> Show Formulas).
.
You can also press Ctrl ~ on your keyboard to put your sheet into “show formulas” state.
.
.
Sometimes the formulae get bigger and bigger and they eventually become so complex that they become a pain. You can use an not very well known function in Excel library to comment parts of your formulae so that they become somewhat readable.
--Want your shapes to really "pop" off the page? Add a drop shadow to them, as described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)
--When we are in a hurry we don't like the fact that Office Help takes long to connect to the web and then help us out. Slow internet connection annoys us as the Help Dialog in Microsoft Office tries to download new content as we open it. The feature of online help is there to benefit us from the latest help files. However, most of the times we want to get things done fast without having to wait for it. There is a simple trick to speed up the Help Dialog in Office 2007 and 2010. Follow these easy steps to speed up the Help Dialog in Office 2007 and 2010:
--All your work or data in the Office application is saved automatically after that specific time interval. This feature keeps you from losing work you have done. By default, this AutoRecover interval is set to 10 minutes in all the Office 2007 applications. However, it is possible to change AutoRecover interval in Office 2007. If you change AutoRecover interval in Office 2007 to less than 10 minutes such as after every 5 minutes, you will be able to save your work or data frequently.
--Michael asked if there is a way to selectively load add-ins for specific worksheets. There is a way to do this, but it involves the use of macros attached to the Workbook module for the specific worksheets. Follow these general steps:
A great way to customize Excel is to add your macros to a toolbar. That way you can run them quickly and easily. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)
--Word is a very flexible program. You can configure it to work just about any way you can imagine. For instance, you can create a macro, and then add it to a toolbar.
--The VLookup formula returns data from any column you choose in the data table, simply change the number of the column in the third argument.
Static array variables
Insted of using several unique variables to store information, you can use an array variable.
When you know how many elements you need to store in the array, you can use a static array variable like this :
Adding the Code 3: Manipulating the Form
Finally, an example of how the controls on a form can be manipulated whilst it is in use. When the control properties were set, the Enabled property of the Vegetarian check box was set to False. When a control is not enabled the user cannot enter a value into it, although it can hold a value that was there already, and VBA can add, remove or change the value.
Adding the Code: 2 Making the Buttons Work
There are three command buttons on the form and each must be powered by its own procedure. Starting with the simple ones…Coding the Cancel Button:Earlier, we used the Properties Window to set the Cancel property of the Cancel button to True. When you set the Cancel property of a command button to True, this has the effect of "clicking" that button when the user presses the Esc key on their keyboard. But this alone will not cause anything to happen to the form. You need to create the code for the click event of the button that will, in this case, close the form. Here's how:1. With the UserForm open for editing in the Visual Basic Editor, double-click the Cancel button. The form's code window opens with the cmdCancel_Click() procedure ready for editing.2. The code for closing a form is very simple. Add a line of code to the procedure so it looks like this:Private Sub cmdCancel_Click()
Unload Me
End Sub
Download Sample Excel file
The Course Booking Form
The Course Booking Form is a simple form illustrating the principles of UserForm design and the associated VBA coding.It uses a selection of controls including text boxes, combo boxes, option buttons grouped in a frame, check boxes and command buttons.When the user clicks the OK button their input is entered into the next available row on the worksheet.
View Sample Excel file
Why Loops?
The purpose of a loop is to get Excel to repeat a piece of code a certain number of times. How many times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this for as many times as there are rows of data).Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of different ways to use loops.
To help you exploit these advantages in the handling of Vietnam tinhd table to Microsoft Excel software, would introduce a number of tips is considered the best of this software:
Displaying the argument of the function
To see the argument in a formula, press Ctrl-Shift-A For example, if you type = RATE, and press Ctrl-Shift-A, you can see all the arguments for this function (eg = RATE (nper, pmt, pv, fv, type, guess)).
If you want more details, type in: = RATE
And press Ctrl + A to display in the Function Wizard.
