CP212 Lab 6 : Modular Programming

More Arrays

A 2 dimensional array is used to store information that is normally stored in a table. For example, let's consider this worksheet of student information:

In this simple example 6 student records are shown, but our code should work regardless of the number of students. For example, we could be writing an application that a teacher runs every term where the student list will always change.

In this case, it would be better to use a dynamically resized, 2-dimensional array to store the information about the students. Storing the information in an array makes accessing the information much faster than if we were to access the worksheet directly.

First we'll need to declare some variables:

Dim intNumStudents As Integer
Dim intNumColumns As Integer
Dim nRow As Integer, nCol As Integer
Dim rngStudents As Range

To declare the array and populate it with the data from the workheet called "Students" with the code name wsStudents:

Dim aStudents() As Variant ' To use quick array fill later

' Get the size of the range
With wsStudents.Range("A2")
   intNumStudents = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
   intNumColumns = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count

   ' Create an range object variable for all the students
   ' but not the column headings
   Set rngStudents = Range(.Offset(0, 0), .End(xlDown).End(xlToRight))

   ' Creating a named range could be done with this but
   ' using the above line makes it unncessary
   Range(.Offset(0, 0), .End(xlDown).End(xlToRight)).Name = "Students"
End With

' Resize the array to fit all the data
ReDim aStudents(intNumStudents, intNumColumns)

To load the data from the worksheet into the array, we could loop through the rows and loop through the columns. This works as expected with no side effects.

However, a quicker way is:

' Load the array with the values from the worksheet
aStudents = rngStudents

This works very quickly. However, the array must be declared a Variant, and a strange thing happens: the LBound of the array is now 1 and not 0.

Now the data is stored in an array and can be analyzed in a number of different ways, or some of the columns could be written to a worksheet and a chart created from the results. The possiblities are pretty much endless.

---

About The Names Collection and Name Object

The Names collection is the collection object which stores all the Name objects used in a Workbook. Because a Name can have different settings for its scope (where it is a valid reference), the Names collection resides in the Workbook, not the Worksheet.

When using Excel worksheets, Range Names are created and managed from the Formulas tab:

The collection of Names has a few important methods and properties, including Names.Count which returns the number of Name objects in the workbook, and Names.Add which allows the programmer to add named ranges.

The Name object refers to a specific defined name on the worksheet (also called a Range Name or a Named Range). The object has a few properties as well, including RefersTo:

strRangeAddress = someName.RefersTo

which contains the complete address of what cell on which worksheet the name refers to.

You can find out how many names are in a worksheet because almost all Collections (including the Names collection) have a Count method.

intNumNames = ActiveWorkbook.Names.Count

You can Add a name to the collection:

Dim nmeWorkers As Name
Set nmeWorkers = ActiveWorkbook.Names.Add("rngWorkers", "$B$5:$B$20")

Or use the format shown in the textbook.

You can also simply set a name using the Name property of a range which is the simplest way:

Range(rngAllData).Name = "DataValues"

---

Practice

1. Write a subroutine that looks through all the range Names in the Workbook and store the range Name and the cells that it refers to, in an array. Output the list of range names and what it refers to a set of cells in a new worksheet. Include column headings for your output, but you don't have to check or delete the results in your sub, though you can add that if you wish.
2. Using the file practice.xlsx, write a subroutine that determines the amount of data in the file and stores it all into a 2 dimensional array.

---

Instructions

• Save the spreadsheet file as a Macro Enabled Workbook (.xlsm) file before you write more code. Name your spreadsheet with your network login followed by _l06.xlsm. For example, if your network login was barn4520, then your lab file should be named barn4520_l06.xlsm. Save often.

• All tasks are to be completed on your own.

• Show the Developer Tab in Excel. Click File / Options Customize Ribbon and check the option for Developer Tab.

• Put all code into a Module. Use a different Module for each task and name them appropriately as Task1 or Task2 etc.

• Copy and paste this header into your code modules and fill it out:

  Option Explicit
  
  ' ==== CP212 Windows Application Programming ===============+
  ' Name: Your Name
  ' Student ID:
  ' Date:
  ' Program title:
  ' Description:
  '===========================================================+
  

• Make sure you test your tasks before submitting your lab to the dropbox to be marked.

• When finished upload your file to the Lab 6 Task Dropbox in MyLearingSpace.

---

Lab Tasks

Task 1:

• Download the file Numeric in Text.xlsx and complete the following tasks. Open the file and save it as a Macro Enabled Workbook (file extension .xlsm). Name this spreadsheet with your network login followed by _l06.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_l06.xlsm. Remember to save your work frequently in case there are problems.

• Write a function that takes one stringvalue as arguments. It should extract the numeric parts from the alphanumeric string. It should return the extracted numbers as Long type. Example:

  numericPart = NumericInText("A2")
  

  Results in "212". Test to see that it works when the input is from a spreadsheet. For example:

  

• You can use the function

   IsNumeric() 

  to test for the numeric parts in the String.

• Use a loop to go through all the characters of the string testing for numeric values. Applying the function on the text cells of the worksheet should give an output similr to:

  

Task 2:

• Write a function named ArrayEqual. It should accept two arrays of type Single, both of the same size, and it should return a boolean value indicating whether the two arrays are equal or not. It should return True if they are equal and False otherwise. For example, if you pass in the arrays (3,5,1) and (4,3,6) it should return False and the program should display a message indicating that two arrays are not equal.
• Write a subroutine named TestMyFunctions that declares the two arrays you can hardcode the values in) and calls your arrayEqual function and displays the results. Write the results out to a worksheet cell. Your function should test that the two arrays have the same number of elements.
• The output for the two tasks may look like this:

  

Remember: A function only returns a value. Your function should not display any output. The calling sub displays the results after the function is called and its value is returned.

You may refer to the lab web page or to Chapter 10 of the text for help.

Note: If you want to return real error messages in case your function fails (for example, if the arrays are not the same size), you can refer to: Returning Errors From User Defined Functions In VBA.

However the use of CVErr is not a requirement for this task.

Each of the items can be written in the same subroutine but if you wish to make the program more modular, feel free.

Note

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

You can use only the covered material in the course till now. You can't use uncovered topics. You can't use topics out of the context of this course.