Hi 022 students,

    Here is a review of today's class and some of the Excel VBA macro 
code, plus a nice sample test question where you show the output of the 
given code.


Sub tallyResults()
    hadRepeat = False
    
    theRow = 3
    
    For Each theCell In Range("B3:B24")
    
        If theCell.Value = "Repeat" Then
            hadRepeat = True

            '      column C                 column A
                          -                        -                        
            Cells(theRow, 3).Value = Cells(theRow, 1)  ' column C is 
                          -                        -     formatted to 
                          C                        A     show Jan-06 and 
                                                         Dec-24 and Aug-09 
                                                         and Oct-25, for
        End If                                                   examples.

        theRow = theRow + 1
        
    Next theCell
    
    If hadRepeat Then
        Cells(3, 4).Value = Cells(3, 4).Value + 1
    End If
    
    Cells(3, 5).Value = Cells(3, 5).Value + 1
    
End Sub

---------------------------------------------------------------------------
Note on using Cells( 10, 5 ) and why it would be row 10, column E, and
              --------------                        specifies cell E10.
                                                                   ---
          i. Range("E10").Value = "UNI Panthers"

                 and 

         ii. Cells(10, 5).Value = "UNI Panthers"     do exactly the 
                                                     same action and have
                                                     an IDENTICAL result.
                                                         
          Cells(rowIndex, columnIndex) specifies cell A1 when you say

                                       Cells(1, 1), and
 
          Cells(2, 4) would specify cell D2, i.e. column 4 is column D,
                                                    and row 2 is row 2.
---------------------------------------------------------------------------

Sub run100Times()
    Application.ScreenUpdating = False
    
    For i = 1 To 100
        Birthday24
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Sub demoRange()

    theNumber = 1                       ' initialize for the loop
    
    For Each theCell In Range("B4:D13")    ' HOME in ON THE RANGE concept...
                                        
        theCell.Value = theNumber
                                           ' BODY of the For Each loop
        theNumber = theNumber + 1          

    Next theCell

End Sub
B       C       D       Row
--      --      --      --- 
1	2	3        4                   Row 4 to Row 13
4	5	6        5
7	8	9        6                   Column B to Column D
10	11	12       ...
13	14	15
16	17	18
19	20	21       ...          RANGE("B3:D13")
22	23	24
25	26	27       12
28	29	30       13
--      --      --      ---
B       C       D       row number

The above is the output of the Sub demoRange().  It is an Excel VBA macro.

Sub demoRange()
   ...
End Sub

-------------------------------------------------
Show the OUTPUT of the following Excel VBA macro?
-------------------------------------------------

Sub practiceTestQuestionShowTheOutput()     <----- Show the output...

    theNumber = 2000

    For i = 12 To 21

        Cells(i, 1).Value = i
        Cells(i, 2).Value = i + i

        Cells(i, 3).Value = theNumber

        theNumber = theNumber - 50

    Next i

End Sub


Row        A       B       C       D
---       ---     ---     ---     ---
 1
 2
 3
 4
 5
 6
...
11
12               I would give you a screen snapshot of a spreadsheet 
13               on the exam so you could fill in the cells with your
14               answer, ie. show the output on a SPREADSHEET.
15
16               Obviously, you can check your results to these practice
17               test questions and sample code using Microsoft Excel
18               and its macro capability.
19
20               It does not matter whether its Excel 97 or Excel 2003
21                                           or Excel 2007 for most of
22                                                this Excel VBA stuff.
---       ---     ---     ---     ---
Row        A       B       C       D
---       ---     ---     ---     ---

    See you on Wednesday at the exam.

    We will be back to Fireworks and Dreamweaver and perhaps some Flash on
Friday and next week.

    http://www.cs.uni.edu/~jacobson/c022.html

    Mark