You may think of Microsoft Excel as a simple tool used to simplify your book keeping job, but Microsoft excel is developed by Microsoft using a programming language called Visual Basic for Applications or VBA and there are various features in this language which you can apply to increase the functionality of Microsoft Excel. Macro is one such feature of Visual Basic for Applications. Macro contains small bits of code that can perform simple tasks from within your Excel worksheet. If you learn how to write a macro, you will see that you can do additional tasks in Microsoft Excel as per your requirements besides simple calculations and bookkeeping jobs.
Let’s explore the process of creating a simple macro in Microsoft Excel which counts the number of rows in the Excel Spreadsheet.
Step1: Open the Visual Basic Editor by clicking on the visual basic option on the Developer tab in the Excel spreadsheet.
Step2: Click on the Tool menu and select Macro. There type a name for the macro in the Macros dialog box and click on create. A Module window will open with
already written in it.
You are going to write your macro in this module window between Sub m()and End Sub. Let’s write a loop macro that counts a number of rows till it reaches a specified point.
Step 3: Start the loop from an empty cell. Type
Do While Cells(a, 1). Value < > “”A = a + 1b = b + 1Loop
If you want the name to look darker than another text throughout your Excel spreadsheet, write
If MyCell.Value Like “Name” ThenMyCell.Font.Bold = True End If
Step 4: Save the macro and close the Visual Basic for Applications editor. You have created your macro which has been saved in VBA Project folder.
You can also assign an existing macro to an object in your Excel Spreadsheet. An object in Microsoft Excel is any graph, chart or company logo. For assigning a macro to an object, follow the instructions given below:
Step1: Select an object you want your macro to attach to and right click on it.
Step2: Select Assign Macro from the drop down menu. Assign Macro dialog box will open with list of macros you have created. Select the macro you want to assign and click OK. The macro will be assigned to the object you have specified.
For deleting a macro do the following:
Step1: Go to the Microsoft Office logo on the upper left corner of Excel window and click on it. From the drop down menu, open the workbook. If you cannot see the workbook then it is hidden in Personal.xlsm macro workbook. You have to make it unhidden.
Step2: Click on the View tab on the main menu bar and choose Unhide.
Step3: Click on Personal option under the Unhide workbooks tab and then click OK.
You can also create a macro from a macro that you already have in your workbook. For this, you need to open the workbook that contains the macro from which you wish to create a new macro.
On the developer tab, click on Macros and the Macros dialog box will come up. Choose the macro and select edit. Select the desired code, copy it and paste it to module you want to.