Code Example 1

Radius = 4
Circumference = Radius * 2 * 3.1416



Code Example 2

Radius = 4
Circumference = Radius * 2 * 3.1416
Radius = 10



Code Example 3

ValueA = 10
ValueA = ValueA * 2



Code Example 4

Circle = 3.1416 * Radius ^ 2
Square = Side ^ 2
If Circle > Square then
 AreaCompare = “Circle”
ElseIf Circle < Square then
 AreaCompare = “Square”
Else
 AreaCompare = “Same”
End If



Code Example 5

‘ This entire line is a comment
X = 5 ‘The comment starts at the apostrophe



Code Example 6

X = 3.14 * 7.2 ' Calculate value for X
?X ' Display value
 22.608 



Code Example 7

?X = 32
False



Code Example 8

X = 6.28 * 7.2 ' Calculate value for X
Y = X * 2 ' Reuse the X value
?Y ' show the value of Y
90.432



Code Example 9

X = 3.1415926 * _
33    ' This is treated as part of the first line
?X
 103.6725558



Code Example 10

X = 54 * 1.2 : ?X
 64.8



Code Example 11

X = 2 ' assign a numeric value
X = X / 3 ' do a calculation
?X ' display the result
 0.666666666666667



Code Example 12

d1 = #2/15/2000#
?d1
2/15/2000 



Code Example 13

t1 = #3:15:55#
?t1
3:15:55 AM 



Code Example 14

d = #3/15/2000 8:15 PM#
?DateValue(d)
3/15/2000 
?TimeValue(d)
8:15:00 PM



Code Example 15

X = #Jan 1, 2011# ' Start value
Y = X + 1 ' add 1 (one day)
?Y 'display result
1/2/2011



Code Example 16

X = #Jan 1, 2011# ' Start value
Y = X + .5 ' add .5 (12 hours)
?Y 'display result
1/1/2011 12:00:00 PM



Code Example 17

OneHour = 1/24    ' one twenty-forth of a day
?OneHour
 4.16666666666667E-02 
 
OneMinute = OneHour / 60  'one sixtyth of an hour
?OneMinute
 6.94444444444444E-04 
 
T = #Jan 1, 2000 11:30 AM#
T = T + 2*OneHour + 15 * OneMinute 'add 2 hours and 15 minutes
?T
1/1/2000 1:45:00 PM 



Code Example 18

?#Jan 10, 2010# - #Jan 1, 2010#
 9 
?#Jan 10, 2010 12:00# - #Jan 1, 2010#
 9.5
?#Jan 10, 2010 12:00# - #Jan 10, 2010 11:00#
 4.16666666642413E-02



Code Example 19

X = #Jan 10, 2010 12:00# - #Jan 10, 2010 11:00# ' one hour as a Date
H = 1/24      ' one hour as a double
D = X/H       ' divide one by the other
?D = 1        ' is it one?
False
?D            ' display it
 0.999999999941792 



Code Example 20

?Round(D,5) = 1 ' is it one to 5 decimals?
True



Code Example 21

H = 1/24      ' one hour as a double
?H = 1/24     ' compare to exact value
True



Code Example 22

X = 1/3
?X
 0.333333333333333 
?X = 0.333333333333333
False
?X - 0.333333333333333
 3.33066907387547E-16



Code Example 23

A = "First "  ' Note the space at the end
B = "Second" ' More text
C = A & B & " Third" ' put all together
?C ' display the result
First Second Third



Code Example 24

X = "-1,200.5"
?X * 2 ‘This is OK
-2401 
X = "15%"
?X * 2 ‘THIS WILL CAUSE AN ERROR



Code Example 25

X = "1.5000"
?X
1.5000
?CDbl(X)
 1.5



Code Example 26

?CDate(10000.75)
5/18/1927 6:00:00 PM
?CDate("Jan. 15, 1955")
1/15/1955



Code Example 27

X = Array(11, 12, 13, 14, 15, 16)
Y = X



Code Example 28

X = Array(11, 12, 13, 14, 15, 16) ' Load array X
A = X(3)    ' entry at index 3
?A          ' print it
 14



Code Example 29

X = Array(11, 12, 13, 14, 15, 16) ' Load array X
?X(0)     ' first value
 11



Code Example 30

