This web page contains material for the computing an data ...



Visual Basic – Exercises

Introduction

We will begin our writing of procedures in VB by some simple exercises on user-defined functions. These cannot easily be produced by using the macro recorder, so are required to be written by directly typing the code into a module.

As an example of a simple user-defined function, consider the scalar (dot) product of two three-dimensional vectors, a = [pic] and b = [pic] in Cartesian coordinates. Mathematically this is given by

[pic]

The function procedure ScalarProduct given below carries out this calculation. The two vectors are given as the arguments aVector and bVector.

Option Explicit

Option Base 1

'

Function ScalarProduct(aVector, bVector) As Single

' calculates scalar product of two vectors A and B

Dim product As Single, i As Integer

product = 0

For i = 1 To 3

product = product + aVector(i) * bVector(i)

Next

ScalarProduct = product

End Function

Note that the procedure statement

product = product + aVector(i) * bVector(i)

would be equivalent to

[pic]

with [pic], in mathematics and would be nonsense. However the computing expression is NOT a mathematical statement, but is an assignment statement. Thus in the code statements such as xVal = xVal + aVal means, starting on the RHD, take the value in the memory location named xVal and add to it the value in the location named aVal, then put the result back into the location named xVal. Hence if this statement is executed repeated (in a loop) it accumulates into xVal successive values of aVal, i.e. it sums.

Exercise 1

You will create a suite of procedures based on the example above.

The length of a vector a is given by

[pic]

The angle, (, between the vectors a and b can found from

[pic]

The vector product of two vectors a and b is a vector c with components given by

[pic]

The volume, V, of the parallelepiped with sides formed from the three vectors a, b, and c is given by

[pic]

If the three vectors are coplanar, V = 0.

• Open a new (blank) workbook and start the VB Editor (if not already running) using Alt+F11. Type or copy and paste the Function procedure ScalarProduct into the module sheet of the VB Editor. You can use the function ScalarProduct in your spreadsheet. Enter some numbers into cells A1:A3 and B1:B3. In cell A5 enter =ScalarProduct(A1:A3,B1:B3). If done correctly the spreadsheet should compute the value of the scalar product of the two vectors defined by components in cells A1:A3 and B1:B3. Check the result on a pocket calculator. What happens if you enter =ScalarProduct(A1:C1,A2:C2)in cell B5?

• Write a Sub procedure VectorProduct which takes three arguments which are arrays for the vectors a, b (as input) and c (as output). (Notice that it cannot be a Function procedure as it computes three quantities and so does not return a single value).

• Write a Function procedure Volume with three arguments that computes the volume making use of calls to the previous procedures VectorProduct and ScalarProduct.

• Write a Function procedure, VectorLength to calculate the length of the vector a.

• Write a Function procedure, VectorAngle, to calculate the angle (in degrees) between the vectors a and b making use of calls to the procedures ScalarProduct and VectorLength. (Note VB does not have a function for [pic] so you have to access the one provided in Excel by using the statement Application.Acos(cosineOfAngle).

Save your spreadsheet and its associated macros as “username-Vectors”.

Exercise 2

These exercises are based on mathematical series. As an example we look at the binomial expansion

[pic]

The r-th term is [pic]. The Function procedure below calculates this term, given x, r (= nbTerm) and n (= nPower) .

Function BinomialTerm(x, nbTerm, nPower) As Double

' calculates term nbterm of the binonial expansion of

' (1 + x) ^nPower

Dim binTerm As Double, k As Integer, termOK As _ Boolean

' check on valid values for term and power

termOK = True

binTerm = 0

If Abs(nbTerm - Int(nbTerm)) 0 Then

MsgBox "Error * Non-integer term requested " & _

nbTerm

termOK = False

End If

If nbTerm < 0 Then

MsgBox "Error * Negative-integer term " & _

nbTerm

termOK = False

End If

If Abs(nPower - Int(nPower)) 0 Then

MsgBox "Error * Non-Integer power supplied " & _

nPower

termOK = False

End If

If nPower > 0 And nbTerm > nPower + 1 Then

MsgBox "Error * term " & nbTerm & _

" greater than positive power of series " & _

nPower

termOK = False

End If

If termOK Then

If nbTerm = 1 Then

binTerm = 1

Else

k = 1

binTerm = 1

Do While k < nbTerm

binTerm = binTerm * x *(nPower - k + 1)/k

k = k + 1

Loop

End If

End If

BinomialTerm = binTerm

End Function

One thing to notice is that the majority of the code in the procedure is NOT devoted to evaluating the mathematical term, rather it is devoted to checking that it is being used properly. Hence the numerous checks on the values of the data supplied to the function by the user. When a user writes a Function procedure there is a strong temptation to believe that it will used as the writer intended. This, alas, is more often not the case.

The r-th term could be written as

[pic]

but it is not evaluated by directly coding this expression in the procedure. This is because if n and/or x and r, are large then the factorials or powers will evaluate to very large numbers, or if x is small [pic] may be very small and precision is lost due to the small number of significant figures stored by the computer. The loop in the procedure is based on [pic]

and avoids some of these problems.

The exponential function has the power series expansion,

[pic]

with the r-th term being [pic].

The sinc(x) function has the expansion

[pic]

• Using the BinomialTerm function as an example, write a Function procedure, ExpTerm, to calculate the r-th term of the exponential series.

• Write a Function procedure, ExpSum, to calculate the exponential series up to term N, i.e. [pic]. Use your function in a spreadsheet cell. See how the value your function compares with that obtained from Excel’s exp(x) function for different choices of x and N.

• Write a Function procedure, SincTerm, to calculate the r-th term of the sinc(x) series.

• Write a Function procedure, SincSum, to calculate the sinc series up to term N.

• Plot your sinc(x) function over the x range from 0 to 20.

Save your spreadsheet and its associated macros as “username-Series”.

Numerical integration

The final task on writing Function procedures will be to create a function that carries out numerical integration. You may be familiar with the trapezium rule method which approximates the area under the curve by a succession of trapezia. A better approximation is afforded by Simpson’s rule. The range of integration (a, b) is divided into an even number, n, of intervals each of width[pic]. If the ordinates are [pic] [pic] [pic] then the integral

[pic]

You are to write a procedure which uses Simpson’s rule to evaluate an integral. The procedure SimpsonInt should

• Take three arguments; the lower and upper limits of integration and the number of intervals.

• Since the rule is only valid if the number of intervals is even and at least two, your procedure should check for valid data and give an error message if necessary.

• The procedure should evaluate the integrand by using a user-defined function Func (= y(x)).

Test your procedure using known functions with known integrals, e.g. [pic] or [pic] over a range such as (0, 4) which you can check analytically. Then try some other functions, such as

1. the sinc(x) function which you wrote earlier, with limits (0, 20),

2. [pic] which you cannot do analytically. (You can check it using the NORMDIST function within Excel).

Save your spreadsheet and its associated macros as “username-Simpson”.

-----------------------

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download