Visual Basic Introduction

Rosalind Archer

1.2 Running Visual Basic

• Visual Basic is included with Microsoft Excel. My examples use Excel ‘97.

• When you open a file containing Visual Basic code make sure you choose “Enable Macros”.

1.3

Your message may look a little different to this one.

1.4

To access the Visual Basic code used in any spreadsheet choose Tools->Macro->Visual Basic Editor (or hit Alt+F11)

1.5 The Visual Basic Editor

1.6 Modules

• Visual Basic code can be divided into a series of “modules”. To start new program you need to insert a new module:

1.7 VB as an Extension of Excel

• Excel has many built in functions sin(), cos(), sum(), sqrt() etc.– what if you wanted to add your own functions?– Visual Basic allows you to do that.

Let’s add a function which computes relative permeability.

1.8 Krw Function

1.9 Using the Function in Excel

Now that the function is defined we can use it like any other Excel function.

1.10 The Visual Basic Language

• Comments … statements that allow the programmer to describe what a piece of code does.

• Begin with an ‘

• Shown in green automatically

1.11 Comments• Comments are important!

• They provide English language statements to describe what the computer code is doing.

• Make liberal use of comments - it will help you and others understand your program.

1.12 Variables

• Variables are labels assigned to numeric values (we’ll ignore text for now). You can manipulate them in algebraic expressions.

• It’s a good idea tell the program what variables you’re going to use ahead of time. This can be enforced using:

1.13 Variable Types

• Variables all have a type associated with them. The types will be using are integers and doubles.– Integers ..,-1,0,1,2,3 …– doubles -1.89475, 2.0, 3.498 etc

• Variables are declared using the statement Dim.

1.14 Declaring Lots of Variables

Dim a,b,c,d As Integer• This might look like a good idea to save

some typing (and in some languages stuff like this works.)

• In Visual Basic only d would be declared correctly. a,b and c would exist but may not have the correct type.

1.15 Combing Integers and Doubles

• Be careful with the difference between integers and doublesDim n As Integer

n = 4/3

The value that n will actually take is 1 because that is the nearest integer to 4/3 = 1.3333

1.16

• Visual Basic adds a # sign to doubles that have fractional part e.g.:

Dim dx As Double• If you type dx = 1.0

Visual Basic will change this to

dx = 1#

1.17 Converting Variable Types

• Sometimes we want to convert an integer (such as a counter in a For loop) to a double.

• The CDbl function does this.

Dim x As Double

Dim I As Integer

x = CDbl(I)

1.18 Constants

• If we want to define a constant we can use the keyword Const

Const g As Double = 9.81

1.19 Arrays

• We can store matrices and vectors in array variables. Declaring them is a two step process.‘This code solves Ax=bDim A() As Double, x() As Double, b() As DoubleReDim A(10,10), x(10), b(10)

• Do NOT assume that the elements in matrix are set to zero after it is declared. This can get you into trouble!

1.20 Array Elements

• The elements in the array can be accessed using ( , ) e.g.:A(2,3) = 4.4

• Note that the indices start from 1 i.e. A(1,1) is the first element in the matrix (some other languages have indices which start from 0)

1.21 Intrinsic Functions• Visual Basic has many common functions built in e.g.:

Dim f As Double, x As Doublef = Log(x)

Note that in Log(x) is the natural log

not log base 10.

1.22 Input and Output to/from Excel

• We’ll use a worksheet to supply the input to the program and to display the output.

• Note the a single workbook (.xls file) can have several worksheets inside it e.g.:

1.23

• Before we do any input/output we have to choose which worksheet to work with, e.g. to choose the “Results” sheet:With Worksheets(“Results”)

… End With

• Within the With statement we can read or write values from/to the worksheet.

1.24

• We can read or write text and numbers in the same way:

.Cells(1, 1) = "Time” ‘ Writes to A1

.Cells(1,2) = delx ‘ Writes to cell B1

x = .Cells(1,3) ‘ Reads from cell C1

• If you a certain sheet to come to the frontof the workbook you can activate it.This is just like clicking on its tab. The statement involved is:

.Activate

1.25

• Another useful function is clearing all the cells in a worksheet:.Cells.ClearContents

• All statements beginning with . must be inside With/End With

1.26 Conditional Statements

• If, then, else allows us to test a condition and do something as a result:

If x<10 Then

y = 10.3

Else

y = 0.5

End If

1.27 Looping

