- By
**tiva** - Follow User

- 147 Views
- Uploaded on

Download Presentation
## 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

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

Variables

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

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

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

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

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)

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)

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.

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

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

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

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

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:

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 exerciseUse operator &

to concatenate

strings:

”result is ” & tax

Improvement

- In order to help subroutine CalculateTax, use a subroutine DoCalc which needs
- income,
- income level,
- fixed tax,
- tax percentage

and calculates the tax.

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 improvementtax=fixedTax

tax=income*taxPercentage

Call

DoCalc(income,

50000,9000,0.2,tax)

Call

DoCalc(income,

20000,3000,0.15,tax)

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

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

FunctioncalcTax=fixedTax

calcTax=income*taxPercentage

tax=calcTax(income, 50000,9000,0.2)

tax=calcTax(income, 20000,3000,0.15)

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

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:

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

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

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

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

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)

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)

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

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

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

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!

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)

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.

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

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

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)

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

SolutionProblem 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!

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 solutionHomework (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.

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)

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

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

SolutionWHILE-WEND loop

- We want to repeat the same procedure in the same way until something happens

WHILE: (condition)

(operations)

WEND: GO BACK

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

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

Homework (example17)

- Build a function which receives x (array of Single). It goes through each value of x calculating the sum of the values, stopping when a zero value is found. Then it returns the sum.
- Hints
- The loop can end for two reasons: a zero value is found or all 30 values have been examined
- You can use the FOR-NEXT loop, but in this case you must add a condition which “jumps” to the end of the program when a zero is found
- You can instead use the WHILE-WEND loop, but in this case you must add a manual counter to go through x which takes care of ending when it reaches 30
- Build a sub which takes data from column D to test the function

Start with x (Array of 30 Single)

End returning sum

Start with x (Array of 30 Single)

Do only if

x(j)<>0 and j<=last index

sum = sum + x(j)

true

sum = sum + x(j)

false

sum (Single)

sum = 0

j (Integer)

j = first index

sum (Single)

sum = 0

x(j) <> 0

j = j + 1

Set j (Integer) to first index

and loop

Increment j by 1 and loop until j exceeds last index

go back

SolutionsExtremely difficult homework

- Build a program which reads an array of singles (for example D1:D22) and reprints them sorted from smallest to largest
- Hints
- First think at how you would do this task step by step manually
- Then rethink carefully at each step of the manual procedure
- Then, according to the algorithm you have invented, it might be useful either to build a separate array to put the data or to build a subroutine which accepts an array and two indexes and switches the position of the elements in the array

Download Presentation

Connecting to Server..