for next time in Codecademy, in Functions,
"Defining Functions"

codecademy

Sub foo()
    I = 0
    While I <= 4
        Debug.Print "I is now " & I
        I = I + 1
    Wend
End Sub

Sub bar()
    I = 1
    Do
        Debug.Print "I is now " & I
        I = I + 1
    Loop While I < 5
End Sub

isLeap?
if divisible by 4: is leap
except, if divisible by 100, not leap year
except, if divisible by 400, is leap

=IF(MOD(A41, 400)=0, "leap", IF(MOD(A41, 100)=0, "not leap", IF(MOD(A41, 4)=0, "leap", "not leap")))

HW: Try writing an IsLeap function in VBA



contine with dates and times
hopefully finish ch 6 today (Formulas book)

Sentence sits in A1
skip a line
in row 3, we have:
startPos spacePos wordLength word
in row 4, we have
1 =FIND(" ", Sentence, A4) =B4-A4 =MID(Sentence, A4, C4)
in row 5 we have:
=B4+1 =FIND(" ", Sentence, A5) =B5-A5 =MID(Sentence, A5, C5)

Sub SplitString()
    sentence = "The rain in Spain stays mainly in the plain"

    startpos = 1
    Spacepos = InStr(startpos, sentence, " ")
    wordlength = Spacepos - startpos
    word = Mid(sentence, startpos, wordlength)
    Debug.Print word
   
    startpos = Spacepos + 1
    Spacepos = InStr(startpos, sentence, " ")
    wordlength = Spacepos - startpos
    word = Mid(sentence, startpos, wordlength)
    Debug.Print word
   
End Sub

look over ch 3 in hands on book, do the practice exercises

Formulas book, we started ch 6, dates and times
in Excel, dates are really OFFSETS from Jan 0, 1900
really just a number
that it looks like a date is just formatting

Function MyAdding(X As Integer) As Integer
    MyAdding = X + 15
End Function

Sub foo()
    For i = 4 To 1 Step -1
        Debug.Print "i is now " & i
    Next
End Sub

i = i - 1
i = i + 1

Function Ordinal(X As Integer) As String
    Last2Digits = Val(Right(X, 2))
    If Last2Digits = 11 Or Last2Digits = 12 Or Last2Digits = 13 Then
        Ordinal = X & "th"
        Exit Function
    End If

    lastdigit = Val(Right(X, 1))
    If lastdigit = 1 Then
        Ordinal = X & "st"
    ElseIf lastdigit = 2 Then
        Ordinal = X & "nd"
    ElseIf lastdigit = 3 Then
        Ordinal = X & "rd"
    Else
        Ordinal = X & "th"
    End If
End Function

=FIND("Profit", A21)
=LEN("Profit")
=REPLACE(A21, B22, B23, "Loss")


Sub ReplaceProfitWithLoss()
    Dim Sentence As String
    Dim Result As String
    Sentence = "Quarterly Profit, 2012"
   
    Dim pos As Integer
    pos = InStr(1, Sentence, "Profit")
   
    Dim length As Integer
    length = Len("Profit")
   
    Result = Application.WorksheetFunction.Replace(Sentence, pos, length, "Loss")
    Debug.Print Result
End Sub

Function ReplaceProfitWithLoss(Sentence As String) As String
    Dim Result As String
    Sentence = "Quarterly Profit, 2012"
   
    Dim pos As Integer
    pos = InStr(1, Sentence, "Profit")
   
    Dim length As Integer
    length = Len("Profit")
   
    Result = Application.WorksheetFunction.Replace(Sentence, pos, length, "Loss")
    ReplaceProfitWithLoss = Result
End Function

HW:
Write:
CountNumberOfOccurrences("Big Bob's Burgers", "B")

CountNumberOfOccurrencesOfString("Big Bob's Burgers", "Bi")

Option Explicit

Sub PracticeInIfs1()
    Number = InputBox("Please guess a number")
    If Number = "7" Then
        Debug.Print "You got it!!"
    ElseIf Number = 6 Then
        Debug.Print "Close! Try guessing a little higher."
    Else
        Debug.Print "You were way off! Sorry..."
    End If
