CP212 Lab 3 : Writing/Formatting a Spreadsheet Range

Labs Submission due: Friday 11:59 pm

Week of

Writing to a Worksheet

Examples:

' Get user input and write it to a couple of worksheets
Dim strUserName As String

strUserName = InputBox("What is your username?")

' Write it to the active worksheet
Activesheet.Range("A1").Value = strUserName
' also works
Range("A1").Value = strUserName

' Specify a worksheet with the name Results
Worksheets("Results").Range("A1").Value = strUserName

' Textbook uses Code Names for worksheets like this
wksResults.Range("A1").Value = strUserName

Getting Data from a Worksheet

Examples:

' Get the value from A1 and store it in a variable
Dim strColour As String
strColour = Range("A1").Value
' Get a value from a different worksheet
strColour = Worksheets("Colour Names").Range("A1").Value

' Also works if the worksheet has the code name wksColourNames
strColour = wksColourNames.Range("A1").Value

' Can also use Object variables for the Range along with the Worksheet code name
Dim rngStartCell As Range
Set rngStartCell = wksColourNames.Range("A1")

strColour = wksColourNames.rngStartCell.Value

Select an Entire Range of Unknown Size

Imagine a spreadsheet with the following data:

To select the data in the table you can use the Offset command so you can define the range, but specify only the data if you need to.

Example:

' Uses "first cell, last cell" notation
Range("B3", "E6").Interior.Color = vbYellow

MsgBox "See? The cells are yellow.", , "Just a pause"

'Basic Usage 'Colors the range no matter what size
Range("B4", Range("B4").End(xlDown).End(xlToRight)).Interior.Color = vbGreen

See the code and more examples in EndAndOffset.xlsm. If you have trouble downloading the file, look at the code here and copy/paste it into your own workbook. Use the data in the image above if you want to make it interesting.

You can also examine the file Ranges.xlsm.

The subroutine Range6() demonstrates how to select a range of cells whose size you don't know. It also shows how to determine its size. See the textbook for an explanation.

Removing "Dancing Ants"

After you copy a range of cells, the marquee selection indicator (or dancing ants) remains around the selection. When working on the spreadsheet, press the Esc key to remove them. In VBA code use the line: 

Application.CutCopyMode = False

That is what would be recorded if you were recording a macro of copying cells and pasting them, then pressing the Esc key.

Writing Equations into a Worksheet

Sometimes you want to write an equation into a range of cells instead of just the answer. If you wanted to write the answer you could use the WorksheetFunction method described in the previous lab. However, if you want to place the actual equation into the spreadsheet (so the answer updates if the data changes in the worksheet) use this method: 

Range("A8").Formula = "=sum(A1:A8)"

Notice that the syntax is different than how you would write it in VBA code because it has to be written the way a user would type it into a spreadsheet cell.

Set the Code Name of a Worksheet

A worksheet has both a name and a code name. The name of a worksheet appears on the tab of the worksheet inside the workbook. The code name is only visible in the VBE and users normally never see it without going into the VBE. Using worksheet code names can simplify your code. Set the code name for a worksheet using the Project Explorer inside the VBE:

Setting a worksheet code name.

Name a Range of Cells

In the following example, a number of cells are given Names using VBA code. If you are designing a worksheet as part of the application, range names can be created right from the beginning. However, creating range names using code can make your code simpler even if you choose to delete them after your program is done with them.

This example also includes how to declare a range object as a variable and use it in your code.

Sub SimpleExample()
    ' Name a range of cells
    Dim rngTitle as Range
    Range("A1").Name = "Title"
    Range("Title").Value = "Heart Rate App"

    ' Example of using the range object variable
    Set rngTitle = Range("Title")
    rngTitle.Font.Bold = True
End Sub

Practice

You can practice some of the techniques in this lab by completing the samples below if you wish.

  1. Create a subroutine that asks for 2 Integers using an inputbox and adds them together. Your program should display the results in a message box and write the result onto cell A1 of a worksheet.

    Tip: The result from an input box is a String, so remember to convert the strings to an Integer using CInt(). Otherwise "1" + "1" = "11".

  2. The file Formatting3.xls contains code that is very difficult to read. Besides that, it contains an error. Reformat it with indenting, white space, and comments, and fix the error so that it runs correctly. Save the file as a macro-enabled workbook. The program contains one error. If you get blue shading in $B$5:$B$8 instead of $B$3:$B$6 then your correction is simply in the wrong spot. Your final results should look like this:

Instructions

All tasks are to be completed on your own.

Tasks

Download the file 2020-2021_NHL_Standing.xlsx and complete the following tasks. Start by opening the file and saving it as a Macro Enabled Workbook (file extension .xlsm). Name this spreadsheet with your network login followed by _l03.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_l03.xlsm. Remember to save your work frequently in case there are problems.

Make sure you place a button on the worksheet to run every subroutine of the required tasks.

Put all items below in a subroutine. Write the code, don't record a macro.

  1. Use VBA to name the following ranges with the specified name: cell A1 as Title, Boldface the font of the label in the Title cell, and change its font size to 16. Merge cell range A1:H1 and center the text.

  2. Use VBA to name the following ranges with the specified name: the headings in row 2 as Headings,the teams sequence in column A as TeamsSequence.

  3. Complete the following items by using VBA to reference the named cell ranges:

    1. Boldface and italicize the headings in the Headings row, and change their font color to blue.

    2. Change the colour of the font for the team numbers in the TeamsSequence range to brown (any shade you like) and Italic.

    3. Enter the label Average in cell B34 and boldface it.

    4. Enter a formula in cell C34 that averages the scores above it.

    5. Enter the label Max in cell B35 and boldface it.

    6. Enter a formula in cell D35 that finds the maximum values in the W(Win) column. Copy this formula to the range E35:F35 (using VBA).

    7. Enter a formula in cell G3 that finds total point (PTS) = W*2+OT. Copy this formula to the range G4:G33 (using VBA).

    8. Enter a formula in cell H3 that finds points percentage (p%) = PTS / (no. of played games(GP) * 2). Copy this formula to the range H4:H33 (using VBA).

  4. Write a new subroutine to clear all the changes done on the original NHL worksheet. Place a button in the worksheet to run this subroutine.

Note
You have to use Button(s) on worksheet(s) to run the task(s) in this lab and all next labs. Marks will be deducted if you do not use buttons.

Links

Debugging

Programming Checklist

The following is a list of things you should have in all of your programs. Your IAs check for these things when working with you in the lab and they should be included in all of your assignments.