Practical 18 – Mastering Macros for Task Automation

1. Objective

The primary objective of this practical is to equip students with the ability to create and use macros in Excel to automate repetitive tasks. Students will learn to:

·         Enable the Developer tab in the Excel ribbon.

·         Record simple macros using the Macro Recorder.

·         Understand the difference between absolute and relative references when recording macros.

·         Run macros from various locations.

·         Assign macros to buttons or shapes for easy execution.

·         View and make minor edits to VBA code generated by the Macro Recorder.

·         Appreciate the efficiency and error reduction that automation brings to spreadsheet management.

2. Introduction

In Excel, you often perform the same sequence of actions repeatedly – perhaps formatting a report, generating a summary, or preparing data for analysis. Macros are a way to record these sequences of commands and then play them back at any time, saving significant time and effort, and ensuring consistency. A macro is essentially a small program written in VBA, Excel’s underlying programming language. While writing complex VBA code can be challenging, Excel’s "Macro Recorder" allows anyone to create powerful macros without needing to know any programming. This practical will introduce you to the magic of task automation through macros.

3. Pre-requisites

·         Basic understanding of Microsoft Excel interface and its functionalities.

·         Familiarity with basic data entry, formatting, and formula application.

·         Successful completion of Practical 3.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will use your marksheet from Practical 3 and a new blank worksheet to demonstrate the recording and running of macros.

Step 5.1: Enabling the Developer Tab

The Developer tab is where you find most of the macro-related tools. By default, it might be hidden.

1.     Open Excel: Launch Microsoft Excel.

2.     Access Excel Options:

o    Click File > Options.

o    In the Excel Options dialog box, click Customize Ribbon in the left pane.

3.     Enable Developer Tab:

o    In the right pane, under Main Tabs, check the box next to Developer.

o    Click OK.

4.     Observation: The Developer tab will now appear in your Excel ribbon.

Step 5.2: Recording a Simple Macro

Let’s record a macro to format the heading of your marksheet.

1.     Open Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Select Target Cell: Click on cell A1 (where your marksheet heading "Dr. G. Y. Pathrikar College" is located).

3.     Start Recording Macro:

o    Go to the Developer tab.

o    In the Code group, click Record Macro.

4.     Record Macro Dialog Box:

o    Macro name: Type FormatMarksheetHeading..

o    Shortcut key: You can assign a keyboard shortcut (e.g., Ctrl+Shift+H). Be careful not to overwrite existing shortcuts.

o    Store macro in: Select This Workbook.

o    Description: Type Formats the main heading of the marksheet..

o    Click OK.

5.     Observation: The Record Macro button on the ribbon changes to Stop Recording, indicating that Excel is now recording your actions.

6.     Perform Actions to Record:

o    With A1 still selected, go to the Home tab.

o    Click Merge & Center.

o    Change Font Size to 18.

o    Change Font Color to Dark Blue (or any color).

o    Change Fill Color to Light Blue (or any color).

o    Bold the text.

7.     Stop Recording:

o    Go back to the Developer tab.

o    In the Code group, click Stop Recording.

8.     Observation: Your actions are now saved as a macro.

Step 5.3: Running a Macro

Now, let’s see our recorded macro in action.

1.     Revert Changes: Undo the formatting on cell A1 (Ctrl+Z or use the Undo button).

2.     Run from Developer Tab:

o    Go to the Developer tab.

o    In the Code group, click Macros.

o    In the Macro dialog box, select FormatMarksheetHeading.

o    Click Run.

3.     Observation: The formatting should be instantly reapplied to cell A1.

4.     Run using Shortcut Key (if assigned): Undo the formatting again. Press Ctrl+Shift+H (or whatever shortcut you assigned). The macro should run.

Step 5.4: Understanding Relative References

The previous macro used absolute references, meaning it would always format cell A1 regardless of which cell was selected. Let’s record a macro that formats the currently selected cell.

1.     Open a New Sheet: Insert a new worksheet in your workbook.

2.     Enable Relative Reference:

o    Go to the Developer tab.

o    In the Code group, click Use Relative References..

3.     Start Recording Macro:

o    Click Record Macro.

o    Macro name: Type HighlightCell.

o    Store macro in: This Workbook.

