Create & Use macros in Spreadsheet | Information Technology for Class 10 PDF Download

What is macro?

The best way to remember and learn something is by listening. So if you want to learn something you can record the contents in your own voice and listen as many times as you want and you can learn anything easily. Similarly, a spreadsheet provides a feature called macro to record the commands, tasks, or the activities performed by the user on a specific worksheet or set of worksheets, and then it can be used to avoid repetition of commands or similar tasks in other worksheets.

A macro is a recorded name of set of tasks or commands in MS Excel which is used to repeat them by single click or shortcut key.

Advantages of Macro

• It saves user’s time
• Repeat a number of commands in just one click or using the shortcut key
• User can record numerous formatting commands or formulas in a single worksheet
• User can use their names for each macro
• It reduces the errors occurring with repetitive tasks
• It is capable to perform any kind of complex calculations easily

The developer tab

MS Excel provides a developer tab to work with macros. By default, it is not activated in excel. Follow these steps to activate the Developer tab.

• Click on File –> Options.
• A new dialog box appears, Find the customize ribbon from the dialog box.
• Find the main tabs then Developer check box.
• Click on the Developer checkbox.
• Click on the OK button.

Creating a macro

I have prepared data for salesmen for month wise sales and recording macro to prepare a report. To create a macro follow these steps:

Step 1: Place the cursor in the cell C2.
Step 2: To start the process, click on the Developer –> Use relative references to copy the relevant cell address of the worksheet.

Step 3: Now click on Developer –> Record Macro.

Step 4: Now record macro dialog box will appear as displayed in the following screenshot.

1. Macro Name: Give a name for your macro.
2. Shortcut key: You can assign a shortcut key for the macro.
3. Store macro in: It provides three locations to store your macro as follows:
   • Personal macro workbook
   • New Workbook
   • This Workbook
4. Description: You can write a brief description of your macro.

Step 5: I have selected the option this workbook. Click on OK button. It will display two buttons that indicates recording is started. Observer the following screen shot as given 1 and 2 number.

Step 6: Now type formulas in C13, C14, and C15. Similarly type the formulas for Maximum and Minimum. Apply formatting if you want to apply and click on Stop recording button as displayed in step 5.

Running Macro

Step 1: Place the cursor in D4.
Step 2: Now click on Developer –> Macros option.
Step 3: Macro dialog box will appear with the list of Macros.
Step 4: Select a macro to run from the list and click on Run button.

Step 2: Open macros dialog box

Step 3 and 4: Select and macro and run

Step 5: Display result after running macro

Introduction to using macro as function

AS you are aware that MS excel provides numerous functions and formulas. At the other side there are certain tasks which cannot be performed by a function.
In this scenario user need to create a function to do such tasks using macro as a function. In this article we are going to discuss how to create a function in MS Excel using macro.

Creating a function for using macro as function

To create a function for using macro as function class 10 follow these steps:

Step 1: Click on Developer –> Visual Basic option from code group.

Step 2: Write following code in module:

Function Multiply(a As Integer, b As Integer) As Integer
Multiply = a * b
End Function

In above function, use the same name inside line as and pass the values in first line. The words [su_highlight background=”#f8ff73″]Keywords – Function, Integer, End Function[/su_highlight] converts the first letter into capital by itself.

a and b are two values can be taken from excel worksheet.

Step 3: Now return to MS Excel screen and just type =, you will get Multiply function there as a formula. Observe the following screenshot:

Step 4: Select the Multiply function and now use it as traditional function or formula.

Passing Arguments and Values to a macro

In the above function variable, a and variable b is written inside a function in line 1 code that is considered as arguments.
The values of these arguments will come from excel worksheet when you write the function in the MS Excel cell.
Select the cells which reference you want to take to compute the data using function

Accessing cells directly

• To access cells directly refers to accessing cells in VB code using developer tab.
• Visual basic provides range() function to access cells directly. The value can be written in following manner.
• [su_highlight background=”#f8ff73″]range(celladress).value[/su_highlight]