EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 17 Arrays
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 17.1 Introduction
• 17.2 When and Why to Use Arrays
• 17.3 Defining Arrays
• 17.4 Dynamic Arrays
• 17.5 Sorting Arrays
• 17.6 Applications
• 17.7 Summary

3
Introduction

• Arrays and how they are beneficial
• Defining one-dimensional and multi-dimensional
  arrays
• Modifying the indexing standard for arrays in VBA
• Dynamic arrays
• Sorting arrays
• An application which uses several arrays

4
When and Why to Use Arrays

• Arrays store series of data that can be
  manipulated or referred to later.
• The set of values stored in an array must all be
  of the same data type.
• You can refer to the array as a whole or you can
  refer to its individual elements by an index
  value.

5
Defining Arrays

• Multi-Dimensional Arrays
• Indexing

6
Defining Arrays

• To define an array, we can use the Dim, Private,
  or Public variable declarations.
• There is not an array data type however.
• The array will be named and the data type will be
  assigned to all of the elements in that array.
• VBA will recognize that your variable is an array
  and not a scalar variable because you must
  specify the size of the array in the variable
  declaration.
• Here is a quick example of defining an array of
  double data type, which has 10 elements and is
  named data.
• Dim data(10) As Double

7
Array values

• To insert values into this array, we can use the
  entire array variable and the Array function.
• The Array function allows you to list values for
  each array element.
• For example to insert 10 values into the data
  array, we could type the following.
• data Array(12.3, 13.4, 16.5, 13.8, 7, 2.9,
  24.2, 5.5, 8, 9.1)

8
Array values (cont)

• To set the value of a specific element of the
  array, we use an index.
• For example, if we want to change the third value
  in the above data array from 16.5 to 10.5, we
  type.
• data(3) 10.5
• To enter multiple element values using indices,
  we use a For, Next loop with a counter variable.
• For example, if we want to set each element in
  the data array as its index number we type.
• For i 1 to 10
• data(i) i
• Next i

9
Size Multi-dimensional Arrays

• For a one-dimensional array, a single number is
  all that is necessary to specify the size.
• To define multi-dimensional arrays, you just need
  to specify the size of each dimension, separated
  by a comma.
• For example, if we want to define a
  two-dimensional array of size 5 by 10 (number of
  rows by number of columns) we could type
• Dim data(5, 10) As Double

10
Multi-dimensional (contd)

• To insert values into a multi-dimensional array
  or to search for a value, use nested For, Next
  loops with different counter variables.
• For example, to set the value of each element in
  the above two-dimensional array equal to the
  product of its index numbers.
• For i 1 to 5
• For j 1 to 10
• data(i, j) ij
• Next j
• Next i

1 2 3 4 5 6 7 8 9 10
2 4 6 8 10 12 14 16 18 20
3 6 9 12 15 18 21 24 27 30
4 8 12 16 20 24 28 32 36 40
5 10 15 20 25 30 35 40 45 50
11
Indexing

• The default initial index value of arrays in VBA
  is 0.
• However, to change the initial index value of all
  arrays in your module to 1, simply type Option
  Base 1 at the top of the module.
• If you want to keep the default initial index as
  0 but have a specific array which you want to
  index starting at 1, you can specify the starting
  index value in the array declaration as.
• (1 to arraysize)

12
Indexing (cont)

• To compare, in the example below, Option Base 1
  is used.
• Option Base 1
• Dim data(10) As Double, results(12) As Double
• Here both the data and results arrays will have
  an initial index of 1 (as will any other array
  which is later defined in this module).
• Below we do not specify the Option Base setting
  but instead change the index of one of the
  arrays.
• Dim data(1 to 10) As Double, results(12) As
  Double
• Now the data array will begin with index of 1,
  but the results array will begin with an index of
  0.

13
Indexing (cont)

• There may be situations in which specific arrays
  need different indexing bounds.
• In this case, you can start the index at any
  value, just be aware of the size of your array.
• size upper index bound lower index bound 1
• Dim results(2 to 13) As Double
• Whichever initial index value is chosen should be
  coordinated with the counter variable used in
  For, Next loops.
• For i 2 to 13
• results(i) value
• Next i