o    Click OK.

4.     Perform Actions to Record:

o    With Use Relative References active, format the currently selected cell (e.g., B2) with a yellow fill and bold text.

5.     Stop Recording:

o    Go to the Developer tab.

o    Click Stop Recording.

6.     Test Relative Macro:

o    Click on a different cell (e.g., D5).

o    Run the HighlightCell macro (via Macros dialog or shortcut).

o    Observation: The macro should format cell D5, not B2, because it was recorded using relative references.

Step 5.5: Assigning a Macro to a Button/Shape

Making macros easy to run with a click is a common practice.

1.     Go to your Marksheet tab.

2.     Insert a Shape:

o    Go to the Insert tab.

o    In the Illustrations group, click Shapes.

o    Choose a simple shape (e.g., a rectangle).

o    Draw the shape on your worksheet (e.g., near A1).

3.     Add Text to Shape: Right-click the shape, select Edit Text, and type Format Heading.

4.     Assign Macro:

o    Right-click the shape.

o    Select Assign Macro....

o    Choose FormatMarksheetHeading from the list.

o    Click OK.

5.     Test Button: Undo the formatting on A1. Click your newly created button.

o    Observation: The macro should execute, and the heading should be formatted.

Step 5.6: Viewing and Basic Editing of Macro Code

Even without knowing VBA, looking at the code can be insightful.

1.     Open VBA Editor:

o    Go to the Developer tab.

o    In the Code group, click Visual Basic. (Alternatively, press Alt+F11).

2.     Observation: The VBA editor window will open. In the Project Explorer pane on the left, expand VBAProject > Modules > Module1 (or a similar module name).

3.     View Code: Double-click on Module1.

o    Observation: You will see the VBA code for your recorded macros (e.g., Sub FormatMarksheetHeading()).

4.     Make a Small Change:

o    Find the line related to font size (e.g., .Size = 18). Change 18 to 20.

o    Close the VBA editor.

5.     Test Change: Undo the formatting on A1, then run the FormatMarksheetHeading macro again (via button or Macros dialog).

o    Observation: The font size should now be 20.

6.     Save Workbook as Macro-Enabled:

o    If you haven’t already, save your workbook. Excel will prompt you to save it as an Excel Macro-Enabled Workbook (.xlsm) to preserve the macros. Choose Yes or select this file type from the Save As dialog.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     Automate Marksheet Borders:

o    Record a macro named ApplyBorders that selects your entire marksheet data range (e.g., A3:J8) and applies All Borders.

o    Assign this macro to a keyboard shortcut (Ctrl+Shift+B).

o    Test the macro.

2.     Quick Data Entry Row:

o    Record a macro named InsertNewStudentRow that does the following:

§  Selects a cell in your marksheet (e.g., A9).

§  Inserts a new row above it (Home tab > Insert > Insert Sheet Rows).

§  Copies the formulas from the row above (e.g., G8:J8) to the newly inserted row (e.g., G9:J9).

§  Selects the Student Name cell in the new row (e.g., A9).

o    Important: When copying formulas, ensure you record it such that the formulas adjust for the new row. You might need to use Use Relative References for part of this, or carefully select and copy/paste formulas.

o    Assign this macro to a button named "Add New Student".

o    Test the macro by adding a few new student rows.

3.     Clear Formatted Cells Macro:

o    Record a macro named ClearSubjectMarks that selects the Subject 1 to Subject 5 cells for the last student row you just added and clears their contents.

o    Assign this macro to a button named "Clear Marks".

o    Test the macro.

4.     Font Formatting Macro:

o    Record a macro named ChangeStudentNameFont that, when run, changes the font of all student names in your marksheet to Arial Black and size 11.

o    View the VBA code for this macro and identify the lines that set the font name and size.

o    Run the macro.

5.     Macro-Enabled Workbook: Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm).

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsm file) containing your Marksheet and all macros created in the procedure and exercises.

·         Ensure the Developer tab is enabled.

·         Verify that all macros run correctly and perform their intended tasks.

·         Demonstrate to your instructor how to:

o    Run a macro using a shortcut key and a button.

o    Locate and identify the VBA code for one of your recorded macros.

·         Be prepared to explain the difference between absolute and relative references in macro recording.