vba answers

Option Explicit

Function JoshFunc(r As Range)
    Dim temp As Double
    temp = WorksheetFunction.Sum(r)
    JoshFunc = Evaluate("sqrt(" & temp & ")")
End Function

Function JoshFunc2(r As Range)
    Dim theSum As Double
    Dim cell As Range
    For Each cell In r
        theSum = theSum + cell.Value
    Next cell
   
    JoshFunc2 = Evaluate("sqrt(" & theSum & ")")
    JoshFunc2 = theSum ^ 0.5
End Function

Sub Macro1()
    For Each cell In Selection
      If cell.Value >= 5 And cell.Value <= 50 Then
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
      End If
    Next cell
End Sub

Sample midterm 2

Date and Time:
1) Using just arithmetic, give me a date 5 years from now. (Pretend there are no leap years.)

2) Do the same using data and time functions.

Counting and Summing:

Generate a list of Salespeople and the number of Widgets they sold, and total revenue they brought in.
1) Count how many people sold between 8 and 23 widgets. Use summing functions. (e.g. sumif, countif)
2) Do the same using an array formula

Do the same based on length of salesperson's name (between 4 and five letters long)
1) using summing funcs
2) using array formulas

Lookup functions:
Look up for me Bob's revenue and #widgets sold, from same table

financial functions
If I borrow 1 million for 100 years, at 7% APR, compounded quarterly, with a balloon payment of half a million, what will be my payment?


If I borrow 1 million for 100 years, with a PMT of $1000 monthly, compounded quarterly, with a balloon payment of half a million, what will be my APR? What will be my annual effective rate?


VBA question:
write a UDF that takes in a range and returns the square root of the sum of all the elements in the range
make a macro that will color all values in a range between 5 and 50 the background color red.

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