Assignments Of Class 11: Introduction to Macros and VBA,

Assignments Of Class 11: Introduction to Macros and VBA,

---

Assignment 1: Record and Run a Macro

Objective: Record a macro to format a range of cells and run it.

Instructions:

1. Open a new Excel workbook.
2. Record a macro to perform the following actions:
• Select the range A1 to A10.
• Change the font color to red.
• Change the font size to 14.
• Fill the background color with light green.
3. Stop the macro recording.
4. Run the recorded macro to apply the formatting.

Solution:

1. Go to the View tab, click Macros, and select Record Macro.
2. Name the macro FormatCells, assign a shortcut (e.g., Ctrl + Shift + F), and click OK.
3. Select the range A1 to A10 and perform the formatting actions.
4. Stop recording the macro.
5. To run the macro, use the shortcut or go to View > Macros > View Macros, select FormatCells, and click Run.

---

Assignment 2: Create a Basic VBA Script

Objective: Write a VBA script to calculate the total of a specified range and display the result in a message box.

Instructions:

1. Open the VBA Editor (press ALT + F11).
2. Create a new module (Right-click on any existing module in the "Modules" section > Insert > Module).
3. Write a script that:
• Sums the values in the range B1 to B10.
• Displays the total in a message box.
4. Run the script to verify it works.

Solution:

Sub CalculateTotal()

    Dim total As Double

    total = Application.WorksheetFunction.Sum(Range("B1:B10"))

    MsgBox "The total of the range B1 to B10 is: " & total

End Sub

• Run the script by pressing F5 in the VBA Editor or from Excel by going to View > Macros and selecting the CalculateTotal macro.

---

Assignment 3: Edit a Recorded Macro

Objective: Modify a previously recorded macro to include additional formatting.

Instructions:

1. Record a macro that changes the font to italic and adds a border to the range C1 to C5.
2. Open the VBA Editor and find the recorded macro.
3. Edit the macro to also change the font color to blue and the fill color to light yellow.
4. Save and run the edited macro.

Solution:

1. Record the macro as FormatC1C5 by selecting the range C1 to C5 and applying italic font and borders.
2. In the VBA Editor, the recorded macro might look like this:

Sub FormatC1C5()

    Range("C1:C5").Font.Italic = True

    Range("C1:C5").Borders.LineStyle = xlContinuous

End Sub

3. Edit the macro as follows:

Sub FormatC1C5()

    Range("C1:C5").Font.Italic = True

    Range("C1:C5").Borders.LineStyle = xlContinuous

    Range("C1:C5").Font.Color = RGB(0, 0, 255) ' Change font color to blue

    Range("C1:C5").Interior.Color = RGB(255, 255, 153) ' Change fill color to light yellow

End Sub

4. Save the changes and run the macro.

---

Assignment 4: Create a Macro with a Loop

Objective: Write a macro that applies formatting to a range of cells using a loop.

Instructions:

1. Open the VBA Editor.
2. Write a macro that:
• Loops through the range D1 to D10.
• Changes the background color of even-numbered rows to light gray and odd-numbered rows to white.
3. Run the macro to see the formatting applied.

Solution:

Sub FormatRows()

    Dim i As Integer

    For i = 1 To 10

        If i Mod 2 = 0 Then

            Range("D" & i).Interior.Color = RGB(211, 211, 211) ' Light gray for even rows

        Else

            Range("D" & i).Interior.Color = RGB(255, 255, 255) ' White for odd rows

        End If

    Next i

End Sub

• Run the macro to apply the alternating colors to the rows in the range D1 to D10.

---