1 / 53

Visual Basic for Applications

Visual Basic for Applications. Programming language Very similar to Visual Basic Embedded in Microsoft Office applications “Object oriented” Automate Office operations Build macros and functions We will only learn enough VBA to write simple functions. Program. Program = code

tiva
Télécharger la présentation

Visual Basic for Applications

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. Visual Basic for Applications • Programming language • Very similar to Visual Basic • Embedded in Microsoft Office applications • “Object oriented” • Automate Office operations • Build macros and functions • We will only learn enough VBA to write simple functions

  2. Program • Program = code • call = run = execute = launch = start • List of instructions • Input • Got at program’s start • Asked to the user • Got from Excel table • Does some predetermined operations • Output • Written in Debug window or Message Box • Written in Excel table • “returned” like Excel functions

  3. Variables • Memory places to store values • Types • Integer numbers: Integer and Long • Real number: Single and Double • Dates: Date • True/False: Boolean • Sentences: String

  4. Start sub example1 End Get value from userand store in x (Single) Calculate x+yand store in z (Single) Get value from user and store in y (Single) Print value of z Example • Ask user values of x and y and print x+y • Variables: x Single, y Single, z Single

  5. End Start sub example2 Get value from userand store in a (Integer) Get value from userand store in x (Single) Calculate z*aand store in result (Single) Calculate x+yand store in z (Single) Print value of result Get value from user and store in y (Single) Example • Ask user values of x and y, calculate x+y, ask user for integer value a and print (x+y)*a • Variables: x Single, y Single, z Single, a Integer, result Single

  6. End Start subroutine MySum parameters: z (Single) Get value from userand store in x (Single) Calculate x+yand store in z Get value from user and store in y (Single) Reusing programs • Why should we rewrite a program that we have already written? • We can give the subroutine a name, MySum, to be able to later reuse it. • This reusable subroutine may have parameters, used to “communicate” with the other programs

  7. End End Start subroutine MySum parameters: z (Single) Start sub example2bis Get value from userand store in a (Integer) Get value from userand store in x (Single) Calculate z*aand store in result (Single) Calculate x+yand store in z z(Single) Print value of result Get value from user and store in y (Single) Reusing programs • We “call” the subroutine MySum inside the main program and use its parameter Call MySum(z)

  8. End End Start subroutine GetXY parameters: x (Single), y (Single) Start sub example2ter Get value from userand store in a (Integer) Get value from userand store in x Calculate z*aand store in result (Single) Calculate x+yand store in z (Single) x(Single), y(Single) Print value of result Get value from user and store in y Reusing programs • We could use the subroutine also to get the values only and leave the calculation to the main program Call GetXY(x,y)

  9. Homeworks • Write a program which asks for a an amount of money, an interest rate and a number of years and prints the compound interest • Rewrite the previous program using a separate subroutine GetData to get the data. • Rewrite the previous program using also a separate subroutine PrintInterest to print the compound interest. • Rewrite the previous program using a sub called main which does nothing else but calling subroutines GetData, CalculateInterest, PrintInterest, which do all the job. • Write a program which asks for a an amount of money, an interest rate and two dates StartDate and EndDate. Using the expressionNumberOfYears = DateDiff( “yyyy”, StartDate, EndDate ) to get the difference in years, re-use the previous subroutines CalculateInterest and PrintInterest to print the compound interest.

  10. IF-THEN-ELSE condition • We want to branch the flow according to the value of a variable If (condition) Then(operations if true) Else(operations if false) End If

  11. false true Start with x (Single), y (Single) End x < y Calculate y-x and store in diff (Single) Put “sorry” in sentence (String) Print value of diff Print value of sentence Example • Start with already values for x (Single) and y (Single). If x<y, print y-x, else print sentence “sorry” • Variables: x Single, y Single, diff Single, sentence String

  12. Start End false true x > 0 Get value from userand store in x (Integer) Get value from userand store in y (Single) Put “positive” in sentence (String) Print value of y Print value of sentence Example • Ask integer value. If it is positive, print “positive”, if it is not positive, ask for a real value and print it • Variables: x Integer, y Single, sentence String

  13. Start End true false x > 0 Get value from userand store in x (Integer) Get value from userand store in y (Single) false true Put “positive” in sentence (String) x < 0 Print value of sentence Print value of y Example • Build sub example5 which asks for an integer value. If it is positive, it prints “positive”. If it is negative, it asks for a real value and prints it. If it is zero it does nothing. • Variables: x Integer, y Single, sentence String

  14. Class exercise • Suppose there are two taxation systems, A and B. • Build sub CalculateTax with flowchart which asks for the chosen taxation system and for the income and then prints the tax according to the rule:

  15. End Start sub CalculateTax false true system = “A” Get value from userand store in income (Long) Get value from userand store in system (String) tax (Double) tax=3000 tax=income*0.2 tax=9000 tax=income*0.15 true true false false income< 50000 income< 20000 Print value of tax Solution of class exercise Use operator & to concatenate strings: ”result is ” & tax

  16. Improvement • In order to help subroutine CalculateTax, use a subroutine DoCalc which needs • income, • income level, • fixed tax, • tax percentage and calculates the tax.

  17. Start subroutine DoCalc parameters: income (Long), incomeLevel (Long), fixedTax (Long), taxPercentage (Single), tax (Double) End End Start sub CalculateTax false true system = “A” Get value from userand store in income (Long) Get value from userand store in system (String) tax (Double) true income < incomeLevel false Print value of tax Solution of improvement tax=fixedTax tax=income*taxPercentage Call DoCalc(income, 50000,9000,0.2,tax) Call DoCalc(income, 20000,3000,0.15,tax)

  18. Function • Whenever a subroutine • does not interact with the user • gets its input from the parameters • returns only one variable as output it is much more efficient to use a function instead of a subroutine Function calcTax2( … parameters … )as Single … do the calculations here and put the result in a variable with the same name as the function … End Function

  19. Start function calcTax (Double) parameters: income (Long), incomeLevel (Long), fixedTax (Long), taxPercentage (Single) End returning calcTax End Start sub CalculateTax false true system = “A” Get value from userand store in income (Long) Get value from userand store in system (String) tax (Double) true income < incomeLevel false Print value of tax Function calcTax=fixedTax calcTax=income*taxPercentage tax=calcTax(income, 50000,9000,0.2) tax=calcTax(income, 20000,3000,0.15)

  20. End Start sub CalculateTax false true system = “A” Get value from userand store in income (Long) Get value from userand store in system (String) tax (Double) Print value of calcTax(income, 20000,3000,0.15) Print value of calcTax(income, 50000,9000,0.2) Usage of functions • Functions are much more versatile and can be used directly in calculations and other functions or commands

  21. Homeworks • Write a program with flowchart which asks for a an amount of money, an interest rate and two dates StartDate and EndDate. Using a function to calculate the compound interest and a subroutine to get the data, print the compound interest. • Write function TaxDeduction (flowchart is too long) which gets as input children, income and age and returns the Italian tax deduction according to:

  22. Start End Set n (Integer) to 1 Calculate n*n and store value in y (Long) Increment n by 1 Calculate n*n and store value in y Print value of y Print value of y Example • Write the squares of the numbers from 1 to 20 Increment n by 1

  23. FOR-NEXT loop • We want to repeat the same procedure in a similar way several times FOR: SET UP A COUNTER (operations) NEXT: INCREMENT AND GO BACK To go out of the loop before its end: EXIT FOR

  24. Start End Calculate n*n and store value in y (Long) Print value of y Set n (Integer) to 1 and loop Increment n by 1 and loop until n does not exceed 20 Example6 • Write the squares of the numbers from 1 to 20 • Variables: n Integer, y Long

  25. Start End Calculate sqr(n) and store value in y (Single) Print value of y Set n (Integer) to 3126 and loop Increment n by -31 and loop until n does not go below 1087 Example7 • Write the square root of the numbers from 3126 to 1087 going backwards with steps of 31 • Variables: n Integer, y Long

  26. Start End Get value from userand store in goon goon (String) Calculate sqr(n) and store value in y (Single) Print value of y Set n (Integer) to 3126 and loop Increment n by -31 and loop until n does not go below 1087 Example7bis • Write the square root of the numbers from 3126 to 1087 going backwards with steps of 31, asking the user at each step whether he wants the program to end. true false goon =“yes” Exit For

  27. Homework • Write function isEven with flowchart, which wants an integer as input and returns a Boolean (a variable with value True or False, without quotations!). To detect whether a number is even, use as condition Int(number/2)=number/2 . • Write a program with flowchart which asks the user for a positive integer and computes the following sequence for 20 times: if the number is even, halve it; if it's odd, multiply by 3 and add 1. Repeat this process until 20 times have been reached or until the value is 1, printing out each value. Finally print out how many of these operations have been performed. • Typical output might be: initial value is 9; next values are: 28, 14, 7, 22, 11, 34, 17, 52, 26, 13, 40, 20, 10, 5, 16, 8, 4, 2, 1; number of steps 19

  28. Using functions • A functionmaybeused: • by a subroutine (or by another function), via direct usage such as variable=functionName(parameters), where the subroutine is responsible for providing the parameters • inside an Excel sheet, exactly like any other Excel function, where parameters are written directly or indicated as cell reference, such as =functionName(D7;E5;27;0;A1) • Not run directly via Run button, since there is no way to pass parameters and no way to gather the function’s returned output

  29. Reading data from Excel sheet • To access Excel sheet we use • Range(“E2:H9”).Cells(3,2) which corresponds to cell F4, since (3,2) are the row and column of the range! Therefore E2 is (1,1), F2 is (1,2), F3 is (2,2) and F4 is (3,2). • Range(“E2:H9”).Count which tells us the number of cells in the range (in this case 32)

  30. End Start Example • Write program Multiplic which reads number in cell D5, multiplies it by the number in cell D6 and prints the result. • Compact it using a single instruction. a (Single) a= Range(“D5”).Cells(1,1) b (Single) b= Range(“D6”).Cells(1,1) Print value of a*b a = Range(“D1:D20”).Cells(5,1) b = Range(“D5:D6”).Cells(2,1)

  31. End Start Example • Write program FixCell which asks the user for a cell reference and a real number and puts that number in that cell. get a string from user and store it in cellReference (String) get a single from user and store it in value (Single) Range(cellReference).Cells(1,1)=value

  32. Array • We want to store many values of the same type ARRAY • It always needs to be prepared • It is accessed directly indicating the index

  33. Start End Print value in n-th position of x Get value from user and store in j-th position of x Prepare an array x containing 20 single Get value from user andstore in n (Integer) Set j (Integer) to 1 and loop Increment j by 1 and loop until j exceeds 20 Example11 • Ask the user 20 real numbers and store them into an array. Ask the user which value he wants to be printed and print it. • Variables: x Array of Single, j Integer, n Integer

  34. Start End Print value of sum Get value from user and store in j-th position of x Prepare an array x containing 20 single sum (Single) sum = 0 sum = sum + x (at j-th position) Set j (Integer) to 1 and loop j=1 and loop Increment j by 1 and loop until j exceeds 20 Increment j by 1 and loop until j exceeds 20 Example11bis • Ask the user 20 real values. When you have all of them, calculate their sum. Then print it. • Variables: x Array of Single, j Integer, sum Single

  35. Example11ter • Improve the previous program using only one loop and telling each time which number the user is entering (4th, 5th, …) • Improve it further more using 1st, 2nd, 3rd instead of 1th, 2th, 3th and do not use an array!

  36. End Start x array of Single 1 To 11 Increment j by 1 and loop until j exceeds 11 Set j (Integer) to 1 and loop Example15 • Read numbers in range D3:D13 and put them in array x with indexes 1 to 11. Then print the fifth element of the array x(j)= Range(“D3:D13).Cells(j,1) Print value of x(5) For j=3 To 13 x(j–2)=Range(“D1:D13”).Cells(j,1) For j=1 To 11 x(j)=Range(“D1:D13”).Cells(j+2,1)

  37. true false Start End x(j) > bound Print value of count Get value from user and store in bound (Single) x(j)=Range(“D1:D20”).Cells(j,1) count (Integer) count= 0 Prepare an array x containing 20 single count = count+1 Set j (Integer) to 1 and loop j=1 and loop Increment j by 1 and loop until j exceeds 20 Increment j by 1 and loop until j exceeds 20 Example12 • Get 20 values from D1:D20 and put them into an array. When you have all of them, ask the user for a bound. Then print how many of the 20 values exceed that bound.

  38. LBound and UBound • Whenever we are not sure of from where to where the array index goes, we can use functions LBound(x) and UBound(x) which automatically take the value of the first and the last index of the array x • Rewrite the previous program using LBound(x) and UBound(x) instead of 1 and 20

  39. Example12ter • Build a function countLargerThanLimit which accepts as input an array of singles and a limit and returns how many elements of the array are larger than the limit • In this function you must use LBound and UBound because the function cannot know a priori the array bounds

  40. Example13 • Ask the user 20 real numbers. When you have all of them, calculate the average of the positive ones. Then print it. • Hints • First build a program which simply calculates the average • Use count (Integer) variable to count how many are positive • Start setting count=0 (exactly as sum is set equal to 0) • Use IF-THEN-ELSE condition to check whether each number is positive (and therefore should be summed and counted)

  41. true false x(j)>0 Start End count = count + 1 Print value of average Get value from user and store in j-th position of x Prepare an array x containing 20 single sum (Single) sum = 0 average (Single) average = sum / count sum = sum + x(j) count (Integer) count = 0 Set j (Integer) to 1 and loop j=1 and loop Increment j by 1 and loop until j exceeds 20 Increment j by 1 and loop until j exceeds 20 Solution

  42. Problem of the solution • What happens when all 20 values are negative? • The computer crashes since a division by zero was found! • We need to check that count be positive before dividing!

  43. true false x(j)>0 Start End true false count>0 count = count + 1 Get value from user and store in j-th position of x Print value of sentence Print value of average sentence (String) sentence = “sorry no positive” sum = sum + x(j) average (Single) average = sum / count sum (Single) sum = 0 Prepare an array x containing 20 single count (Integer) count = 0 j=1 and loop Set j (Integer) to 1 and loop Increment j by 1 and loop until j exceeds 20 Increment j by 1 and loop until j exceeds 20 Better solution

  44. Homework (example 14) • Build a function which • receives d (array of 20 Date), m (Single) and r (array of 20 Single). • For each d and r, • if d is in the past and r is positive, calculate the interest from d to now, using m as money amount and the corresponding r as interest rate. • Otherwise, do not consider this data. • Return the average of the calculated interests. • Hints • You can use And operator (like in Access) • To get current date, there is Now() function • Write also a program which reads the data from columns G, H, I of Excel sheet and tests the function.

  45. Start with d (array of 20 Date), m (Single), r (array of 20 Single) End returning average d(j)<Now() And r(j)>0 true false count = count +1 sum (Single) sum = 0 count (Int) count = 0 average (Single) average = sum / count interest (Single) interest = m * (1+r(j))(Now –d(j))/365– m sum = sum + interest Set j (Integer) to 1 and loop Increment j by 1 and loop until j exceeds 20 Solution • Starting variables: • d (array of 20 Date) • m (Single) • r (array of 20 Single) • Used variables: • interest (Single) • count (Integer) • sum (Single) • average (Single)

  46. Homework • Build function MaxOfArray which receives an array of singles and returns the maximum • Hint: try to think at how would you do this task manually step by step and then replicate it on a program • Build a subroutine to test the function, using the data in column D

  47. x(j)> maxCandidate true Start function MaxOfArray Parameters: x array of Single false End returning maxCandidate maxCandidate (Single) maxCandidate=first element of array maxCandidate = x(j) Set j (Integer) to second index of array and loop Increment j by 1 and loop until j exceeds the last index of the array Solution

  48. WHILE-WEND loop • We want to repeat the same procedure in the same way until something happens WHILE: (condition) (operations) WEND: GO BACK

  49. Start End While y >=0 Print value of squareroot Get value from user and store in y (Single) Get value from user and store in y Print value of sentence Calculate sqr(y) and store in squareroot (Single) sentence (String) sentence = “bye” go back Example16 • Ask the user a real value. If it is negative, print “bye” and stop. If it is positive or zero, tell the user its square root and ask another. Go on over and over. • Variables: y Single, squareroot Single, sentence String

  50. Start End true false y >= 0 Print value of squareroot Get value from user and store in y (Single) Get value from user and store in y Print value of sentence Calculate sqr(y) and store in squareroot (Single) sentence (String) sentence = “bye” Equivalent example • Do not use WHILE-WEND loop but use IF instead. • You need to “jump” to another place in the program and it is not possible (for the moment) • Variables: y Single, squareroot Single, sentence String

More Related