14
Dynamic Arrays

• If you are not sure what size an array should be,
  or will be depending on the user or other dynamic
  programming reasons, you can define a dynamic
  array.
• When declaring a dynamic array, the size is not
  specified the parentheses are left empty.
• Dim input() As Double

15
Dynamic Arrays (cont)

• However, to use this array or any of its
  elements, we will need to eventually know its
  size.
• To set the size of a dynamic array at some later
  point in the code, we use the ReDim statement.
• The ReDim statement can also be used to set or
  change the number of dimensions and the indexing
  bounds.

16
Dynamic Arrays (cont)

• Suppose we want to ask the user to insert some
  input values, which we will store in our input
  array, but we are unsure how many values they
  want to insert.
• We can first assign a variable to an Input Box
  which asks for the number of values, and then we
  can insert the value of each element using a For,
  Next loop.
• size InputBox(How many values will you enter
  as input?)
• ReDim input(1 to size) As Double
• For i 1 to size
• input(i) InputBox(Please enter value i )
• Next i

17
Dynamic Arrays (cont)

• If you want to change the size of an array but do
  not want to reset its values, then use the ReDim
  Preserve statement.
• That is, suppose we have an array of size 10, but
  we want to add one more element.
• To keep the current values in the array but add
  one more element, we type
• ReDim Preserve input(11)
• input(11) InputBox(Please enter new value.)

18
Sorting Arrays

• There are two general ways to sort arrays in VBA.
• Output array values to the spreadsheet and use
  Excels sorting functions.
• Use a sorting algorithm with loops structures in
  the VBA code.

19
Sorting using Excel

• Output an array to a range in the spreadsheet,
  sort it in this range, and then input it back
  into the array.
• The code to sort an array of size 10 in
  increasing order using Excels sort functions
  would be as follows.
• Sub ExcelSort()
• For i 1 to 10
• Range(ArrayStart).Offset(i, 0).Value
  data(i)
• Next i
• Range(Range(ArrayStart).Offset(1,0),
• Range(ArrayStart).Offset(10,0)).Sort
  Key1Range(ArrayStart), Order1xlAscending
• For i 1 to 10
• data(i) Range(ArrayStart).Offset(i,0).
  Value
• Next i
• End Sub

20
Sorting using VBA

• There are many various sorting algorithms which
  can be implemented in VBA for sorting arrays.
• We give an example of the Bubble Sort algorithm.

21
Bubble Sort Algorithm

• The Bubble Sort algorithm performs passes over
  the elements to be sorted.
• In each pass, it considers an element, say p, in
  order and compares it with the next element, say
  q.
• If p gt q (and we are trying to sort the list in
  ascending order), then the two elements are
  switched since they are out of order.
• A pass ends when each element has been compared
  with the next element and switched (it is found
  to be out of order).
• The algorithm terminates when in one complete
  pass no two elements were found to be out of
  order (implying that all elements have been
  sorted).

22
Bubble Sort Example

• Consider the data array
• data(10) Array(5, 2, 8, 3, 7, 1, 9, 6, 10, 4)
• We first compare 5 with 2 and find that 5 is
  larger, so we switch the two elements.
• We now compare 5 with 8 and find that 5 is
  smaller, so we now select 8 and continue our
  comparisons.
• We compare 8 with 3 and find that 8 is bigger so
  we switch the elements.
• We then compare 8 with 7 and find that 8 is
  bigger so we again switch the elements.
• We compare 8 with 1 and again switch the
  elements.
• Then, we compare 8 to 9 and find that 9 is bigger
  so we now select 9 and continue our comparisons.
• We compare 9 with 6 and find that 9 is bigger so
  we switch the elements.
• We compare 9 with 10, but 10 is bigger so we now
  select 10.
• We compare 10 with 4 and switch the elements
  since 10 is bigger.
• We have now reached the end of the array and have
  completed the first pass of the algorithm. The
  current state of the array is as follows
• data(10) Array(2, 5, 3, 7, 1, 8, 6, 9, 4, 10)

23
Bubble Sort in VBA

• We can use the following procedure to perform the
  Bubble Sort algorithm in VBA.