A = Array(1,2,3,4)
?UBound(A)
 3 
?LBound(A)
 0



Code Example 31

X = Array(11, 12, 13, 14, 15, 16) ' load array
I = 5             ' set index variable
A = X(I)          ' get value from array
?A                ' show it
 16 



Code Example 32

ArrayVal = Array(110, 23, -99, 0, -3.5, 8)
ArrayValCopy = ArrayVal  ' copy it to another
ArrayVal(2) = 0        ' change the first array
?ArrayValCopy(2) = ArrayVal(2) ' still equal?
 False



Code Example 33

Public Function Times2(Val)
 1 Times2= Val * 2
End Function



Code Example 34

Public Function Times2(Val)
 1 Times2 = Val * 2
End Function



Code Example 35

Public Function Fall_1(Secs)
 1 Dist = Secs ^ 2
 2 Dist = Dist * 0.5 * 9.8
 3 Fall_1 = Dist
End Function



Code Example 36

?Fall_1(20)



Code Example 37

?Fall_1(20)
 1960



Code Example 38

Public Function Fall_2(Secs, Body)
 1 If Body = "Moon" Then
 2   Accel = 1.62     ‘Moon’s acceleration
 3 Else
 4   Accel = 9.81     ‘Earth’s acceleration
 5 End If
 6 Dist = Secs ^ 2    ‘square the time
 7 Dist = Dist * 0.5 * Accel ‘calculate distance for this gravity
 8 Fall_2 = Dist
End Function



Code Example 39

  Temp = 60
  If Temp > 100 Or Temp < 50 Then
    IsOK = False
  Else
    IsOK = True
  End If



Code Example 40

If Temp > 100 Then 
If Not(Temp <= 100) Then



Code Example 41

If Temp > 100 Or < 50 Then ‘ERROR, WON’T WORK!!
If Temp > 100 Or Temp < 50 Then ‘This is OK



Code Example 42

Temp = 80
Humid = 70
?(Temp > 70 AND Humid < 50) Or (Temp < 60 And Not(Humid > 65))
False



Code Example 43

?(1 = 2)
False
?(1 = 2) * 1
 0 



Code Example 44

Public Function Fall_3(Secs, Body)
 1 If Body = "Moon" Then
 2   Accel = 1.62
 3 ElseIf Body = "Earth" Then
 4   Accel = 9.81
 5 ElseIf Body = "Mars" Then
 6   Accel = 3.7
 7 Else
 8   Accel = 0
 9 End If
10 Dist = Secs ^ 2
11 Dist = Dist * 0.5 * Accel
12 Fall_3 = Dist
End Function



Code Example 45

  Select Case Body
  Case "Moon"
    Accel = 1.62
  Case "Earth"
    Accel = 9.81
  Case "Mars"
    Accel = 3.7
  Case Else
    Accel = 0
  End Select



Code Example 46

Public Function LoanShark(Principal, Rate, Periods)
 1   For Idx = 1 To Periods
 2     Principal = Principal * (1 + Rate)
 3     Rate = Rate * 2             ‘rate doubles next period
 4   Next
 5   LoanShark = Principal
End Function



Code Example 47

Function NumberArray1()
1  ArrayVal = Array(110, 23, -99, 0, -3.5, 8)
2  Sum = 0
3  For Each AVal In ArrayVal
4    Sum = Sum + AVal
5  Next
6  NumberArray1 = Sum
End Function



Code Example 48

?NumberArray1
 38.5



Code Example 49

Function NumberArray2()
 1   ArrayVal = Array(110, 23, -99, 0, -3.5, 8)
 2   Sum = 0
 3   For Each AVal In ArrayVal
 4     If AVal < 0 Then
 5       Exit For
 6     End If
 7     Sum = Sum + AVal
 8   Next
 9   NumberArray2 = Sum
End Function



Code Example 50

Option Base 1



Code Example 51

1 ArrayVal = Array(110, 23, -99, 0, -3.5, 8)
2 Val1 = ArrayVal(1)
3 Val3 = ArrayVal(3)



Code Example 52

Function NumberArrayRef()
1  ArrayVal = Array(110, 23, -99, 0, -3.5, 8)
2  Offsets = Array(1, 3, 4, 6)
3  Sum = 0
4  For Each OffIdx In Offsets
5    Sum = Sum + ArrayVal(OffIdx)
6  Next
7  NumberArrayRef = Sum
End Function



