Transcript Chapters 4 & 5 - classAnytime.com

Advanced Programming
Strings
Arrays
Arguments
Modulus
Strings are NOT arrays
•
•
•
•
•
•
Strings have no indexes
Although arrays of strings have indexes
A single string variable has no index
Array indexes can have base of 0 or 1
Strings positions ALWAYS begin at 1
String functions report errors for position=0
Mid() function
MyString = "Mid Function Demo"
' Create text string.
FirstWord = Mid(MyString, 1, 3)
' Returns "Mid".
LastWord = Mid(MyString, 14, 4)
' Returns "Demo".
MidWords = Mid(MyString, 5)
' Returns "Function Demo".
InStr() function
strHaystack ="XXpXXpXXPXXP“
strNeedle = "p"
intWhere = Instr(1, strHaystack, "W")
Returns 0
intWhere = Instr(4, strHaystack, strNeedle)
Returns 6
Other Functions
• Mid() statement (not the mid function)
– strX = "Hello, Jane" ' example of mid() statement
– Mid(strX, 8, 4) = "Bill“ (creates Hello, Bill)
•
•
•
•
Left(strX,n) and Right() – returns L/R sections
Ucase() and Lcase() – changes Up/Low case
Trim(), Rtrim() and Ltrim() – removes blanks
Len() – returns number of characters
Intro to Arrays
• Lists (e.g.,states, income levels, schedules)
• Private, Public, fixed or dynamic size!
• Example:
– Dim strMonthNames(0 to 11) As String
String example: base 0
strMonth(0) = "January"
strMonth(1) = "February"
strMonth(2) = "March"
strMonth(3) = "April"
strMonth(4) = "May"
intInput = InputBox("Enter a month number (1-12)")-1
MsgBox “You entered “& strMonth(intInput)
Which Array to Go?
•
•
•
•
Option Base 1 or Option Base 0?
Public strState(0 to 49) As String
Public strState(1 to 50) As String
Public strState(50) As String
Flexible Array
• Dynamic
• Dim curLevel() As Currency
• Expands and contracts
• Good for uncertain run-time conditions
• Can loose contents unless…
– Redim Preserve curLevel(7)
• Can erase dynamic arrays: Erase
curLevel
Multidimensional arrays
• Referencing locations by field and record
• Example
Dim varDataSet(0 to 4, 0 to 9)
5 fields (columns)
10 records (rows)
First field of the last record: varDataSet(0,9)
Multidimensional arrays
Think of multidimension arrays as datasheets.
This array has 6 columns and 7 rows :
Dim varContributor(0 to 5,0 to 6) as variant
Columns before rows (alpha order)
June Nguyen's contribution is varContributor(5,2)
Sizing up arrays
• Your subs and functions will be more reusable if they work
for any size arrays.
• Lbound(),Ubound() return lower, upper bound of one
dimension, regardless of BASE.
• Lbound(varData, 1 or 2),Ubound(varData, 1 or 2)
– Use 1 for fields count (columns – first again)
– Use 2 for records count (rows – second again)
• How many columns are there in varData?
– Ubound(varData,1) – Lbound(varData,1) + 1
Example: summarize salaries
• Dim curSalary(0 to 7, 0 to 10)
• If column 7 of this array is $ salary
• Summarize the salaries
For intRow = Lbound(curSalary,2) to Ubound(curSalary,2)
curSum = curSum + curSalary(7, intRow)
intN = intN + 1
Next
MsgBox "Total Amount=" & Format(curSum,"Currency")
MsgBox "Average Amount=" & Format( _
iif(intN>0,curSum/intN,0), "Currency")
Memory Considerations
• strAbsentMinded(0 to 9) uses 22 times less
memory than varAbsentMinded(1 to 10)
when they hold the identical data
• intArray(0 to 9, 0 to 19, 0 to 29) reserves
6,000 integer locations, or about 12,000
(plus) bytes. 10 * 20 * 30 = 6,000 * 2
bytes/integer = 12,000 bytes
Parameter Arrays in Procs
• Extremely useful and powerful
• The arguments are treated as one array
• VBA forces Option Base 0, so use Lbound() and
Ubound()
• Example: a general utility to calculate class
averages on quizzes, exams, and assignments after
dropping your lowest score. The number of scores
is unpredictable.
Parameter Arrays: Example
Function DropAvg(ParamArray varScore() as Variant) as Variant
Dim varAverage as Variant, varLowest as Variant
Dim varValue as Variant
' requires at least two scores in the array
varLowest = 99999
if UBound(varScore, 1)<= 0 then DropAvg=0 : Exit Function
For Each varValue in varScore
varAverage = varAverage + varValue
If varValue < varLowest then varLowest = varValue
Next
DropAvg = (varAverage - varLowest) / UBound(varScore, 1)
End Function
Do this to test in the immediate window:
?DropAvg(95,93,87,91,81) ' notice arguments
91.5
?DropAvg(71,85)
85
See next slide to compare Param array args to plain array args
Arrays: Example
Function DropAverage(dblScore() As Double) As Variant
Dim varAverage As Variant, varLow As Variant
Dim varValue As Variant, intI As Integer, intCount As Integer
' requires at least two scores in the array
varLow = 99999
intCount = UBound(dblScore, 1) - LBound(dblScore, 1)
If intCount <= 0 Then Exit Function
For intI = LBound(dblScore, 1) To UBound(dblScore, 1)
varAverage = varAverage + dblScore(intI)
If dblScore(intI) < varLow Then varLow = dblScore(intI)
Next
DropAverage = (varAverage - varLow) / intCount
End Function
Do this to test in the immediate window:
Public Sub XX()
Dim dblData(0 To 2) As Double
dblData(0) = 85.6: dblData(1) = 90.2: dblData(2) = 92.1
Debug.Print DropAverage(dblData) ' notice arg is an array
End Sub
Creating Arrays
• The Array function
• Your Option Base is observed
Option Base 0
Public Function DayOfWeek(intDay As Integer) As String
Dim WeekDay As Variant
WeekDay = _
Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat")
DayOfWeek = WeekDay(intDay)
End Function
In the Immediate Window
?DayOfWeek(1)
Mon
Creating Arrays
• The GetRows Method
• Forces Option Base 0
Public Function Sum()
Dim intRow as Integer, intN as Integer
Dim varDataSet() As Variant
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "tblEmployee", cnn, adOpenDynamic, , adCmdTable
varDataSet = rst.GetRows
rst.Close
For intRow = LBound(varDataSet, 2) To UBound(varDataSet, 2)
Sum = Sum + varDataSet(3, intRow) : intN = intN+1
Next
Sum = iif(intN > 0,Sum/intN , 0)
Debug.Print "Average Amount=" & Format( Sum, "Currency")
End Function
Detecting what data is in a Variant
• VarType(variable)
• TypeName(variable)
Useful for detecting
– Calculation data
– Arrays (VarType=8192+)
– Valid dates
Receiving Arguments
• Default is by reference (ByRef)
– Send the address of the argument
– The contents of the address can be changed by
either the caller or the procedure.
• Option is by value (ByVal)
– Send a copy of the argument
– The variable cannot be changed by the proc
because it only has a copy.
Receiving Arguments: Example
' build in the protection at design time (formal args)
Sub AllNames (ByVal BothNames as String, ByRef LastName
as String, ByRef FirstName as String)
' or use this actual argument format at run time
' it forces ByVal and sends a copy of the variable
Call AllNames ((Names), First, Last)
' But, an array cannot be passed ByVal
Call Average (varDatSet, 6)
Type structure
•
•
•
•
A named data structure
Place in Declarations Module
Instantiate when needed
Result: A data object
Define the structure in the Declarations Module
Type ContributionFormError
FirstName As Boolean
LastName As Boolean
Date As Boolean
Party As Boolean
Sex As Boolean
Amount As Boolean
End Type
Instantiate ErrorIn structure in the Form Module
' create a module-level structure in
' the Declarations section of your Form Module
Private ErrorIn As ContributionFormError
' in the error checking code set these flags
ErrorIn.FirstName = True
ErrorIn.Amount = True
' example on next slide shows
1. Passing a data structure as an argument
2. Use of the With…End structure
Use the structure to build a message
MsgBox "The following errors must be corrected:" & vbCR & _
ErrorList(ErrorIn)",vbExclamation,"Mayor Minimum"
Function ErrorList(AnyErr As ContributionFormError) As String
With AnyErr
If .FirstName Then ErrorList = ErrorList + vbCr & "First Name"
If .LastName Then ErrorList = ErrorList + vbCr & "Last Name"
If .Party Then ErrorList = ErrorList + vbCr & "Party"
If .Sex Then ErrorList = ErrorList + vbCr & "Sex"
If .Date Then ErrorList = ErrorList + vbCr & "Date"
If .Amount Then ErrorList = ErrorList + vbCr & "Amount"
End With
End Function
Use the structure to build a better message
If IsError(ErrorIn) Then MsgBox "There are " & _
IsError(ErrorIn) & " errors." & vbCR & _
ErrorList(ErrorIn)",vbExclamation,"Mayor Minimum"
Private Function IsError(ErrorStructure As _
ContributionFormError) As Integer
IsError = 0
If ErrorStructure.FirstName Then IsError = IsError + 1
If ErrorStructure.LastName Then IsError = IsError + 1
If ErrorStructure.Party Then IsError = IsError + 1
If ErrorStructure.Sex Then IsError = IsError + 1
If ErrorStructure.Date Then IsError = IsError + 1
If ErrorStructure.Amount Then IsError = IsError + 1
End Function
Modulus
VBA and many other languages have this built in function for
finding the remainder of a division. For example,
• 5 mod 2 equals 1 (5/2=2 with 1 left over)
• 6 mod 2 equals 0 (6/2=3 with 0 left over)
• anynumber mod 2 = 1 if anynumber is odd
• anynumber mod 2 = 0 if anynumber is even
Odd and even are the usual reasons for programming with
mod. And there is another handy use for the function:
controlling sequences by limiting the range of a counter.
Using the Modulus: Sequencing
Mod is also useful when you want to limit a series of numbers
to a range. Say you want users to be presented with a
sequence of three choices, or five choices, or seven choices
and not do a lot of programming logic.
choice = 1 + (choice mod N)
The line of code above will limit the values taken on by the
variable choice. They will be in the range of 1, 2,…, N. The
starting value of choice has to be inside the particular range.
An example is next.
Using the Modulus: Program Example
Public Sub RangeOfChoices()
' initialize the variables
Dim strChoice(1 to 3) As String
Dim intChoice As Integer
Dim intTrip As Integer
intChoice = 1
strChoice(1) = "the beach?"
strChoice(2) = "the mountains?"
strChoice(3) = "Philadelphia?"
' body of your logic
Do
intTrip = MsgBox("Would you like to go to " & _
strChoice(intChoice), vbYesNo, "This Summer")
intChoice = (intChoice Mod 3) + 1
Loop Until intTrip = vbYes
End Sub