Private Sub cmdAddThem_Click()
On Error GoTo hello
    Dim num1 As Double
    Dim num2 As Double
    Dim result As Double
   
    num1 = Val(txtFirstNum.Text)
    num2 = Val(txtSecondNum.Text)
   
    result = num1 + num2
   
    lblOutput.Caption = result
    Exit Sub
hello:
    MsgBox "error: it needs to be a number"
End Sub

What have we covered past the midterm?

nothing yet in Grauer (would be ch 5 and 6)
In formulas book:
ch 6 (dates and times)
ch 7 (Counting and summing)
coupled with
ch 14 and 15, array formulas
ch 8, lookup functions
ch 11, financial formulas
on codecademy, the rest. (loops, functions, arrays)

VBA:

Option Explicit

' a UDF that processes every cell in a RANGE
Function MySum(r As Range) As Double
    Dim sum As Double
    sum = 0
    Dim cell As Range
    For Each cell In r
            sum = sum + cell.Value
    Next cell
    MySum = sum
End Function


Function MySpecialSum(r As Range) As Double
' only adds red cells
    Dim sum As Double
    sum = 0
    Dim cell As Range
    For Each cell In r
        If cell.Font.Color = 255 Then
            sum = sum + cell.Value
        End If
    Next cell
    MySpecialSum = sum

End Function

Option Explicit
' write a UDF to convert a word to pig latin
Function PigLatin(T as String) As String
   ' 1. extract first letter
   Dim firstLetter As String
   firstLetter = Left(T, 1)
   if firstLetter = "A" Or firstLetter = "E" Or firstLetter = "I" Or firstLetter = "O" Or firstLetter = "U" Then
PigLatin = T & "way"
   else
PigLatin = Mid(T, 2, 1000) & firstLetter & "ay"
   end if
End Function

latin
1. extract first letter =LEFT(G2)
2. is it a vowel? =OR(G3="A", G3="E", G3="I", G3="O", G3="U")
3. calculation for vowel
3a. Take word, add 'way' =G2 & "way"
4. calculation for consonant
4a. Rest of word, besides first =MID(G2, 2, 1000)
4b. What was that first letter? =LOWER(G3)
4c. Concatenate =G8 & G9
4d. Add 'ay' =G10 & "ay"
5. Select vowel or consonant path =IF(G4, G6, G11)

ch 11: financial functions
time value of money
present value vs. future value
PV, FV
related by RATE
FV = PV + PV * RATE
FV = PV * ( 1 + RATE )
FV2 = PV * ( 1 + RATE ) * (1 + RATE)
FV3 =  PV * ( 1 + RATE ) * (1 + RATE) * (1 + RATE)

for year NPER
FV = PV * (1 + RATE) ^ NPER

nominal rate, APR (annual percentage rate)
7%
periodic effective rate. for a given compunding period (say, a month), what is the rate?
APR / numperiodsperyear

1. What function should I use?
2. What are my parameters?

some code

Function Identity(x)
    Identity = 1
    Exit Function
    Identity = x
End Function

Function Square(x)
    Square = x * x
End Function

Function IsOdd(n)
    If n Mod 2 = 0 Then
        IsOdd = False
    Else
        IsOdd = True
    End If
End Function

Function isLost(x)
    Dim lost(5) As Integer
    lost(1) = 4
    lost(2) = 8
    lost(3) = 18
    lost(4) = 20
    lost(5) = 22
    For i = 1 To 5
        If x = lost(i) Then
            isLost = True
            Exit Function
        End If
    Next
    isLost = False
End Function

for each loop
Function isLost(x)
    Dim lost(5) As Integer
    lost(1) = 4
    lost(2) = 8
    lost(3) = 18
    lost(4) = 20
    lost(5) = 22
    For Each Item In lost
        If x = Item Then
            isLost = True
            Exit Function
        End If
    Next
    isLost = False
End Function

Sample exam

Part I - Grauer:
do the end of chapter exercises, ch 1 - 4

Part II - excel formulas and VBA (up to ifs)
(ch 2, about relative and absolute, and mixed refs.)
(ch 3, about names)
(ch 5, about text functions)
1. Names:
a. Name a 2 x 4 area, as an absolute reference. Call it data.

b. Name a relative reference to 3 cells up and 2 cells to the right. Call it fred.

c. Print out all the names in your workbook.

d. Make separate TaxRates on each worksheet, by limiting the scope.

e. Name an array containing the months of the year.

2. Ch 5, text function.
a. Pig latin.
I love speaking Pig Latin
Iway ovelay eakingspay igPay atinLay  

Take a word and turn it into its Pig Latin equivalent.

3. VBA question.
Do the same thing in VBA

for next time, codecademy: Return

Dim greeting As String
Sub Initialize()
    greeting = "hello"
End Sub
Sub Greet()
    Dim greeting As String
    Debug.Print greeting
End Sub
Sub sayHelloTo(name)
    Debug.Print "hello " & name
End Sub
Sub RunMe()
    'Initialize
    'Greet
    sayHelloTo "Josh"
End Sub

=INDEX(Data,SMALL(IF(Data>0,{1;2;3;4;...20}, FALSE),{1;2;3;4;...20}))

=IF(ISERR(SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))


=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))

=INDEX(data,SMALL(IF({1,1,1,1,5...}={1...19},MATCH(data,data,0),""),{1...19}))

Sub foo()
    MsgBox "hello"
End Sub

Sub bar()
    Call foo
    Call foo
End Sub


Tuesday, Mar 13, Exam 1:
formulas (ch 1-5)
grauer (ch 1 - 4)
vba, up to ifs, but be able to write subs and functions (UDFs)