writing / creating structured programs. vba programming blocks project container for the modules,...

61
CST-273-01 Microsoft VBA R. Juhl Writing / Creating Structured Programs

Post on 19-Dec-2015

220 views

Category:

Documents


0 download

TRANSCRIPT

  • Slide 1
  • Writing / Creating Structured Programs
  • Slide 2
  • VBA Programming Blocks Project Container for the modules, class modules, and forms for a particular file Module, Class Module, and Form Containers for main programming elements Class Descriptions Procedures Function and Sub Function excutes instruction and returns a value to the calling function or sub Sub executes a set of instructions without returning a value Statement An individual line of code
  • Slide 3
  • Writing / Creating Structured Programs Subs Use to execute code Main entry point Perform tasks that do not require a return value Display information Modifying information Subs can pass arguments to functions Modularize Code Functions Used to preform calcualtions and return results Use for repeating code
  • Slide 4
  • Modifying the Project Settings
  • Slide 5
  • Writing / Creating Structured Programs Conditional Compulation May be used for debug Public sub CheckConditional() #If myDebug = 0 Then MsgBox In Standard Mode #Else Msgbox In Debug Mode #End If End Sub
  • Slide 6
  • Writing / Creating Structured Programs Locking Code Prevents modification of code Password Protected Disadvantages Does not prevent viewing code You may forget the password
  • Slide 7
  • Writing / Creating Structured Programs
  • Slide 8
  • Complier Options Option Base {0|1} Sets index point for arrays Option Explicit Forces defining variables before using them Option Compare Change how VBA compares strings Binary Case sensitive Hello != hello Text Not case sensitive: Hello = hello Option private Make a module private so no other module can see what it contains
  • Slide 9
  • Writing / Creating Structured Programs Use Option Explicit to Reduce Errors Use the Lego approach to program Break up code into Modules / Subs / Procedures ~ 50 lines of code per module Create an Application Plan Start with a list of tasks What are the main objectives What tools do you have at your disposal Projects Modules Forms Active X controls Class Modules Sub Procedures and Functions
  • Slide 10
  • Writing / Creating Structured Programs Define the project One Application / Project Needed ? Word Excel Access Multiple Applications Needed Word Project Excel Project Access Project
  • Slide 11
  • Writing / Creating Structured Programs Adding a Module Most beginning programs will have one module Do not try and program many ideas into one module WHY ? Do you want to interact with the user ? Input Process Output Use Class Modules A special reusable module that defines an object class Math Math.PI = 3.14xxxxxxxxx Math.sin() Math.sqr()
  • Slide 12
  • Writing / Creating Structured Programs Design Procedures Sub Procedures Functions Determine what type you will need Writing Statements Begin by writing a pseudo-code procedure Chage pseudo-code to VBA statements Provides Documentation / comments Code Use White Space to make your code readable Indent Use comments to describe how your code works
  • Slide 13
  • Writing / Creating Structured Programs Writing A Sub Getting Author Information from a Document Click the Office Button Choose Prepare Properties Document Properties Advanced Properties Fill In the Author, Title, And Subject Fields Accessing a Document Object (Built In Document Properties) Open the Object Browser and key in the search field builtinDocumentProperties
  • Slide 14
  • Writing / Creating Structured Programs
  • Slide 15
  • Change the ActiveWorkbook object to the object that your application supports Document or Template in Word Word.document Word.template Excel Excel.worksheet
  • Slide 16
  • Writing / Creating Structured Programs Public Sub GetSummary() 'declare a documentProperty object to hold the information Dim MyPropertyName As DocumentProperty Dim MyPropertyTitle As DocumentProperty Dim MyPropertySubject As DocumentProperty 'set the DocumentProperty object equal to the autohr information Set MyPropertyName = ActiveWorkbook.BuiltinDocumentProperties("Author") Set MyPropertyTitle = ActiveWorkbook.BuiltinDocumentProperties("Title") Set MyPropertySubject = ActiveWorkbook.BuiltinDocumentProperties("Subject") 'display a message box containing the property value MsgBox "Name: " & MyPropertyName.Value & " Title: " _ & MyPropertyTitle.Value _ & " Subject: " & MyPropertySubject.Value _, vbOKOnly, "Author Name & Title" End Sub
  • Slide 17
  • Writing / Creating Structured Programs Writing a function (example) Private Function GetDocProperty(Name as String) As String. End Function Private Optional. Indicates that the Function procedure is accessible only to other procedures in the module where it is declared. Function Key word Function Name Make it meaningfull Paramater List (Name as String) declars on paramater Name as a string datatype As String Optional return value datatype
  • Slide 18
  • Writing / Creating Structured Programs Public Sub GetSummary2() 'declare a string to hold the output information.. Dim DocumentData As String 'store the name of the information.. DocumentData = "Author Name: " 'Get the Author Name DocumentData = DocumentData + GetDocProperty("Author") 'add an extra line DocumentData = DocumentData + vbCrLf 'store the name of the information DocumentData = DocumentData + "Company: " 'get the company name.. DocumentData = DocumentData + GetDocProperty("Company") 'display MsgBox containing values. MsgBox DocumentData, vbOKOnly, "Summary" End Sub Private Function GetDocProperty(Name As String) As String declare a document property object to hold the information Dim MyProperty As DocumentProperty set the DocumentProperty object equal to the author info Set MyProperty = ActiveWorkbook.BuiltinDocumentProperties(Name) 'return the information GetDocProperty = MyProperty.Value End Function
  • Slide 19
  • Writing / Creating Structured Programs Scope The range of what a program can see and how much it lets others know MsgBox function Output are public / show Inner workings are invisible Protected Public All modules / Program elements can use Private Only the module or element where declared can use
  • Slide 20
  • Writing / Creating Structured Programs Effects of Scope 'declare a private global variable Private MyGlobalVariable As String Public Sub globalTest() 'set the value of the global variable MyGlobalVariable = "hello" 'display value MsgBox MyGlobalVariable 'call the GlobalTest2 sub GlobalTest2 'display the value on return from the call MsgBox MyGlobalVariable End Sub Private Sub GlobalTest2() 'show that the global variable is truly global MsgBox MyGlobalVariable 'change the value of the global variable MyGlobalVariable = "good bye" End Sub
  • Slide 21
  • Writing / Creating Structured Programs Create Readable Code Comments must be used in at least 3 areas. The header section Name / Date / Class / Assignment Before any procedure / function that explains what the procedure / function purpose is. Line comments that identify what code or variables do. Indentation / white space Use of white space to make your program readable Example code uses a comment / statement pair followed by a blank line Indent code inside the module declaration Indent within data structures with beginning and ending construction code i.e. If Then Else or Case
  • Slide 22
  • Writing / Creating Structured Programs Create a function that receives two numbers x, y and returns x^y power (x raised to the y) result How many parameters will the function have?
  • Slide 23
  • Writing / Creating Structured Programs Create a function that receives two numbers x, y and returns x^y power (x raised to the y) result How many parameters will the function have? Two x double y single
  • Slide 24
  • Writing / Creating Structured Programs Create a function that receives two numbers x, y and returns x^y power (x raised to the y) result How many parameters will the function have? Two x double y single What will the function return ?
  • Slide 25
  • Writing / Creating Structured Programs Create a function that receives two numbers x, y and returns x^y power (x raised to the y) result How many parameters will the function have? Two x double y single What will the function return ? A number that represents x^y double
  • Slide 26
  • Writing / Creating Structured Programs Create a function that receives two numbers x, y and returns x^y power (x raised to the y) result How many parameters will the function have? Two x double y single What will the function return ? A number that represents x^y Double Coding the function.. How do you raise a number to a power i.e. what is the operator ?
  • Slide 27
  • Writing / Creating Structured Programs Create a function that receives two numbers x, y and returns x^y power (x raised to the y) result How many parameters will the function have? Two x double y single What will the function return ? A number that represents x^y Double Coding the function.. How do you raise a number to a power i.e. what is the operator ? ^ x = x ^ y
  • Slide 28
  • Writing / Creating Structured Programs What is the function statement that returns the calculated value? functionName = x Write the main procedure that prompts the user to input two numbers Number Exponent Convert the numbers from text to numeric Assign the results of a function call to a variable Output the results
  • Slide 29
  • Writing / Creating Structured Programs Excel has a built in function called Power(x, y) Result = Application.WorksheetFunction.Power(number, n) Logical Operators in VBA = < > =
  • Slide 30
  • Writing / Creating Structured Programs And, Or and Not operators And A B A*B ------------------------ F F F T F F F T F T T T
  • Slide 31
  • Writing / Creating Structured Programs OR A B A + B ------------------------ F F F T F T F T T T T T
  • Slide 32
  • Writing / Creating Structured Programs Not InitialNot condition T F F T
  • Slide 33
  • Writing / Creating Structured Programs If Then Else Single Statment If (condition) Then Code Statement Block statement If (condition) Then Block of code / multiple statements End If
  • Slide 34 answer) Then MsgBox "To High!" MsgBox "The number is " & answer End If If (userGuess < answer) Then MsgBox "To Low!" MsgBox "The number is " & answer End If If (userGuess = answer) Then MsgBox "You Got It!" End Sub">
  • Writing / Creating Structured Programs Guessing Game Private Sub NumberGuess() Dim userGuess As Integer Dim answer As Integer answer = Rnd * 10 'produces a number between 0 and 10 'answer = Int((10 - 1 + 1) * Rnd + 1) userGuess = Val(InputBox("Guess a number between 1 and 10.", "Number Guess Game")) If (userGuess > answer) Then MsgBox "To High!" MsgBox "The number is " & answer End If If (userGuess < answer) Then MsgBox "To Low!" MsgBox "The number is " & answer End If If (userGuess = answer) Then MsgBox "You Got It!" End Sub
  • Slide 35
  • Writing / Creating Structured Programs If (theDayOfTheWeek = 0) Then MsgBox (Its Sunday) IF (theDayOfTheWeek = 1) Then MsgBox (Its Monday) IF (theDayOfTheWeek = 2) Then MsgBox (Its Tuesday) IF (theDayOfTheWeek = 3) Then MsgBox (Its Wednesday) IF (theDayOfTheWeek = 4) Then MsgBox (Its Thursday) IF (theDayOfTheWeek = 5) Then MsgBox (Its Friday) IF (theDayOfTheWeek = 6) Then MsgBox (Its Saturday)
  • Slide 36
  • Writing / Creating Structured Programs Else Clause If (theDayOfTheWeek = 0) Then MsgBox (Its Sunday) ElseIF (theDayOfTheWeek = 1) Then MsgBox (Its Monday) ElseIF (theDayOfTheWeek = 2) Then MsgBox (Its Tuesday) ElseIF (theDayOfTheWeek = 3) Then MsgBox (Its Wednesday) ElseIF (theDayOfTheWeek = 4) Then MsgBox (Its Thursday) ElseIF (theDayOfTheWeek = 5) Then MsgBox (Its Friday) Else MsgBox (Its Saturday) End If
  • Slide 37
  • Writing / Creating Structured Programs Select Case Select Case expression Case condition 1 statement or block of statements Case condition 2 statement or block of statements. Case Else statement or block of statements if no other condition is true End Select
  • Slide 38
  • Writing / Creating Structured Programs Public Function AssignGrade(grade As Single) As String Select Case grade case 90 to 100 assignGrade = A case Is >= 80 assignGrade = B case 70 to 80 assignGrade = C case Is >= 60 assignGrade = D Case Else assignGrade = F End Select
  • Slide 39
  • Writing / Creating Structured Programs Image Control Used to display image files (bmp, jpg, or gifs) Can be added to a worksheet via the control toolbox like any ActiveX control Can be loaded at Design time Run time Via the picture property
  • Slide 40
  • Writing / Creating Structured Programs Find via the Internet six images of Dice (1 6) and save them to a folder for future use.
  • Slide 41
  • Writing / Creating Structured Programs Do Loops Do code executes at least once and continues to loop while condition is False Loop Until (condition) is true ------------------------------------------------------------------------------------------------ Do Until (condition) is true code executes and continues to loop while condition is False Loop ------------------------------------------------------------------------------------------------ Do code executes at least once and continues to loop while condition is True Loop While (Condition) is true ------------------------------------------------------------------------------------------------ Do While (condition) is true code executes and continues to loop while condition is True Loop
  • Slide 42
  • Writing / Creating Structured Programs For Loops For variable = start to end Step value block of code Next variable For i = 1 to 10 Step 1 MsgBox (i) Next i Produces: 1 2 3 4 5 6 7 8 9 10 in 10 MsgBoxs
  • Slide 43
  • Writing / Creating Structured Programs For Loops For i = 1 to 10 Step 2 MsgBox (i) Next i Produces 1, 3, 5, 7, 9
  • Slide 44
  • Writing / Creating Structured Programs Write a program that asks a user for an integer number between 1 and 10,000 and outputs weather its a prime number or not. Prime number only divide by one and themselves Indicate to the user if the number is out of range i.e. 10,000 Use the Val() function to convert the number from text to numeric. Pay attention to doing as little processing as possible Make you code efficient Why ? How Look at all the tests you can do on a number an eliminate any unnecessary tests or numbers Write out the steps of this program in Pseudo Code and e-mail to me prior to the actual coding EXTRA CREDIT Remove the upper limit of 10,000 on the user entering a number but, still, do not allow a negative number
  • Slide 45
  • Writing / Creating Structured Programs
  • Slide 46
  • Slide 47
  • Slide 48
  • Slide 49
  • Slide 50
  • Slide 51
  • Slide 52
  • Slide 53
  • Slide 54
  • Slide 55
  • Slide 56
  • Slide 57
  • Slide 58
  • Slide 59
  • Slide 60
  • Slide 61