Filter a range - GRADE IX



MACROS

If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) that are stored in a Microsoft Visual Basic module (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.) and can be run whenever you need to perform the task.

Recording macros

When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands. If you make a mistake when you record the macro, corrections you make are also recorded.

Making a macro easy to run

You can run a macro by choosing it from a list in the Macro dialog box. To make a macro run whenever you click a particular button or press a particular key combination, you can assign the macro to a toolbar button, a keyboard shortcut (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.), or a graphic object on a worksheet.

Macro security

Excel provides safeguards that help protect against viruses (virus: A computer program or macro that "infects" computer files by inserting copies of itself into those files. When the infected file is loaded into memory, the virus can infect other files. Viruses often have harmful side effects.) that can be transmitted by macros. If you share macros with others, you can certify them with a digital signature (digital signature: An electronic, encryption-based, secure stamp of authentication on a macro or document. This signature confirms that the macro or document originated from the signer and has not been altered.) so that other users can verify that they are from a trustworthy source. Whenever you open a workbook that contains macros, you can verify their source before you enable them.

STEPS:

1. Set the security level to Medium or Low.

1. On the Tools menu, click Options.

2. Click the Security tab.

3. Under Macro Security, click Macro Security.

4. Click the Security Level tab, and then select the security level you want to use.

2. On the Tools menu, point to Macro, and then click Record New Macro.

3. In the Macro name box, enter a name for the macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.).

[pic]

Notes

o The first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator.

o Do not use a macro name that is also a cell reference or you can get an error message that the macro name is not valid.

4. If you want to run the macro by pressing a keyboard shortcut key (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.), enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #.

Note:   The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open.

5. In the Store macro in box, click the location where you want to store the macro.

If you want a macro to be available whenever you use Excel, select Personal Macro Workbook.

6. If you want to include a description of the macro, type it in the Description box.

7. Click OK.

8. If you want the macro to run relative to the position of the active cell, record it using relative cell references. On the Stop Recording toolbar, click Relative Reference [pic]so that it is selected. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference [pic]again, so that it is not selected.

9. Carry out the actions you want to record.

10. On the Stop Recording toolbar, click Stop Recording [pic].

Data validation

To Prevent invalid data entry in a worksheet.

In many worksheets that you create, users will enter data to get the desired calculations and results. Ensuring valid data entry is an important task.

Steps:

1. select any one cell

2. data menu----validation

[pic]

3. Select any one option from the drop down arrows and set a criteria.

[pic]

4. Input message :- Type a title and message.(when you select that cell excel displays that message)

[pic]

5. Error alert:- type title and message (when the data you entered is invalid excel displays that message)----OK

[pic]

When you click a cell it displays the input message and when you type an invalid data in the cell it displays the error message.

| |[pic][pic][pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download