End Sub

Sub PracticeInIfs2()
    Name = InputBox("Please enter your name")
    If Name = "Sam" Then
        Debug.Print "you are Sam!"
    Else
        Debug.Print "you are not Sam!"
    End If
End Sub

Sub PracticeInIfs3()
    Dim Number As Integer
    Number = InputBox("Please guess a number")
    If Number = "5" Then
        Debug.Print "equals"
    ElseIf Number > 5 Then
        Debug.Print "greater"
    ElseIf Number < 5 Then
        Debug.Print "less than"
    End If
End Sub

lecture 5

HW (not to hand in):
Make your own subroutine.
Assign a variable the day of the week
Using string concatenation (&), create
another variable that reads:
"Today is Wednesday", or whatever the day
of the week is.
Print out to the immediate window (Debug.Print)
that string variable.

On code academy, try doing section "What if"

Courier New is a fixed width font
i
W

lecture 4

HW: Do the editor and arrays section on codeAcademy
Also, give me the Excel formulas to convert a lowercase letter to capital
Also, give me the VBA code to convert a lowercase letter to capital

The VBA from class

number = 42

? number

 42 

? number / 2

 21 

? number % 10

? number Mod 10

 2 

myString = "hello"

? myString

hello

? Left(myString, 2)

he

? Left("hello", 2)

he

? Right("hello", 2)

lo

? Right(myString, 2)

lo

? Mid("hello", 2, 3)

ell

? Mid(myString, 2, 3)

ell

three = Left("Joshua", 3)

? three

Jos

three = Mid("Joshua", 1, 3)

? three

Jos

? replace("coding rules", "coding", "programming")

programming rules

result = replace("coding rules", "coding", "programming")

? result

programming rules

? Upper("hello")

? toupper("hello")

? UCase("Hello")

HELLO

? LCase("Hello")

hello

? chr(65)

A

? Asc("A")

 65 

letter = "A"

? letter

A

theCode = Asc(letter)

? theCode

 65 

theCode = theCode + 32

? theCode

 97 

letter = Chr(theCode)

? letter

a

A1 = "A"

? A1

A

A2 = Asc(A1)

? A2

 65 

A3 = A2 + 32

? A3

 97 

A4 = Chr(A3)

? A4

a

letter = "a"

? Chr(Asc(letter) + 32)

a

? Chr(Asc(letter) - 32)

A

letter = "a"

theCode = Asc(letter)

theCode = theCode - 32

letter = Chr(theCode)

? letter

A

? "Hello" = "hello"

False

option compare text

? UCase("Hello") = UCase("hello")

True

? "hello" & "world"

helloworld

? "hello" & " " & "world"

hello world

? "hello" & chr(44) & chr(32) & "world"

hello, world

? "hello" & chr(10) & "world"

hello

world

? format("140000", "$#,##0.00")

$140,000.00

lecture

HW: do Numbers n' Strings in codeacademy

we saw variable in programming languages in general
how about in Excel?

how about in VBA?
Dim myName
is equivalent to
var myName;
from codeacademy

it won't work in the Immediate window, so we need to memorize for now

I CAN say:
myName = "Joshua"

and that wil;l work in the Immediate window


=NJ!$D$11
better
=NJ!D11

currentStudentMidterm:
=Sheet4!$B5

we did the simple stuff, including names for relative and mixed refs
what is left?
* indirect
dynamic names (later)
names for arrays

=INDIRECT("A1")

we finished ch 3 in formulas book (for now)

HW: read through ch 2 in grauer


ch 6 is date and time functions

date in Excel is REALLY just a number
'it is an offset from Jan 0, 1900

Navigating Windows, Part I

The root folder and file paths

Call MsgBox("Proceed?", vbOKOnly)
Call MsgBox("Proceed?", vbOKCancel)
Range("A1") = "Hello"

digression: range operators
1) :   range operator
2) ,   union operator
3)     intersection operator

HW: The practice exams
The practice exercises at the end of Ch 1