INFS630

1
INFS630

• Spring 2004
• Excel VBA
• Chapters 4

2
Overview

• Looping
• Preprocessing Postprocessing
• Counted
• Nested
• Arrays
• Other
• List Boxes Combo Boxes

3
Loops

• ALWAYS make sure that for each loop you deal
  with, you know
• The initial conditions
• The ending condition
• How the condition changes within the loop

4
Preprocessing / Postprocessing

• There are two basic places to check for the
  stopping condition in a loop at the beginning
  (before anything is done) and at the end (after
  something is done at least once). Therein lies
  the basic question Do you want the loop
  performed at least once. If yes, then you write a
  postprocessing loop if no, then you construct a
  preprocessing loop.

Preprocessing Postprocessing
process
process
5
Preprocessing / Postprocessing

• Preprocessing in VB
• Do While (condition) Do While (strA ltgt x)
• processing steps msgbox strA
• Loop strA
  InputBox(New char)
• Loop

Preprocessing Postprocessing
process
process
6
Preprocessing / Postprocessing

• Postprocessing in VB
• Do Do
• processing steps msgbox strA
• Loop While (condition) strA InputBox(New
  char)
• Loop
  While (strA ltgt x)

Preprocessing Postprocessing
7
Preprocessing / Postprocessing

• Rules of thumb
• Know the stopping condition
• Know whether you want the statement block to
  execute at least once
• If yes, postprocess
• If no, preprocess
• Make sure that within the statement block the
  stopping condition is being changed

8
Counted Loops

• Sometimes the programmer knows how many times a
  loop needs to be executed (or the formula/logic
  whereby to determine the number of times). In
  that case, the programmer should use a COUNTED
  LOOP. VB uses the construct called a FOR NEXT loop

FOR variable startvalue TO stopvalue
STATEMENT BLOCK NEXT variable
9
FOR NEXT
intA 1

• Generic
• FOR variable startvalue to stopvalue
• statement block
• NEXT variable
• Specific
• FOR intA 1 TO 10
• msgbox intA
• NEXT intA

(intA gt 10)
intA intA 1
msgbox intA
10
FOR NEXT (expanded)

• Generic
• FOR variable startval TO stopval STEP increment
• statement block
• NEXT variable

intA 1
How many times is the statement block executed in
the FOR NEXT loops 1.) FOR x 1 TO 10 STEP 3
msgbox (loop) NEXT x 2.) FOR x 1 TO
10 STEP 5 msgbox(loop) NEXT x 3.)
FOR x 1 TO 10 STEP 1 msxbox(loop)
NEXT x
(intA gt 10)
intA intA STEP
msgbox intA
11
Nested Loops
intA 1
FOR intA 1 TO 10 FOR intB 1 TO 5
msgbox(loop) NEXT intB NEXT intA How many
times is the msgbox statement performed? How
many if FOR intA 1 TO 10 STEP3 FOR intB 1
TO 5 STEP 5 are used?
intAgt10
intA intA 1
intB 1
intBgt5
intB intB 1
msgbox intA
12
Nested Loops (cont)
FOR intA 1 TO 3 FOR intB 1 TO 4
msgbox intA, intB NEXT intB NEXT intA
Given the above nested loops, what is displayed?
13
Nested Loops (cont)
FOR intA 1 TO 3 FOR intB 1 TO 4 intAns
intA intB msgbox A x B intAns
NEXT intB NEXT intA
Given the above nested loops, what is displayed?
14
FOR EACH Loops
MyRange

• For Each Item in Collection
• code
• Next
• Dim myrange as Range
• Dim mycell as Range
• Set myrange Range(A1A15)
• For Each mycell in myrange
• do something
• Next

MyCell
15
Summary

• You should be able to understand
• the difference between a DO WHILE and a DO LOOP
  WHILE
• the basic FOR NEXT counted loop
• the FOR NEXT STEP loop
• and to trace the code of nested loops and
  understand how they operate
• Note how the combo box and list box store and
  retrieve data
• Using the .list property
• Identifies with subscripts in parens (i.e.
  lstname.list(0) is the first name or data item in
  the list)

16
Array Overview

• General Discussion
• Uses Structure Declaring
• Table Look up
• Searching
• Sorting

17
Arrays
intj
intd
intc
inte
intb
inti
intf

• Arrays are short cuts for when you need a lot of
  the same type of variable.
• Instead of ten integer variables for ten costs,
  an array can handle all ten