Code Example 53

For idx = 1 to 10



Code Example 54

For idx = 10 to 1 step -1



Code Example 55

For idx = 100 to 0 step -3: Next
?idx 
-2  



Code Example 56

Public Function RandomAdder(inValue)
1 RandomAdder = inValue
2 While RandomAdder < 100
3   RandomAdder = RandomAdder + Rnd() * 10
4 Wend
End Function



Code Example 57

Public Function RandomAdder2(inValue)
1 RandomAdder2 = inValue
2 Do
3   RandomAdder2 = RandomAdder2 + Rnd() * 10
4 Loop Until RandomAdder2 >= 100
End Function 



Code Example 58

? RandomAdder(100)
 100 
? RandomAdder2(100)
 102.2687



Code Example 59

r = Range("A1”, "C2")
?Ubound(r)
 2



Code Example 60

r = Range("A1:C2")
?Ubound(r)
 2



Code Example 61

S = Range("Securities")
?Ubound(S)
 51



Code Example 62

Y = Range("B1") ' Get value
?Y          ' display it
0.035



Code Example 63

X = Range("InterestRate") ' Named Range Value
?X          ' display it
 0.035



Code Example 64

A = X(2,3)



Code Example 65

X = Range("B1:B3")  ' Get a single column
?X(2, 1)      ' middle cell value
 10000 
X = Range("A3:B3")  ' Get a single row
?X(1, 2)      ' right cell value
 350 
X = Range("A1:B3")  ' Get multiple values
?X(1, 1)      ' top left cell value
Rate
?X(3, 2)      ' bottom right cell value
 350 



Code Example 66

X = Range("A1:B3")
?UBound(X)
 3



Code Example 67

X = Range("A1:B3")
?UBound(X,1)               ‘get row count
 3 
?UBound(X,2)               ‘get column count
 2



Code Example 68

NameAgeLookup(Range(“NamesAges”), “Jim)



Code Example 69

Function NameAgeLookup(NameAgeRange, Who)
1   NameAgeArray = NameAgeRange
2   For rowIdx = 1 To UBound(NameAgeArray)
3     NameVal = NameAgeArray(rowIdx, 1)
4     AgeVal = NameAgeArray(rowIdx, 2)
5     If NameVal = Who Then
6       NameAgeLookup = AgeVal
7       Exit Function
8     End If
9   Next
10  NameAgeLookup = 0
End Function



Code Example 70

Public Function TestedCounter(InRange)
1  RData = InRange
2  If IsArray(RData) Then
3   TestedCounter = UBound(RData)
4  Else
5   TestedCounter = 1
6  End If
End Function



Code Example 71

x = Selection
?UBound(x)
 10 
?Selection.Address
$A$1:$C$10



Code Example 72

?Selection.Address(External:=True)
'[JustABit.xlsm]RangeExample'!$A$1:$C$10



Code Example 73

x = Selection
?x.Address    ‘ This will cause an error



Code Example 74

Function NameAgeLookup(NameAgeRange, Who)
1   NameAgeArray = NameAgeRange
2   For rowIdx = 1 To UBound(NameAgeArray)



Code Example 75

2   For rowIdx = 1 To UBound(NameAgeRange)



Code Example 76

1   NameAgeArray = NameAgeRange.value



Code Example 77

X = Selection ' Get the data
?Ubound(X)    ' row count
 11 
?UBound(Selection)  ' This causes an error
?Selection.Address  ' Get the Range address
$A$1:$B$11
?X.Address        ' This causes an error



Code Example 78

Public Function SafeCounter(InRange)
1  On Error GoTo Badness
2  SafeCounter = UBound(InRange.value)
3  Exit Function
4 Badness:
5  SafeCounter = 1
End Function



Code Example 79

Public Function SafeCounter2(InRange)
1  On Error GoTo Badness
2  rangeCount = UBound(InRange.value)
3  SafeCounter2 = "The count is: " & rangeCount
4  Exit Function
5 Badness:
6  rangeCount = 1
7  Resume Next
End Function



Code Example 80

?SafeCounter2("A1")
The count is: 1



Code Example 81

Public Function NoErrors(InRange)
1  On Error Resume Next
2  rowCount = UBound(InRange.value)
3  NoErrors = "Rows: " & rowCount
End Function



Code Example 82

?NoErrors(Range("A1"))
Rows: 



Code Example 83

Public Function NoErrors1(InRange)
1  On Error Resume Next
2  rowCount = "Single Cell"
3  rowCount = UBound(InRange.value)
4  NoErrors1 = "Rows: " & rowCount
End Function



Code Example 84

?NoErrors1(Range("A1"))
 Rows: Single Cell



Code Example 85

?NoErrors1("This isn't a Range")
Rows: Single Cell



Code Example 86

Public Function NoErrors2(InRange)
1  On Error GoTo NotRange
2  X = InRange.Address
3  On Error Resume Next
4  rowCount = "Single Cell"
5  rowCount = UBound(InRange.value)
6  NoErrors2 = "Rows: " & rowCount
7  Exit Function
8 NotRange:
9  NoErrors2 = "Not a Range"
End Function



Code Example 87

Public Function NoErrors3(InRange)
1  If "Range" = typeName(InRange) Then
2   X = InRange
3   If IsArray(X) Then
4    NoErrors3 = "Rows: " & UBound(InRange.value)
5   Else
6    NoErrors3 = "Single Cell"
7   End If
8  Else
9   NoErrors3 = "Not a Range"
10 End If
End Function



Code Example 88

Public Function CatchError(X)
1  On Error GoTo Badness
2  CatchError = TryError(X)
3  Exit Function
Badness:
4  CatchError = "There was an error"
End Function
 
Public Function TryError(Y)
1  TryError = Y * 2
End Function



Code Example 89

?CatchError(3)
 6 
?CatchError("Yes")
There was an error



Code Example 90

Public Function AvgName(TableRange)
1  TableArray = TableRange
2  Total = 0
3  For Row = 1 To UBound(TableArray, 1)
4    Total = Total + TableArray(Row, 2)
5  Next
6  Avg = Total / UBound(TableArray, 1)
7  MinDiff = 1E+100
8  For Row = 1 To UBound(TableArray, 1)
9    If Abs(TableArray(Row, 2) - Avg) < MinDiff Then
10      MinDiff = Abs(TableArray(Row, 2) - Avg)
11      RowName = TableArray(Row, 1)
12    End If
13  Next
14  AvgName = RowName
End Function



Code Example 91

3  For Row = 1 To UBound(TableArray, 1)
4    Total = Total + TableArray(Row, 2)
5  Next
6  Avg = Total / UBound(TableArray, 1)



Code Example 92

Public Function AvgName(TableRange)
1  TableArray = TableRange
2  Total = 0
3  For Row = 1 To UBound(TableArray, 1)
4    Total = Total + TableArray(Row, 2)
5  Next
6  Avg = Total / UBound(TableArray, 1)
7  MinDiff = 1E+100
8  For Row = 1 To UBound(TableArray, 1)
9    If Abs(TableArray(Row, 2) - Avg) < MinDiff Then
10      MinDiff = Abs(TableArray(Row, 2) - Avg)
11      RowName = TableArray(Row, 1)
12    End If
13  Next
14  AvgName = RowName
End Function



Code Example 93

A = WorksheetFunction.Acos(.5)
?A
 1.0471975511966



Code Example 94

Public Function OptArgs(Optional ValA = 10, Optional ValB = 20)



Code Example 95

?Round(5.456, 2)
 5.46 
?Round(NumDigitsAfterDecimal:=2, Number:=5.456)
 5.46



Code Example 96

StringA = "Hi"



Code Example 97

StringA = "Hi"
StringA = StringA & " There"



Code Example 98

StringA = "Hi"
StringA = StringA & 1
?StringA
Hi1



Code Example 99

X = "3"
Y = X/2
?Y
 1.5 



Code Example 100

X = "3%"
Y = X/2   ' This will error 



Code Example 101

X = "3"
?IsNumeric(X)
True
X = "Hi There"
?IsNumeric(X) 
False



Code Example 102

1 StringA = "Dog"
2 If StringA = "Cat" Then
3   Cry = "Meow"
4 ElseIf StringA = "Dog" Then
5   Cry = "Woof"
6 Else
7   Cry = "?"
8 End If



Code Example 103

1 StringA = "  Dog "
2 StringA = Trim(StringA)



Code Example 104

1 StringA = "Dog"
2 StringA = UCase(Trim(StringA))
3 If StringA = "CAT" Then
4   Cry = "Meow"
5 ElseIf StringA = "DOG" Then
6   Cry = "Woof"
7 Else
8   Cry = "?"
9 End If



Code Example 105

Function Salutation(Name)
1  SubA = UCase(Trim(left(Trim(Name), 3)))
2  If SubA = "MR" Then
3    Salutation = "Sir"
4  ElseIf SubA = "MRS" Then
5    Salutation = "Madam"
6  Else
7    Salutation = "Ms"
8  End If
End Function



Code Example 106

1 SubA = UCase(Trim(left(Trim(Name), 3)))



Code Example 107

Name = " Mrs Green"



Code Example 108

?Mid("ABC DEF”, 2, 4)
BC D



Code Example 109

?Mid("ABC DEF”, 2)
BC DEF



Code Example 110

?Len("ABC DEF")
 7



Code Example 111

S = "ABC DEF"
L = Len(S)
?Left(S, L - 1)
ABC DE



Code Example 112

SS = "This is a test"
?InStr(SS, "a")
 9 
?InStr(SS, "x")
 0 
?InStr(SS, "is")
 3



Code Example 113

Function ExName(BondID)
1 EndWords = Array("CO”, "CORP”, "CORPORATION”, "INC”, "FDIC”, "PLC")
2 WordArray = Split(Trim(BondID), " ")
3 ExName = ""
4 If Not (IsEmpty(WordArray)) Then
5  For WIdx = 0 To UBound(WordArray)
6   ExName = ExName & WordArray(WIdx)
7   For EIdx = 1 To UBound(EndWords)
8    If EndWords(EIdx) = WordArray(WIdx) Then
9     Exit Function
10    End If
11   Next
12   ExName = ExName & " "
13  Next
14 End If
End Function



Code Example 114

2 WordArray = Split(Trim(BondID), " ")



Code Example 115

4 If Not (IsEmpty(WordArray)) Then



Code Example 116

5  For WIdx = 0 To UBound(WordArray)
6   ExName = ExName & WordArray(WIdx)
7   For EIdx = 1 To UBound(EndWords)
8    If EndWords(EIdx) = WordArray(WIdx) Then
9     Exit Function
10    End If
11   Next
12   ExName = ExName & " "
13  Next



Code Example 117

8    If EndWords(EIdx) = WordArray(WIdx) Then
9     Exit Function
10    End If
11   Next
12   ExName = ExName & " "



Code Example 118

?ExName("ALLSTATE CORP DEB 7.50000% 06/15/2013")
ALLSTATE CORP



Code Example 119

Function ExUtil(BondID, SrchChar)
1 WordArray = Split(Trim(BondID), " ")
2 If Not (IsEmpty(WordArray)) Then
3  For WIdx = 0 To UBound(WordArray)
4   If InStr(WordArray(WIdx), SrchChar) > 0 Then
5    ExUtil = WordArray(WIdx)
6    Exit Function
7   End If
8  Next
9 End If
End Function



Code Example 120

4  If InStr(WordArray(WIdx), SrchChar) > 0 Then
5    ExUtil = WordArray(WIdx)
6    Exit Function
7  End If



Code Example 121

Function ExMatDate(BondID)
1  ExMatDate = left(ExUtil(BondID, "/"), 10)
2  If IsDate(ExMatDate) Then
3    ExMatDate = CDate(ExMatDate)
4  Else
5    ExMatDate = "No Date Value"
6  End If
End Function



Code Example 122

Function ExCoupon(BondID)
1  CouponStr = ExUtil(BondID, "%")
2  If Not(IsEmpty(CouponStr)) Then
3    PctPos = InStr(CouponStr, "%")
4    CouponStr = left(CouponStr, PctPos - 1)
5    If IsNumeric(CouponStr) Then
6       ExCoupon = CouponStr / 100
7       Exit Function
8    End If
9  End If
10 ExCoupon = "No Coupon Value"
End Function



Code Example 123

Function PartPrefix(PartID)
1  numbers = "0123456789"
2  For Idx = 1 To Len(PartID)
3    tstChar = Mid(PartID, Idx, 1)
4    If InStr(numbers, tstChar) = 0 Then
5      Exit For
6    End If
7  Next
8  PartPrefix = left(PartID, Idx - 1)
End Function



Code Example 124

Function PartSuffix(PartID)
1  suffPos = InStr(PartID, ”.")
2  If suffPos > 0 Then
3    PartSuffix = Mid(PartID, suffPos + 1)
4  Else
5    PartSuffix = ""
6  End If
End Function



Code Example 125

Function PartMid(PartID)
1  suffLen = Len(PartSuffix(PartID))
2  If suffLen > 0 Then
3    suffLen = suffLen + 1
4  End If
5  preLen = Len(PartPrefix(PartID))
6  midLen = Len(PartID) - suffLen - preLen
7  If midLen > 0 Then
8    PartMid = Mid(PartID, preLen + 1, midLen)
9  Else
10    PartMid = ""
11  End If
End Function



Code Example 126

S = "It's ""Dave"" here"
?S
It's "Dave" here



Code Example 127

S1 = "Pea"
X = 3.14159
S = S1 & " " & X & " Poe Pooh"
?S
Pea 3.14159 Poe Pooh



Code Example 128

X = Chr(169) & "2010"
?X
©2010



Code Example 129

X = "First Line" & vbNewLine & "Second Line"
?X
First Line
Second Line



Code Example 130

X = ASC(" ABC")
?X
 32



Code Example 131

T = Left("ABCDEFG”, 2)
?T
AB



Code Example 132

T = Right("ABCDEFG”, 2)
?T
FG



Code Example 133

T = Mid("ABCDEFG”, 3,2)
?T
CD



Code Example 134

T = Mid("ABCDEFG”, 3)
?T
CDEFG



Code Example 135

P = InStr("ABCDEF”, "CD")
?P
 3



Code Example 136

T = "ABCxDXFxGHI"
P = InStr(T, "x")
?P
 4 
P1 = InStr(P+1, T, "x")
?P1
 8



Code Example 137

T = "ABCxDXFxGHI"
P = InStr(T, "x")
P1 = InStr(P+1, T, "x”, vbTextCompare)
?P1
 6



Code Example 138

L = Len("ABCD")
?L
 4



Code Example 139

S = "A" & vbCRLF & "B"
?Len(S)
 4 
?ASC(Mid(S,2,1)) & " " & Asc(Mid(S,3,1))
13 10



Code Example 140

P = InStrRev("ABxCxD”, "x")
?P
 5



Code Example 141

TA = Split("A Bx C D12”, " ")
?TA(0)
A
?TA(3)
D12



Code Example 142

TA = Split("AsBxsCSD12sXYZ”, "s")
?TA(3)
XYZ
TA = Split("AsBxsCSD12sXYZ”, "s”, compare:=vbTextCompare)
?TA(3)
D12



Code Example 143

TA = Split("3.14, 56.7, 12.3", ", ")
?TA(1)
56.7
TB = Split(TA(1), ".")
?TB(0)
56



Code Example 144

TA = Array("First”, "Second”, "Third")
?Join(TA, vbCrLf)
First
Second
Third



Code Example 145

T = "XYZZY"
TR = Replace(T, "Y”, " why ")
?TR
X why ZZ why



Code Example 146

S = "Fee Fie Foe Foo"
Sr = Replace(S, "F”, "FL”,6,1)
?Sr
ie FLoe Foo



Code Example 147

S = "Fee Fie Foe Foo"
Sr = Left(S, 5) & Replace(S, "F”, "FL”,6,1)
?Sr
Fee Fie FLoe Foo



Code Example 148

Public Function ReplaceKeep(T, F, R, P)
  ReplaceKeep = left(T, P - 1) & Replace(T, F, R, P, 1)
End Function



Code Example 149

S = "Fee Fie Foe Foo"
?ReplaceKeep(S, "F”, "FL”, 6)
Fee Fie FLoe Foo



Code Example 150

Public Function FormatUSPhone(ACode, PNumber, Exten)
1 FormatUSPhone = "(" & ACode & ") " & PNumber & " x" & Exten
End Function



Code Example 151

?FormatUSPhone("800”, "555-1212”, "123")
(800) 555-1212 x123



Code Example 152

x = 123.456
?Format(x, "General Number")
123.456
?Format(x, "Currency")
$123.46
?Format(x, "General Date")
5/2/1900 10:56:38 AM



Code Example 153

X = 13.14159
?Format(X, "#.#")
13.1
?Format(X, "#.##")
13.14
?Format(X, "000.##")
013.14
?Format(X, "#E-")
1E1



Code Example 154

x = "AA"
?"Start:" & Format(x, "@@@@") & ":End"
Start:  AA:End
?"Start:" & Format(x, "@@@@!") & ":End"
Start:AA  :End  



Code Example 155

?Format((1 = 2), "True/False")
False
?Format(1 <> 2, "Yes/No")
Yes



Code Example 156

Function EndTime(startDate, duration)
1  shortMin = 45
2  mediumMin = 120
3  longMin = 210
4  duration = UCase(Trim(duration))
5  If duration = "SHORT" Then
6    minCount = shortMin
7  ElseIf duration = "MEDIUM" Then
8    minCount = mediumMin
9  ElseIf duration = "LONG" Then
10    minCount = longMin
11  Else
12    minCount = 0
13  End If
14  EndTime = startDate + minCount / (24 * 60)
End Function



Code Example 157

Function SafeEndTime(startDate, duration)
1  If IsDate(startDate) Then
2    SafeEndTime = EndTime(startDate, duration)
3  Else
4    SafeEndTime = "Date Error"
5  End If
End Function



Code Example 158

Function BetterEndTime(startDate, duration)
1  If Not (IsDate(startDate)) Then
2    BetterEndTime = "Date Error"
3  End If
4  duration = UCase(Trim(duration))
5  DurationDefs = Range("DurationDefs").value
6  rowCount = UBound(DurationDefs, 1)
7  For dIdx = 1 To rowCount
8    If UCase(Trim(DurationDefs(dIdx, 1))) = duration Then
9      minCount = DurationDefs(dIdx, 2)
10      Exit For
11    End If
12  Next
13  BetterEndTime = startDate + minCount / (24 * 60)
End Function



Code Example 159

5  DurationDefs = Range("DurationDefs").value
6  rowCount = UBound(DurationDefs, 1)
7  For dIdx = 1 To rowCount
8    If UCase(Trim(DurationDefs(dIdx, 1))) = duration Then
9      minCount = DurationDefs(dIdx, 2)
10      Exit For
11    End If
12  Next



Code Example 160

10      Exit For
11    End If
12  Next
13  BetterEndTime = startDate + minCount / (24 * 60)
End Function



Code Example 161

D = #6/14/2010 1:30:15 pm#
?D
6/14/2010 1:30:15 PM



Code Example 162

X = Now()
Y = Now()
?Y - X
 0 
?X
4/1/2011 12:18:05 AM



Code Example 163

d = #Jan 15, 1999 3:30 PM#
?DateValue(d)
1/15/1999 
?TimeValue(d)
3:30:00 PM



Code Example 164

?Day(Now())
 2



Code Example 165

?WeekDayName(Weekday(Now()))
Monday



Code Example 166

X = Timer
Y = Timer
?Y - X
 0.2000122



Code Example 167

D = 2
M = 5
Y = 2000
?DateSerial(Y, M, D)
5/2/2000



Code Example 168

D = -2
M = -5
Y = 2000
?DateSerial(Y, M, D)
6/28/1999



Code Example 169

Dp = DatePart("m”, #March 15, 2010#)
?Dp
 3 
?MonthName(Dp)
March



Code Example 170

d = #1/1/2000#
?DateAdd("m”, 3, d)
4/1/2000 
?DateAdd("q”, -6, d)
7/1/1998 



Code Example 171

d = #4/15/2000#
q = DatePart("q”, d)
ystart = DateSerial(Year(d), 1, 1)
?DateAdd("q”, q + 5, ystart)
10/1/2001 



Code Example 172

d = #1/1/2000#
?DateAdd("d”, 3000, d)
3/19/2008 
?d + 3000
3/19/2008



Code Example 173

d = #1/1/2000#
?DateAdd("d”, -1, d)
12/31/1999 
?d - 1
12/31/1999



Code Example 174

d = #1/1/2000 11:45#
?DateAdd("h”, 32, d)
1/2/2000 7:45:00 PM



Code Example 175

d1 = #2/15/2000#
d2 = #3/25/2000#
?DateDiff("q”, d1, d2)
 0 
d3 = #12/15/1999#
?DateDiff("q”, d1, d3)
-1



Code Example 176

Public Function BondInc(TestDt, HoldingRange)
1  Securities = Range("Securities").value
2  Holdings = Range("Holdings").value
3  BondInc = 0
4 For HoldRow = 1 To UBound(Holdings)
5  HoldName = Trim(Holdings(HoldRow, 1))
6  If HoldName <> "" Then
7   For SecRow = 1 To UBound(Securities)
8    If Trim(Securities(SecRow, 1)) = HoldName Then
9     MatDt = Securities(SecRow, 3)
10     Pay = UCase(Trim(Securities(SecRow, 4)))
11     Fraction = CouponFraction(Pay, MatDt, TestDt)
12     If Fraction > 0 Then
13      Coupon = Securities(SecRow, 2) / 100
14      HoldAmount = Holdings(HoldRow, 2)
15      BondInc = HoldAmount * Coupon * Fraction
16      If TestDt = MatDt Then
17       BondInc = BondInc + HoldAmount
18      End If
19     End If
20     Exit For
21    End If
22   Next
23  End If
24  Next
End Function



Code Example 177

Public Function CouponFraction(Pay, MatDt, TestDt)
1   CouponFraction = 0
2   If MatDt < TestDt Then
3     Exit Function
4   End If
5   If Day(TestDt) <> Day(MatDt) Then
6     If Not ((Day(TestDt) < Day(MatDt)) And _
        (Day(TestDt + 1) = 1)) Then
7         Exit Function
8     End If
9   End If
10  MatMonth = Month(MatDt)
11  TestMonth = Month(TestDt)
12  Select Case Pay
    Case "M"
13    CouponFraction = 0.0833333
14  Case "Q"
15    If ((MatMonth - 1) Mod 3) = 0 Then
16      CouponFraction = 0.25
17    End If
18  Case "H"
19    If (MatMonth Mod 6) = (TestMonth Mod 6) Then
20      CouponFraction = 0.5
21    End If
22  Case Else
23    CouponFraction = 0
24  End Select
End Function 



Code Example 178

12  Select Case Pay
    Case "M"
13    CouponFraction = 0.0833333
14  Case "Q"
15    If ((MatMonth - 1) Mod 3) = 0 Then
16      CouponFraction = 0.25
17    End If
18  Case "H"
19    If (MatMonth Mod 6) = (TestMonth Mod 6) Then
20      CouponFraction = 0.5
21    End If
22  Case Else
23    CouponFraction = 0
24  End Select



Code Example 179

Public Function BondInc(TestDt, HoldingRange)
1  Securities = Range("Securities").value
2  Holdings = Range("Holdings").value
3  BondInc = 0
4 For HoldRow = 1 To UBound(Holdings)
5  HoldName = Trim(Holdings(HoldRow, 1))
6  If HoldName <> "" Then
7   For SecRow = 1 To UBound(Securities)
8    If Trim(Securities(SecRow, 1)) = HoldName Then
9     MatDt = Securities(SecRow, 3)
10     Pay = UCase(Trim(Securities(SecRow, 4)))
11     Fraction = CouponFraction(Pay, MatDt, TestDt)
12     If Fraction > 0 Then
13      Coupon = Securities(SecRow, 2) / 100
14      HoldAmount = Holdings(HoldRow, 2)
15      BondInc = HoldAmount * Coupon * Fraction
16      If TestDt = MatDt Then
17       BondInc = BondInc + HoldAmount
18      End If
19     End If
20     Exit For
21    End If
22   Next
23  End If
24  Next
End Function



Code Example 180

12     If Fraction > 0 Then
13      Coupon = Securities(SecRow, 2) / 100
14      HoldAmount = Holdings(HoldRow, 2)
15      BondInc = HoldAmount * Coupon * Fraction
16      If TestDt = MatDt Then
17       BondInc = BondInc + HoldAmount
18      End If
19     End If
20     Exit For
21    End If
22   Next
23  End If
24  Next
End Function