CST-273-01 Microsoft VBA R. Juhl

1. CST-273-01Microsoft VBAR. Juhl Writing / Creating Structured Programs

2. Writing / Creating Structured Programs • 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

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

4. Modifying the Project Settings

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

6. Writing / Creating Structured Programs • Locking Code • Prevents modification of code • Password Protected • Disadvantages • Does not prevent viewing code • You may forget the password

7. Writing / Creating Structured Programs

8. Writing / Creating Structured Programs • Complier Options • Option Base {0|1} • Sets index point for arrays • Option Explicit • Forces defining variables before using them • Option Compare <Method> • 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

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

10. Writing / Creating Structured Programs • Define the project • One Application / Project Needed ? • Word • Excel • Access • Multiple Applications Needed • Word • Project • Excel • Project • Access • Project

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()

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

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

14. Writing / Creating Structured Programs

15. Writing / Creating Structured Programs • Change the ActiveWorkbook object to the object that your application supports • Document or Template in Word • Word.document • Word.template • Excel • Excel.worksheet

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

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

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

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

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

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

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?

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

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 ?

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

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 ?

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

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

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 • = • <> • < • > • <= • >=

30. Writing / Creating Structured Programs • And, Or and Not operators • And A B A*B ------------------------ F FF T F F F T F T TT

31. Writing / Creating Structured Programs • OR A B A + B ------------------------ F FF T F T F TT T TT

32. Writing / Creating Structured Programs • Not Initial Not condition T F F T

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

34. 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

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”)

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

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

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

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

40. Writing / Creating Structured Programs • Find via the Internet six images of Dice (1 – 6) and save them to a folder for future use.

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

42. Writing / Creating Structured Programs • For Loops For variable = start to end Step value ‘block of code Next variable For i= 1to 10Step 1 MsgBox (i) Next i ‘Produces: 1 2 3 4 5 6 7 8 9 10 in 10 MsgBox’s

43. Writing / Creating Structured Programs • For Loops For i = 1 to 10 Step 2 MsgBox (i) Next i Produces 1, 3, 5, 7, 9

44. Writing / Creating Structured Programs • Write a program that asks a user for an integer number between 1 and 10,000 and outputs weather it’s 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 or > 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

45. Writing / Creating Structured Programs

46. Writing / Creating Structured Programs

47. Writing / Creating Structured Programs

48. Writing / Creating Structured Programs

49. Writing / Creating Structured Programs

50. Writing / Creating Structured Programs