• Sub BubbleSort()
• Dim sorted As Boolean, switches As Integer,
  temp As Double
• Do While sorted False
• switches 0
• For i 1 To 10
• If data(i) gt data (i 1) Then
• temp data (i)
• data (i) data (i 1)
• data (i 1) temp
• switches switches 1
• End If
• Next i
• If switches 0 Then
• sorted True
• End If
• Loop
• End Sub

24
Arrays with Function Procedures

• Arrays can be used with function procedures as
  well.
• You can pass the name of an array to a function
  procedure to manipulate some or all of its
  elements.
• You can also pass elements of an array to a
  function procedure to manipulate.
• You can initialize a new array variable using a
  function procedure as follows.
• Dim NewArray() As Double
• ArrayFunction(NewArray)
• You could also create a function procedure to
  pass an array and sort it.
• Function BubbleSort(ArrayName)

25
Applications

• Phonebook

26
Applications (contd)

• We develop a phonebook application which uses
  dynamic arrays
• This application allows a user to search through,
  add new entries to, and view all listings in a
  phonebook.
• The phonebook stores a name and number for each
  entry.

27
Preparation

• There are only two worksheets for this program
• Phonebook Welcome
• Phone Data
• The Phonebook Welcome sheet is the basic
  welcome sheet with a problem description.
• The button options appear on this sheet as well.
• The Phone Data sheet has a table with the name
  and number of each entry in the phonebook.
• There is also a button to return to the
  Phonebook Welcome sheet.

28
Figure 17.1
29
Figure 17.2
30
Procedures

• We will only need two main sub procedures.
• Search procedure which will search for an entry
  in the phonebook by name.
• NewEntry procedure which will enter a new name
  and number to the phonebook.
• There will also be two navigational procedures
• One to take the user from the welcome sheet to
  view all listings.
• One to return them to the welcome sheet, or the
  phonebook menu.
• In the Search procedure we will call a
  CreateArray procedure to put all names and
  numbers from the Phone Data sheet into two
  arrays which we will use to search.

31
Variables

• Counting variables
• Two array variables (one for the names and one
  for the numbers)
• Two range variables (for the two columns of data
  in the phonebook table)
• Two variables to capture the name and number for
  searching or adding a new entry
• Public i As Integer, n As Integer, PhoneName() As
  String, _
• PhoneNumber() As Double, NewName As String,
  NewNumber As Double,_
• NameStart As Range, NumStart As Range

32
Navigational Procedures

• These procedures will be assigned to the View
  All Listings and Return to PhoneBook Menu
  buttons respectively.
• Sub ViewBook()
• Worksheets("Phone Data").Visible True
• Worksheets("Phonebook Welcome").Visible
  False
• End Sub
• --------------------------------------------------
  --------------------------
• Sub ViewMenu()
• Worksheets("Phonebook Welcome").Visible
  True
• Worksheets("Phone Data").Visible False
• End Sub

33
Search

• The first action is to ask the user for the name
  they want to search for in the phonebook.
• We assign the NewName variable to our InputBox
  function and provide a default value.
• Next we will call the CreateArray procedure to
  create an array of all current names, PhoneName,
  and numbers, PhoneNumber, in the phonebook.
• This makes our search easier since now we can
  just use a For, Next loop to check if any of the
  array elements are equal to our NewName value.

34
Search (cont)

• If a match is found, the corresponding element
  (i.e., the same index number) from the
  PhoneNumber array is assigned to the NewNumber
  variable and the result of the search is shown to
  the user.
• If no match is found, we consider that the
  NewNumber variable was never changed from its
  default value of 0.
• Therefore, using this check we can tell the user
  that no match was found.

35
Search (cont)

• Sub Search()
• NewName InputBox("Please enter name you
  wish to search for _
• using the following format Last, First",
  "Name Search", "Smith, John")
• Call CreateArray
• NewNumber 0
• For i 1 To n
• If PhoneName(i) NewName Then
• NewNumber PhoneNumber(i)
• MsgBox "The phone number for "
  NewName " is " _
• Format(NewNumber, "() -") "."
• End If
• Next i
• If NewNumber 0 Then
• MsgBox "There was no phone book entry by
  that name."
• End If
• End Sub

