Transcript VBA 1-2

Concept of variables, If -Then , If - Then – Else, Go To, Do - Loop;
Lab2 (VBA1):
Sub Lab2_Pr1()
Dim k As Integer, n%, x As Double, y#
Dim a As String, b$
k = Cells(2, 2): n = Cells(2, 3)
Cells(4, 2) = k: Cells(4, 3) = n: Cells(4, 4) = k + n
x = Cells(2, 2): y = Cells(2, 3)
Cells(5, 2) = x: Cells(5, 3) = y: Cells(5, 4) = x + y
a = Cells(2, 2): b = Cells(2, 3)
Cells(6, 2) = a: Cells(6, 3) = b: Cells(6, 4) = a + b
End Sub
1. Write on an Excel worksheet the values and
strings below, then insert a modul-sheet, type in
the given program (Lab2_Pr1), save the file, and
run the program.
2. Write a VBA program to the given
3. Write a VBA program using the given
flowsheet, then run the program using flowsheet to approximate by halving
Debug  Step Into F8
method the root of the equation x^4 –
81 = 0. Give the starting value of the
variables a and b by InputBox. The
Start
table written by the program should be
the following:
write: „x” „x cube”
x=2
write: x,
x=x+1
yes
x<=5
?
x3
a
1
1
2,75
2,75
2,75
2,96875
2,96875
2,96875
2,996094
2,996094
2,996094
b
8
4,5
4,5
3,625
3,1875
3,1875
3,078125
3,023438
3,023438
3,009766
3,00293
m
4,5
2,75
3,625
3,1875
2,96875
3,078125
3,023438
2,996094
3,009766
3,00293
2,999512
f(m)
329,06
-23,81
91,68
22,23
-3,32
8,77
2,56
-0,42
1,06
0,32
-0,05
Start
give: a, b
fa=a^4-81: fb=b^4-81
m=(a+b)/2 : fm=m^4-81
write: a, b, m, fm
no
yes
fa*fm<0
?
a=m : fa=fm
b=m : fb=fm
no
Stop
Write the 3. program using Do-Loop
statement, too!
yes
Abs(fm)>0,1
?
no
Stop
Lab3 (VBA2):
For - Next (Sequences) ; Function (Area approximation)
1. Write a program where the input is an m0 integer, and the program
calculates the reciprocals of the first (m+1) odd numbers, then evaluates
the following sum:
m
 1
1 1 1
S  1     ..... 
3 5 7
2m  1
Use the For k=0 To m … Next k statement. The output of
the program is shown in the table (right, m=10).
2. Write a program for the approximation of the area between the x
axis and the graph of the function f(x) on the intervall [a;b], as it is
shown on the figure.
To evaluate the area divide the intervall [a;b] into n equidistant parts,
and approximate the area under the curve by the sum of the
trapezoids’ area, where the height of the trapezoids is h=(b-a)/n.
(On the figure the number of trapezoids is n=3.)
The elements of the sequence are now the trapezoids:
t1  h 
y
f(x)
x
a
a+h
f a   f a  h 
f a  h   f a  2h 
f a  (n  1) * h   f b 
; t2  h 
; ... ; t n  h 
2
2
2
For the definition of the function f(x) use in the
program a VBA Function as it is shown in the
example below:
For the input of the boundaries of the intervall
Function f(x#) As Double
f = 4 / (1 + x ^ 2)
End Function
(a, b) and of the number of trapezoids (n) use
Inputbox .
The outputs of the program (if a=0, b=1, n=10)
are shown in the table (right).
a+2h
b