1 / 22

Programming with VBA

Programming with VBA. Planning for Programming Variables and Constants Assignment Statements Functions Programming. Planning for Programming. Observe the process to be automated. Price = Cost + OH + Profit. Observe the Process.

aladdin-delaney
Download Presentation

Programming with VBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Programming with VBA • Planning for Programming • Variables and Constants • Assignment Statements • Functions • Programming

  2. Planning for Programming • Observe the process to be automated

  3. Price = Cost + OH + Profit 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) Identify: • Variables - entered by user • Material cost • Labor cost • Constants -set by programmer • Overhead percentage • Profit percentage

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

  5. Planning for Programming • Observe the process to be automated • Flowchart

  6. Start or Stop DataInput/Output Process steps PredefinedProcess Decisions Flow Charts • Schematic Drawing of Process • Use Standard Symbols • Flow from Top to Bottom • Connect with Arrows

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

  8. Get: Labor costMaterial cost Determine:Total costBid Price Show Bid Price Quit Cost Model • Start • Get user inputs • Material • Labor • Determine cost • Determine price • Output results • Quit Start

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

  10. Ask user for labor cost [vLabor] and material cost [vMaterial] Get: Labor costMaterial cost [vCost] = [vLabor] + [vMaterial] [vPrice] = [vCost]*(1+[cOH]*[cProfit]) Determine:Total costBid Price Show Bid Price Display message with [vPrice] Quit Pseudo Code Write steps, formulas needed Start Pseudo Code often ends up as comments in the VBA code

  11. Write VBA Code

  12. Variables and Constants • Storage “bins” in memory • Identified by a unique name • Variables can be changed by the user • vName = InputBox(“Enter your name”) • Constants can only be changed by editing the VBA code • Const cMarkUp = 0.25

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

  14. Declaration and Scope • Procedure Level – Dim • Dim vName asText • Place below Sub() • Value resets to Empty after End Sub • Module Level • Place ahead of all Sub() statements • Value from one Sub() available in others • All procedures in all Modules – Public • Public vProfit as Single

  15. Constants • Declarations • Const myDogs as Integer = 4 • Must appear before assignment statements • Built-in • Typically named “vb????” • vbYes • vbRed • Look at Help under Constants

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

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

  18. VBA String Functions If vText = “Hi, Bob” • Len(vText) = 7 • Left(vText, 5) = “Hi, B” • Right(vText, 2) = “ob” • Mid(string, start, [length]) • Mid(vText, 3, 4) = “, Bo” • InStr(start, string1, string2) • InStr(1, vText, “,”) = 3

  19. VBA String Functions Given vName = “Williams, Ron”How would you extract “Ron”? • Find the position of the comma vCommAt = InStr(1, vName, “,”) • Use the Mid() function starting 2 characters after the comma vFirstName = Mid(vName, vCommAt+2) • Functions can be nested vFirstName = Mid(vName, Instr(1, vName, “,”)+2)

  20. VBA Type Conversions • Format(Number, “Format”) • Format(123.4, “$###.00”) = “$123.40” • Val(String) • Val(“125 lbs”) = 125 • DateValue(String) • DateValue(“January 30, 2003”) = 37,624

  21. Methods • Actions to be taken • Select • Copy • Paste • Clear • Delete • Connect to object with period • Range(“B3”).Clear

  22. Try it out!

More Related