Advanced Programming Strings Arrays Arguments Modulus Strings are

  • Slides: 30
Download presentation
Advanced Programming Strings Arrays Arguments Modulus

Advanced Programming Strings Arrays Arguments Modulus

Strings are NOT arrays • • • Strings have no indexes Although arrays of

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 My. String = "Mid Function Demo" ' Create text string. First. Word

Mid() function My. String = "Mid Function Demo" ' Create text string. First. Word = Mid(My. String, 1, 3) ' Returns "Mid". Last. Word = Mid(My. String, 14, 4) ' Returns "Demo". Mid. Words = Mid(My. String, 5) ' Returns "Function Demo".

In. Str() function str. Haystack ="XXp. XXPXXP“ str. Needle = "p" int. Where =

In. Str() function str. Haystack ="XXp. XXPXXP“ str. Needle = "p" int. Where = Instr(1, str. Haystack, "W") Returns 0 int. Where = Instr(4, str. Haystack, str. Needle) Returns 6

Other Functions • Mid() statement (not the mid function) – str. X = "Hello,

Other Functions • Mid() statement (not the mid function) – str. X = "Hello, Jane" ' example of mid() statement – Mid(str. X, 8, 4) = "Bill“ (creates Hello, Bill) • • Left(str. X, 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,

Intro to Arrays • Lists (e. g. , states, income levels, schedules) • Private, Public, fixed or dynamic size! • Example: – Dim str. Month. Names(0 to 11) As String

String example: base 0 str. Month(0) = "January" str. Month(1) = "February" str. Month(2)

String example: base 0 str. Month(0) = "January" str. Month(1) = "February" str. Month(2) = "March" str. Month(3) = "April" str. Month(4) = "May" int. Input = Input. Box("Enter a month number (1 -12)")-1 Msg. Box “You entered “& str. Month(int. Input)

Which Array to Go? • • Option Base 1 or Option Base 0? Public

Which Array to Go? • • Option Base 1 or Option Base 0? Public str. State(0 to 49) As String Public str. State(1 to 50) As String Public str. State(50) As String

Flexible Array • Dynamic • Dim cur. Level() As Currency • Expands and contracts

Flexible Array • Dynamic • Dim cur. Level() As Currency • Expands and contracts • Good for uncertain run-time conditions • Can loose contents unless… – Redim Preserve cur. Level(7) • Can erase dynamic arrays: Erase cur. Level

Multidimensional arrays • Referencing locations by field and record • Example Dim var. Data.

Multidimensional arrays • Referencing locations by field and record • Example Dim var. Data. Set(0 to 4, 0 to 9) 5 fields (columns) 10 records (rows) First field of the last record: var. Data. Set(0, 9)

Multidimensional arrays Think of multidimension arrays as datasheets. This array has 6 columns and

Multidimensional arrays Think of multidimension arrays as datasheets. This array has 6 columns and 7 rows : Dim var. Contributor(0 to 5, 0 to 6) as variant Columns before rows (alpha order) June Nguyen's contribution is var. Contributor(5, 2)

