Chapter 7 continued ...
Protecting a cell or a group of cells prevents writing, editing or deleting
the cell, or damaging the formula. Two conditions must be met to protect a cell:
the cell must be locked, and the sheet must be protected.
Select a cell in the sheet and press Ctrl+1. In the Format Cells dialog box, select Protection. Select the Locked option.
Protecting a sheet, the second condition
From the Tools menu, select Protection, Protect sheet, and click OK (password is optional).
Protecting formulas requires isolating cells with formulas from the rest of the cells in the sheet, locking them, and then protecting the sheet.
Stage 1 Canceling the locked format of all the cells in the sheet
1. Select all the cells in the sheet by pressing Ctrl+A.
2. Press Ctrl+1.
3. Select the Protection tab.
4. Clear the selection of the locked option.
5. Click OK.
Stage 2 Selecting cells with formulas
1. Press F5.
2. Click Special.
3. Select the Formulas option.
4. Click OK.
Stage 3 Locking cells with formulas
1. Press Ctrl+1.
2. Select the Protection tab.
3. Select the Locked option.
4. Click OK.
Stage 4 Protecting the sheet
1. From the Tools menu, select Protection, Protect sheet.
2. Click OK (password is optional).
| The Protect Sheet dialog box in Excel 2002 allows you to select various topics for protection. In Allow all users of this worksheet to in the Protect Sheet dialog box, select the desired options. |
New
In
2002 |
Chapter 7 continued ...
Displaying a Formula in a Cell and Printing Formulas
The result of calculating a formula (value) appears on the screen and can be printed by clicking the Print icon. However, you can usually view the syntax of a formula only by selecting the cell. To see all formulas on the sheet, press Ctrl+~ (the ~ sign is located to the left of the number 1 on the keyboard), or from the Tools menu, select Options. Then select the View tab, and select the Formulas option.
To return to the normal view, press Ctrl+~ again.
Use the usual method of printing in Excel to print from the sheet while formulas are displayed using Ctrl+~.
Using VBA to Create and Add Functions to the Function Wizard
When you work in Excel, there will be times when you need to create a complex custom formula.
Take, for example, a formula to calculate the net salary from a gross salary. This is a very complex calculation, as this type of formula is made up of fixed pieces of data, including income tax schedule, social security laws, tax credits and additional credits and deductions.
Another example would be a formula for calculating sales commission that varies depending on the level of sales, formulas for converting currency, conversion of weights and measures (a Convert function already exists in Excel), or a conversion of text. See Chapter 16, Importing Text Files.
VBA formulas can be used for several reasons, one of which is to create custom functions for your personal use. You can add these functions to the Function Wizard.
Custom functions, example and explanation
Create a function called Add to total two numbers in different cells. The function has two argument boxes.
Formula structure:
© Function name = Add
© The function arguments are placed in parentheses. The above example has two argument boxes.
© The last piece of the formula, As Integer, defines the value of the variables as a whole number. See the explanation in the Variables section of Chapter 28, Other VBA Techniques.
© Function calculation where the function receives the results of the calculation and returns the result.
1. Type numbers into cells A1:A2.
2. Select cell A3 in the same sheet.
3. Press Shift+F3 (opens the Paste Function dialog box).
4. Select the User Defined category.
5. Select the Add function.
6. In the first argument box, select cell A1.
7. In the second argument box, select cell A2.
8.
Click OK.
Using a function to calculate accrued income tax from gross salary
This function will calculate the tax payable based on gross salary, marginal tax schedule and credit point schedule.
The function has a single edit box gross salary (income).
Schedules the values are defined using a constant variable, Const, which cannot be changed when the function is operating. The constant variables are loaded into memory cells when the function is operated.
Case like IF, the function allows you to check a large number of instances.