36
Search (cont)

• We have used the Format function to display the
  NewNumber value as a standard 10-digit phone
  number.
• This function performs the same actions as
  formatting a cell in Excel.
• The notation "() -" specifies the
  custom format we want.

37
CreateArray

• Notice in our variable declarations, our two
  arrays, PhoneName and PhoneNumber, were defined
  as dynamic arrays.
• Now we need to find the size of these arrays and
  use the ReDim statement.
• To find the size of the arrays we can use our
  range variables and the End property to count the
  number of values currently in our phonebook
  table.
• To count the number of values in this range, we
  use the Rows property and Count sub property.
• We can then use a For, Next loop to enter each
  value in the table as an element in one of our
  arrays.

38
CreateArray (cont)

• Sub CreateArray()
• With NameStart
• n Range(.Offset(1, 0),
  .End(xlDown)).Rows.Count
• End With
• ReDim PhoneName(n)
• ReDim PhoneNumber(n)
• For i 1 To n
• PhoneName(i) NameStart.Offset(i, 0)
• PhoneNumber(i) NumStart.Offset(i, 0)
• Next i
• End Sub

39
NewEntry

• In this procedure we begin by prompting the user
  for both a name and a number to add to the
  phonebook.
• We assign Input Boxes to the NewName and
  NewNumber variables.
• We ensure that some non-empty string is entered
  as a name using an If, Then statement with the
  condition NewName .
• If this condition is true, then the sub should be
  exited and the user should be prompted to
  re-enter a name.
• We also ensure that the phone number entered is
  10-digits in length.
• We do this using some simple math checks.

40
NewEntry (cont)

• We then again call the CreateArray procedure.
• We do this in order to search all names in the
  current list so as to avoid duplicate entries.
• If the NewName value is found in the PhoneName
  array, the user is told that this entry already
  exists.
• If no match is found, we can proceed with adding
  the new entry to our table we do this using our
  range variables.
• We now select our modified table and sort all of
  the values using the Sort method.
• We also use the ScreenUpdating property of the
  Application object to ensure that the user does
  not see this background work on the Phone Data
  sheet.

41
NewEntry (cont)

• Sub NewEntry()
• Application.ScreenUpdating False
• Worksheets("Phone Data").Activate
• NewName InputBox("Please enter the new
  entry name using the _
• following format Last,
  First", "New Name", "Smith, John")
• If NewName "" Then
• MsgBox "Please enter a name."
• Exit Sub
• End If
• NewNumber InputBox("Please enter the
  10-digit phone number for " _
• NewName " using the following format
  1234567890", _
• "New Number", 1234567890)
• If NewNumber / 10 10 lt 0.1 Or NewNumber /
  10 10 gt 1 Then
• MsgBox "Please enter a 10-digit number."
• Exit Sub
• End If

42
NewEntry (cont)

• Call CreateArray
• For i 1 To n
• If PhoneName(i) NewName Then
• MsgBox "There is already an entry
  for this person in the _
• phone book."
• Exit Sub
• End If
• Next i
• NameStart.Offset(n, 0).value NewName
• NumStart.Offset(n, 0).value NewNumber
• Range(NameStart, NumStart.Offset(n,
  0)).Select
• Selection.Sort Key1NameStart,
  Order1xlAscending
• Worksheets("Phonebook Welcome").Activate
• Application.ScreenUpdating True
• MsgBox NewName " has been added to the
  phone book."
• End Sub

43
Application Conclusion

• The application is now complete.
• Assign the procedures to the respective buttons
  and check the functionality of the program.

44
Summary

• Arrays store series of data that we can
  manipulate or refer to later.
• To define an array, use the Dim, Private, or
  Public variable declarations.
• For a one-dimensional array, we just need a
  single number to specify the size.
• To define multi-dimensional arrays, we must
  specify the size of each dimension, separated by
  a comma.
• The default initial index value of arrays in VBA
  is 0. To change the initial index value of all
  arrays in our module to 1, type Option Base 1 at
  the top of the module.
• To set the size of a dynamic array at some later
  point in the code, use the ReDim statement.
• The ReDim Preserve statement retains any
  previously assigned element values.

45
Additional Links

• (place links here)