Sizing up arrays • Your subs and functions will be more reusable if they

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(var. Data, 1 or 2), Ubound(var. Data, 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 var. Data? – Ubound(var. Data, 1) – Lbound(var. Data, 1) + 1

Example: summarize salaries • Dim cur. Salary(0 to 7, 0 to 10) • If

Example: summarize salaries • Dim cur. Salary(0 to 7, 0 to 10) • If column 7 of this array is $ salary • Summarize the salaries For int. Row = Lbound(cur. Salary, 2) to Ubound(cur. Salary, 2) cur. Sum = cur. Sum + cur. Salary(7, int. Row) int. N = int. N + 1 Next Msg. Box "Total Amount=" & Format(cur. Sum, "Currency") Msg. Box "Average Amount=" & Format( _ iif(int. N>0, cur. Sum/int. N, 0), "Currency")

Memory Considerations • str. Absent. Minded(0 to 9) uses 22 times less memory than

Memory Considerations • str. Absent. Minded(0 to 9) uses 22 times less memory than var. Absent. Minded(1 to 10) when they hold the identical data • int. Array(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

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 Drop. Avg(Param. Array var. Score() as Variant Dim var. Average

Parameter Arrays: Example Function Drop. Avg(Param. Array var. Score() as Variant Dim var. Average as Variant, var. Lowest as Variant Dim var. Value as Variant ' requires at least two scores in the array var. Lowest = 99999 if UBound(var. Score, 1)<= 0 then Drop. Avg=0 : Exit Function For Each var. Value in var. Score var. Average = var. Average + var. Value If var. Value < var. Lowest then var. Lowest = var. Value Next Drop. Avg = (var. Average - var. Lowest) / UBound(var. Score, 1) End Function Do this to test in the immediate window: ? Drop. Avg(95, 93, 87, 91, 81) ' notice arguments 91. 5 ? Drop. Avg(71, 85) 85 See next slide to compare Param array args to plain array args

Arrays: Example Function Drop. Average(dbl. Score() As Double) As Variant Dim var. Average As

Arrays: Example Function Drop. Average(dbl. Score() As Double) As Variant Dim var. Average As Variant, var. Low As Variant Dim var. Value As Variant, int. I As Integer, int. Count As Integer ' requires at least two scores in the array var. Low = 99999 int. Count = UBound(dbl. Score, 1) - LBound(dbl. Score, 1) If int. Count <= 0 Then Exit Function For int. I = LBound(dbl. Score, 1) To UBound(dbl. Score, 1) var. Average = var. Average + dbl. Score(int. I) If dbl. Score(int. I) < var. Low Then var. Low = dbl. Score(int. I) Next Drop. Average = (var. Average - var. Low) / int. Count End Function Do this to test in the immediate window: Public Sub XX() Dim dbl. Data(0 To 2) As Double dbl. Data(0) = 85. 6: dbl. Data(1) = 90. 2: dbl. Data(2) = 92. 1 Debug. Print Drop. Average(dbl. Data) ' notice arg is an array End Sub

Creating Arrays • The Array function • Your Option Base is observed Option Base

Creating Arrays • The Array function • Your Option Base is observed Option Base 0 Public Function Day. Of. Week(int. Day As Integer) As String Dim Week. Day As Variant Week. Day = _ Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat") Day. Of. Week = Week. Day(int. Day) End Function In the Immediate Window ? Day. Of. Week(1) Mon

Creating Arrays • The Get. Rows Method • Forces Option Base 0 Public Function

Creating Arrays • The Get. Rows Method • Forces Option Base 0 Public Function Sum() Dim int. Row as Integer, int. N as Integer Dim var. Data. Set() As Variant Dim cnn As ADODB. Connection Set cnn = Current. Project. Connection rst. Open "tbl. Employee", cnn, ad. Open. Dynamic, , ad. Cmd. Table var. Data. Set = rst. Get. Rows rst. Close For int. Row = LBound(var. Data. Set, 2) To UBound(var. Data. Set, 2) Sum = Sum + var. Data. Set(3, int. Row) : int. N = int. N+1 Next Sum = iif(int. N > 0, Sum/int. N , 0) Debug. Print "Average Amount=" & Format( Sum, "Currency") End Function

Detecting what data is in a Variant • Var. Type(variable) • Type. Name(variable) Useful

Detecting what data is in a Variant • Var. Type(variable) • Type. Name(variable) Useful for detecting – Calculation data – Arrays (Var. Type=8192+) – Valid dates

Receiving Arguments • Default is by reference (By. Ref) – Send the address of

Receiving Arguments • Default is by reference (By. Ref) – 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 (By. Val) – 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

Receiving Arguments: Example ' build in the protection at design time (formal args) Sub All. Names (By. Val Both. Names as String, By. Ref Last. Name as String, By. Ref First. Name as String) ' or use this actual argument format at run time ' it forces By. Val and sends a copy of the variable Call All. Names ((Names), First, Last) ' But, an array cannot be passed By. Val Call Average (var. Dat. Set, 6)

Type structure • • A named data structure Place in Declarations Module Instantiate when

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 Contribution. Form. Error First. Name As

Define the structure in the Declarations Module Type Contribution. Form. Error First. Name As Boolean Last. Name As Boolean Date As Boolean Party As Boolean Sex As Boolean Amount As Boolean End Type

Instantiate Error. In structure in the Form Module ' create a module-level structure in

Instantiate Error. In structure in the Form Module ' create a module-level structure in ' the Declarations section of your Form Module Private Error. In As Contribution. Form. Error ' in the error checking code set these flags Error. In. First. Name = True Error. In. 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 Msg. Box "The following errors must be

Use the structure to build a message Msg. Box "The following errors must be corrected: " & vb. CR & _ Error. List(Error. In)", vb. Exclamation, "Mayor Minimum" Function Error. List(Any. Err As Contribution. Form. Error) As String With Any. Err If. First. Name Then Error. List = Error. List + vb. Cr & "First Name" If. Last. Name Then Error. List = Error. List + vb. Cr & "Last Name" If. Party Then Error. List = Error. List + vb. Cr & "Party" If. Sex Then Error. List = Error. List + vb. Cr & "Sex" If. Date Then Error. List = Error. List + vb. Cr & "Date" If. Amount Then Error. List = Error. List + vb. Cr & "Amount" End With End Function

Use the structure to build a better message If Is. Error(Error. In) Then Msg.

Use the structure to build a better message If Is. Error(Error. In) Then Msg. Box "There are " & _ Is. Error(Error. In) & " errors. " & vb. CR & _ Error. List(Error. In)", vb. Exclamation, "Mayor Minimum" Private Function Is. Error(Error. Structure As _ Contribution. Form. Error) As Integer Is. Error = 0 If Error. Structure. First. Name Then Is. Error = Is. Error + 1 If Error. Structure. Last. Name Then Is. Error = Is. Error + 1 If Error. Structure. Party Then Is. Error = Is. Error + 1 If Error. Structure. Sex Then Is. Error = Is. Error + 1 If Error. Structure. Date Then Is. Error = Is. Error + 1 If Error. Structure. Amount Then Is. Error = Is. Error + 1 End Function

Modulus VBA and many other languages have this built in function for finding the

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

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 Range. Of. Choices() ' initialize the variables

Using the Modulus: Program Example Public Sub Range. Of. Choices() ' initialize the variables Dim str. Choice(1 to 3) As String Dim int. Choice As Integer Dim int. Trip As Integer int. Choice = 1 str. Choice(1) = "the beach? " str. Choice(2) = "the mountains? " str. Choice(3) = "Philadelphia? " ' body of your logic Do int. Trip = Msg. Box("Would you like to go to " & _ str. Choice(int. Choice), vb. Yes. No, "This Summer") int. Choice = (int. Choice Mod 3) + 1 Loop Until int. Trip = vb. Yes End Sub