inta
inth
intg
intArray
18
Arrays

• Each variable location in an array is called an
  element
• Use DIM statement to create, define dimension and
  type
• DIM intArray(9) as Integer creates the structure
  below
• Treat as normal variables Reference using
  subscripts intArray(3) 114
• Once created, MUST refer to using subscript

DIM intArray (9) as integer
114
19
Arrays
intj
intd
intc
inte

• Arrays assist in making computer programs more
  efficient.
• Compare adding all the integers stored in the
  variables.

intb
inti
intf
inta
inth
intg
Sum inta intb intc intd inte intf
intg inth inti inj
intArray
For I 0 to 9 sum sum intarray(I) Next I
20
Arrays

• Arrays come in different dimensions.
• Two dimensional arrays
• require two subscripts
• are row dominant (rows are referenced first)
• Example
• DIM intSales( 2, 3) as integer

0 1 2 3
In this case, what is the value of IntSales
(1,2) ?
0 1 2
1402
7532
21
Arrays

• Three dimensional arrays
• require three subscripts
• are row dominant
• Example
• DIM intSales (2, 3, 1) as integer

1402
So the number 2134 is found where? the
number 4521 is ______ the number 1402 is
______ the number 3425 is ______
4521
2134
3425
22
Coordinated Arrays
strName intSales

• Dim strName (4) as String
• Dim intSales (4) as Integer
• So, if we know that Smith is in strName(1), we
  also know his or her sales is in intSales(1)

Jones Smith Frank Able Zean
1402 2301 0231 6762 0199
23
Creating Arrays

• DIM statement
• Array name Dimensions Bounds Type
• DIM strName (2, 3) as string
• Using
• Any integer or integer variable can be used to
  subscript
• Must stay within the bounds
• Must have the correct number of subscripts
• Must assign the correct type of data
• Given DIM strName (2, 3) as string
• Is strName (0,0) Jones valid
• Is strName (1) Smith valid
• Is strName (3,2) Larry valid
• Is strName(2,3) 1402 valid

24
Table Lookup

• Assume
• DIM curTuition (1, 2) as currency
• intRes is an integer (0 or 1) the represents a
  students residency status
• intClass is an integer (0, 1 or 2) that
  represents a students class standing
• The user interface asks the student for his or
  her residency (1 or 2) and class status (1,2, or
  3)
• Then
• curTuition((intRes -1),(intClass-1)) would
  represent the tuition owe by that student

Class
1 2 3
1 2
Residency
25
Searching Arrays (unordered data)

• Searching implies looking for a target.
• A simple sequential search looks through the
  array for the target.

Given that the array is unordered, no obvious
ordering (ie. Alphabetical), a sequential search
is the best.

• Target Q
• Found False
• For I 0 to 9
• If target strArray(I) Then
• found true
• Exit For
• end if
• Next I

strArray
A F S V W Q Z X Y L
26
Searching Arrays (unordered data)

• Searching two dimensional arrays requires nesting
  loops. Again a simple sequential search looks
  through the array for the target.

• Target Q
• Found False
• For I 0 to 1
• For J 0 to 9
• If target strArray(I,J) Then
• found true
• Exit for
• end if
• Next J
• if found true then exit for
• Next I

strArray
A F S V W Q Z X Y L
O M N G I T K P B
H
27
Searching Arrays (ordered data)

• If your data is ordered, you have a new way to
  determine whether or not the target is in the
  array. For example, in an ordered list if the
  current element is larger than the target (and
  the target is not found) then the target cannot
  be in the array.

Target 17 Found False For I 0 to 9 If
target strArray(I) Then Found true If
(found true) or (strArray(I) gt target) then
Exit For end if Next I
intArray
1 5 7 8 10 15 20 23 25 40
28
Searching Arrays (ordered data)

• The characteristic of ordered data provides
  another search technique called the binary
  search.

Set Target Found False Lowerbound 1
upperbound 10 Do While (Found False) and
(Lowerbound ltgt Upperbound) then If
intArray((lowerbound upperbound)\2) target
then found true else if
intArray((lowerbound upperbound)\2) gt target
then upperbound ((lowerbound
upperbound)\2) - 1 else
lowerbound (( lowerbound upperbound)\2) 1
end if endif Loop
intArray
1 5 7 8 10 15 20 23 25 40
29
Searching Arrays (ordered data)

• The characteristic of ordered data provides
  another search technique called the binary
  search.

