Programming with VBA n n n Planning for

  • Slides: 22
Download presentation
Programming with VBA n n n Planning for Programming Variables and Constants Assignment Statements

Programming with VBA n n n Planning for Programming Variables and Constants Assignment Statements Functions Programming

Planning for Programming n Observe the process to be automated

Planning for Programming n Observe the process to be automated

Observe the Process State the problem: Given the cost of materials and labor, and

Observe the Process State the problem: Given the cost of materials and labor, and knowing the mark-up for overhead and profit, determine the price to bid. Identify the formula to use: Price = (Materials + Labor) * (1 + Overhead + Profit) Price = Cost + OH + Profit Identify: n Variables - entered by user n n n Material cost Labor cost Constants -set by programmer n n Overhead percentage Profit percentage

Observe the Process n Having identified: n n Formula to Use Variables Constants Define

Observe the Process n Having identified: n n Formula to Use Variables Constants Define specifically: n n Inputs – items the user will enter Outputs – form and media

Planning for Programming n n Observe the process to be automated Flowchart

Planning for Programming n n Observe the process to be automated Flowchart

Flow Charts n n Schematic Drawing of Process Use Standard Symbols Flow from Top

Flow Charts n n Schematic Drawing of Process Use Standard Symbols Flow from Top to Bottom Connect with Arrows Start or Stop Data Input/Output Process steps Predefined Process Decisions

Problem: Get Coffee! n n n Any task can be flow-charted Each step could

Problem: Get Coffee! n n n Any task can be flow-charted Each step could have its own chart Note decision flow Start Decide you need a cup of coffee Get cup Go to coffee pot Is pot empty? No Fill cup End Yes Make coffee

Cost Model n n Start Get user inputs n n n Material Labor Determine

Cost Model n n Start Get user inputs n n n Material Labor Determine cost Determine price Output results Quit Start Get: Labor cost Material cost Determine: Total cost Bid Price Show Bid Price Quit

Planning for Programming n n Observe the process to be automated Flowchart Write Pseudo

Planning for Programming n n Observe the process to be automated Flowchart Write Pseudo Code Edit Code with VBA Editor

Pseudo Code Start Get: Labor cost Material cost Determine: Total cost Bid Price Show

Pseudo Code Start Get: Labor cost Material cost Determine: Total cost Bid Price Show Bid Price Quit Write steps, formulas needed Ask user for labor cost [v. Labor] and material cost [v. Material] [v. Cost] = [v. Labor] + [v. Material] [v. Price] = [v. Cost]*(1+[c. OH]*[c. Profit]) Display message with [v. Price] Pseudo Code often ends up as comments in the VBA code

Write VBA Code

Write VBA Code

Variables and Constants n n n Storage “bins” in memory Identified by a unique

Variables and Constants n n n Storage “bins” in memory Identified by a unique name Variables can be changed by the user n n v. Name = Input. Box(“Enter your name”) Constants can only be changed by editing the VBA code n Const c. Mark. Up = 0. 25

Declaration Statements Choose a bin and label it! Dim my. Var as String Data

Declaration Statements Choose a bin and label it! Dim my. Var as String Data Dim type – short – what forcan “dimension” be stored? Variable names n String – text n Must be unique n Integer n Can’t be same as Sub n Single or Double n Can’t use key words n Boolean – True/False n Mixed case traps n Dateerrors n Variant (Default)

Declaration and Scope n Procedure Level – Dim n n Module Level n n

Declaration and Scope n Procedure Level – Dim n n Module Level n n n Dim v. Name as Text Place below Sub() Value resets to Empty after End Sub Place ahead of all Sub() statements Value from one Sub() available in others All procedures in all Modules – Public n Public v. Profit as Single

Constants n Declarations n n n Const my. Dogs as Integer = 4 Must

Constants n Declarations n n n Const my. Dogs as Integer = 4 Must appear before assignment statements Built-in n Typically named “vb? ? ” n n n vb. Yes vb. Red Look at Help under Constants

Arrays n Array declarations n n n Referencing an array n n n Dim

Arrays n Array declarations n n n Referencing an array n n n Dim My. Array(1 to 100) as Integer Dim My. Big. Array(1 to 5, 1 to 10) as String My. Big. Array(3, 5) = “Bob” My. Array(2) = “Mary” Error! Redim

Assignment Statements n n Left side defines object, property, or variable to be changed

Assignment Statements n n Left side defines object, property, or variable to be changed Right side defines desired result n n n v. Name = Input. Box(“Enter your name”) Change occurs after line is processed Concatenation, Replacement n v. Name = “Name: ” & v. Name

VBA String Functions If v. Text = “Hi, Bob” n Len(v. Text) = 7

VBA String Functions If v. Text = “Hi, Bob” n Len(v. Text) = 7 n Left(v. Text, 5) = “Hi, B” n Right(v. Text, 2) = “ob” n Mid(string, start, [length]) n n Mid(v. Text, 3, 4) = “, Bo” In. Str(start, string 1, string 2) n In. Str(1, v. Text, “, ”) = 3

VBA String Functions Given v. Name = “Williams, Ron” How would you extract “Ron”?

VBA String Functions Given v. Name = “Williams, Ron” How would you extract “Ron”? n Find the position of the comma v. Comm. At = In. Str(1, v. Name, “, ”) n Use the Mid() function starting 2 characters after the comma v. First. Name = Mid(v. Name, v. Comm. At+2) n Functions can be nested v. First. Name = Mid(v. Name, Instr(1, v. Name, “, ”)+2)

VBA Type Conversions n Format(Number, “Format”) n n Val(String) n n Format(123. 4, “$###.

VBA Type Conversions n Format(Number, “Format”) n n Val(String) n n Format(123. 4, “$###. 00”) = “$123. 40” Val(“ 125 lbs”) = 125 Date. Value(String) n Date. Value(“January 30, 2003”) = 37, 624

Methods n Actions to be taken n n n Select Copy Paste Clear Delete

Methods n Actions to be taken n n n Select Copy Paste Clear Delete Connect to object with period n Range(“B 3”). Clear

Try it out!

Try it out!