```Visual Basic Introduction
Rosalind Archer
1.1
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.2
Your message may look a little different to this one.
1.3
To access the Visual Basic code used in any
spreadsheet choose Tools->Macro->Visual Basic
Editor (or hit Alt+F11)
1.4
The Visual Basic Editor
1.5
Modules
• Visual Basic code can be divided into a
series of “modules”. To start new program
you need to insert a new module:
1.6
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.7
Krw Function
1.8
Using the Function in Excel
Now that the
function is
defined we can
use it like any
other Excel
function.
1.9
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.10
• Comments are important!
• They provide English language
statements to describe what the
computer code is doing.
• Make liberal use of comments - it
1.11
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.12
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.13
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.14
Combing Integers and Doubles
• Be careful with the difference between
integers and doubles
Dim 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.15
• 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.16
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.17
Constants
• If we want to define a constant we can use
the keyword Const
Const g As Double = 9.81
1.18
Arrays
• We can store matrices and vectors
in array variables. Declaring them is
a two step process.
‘This code solves Ax=b
Dim A() As Double, x() As Double, b() As Double
ReDim 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.19
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.20
Intrinsic Functions
• Visual Basic has many common functions built in
e.g.:
Dim f As Double, x As Double
f = Log(x)
Note that in Log(x) is the natural log
not log base 10.
1.21
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.22
• 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.23
• 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 front
of the workbook you can activate it.
This is just like clicking on its tab. The
statement involved is:
.Activate
1.24
• Another useful function is clearing all the
cells in a worksheet:
.Cells.ClearContents
• All statements beginning with . must
be inside With/End With
1.25
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.26
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.27
Example - For Loop
Dim i As Integer
For i=1 to nx
x = 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.28
Example - While Loop
While T < Tend
T = T + dt
…
Wend
1.29
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 Double
krw = Sw^2.0
End Function
(A function could have several arguments)
1.30
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.31
Dim
Dim
Dim
Dim
Dim
Function Arguments
y1 As Double
x1 As Double
x2 As Integer
x() As Double
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.32
Subroutines
• Subroutines are a way to divide a program
into pieces where each piece performs a
• 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.33
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.34
Example - Subroutines
• When we are ready to use a subroutine use
the statement “Call”
Call BuildA(A,dx,dt)
The Visual Basic Editor
1.35
• Visual Basic Editor automatically colors text
according to the following scheme:
Green
Blue
Black
Red
= VB keyword (For, While etc)
= everything else
= errors
• If you type something VB doesn’t
like it will tell you!
1.36
Indenting
• It’s a good habit to indent code so you
can see the connection between ForNext, If-End If etc.
For i = 1 to 10
For j = 1 to 10
A(i,j) = 0.0
Next j
Next i
1.37
• Here’s an example of a statement
Visual Basic didn’t like.
– What’s wrong with it?
1.38
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
or
Product = a*b*c*d*e*f*g*h _
*i*j*k*l*m
1.39
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.40
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.41
If you don’t reset
your code you’ll get
this message.
1.42
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.43
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.44
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.45
Stepping
• To step through code line by line from a
breakpoint (watching variables) use:
– Debug->Step Into
Debugs into functions and subroutines as
they are called.
– Debug->Step Over
Does not debug into functions and subroutines as
they are called.
– Debug->Step Out
Quits debugging a subroutine or function.
1.46
Adding buttons to worksheets
• If you want to add a new button to a
worksheet, choose View->Toolbar>Forms.
1.47
• Choose the button icon
from the toolbar and
drag an area in the
worksheet to create the
button.
1.48
• 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.
```