• Loops are used to execute a piece of code repetitively. There are two main kinds For and While.– For executes a set number of times– While executes until a condition remains

true.

• Both kinds of loops are very useful.

1.28 Example - For Loop

Dim i As IntegerFor i=1 to nxx = x + dx...

Next i

• Loops can be nested

inside each other e.g.:

For i=1 to N

For j=1 to N

A(i,j) = 0

Next j

Next i

1.29 Example - While Loop

While T < Tend

T = T + dt

…

Wend

1.30 User-defined Functions

• Visual Basic has plenty of common functions but often it’s a good idea to define your own if there is somethng you need to compute often (such as relative permeability):

Function krw(Sw As Double) As Doublekrw = Sw^2.0End Function

(A function could have several arguments)

1.31 Function Arguments

• The parameters passed to a function are known as arguments.

• You don’t have pass arguments with the same names as the ones in the function declaration e.g.

‘This function uses interpolation to evaluate y(xTarget)‘ given known x and y vectors

Function Interpolate(x() As Double, y() As Double, _xTarget As Double) As Double

1.32 Function ArgumentsDim y1 As Double

Dim x1 As Double

Dim x2 As Integer

Dim x() As Double

Dim y() As Double

y1 = Interpolate(x,y,x1) ‘ OK

y2 = Interpolate(x,y,x2) ‘ Not OK

This doesn’t work because x2 is a different type to xTarget.

1.33 Subroutines

• Subroutines are a way to divide a program into pieces where each piece performs a well defined task.

• Using subroutines makes your program much easier to understand!

• Your whole program can be written as a subroutine called when you push a button on a worksheet.

1.34 Example - Subroutines

• In a reservoir simulator we need to form a matrix (A) which depends on dx and dt (among other things).

Sub BuildA(A() as Double, dx as double, dt As Double)

A(1,1) = 1.0/dt

…

End Sub

1.35 Example - Subroutines

• When we are ready to use a subroutine use the statement “Call”

Call BuildA(A,dx,dt)

1.36 The Visual Basic Editor

• If you type something VB doesn’t like it will tell you!

• Visual Basic Editor automatically colors text according to the following scheme:Green = comments

Blue = VB keyword (For, While etc)

Black = everything else

Red = errors

1.37 Indenting

• It’s a good habit to indent code so you can see the connection between For-Next, If-End If etc.

For i = 1 to 10For j = 1 to 10

A(i,j) = 0.0Next j

Next i

1.38

• Here’s an example of a statement Visual Basic didn’t like.

– What’s wrong with it?

1.39 Long lines of code

• Lines of code can be as long as you like. But to make code easier to read it is wise to break long lines up using a continuation character ( _ ).

Product = a*b*c*d*e*f*g*h*i*l*j*k*l*m

orProduct = a*b*c*d*e*f*g*h _

*i*j*k*l*m

1.40 Running the Program

• If everything goes to plan running the program is as simple as clicking the button for it on the worksheet!

• There is no need to “compile” or “link” the program after you edit it.

• If something goes wrong when you run your program you’ll get a message.

1.41

Example

error message ->

The problem

will be highlighted

in yellow. You can edit the code and

rerun your program. But first you must

“reset” it using Tools->Reset in VB.

1.42

If you don’t reset

your code you’ll get

this message.

1.43 Debugging

• When a program

crashes VB

will show you the

value of a

variable if you hold your cursor over it.

Here I was dividing by zero - always a bad thing to do!

1.44 Breakpoints

• By right clicking in

the grey bar by the

code you can set a

breakpoint.

• When the code gets

to this point it will

stop and let step through examining

variable values.

1.45 Watches

• The red dot and highlight show this is a breakpoint.

• Debug->Add Watch adds variable to the bottom window to watch. If you click on the + sign by “b” you can see the values of all the elements in the vector.

1.46 Stepping

• To step through code line by line from a breakpoint (watching variables) use:– Debug->Step IntoDebugs into functions and subroutines asthey are called.

– Debug->Step OverDoes not debug into functions and subroutines asthey are called.

– Debug->Step OutQuits debugging a subroutine or function.

1.47 Adding buttons to worksheets

• If you want to add a new button to a worksheet, choose View->Toolbar->Forms.

1.48

• Choose the button iconfrom the toolbar anddrag an area in theworksheet to create thebutton.

1.49

• Your new button now needs to be assigned to a subroutine. If that subroutine doesn’t exist you’ll get an error.

• Once you’ve assigned a subroutine you can edit the name of the button also.