Assume looking for 20
intArray
1 5 7 8 10 15 20 23 25 40
L
M
U
Pass 1 Pass 2 Pass 3
L
U
M
U
M
L
30
Searching Arrays (ordered data)

• Binary vs Sequential
• (Remember that Data must be ordered for Binary
  search)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
16
In four searches, a binary search can cover a
16 element array In five, it can cover a 32
element array. Compared to the sequential process
this is extremely efficient and especially so
with large numbers
1
4
3
2
Number of elements Worst
case search
Binary Search Sequential 16
4
16 32
5
32 64
6 64 1024
10
1024
See a pattern?
31
Sorting Arrays

• Sorting is defined as rearranging the values in
  the elements of an array to make them ordered
• The goal of a sorting algorithm should be to get
  at least one value in its correct position each
  time or pass through the array.
• Efficient algorithms will stop when all elements
  are in the correct order (regardless if they have
  completed all possible passes)
• Two common sorting algorithms
• Selection Sort
• Bubble Sort

32
Selection Sort

• For I 0 to 8
• largest strArray(I)
• For J (I 1) to 9
• if strArray(j) gt largest then
• intPoslargest j
• end if
• Nest j
• swap (strArray(I), strArray(intPoslargest)
• Next I

A F S V W Q Z X Y L
strArray
intPoslargest
I J
After 1st pass
Z F S V W Q A X Y L
strArray
33
Bubble Sort

• For I 0 to 9
• For J 0 to 8
• if strArray(J) gt strArray(J1) then
• swap (strArray(J), strArray(J1))
• end if
• Nest j
• Next I

A F S V W Q Z X Y L
strArray
no no no
no
yes (q for w)
no (q for Z)
yes (z for X)

yes (z for y)

yes (z for l)
After 1st pass
A F S V Q W X Y L Z
strArray
34
Dynamic Arrays

• VB allows a programming to redimension arrays
  during the run of the program.
• Rules for Dynamic Arrays
• Use empty dimension list intArray() as Integer
• Use ReDim to assign dimensions
• Redim intArray (intNumber, 2)
• Use ReDim to change Bounds
• Redim intArray ( (intNumber 10), 2)
• Use Preserve keyword to save data in array
• ReDim Preserve intArray(1 to intNumber, 2)
• Preserve only allows changes in last dimension

35
Summary

• Arrays are sets of variables know as elements
• These elements operate just like simple variables
• Arrays are created with a DIM statement that
  defines boundaries, dimensions and type
• When referencing elements in an array, you must
  have the correct number of subscripts and their
  values must be within the bounds of the
  dimensions
• The operation of looking through an array for a
  target is called searching
• The operation of reorganizing the data in an
  array is called sorting
• Binary searches are more efficient than
  sequential searches

36
Other

• Check Boxes and Radio Buttons
• List Boxes and Combo Boxes

37
Review

• Given the statement
• DIM intarray (10) as integer
• What are the array bounds?
• Initialize an array with DIM statement
• DIM intarray (9) as integer 3,4,3,4,5,1,1,2,3,4
  
• Initialize an array with assignment statement
• DIM statearray (9) as string
• Statearray(0) Arizona

38
Check Boxes Radio Buttons

• Check Boxes
• Use chk prefix
• Check .value for checked
• .caption
• Non mutually exclusive items
• Can have many checked

• Option Buttons
• Use opt prefix
• Check .value for active
• .caption
• Use for mutually exclusive items
• Can have only one checked

Given the characteristics of the check boxes and
the radio buttons, would you use the same logical
processing to find the controls that were
checked?
39
List Box

• Presents a list from which the user chooses
• Prefix lst
• Properties
• Items
• Count number of items in box
• Add
• Item Array of indexed items
• Insert(index, item)
• Datasource can tie to database files
• SelectedIndex position in list of selected item
• (-1) no item selected
• SelectionMode can more than item be selected
• Event
• DblClick

40
List Property

• Given a list box names lstStates, we have the
  following associations

NOTES 1.) list starts at 0 2.)itemscount 5
which has the correct number but it IS NOT the
last number of the list!!! 3.) lstStates.Items.ite
m(3) Arizona
Washington .Items.item(0) Oregon
Items.item(1) California
.Items.item(2) Arizona
Items.item(3) Nevada .Items.item(4)
lstStates
41
Combo Box

• One line field with pull down menu
• Selected text is stored in .text property
• Notes for Combo Box
• Prefix is cbo
• Similar properties to List Box
• Exception no multi select
• Similar events as List Box