vba guide - morison, an introduction to visual basic for engineers and scientist, uni of canterbury...

• !

I 1.

AN INTRODUCTION TO

VISUAL BASIC

FOR ENGINEERS AND SCIENTISTS

KEN R. MORISON

UNIVERSITY OF CANTERBURY

CHRISTCHURCH, NEW ZEALAND

2002

Morison, Kenneth Robert An Introduction to Visual Basic for Engineers and Scientists

Published by University of Canterbury Private Bag 4800 Christchurch New Zealand

© Copyright Ken R. Morison, 2002

Any examples in this book may be copied and stored electronically but the source should be acknowledged. No other parts of this publication may be reproduced in any way without prior permission from the author.

ISBN 0-473-08445-7

Printed by the University of Canterbury

Additional copies of this book may be obtained for NZ$45.00 (inel GST) within New Zealand or NZ$50.00 for overseas orders from:

Department of Chemical and Process Engineering University of Canterbury Private Bag 4800 Christchurch New Zealand phone +64 3 364 2543 fax +64 3 364 2063 email [email protected]

1 5 (J (T 2fJU2

I "

I t

TABLE OF CONTENTS

THE NA.T10NAL U13Rp,RY OF NEW ZEP,U\ND

1 Introduction .............................................................................................................................. 1 Visual Basic ........................................................................................................................ 1 Useful Books ....................................................................................................................... 1 Typographical Style Used in These Notes .......................................................................... 1 Help Files ............................................................................................................................ 1 Leanling Style ..................................................................................................................... 2

2 Getting Started ........................................................................................................................ .3 Running Visual Basic in Office 2000 ................................ :.·.:c ............................................... .3 A Simple Program ................................................................................................................... 4

Program naming ................................................................................................................. .4 Comments ........................................................................................................................... 4 Declaring variables ............................................................................................................ .4 Built-in functions ............................................................................................................... .4 Saving a project .................................................................................................................. 4 Opening an old file ............................................................................................................. 4

A Simple User-Defined Function for ExceL ......................................................................... 6 Excel functions ................................................................................................................... 6 VBA functions .................................................................................................................... 6

The Message Box Function .................................................................................................... 8

3 Visual Basic Language Statements ........................................................................................ 10 Declaring Variables .............................................................................................................. 1 0

Data types ................................................................................................ : ........................ 10 Computer memory ............................................................................................................ 12 Naming rules and conventions .......................................................................................... 14 Variable naming conventions ........................................................................................... 14 Option Explicit. ................................................................................................................. 16

Basic Mathematical Operations '" ......................................................................................... 18 Built-in Functions ................................................................................................................. 18 If Statements ......................................................................................................................... 20 Select Case ........................................................................................................................... .22 ForlNext Loops .................................................................................................................. : .. 24

Nested For loops ............................................................................................................... 24 Debugging ............................................................................................................................. 26

Desk checks for debugging ............................................................................................ : .. 26 Arrays ................................................................................................................................... .28 More Debugging ................................................................................................................... 32

Breakpoints .................................................................................................................... ; .. .32 Stepping through a program ............................................................................................. 32 Debug.Print ....................................................................................................................... 32 MsgBox ............................................................................................................................. 32 Interrupting a program ..................................................................................................... .32

An Introduction to Visual Basic for Engineers and Scientists I

Do While and Do Until Loops ............................................................................................. .34 Goto ....................................................................................................................................... 38 Error Trapping ...................................................................................................................... 38 Exit.. ...................................................................................................................................... 38 String Operations ................................................................................................................. .40 File Operations ..................................................................................................................... .42

4 Objects and Excel ..................................................................................... : ........................... .46 Objects .................................................................................................................................. 46 Working with Excel .............................................................................................................. 48

Excel objects .................................................................................................................... .48 The With block .. : .............................................................................................................. 50 Creating range objects ...................................................................................................... 52 Interpreting macros in Excel ............................................................................................ .58 Creating and manipulating charts in Excel ....................................................................... 59

5 Structuring Programs ............................................................................................................. 62 Procedures ............................................................................................................................. 62

Functions ........................................................................................................................... 62 User-defined functions ...................................................................................................... 62 Subroutines ....................................................................................................................... 64 Array arguments ... c ••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••• 64 Structuring a program ....................................................................................................... 66 Private and Public procedures and variables .................................................................... 68

6 Visual Basic Forms .....•.......................................................................................................... 70 UserFonns ............................................................................................................................. 70

Running Forms from Excel. .............................................................................................. 73

7 Using the Full Version of Visual Basic ................................................................................. 74 Serial Communication via a COM port ............................................................................ 74 Other Controls ............................................................... , ................................................... 74 Printing .............................................................................................................................. 75

8 Integrating VBA With Other Applications ............................................................................ 77 Compiling Projects ................................................................................................................ 77

Excel Add-ins ................................................................................................................... 77 Workbooks in XIStartup ................................................................................... , ............... 77

Using VBA with Word and Power Point... ........................................................................... 77 Using VBA Word and Excel together .................................................................................. 77 Using Excel Solver ............................................................................................................... 80 Using VBA with Access ....................................................................................................... 82

11 An Introduction to Visual Basic for Engineers and Scientists

I I I

i i I I i

I I I

1/1 it' ~i

1 1 j .&

1 1

LIST OF EXAMPLES

Example 1: A simple program .................................................................................................. .5 Example 2: Functions in Excel (revision) .................................................................................. 7 Example 3: Write a user defined function ................................................................................. 7 Example 4: The MsgBox function ............................................................................................. 9 Example 5: Types of numbers ................................................................................................. 11 Example 6: Other types of variables ........................................................................................ 13 Example 7: Naming conventions ............................................................................................. 15 Example 8: Using Option Explicit.. ......................................................................................... 17 Example 9: Mathematical operations and functions ................................................................ 19 Example 10: The If statement ................................................................................................. .21 Example 11: If Then ElseIf... ................................................................................................... 21 Example 12: Select Case .......................................................................................................... 23 Example 13: Select Case with strings ...................................................................................... 23 Example 14: ForlNext loops .................................................................................................... 25 Example 15: Calculate the factorial of a number using a ForlNext loop ................................ 25 Example 16: Nested ForlNext loops ........................................................................................ 25 Example 17: A desk check for debugging ............................................................................... 27 Example 18: Using arrays ........................................................................................................ 29 Example 19: The Array statement .......................................................................................... .29 Example 20: Two-dimensional arrays ..................................................................................... 31 Example 21: Debugging with Breakpoints and the Locals Window ...................................... .33 Example 22: Debugging with Debug.Print ............................................................................. .33 Example 23: Do Until and Do While loops ............................................................................ .35 Example 24: Sum a series using a Do Until loop .................................................................... 35 Example 25: MacLaurin series using a Do Until loop ............................................................ .37 Example 26: Dice throwing using a Do Until loop ................................................................. 37 Example 27: Error trapping .................................................................................................... .39 Example 28: A more specific error handler ............................................................................ .39 Example 29: String operations ................................................................................................ .41 Example 30: Reading from text files ...................................................................................... .43 Example 31: Reading in a loop .............................................................................................. ..43 Example 32: Reading and writing text files ............................................................................ .45 Example 33: Reading into arrays ............................................................................................ .45 Example 34: Define a simple object type ............................................................................... .47 Example 35: Reading and writing data in Excel worksheets .................................................. .49 Example 36: Using the WorksheetFunctions .. ,. ....................................................................... .49 Example 37: The Cells property ............................................................................................... 51 Example 38 The With / End With structure ........................................................................... .51 Example 39: Producing a table of function values .................................................................. 53 Example 40: Using a Range object. ......................................................................................... 53 Example 41: Opening, adding, saving and closing Excel files ................................................ 55 Example 42: Range methods ................................................................................................... 56 Example 43: Formatting cells in ExceL ................................................................................ .57 Example 44: Using macro recording to produce charts ........................................................... 59 Example 45: Naming and deleting Charts ............................................................................... 60 Example 46: Creating and manipulating charts ....................................................................... 61 Example 47: Using user-defined functions within VB ............................................................ 63 Example 48: Subroutine calling ............................................................................................... 65

An Introduction to Visual Basic for Engineers and Scientists III

Example 49: Passing arrays to subroutines .............................................................................. 65 Example 50: The shell of a structured program ....................................................................... 67 Example 5 I: Public and Private variables ............................................................................... 69 Example 52: Properties of the components offorms ............................................................... 71 Example 53: A simple userform .............................................................................................. 73 Example 54: Using the full version of VB .............................................................................. 74 Example 55: A VB program without a form ........................................................................... 75 Example 56: Printmg from VB ................................................................................................ 75 Example 57: Drawing a graph in a form picture ..................................................................... 76 Example 58: VBA in Word to produce the set offonts ........................................................... 78 Example 59: Opening Word and a file from Excel VBA ........................................................ 79 Example 60: Using Solver from VBA ..................................................................................... 81 Example 61: VBA and Access ................................................................................................. 83

IV An Introduction to Visual Basic for Engineers and Scientists

.... ~~

('

I'·.' ,

1-

I I I i

!

• •

• !

! l

1 Introduction

Visual Basic Visual Basic (VB) is a very useful language for a number ofreasons.

• It is on almost every personal computer in some form. Every version of Microsoft Excel from 6.0 comes with Visual Basic for Applications (VBA) for writing programs (referred to as macros).

• It is easy to learn.

• The syntax is simple.

• It integrates very easily with Excel and Word (from version 8.0).

• It contains most of the structures of modern computer languages.

• It uses object oriented programming (OOP).

There are numerous versions of Visual Basic. The version that comes with Excel is the Excel version of Visual Basic for Applications. It knows all about workbooks, worksheets and the functions in Excel so it is very easy to integrate. VBA also comes with other "applications" such as Word, PowerPoint, Access, Outlook. Each version ofVBA is different in that it knows about the structure and functions of the application it is working with. However VBA for one application knows little about VBA for another application, e.g. VBA with Excel knows little about VBA with Word.

The full version of Visual Basic contains a wider range of commands than VBA including database access, communication with the serial port and the ability to compile the program to a stand-alone executable file. As a simple rule, if you want to make money from programming you will probably need the full version of VB.

The basic language is the same for both VB and VBA so in this book I use VBA in Excel as the environment. This allows greater accessibility for learners, and greater usefulness for engineers and scientists. On page 74 there is a section on the differences when using the full version of VB.

Useful Books This book is only an introduction that should provide most of the basics for an engineer or scientist who wishes to enhance Excel with macros, or who wishes to write small programs for specialist calculations. It is written to get you on to and up the learning curve. My hope is that for many people this book will be enough but for those who wish to continue there are numerous books on Visual Basic available. Some concentrate on VB and others on VBA. For those working with Excel I recommend:

Walkenbach, John, (1999), Microsoft Excel 2000 Power Programming with VBA, IDG Books

Typographical Style Used in These Notes In these notes programs lines or VB words are written in Arial Font. Sometimes Aria/Italics is used to indicate a name that can be changed to any legal name.

Bold Arial is used to show the parts of a program which demonstrate a particular syntax.

Square brackets [ 1 indicate optional parts of a construction.

Help Files VBA has a number of help files. Within VBA if you click on Help on the menu bar you get the Microsoft Visual Basic Help which is improved from previous versions.

An Introduction to Visual Basic for Engineers and Scientists 1

A Simple User-Defined Function for Excel

We will now write a new function for Excel using VBA.

Excel functions Excel has many built in functions. It is assumed that you are familiar with these. If not, look up "worksheeCfunction" in the Index of Microsoft Excel Help, and also lookup "formula palette" using the Answer Wizard in Help. Also follow the Excel example on the right. .

JlBA functions You can write new Visual Basic functions that can be used within Excel. They can also be used within VB programs but more on that later.

In the example on the right a function called SquareRoot is written. This does the same job as the Excel function SQRT so this function isn't very useful except as an example. We use a different name so Excel doesn't get confused.

If this function is contained within a module, any worksheet in the associated Excel workbook will be able to access it as a User Defined function. Just enter a formula into an Excel cell, e.g., =SquareRoot(AI) or use the Insert Function icon II~ and find the function in the User Defined

category. Notice that the name of the variable containing the result of the function is the same as the name of the function (SquareRoot).


i •

,.

l

[

r f

i i. (

!. i

~l -,

~ ;j

1

Example 1: A simple program We wish to calculate the square root of a number given by the user of a Pc.

In plain English the program will perform the following operations

Get a number from the user Calculate the square root Display the result to the user

To do this we:

Open Excel Create a new file Select Tools, Macro, Visual Basic Editor (note that Ait FII is the shortcut key)

Within Visual Basic for Applications Select Insert, Module

Within the Module write the program shown.

Sub MySquareRoolO • This program calculates and displays the square root of II numlber Dim sMyNumber As Single, sMySquareRool As Single sMyNumber = InpulBox("Please enter II number") sMySquareRool = Sqr(sMyNumber) MsgBox sMySquareRoo! End Sub

Then from the menu bar select Run, S!art (F5 is the shortcut key for this).

Enter a number as requested.

Visual Basic Examples 5

A Simple Program Follow Example I on the right as you read this.

Program naming We always start a program with Sub Program_nameO

Sub stands for subroutine which is another name for a program. The program name can be any name (not necessarily Program_name) not used elsewhere which follows naming rules (see page 14).

The name is followed by a set of brackets (). In some cases these contain variables for the program to use and these will be explained later.

All programs end with End Sub.

Comments

Anything after a single quote' is a comment or remark and is ignored by the computer but enables the author or another person can read what the program does. Any program should contain lots of comments.

Declaring variables Variables are declared using lines starting with Dim. For now moment just follow the example. The variables start with the lower case "5" to indicate that it is a real floating point number (more on page 10).

Built-in junctions VBA has many built-in functions. More information is given about these on page 18. Here we use

InputBox (get user input through a pop-up box on the screen) Sq r (calculate the square root.) Msgbox (put a result on the screen using a Message Box)

Note that the square root keyword is Sqr and not Sqrt as used in Excel.

Saving a project Because the project is associated with a file in Excel (currently Bookl), all you need to do is save the file in Excel. You can close VB Editor and then save the main file.

Opening an old file Go into the application you wish to use (normally Excel). Open the file with the project attached to it. Start VB Editor. If Excel security is set too high you will not be able to run the programs. It is recommended that you Select Tools, Macro, Security and set it to medium.


i.·.· !,

I I

i I I ! i

2 Getting Started

Running Visual Basic in Office 2000 You can run the VB Editor from the applications Excel, Word, PowerPoint or Access. The procedures produced will normally be associated with the active file in the main application. From the application you can get to the VB Editor through the menu items Tools, Macros, Visual Basic Editor (Alt FII). The initial screen is likely to contain the Project Explorer window and a Module window but it can contain a number of windows as shown below.

Properties Window. To get this window, select View, Properties Window It shows the properties of the active window (currently Userforml).

Project Window. To get this window, select View, Project Explorer. It shows which modules and forms have been defined.

To get a new module, select Insert, Module. To view an existing module, double click on the module name in the Project Window.

Form Writing Toolbox This is shown when a Userforrn window is active.

Userform Window. To get a new Userform, select Insert, Userform.

Visual Basic Examples

To view an existing module, double click on the userforrn name in the Project Window.

3

If you select a word in a VB program with the mouse then press FI you will get Microsoft Visual Basic Help with help on that word if it exists.

Help in Office 2000 and Office XP has changed somewhat from Office 97.

Ifusing VBA in Office 97 the following will be useful. Within VBA, if you click on the Help menu you get Visual Basic for Applications help. I find the most useful help for VBA with Excel is the Microsoft Excel Visual Basic Reference. From the Help menu within the Visual Basic editor select Contents and Index, then from the Contents tab double click on Microsoft Excel Visual Basic Reference and then double click on Shortcut to Microsoft Excel Visual Basic Reference.

Learning Style The style ofthis book reflects my own experience of how I learned computer languages effectively. I would look for examples from books and other programs that solved problems similar to mine, and only when necessary did I seek to clarifY the syntax. I recalled classes as a student in which I yeamed for the teacher to give examples of programs, complete programs, to show how a particular construction worked.

My own experience of learning Visual Basic was not a good example to anyone. I floundered my way trying to record macros then interpret them. The Microsoft help files of the time were close to useless, partly because I hadn't learnt the vocabulary of words such as Method, Property and Application, and didn't think with a Microsoft brain. Then I tried reading numerous texts but found them full of business and commerce type examples with few real calculations in sight. But after some time and with my own examples I managed to distil the skills I required.

To leam from this book, copy the examples, run them and then read the explanation that goes with them.

Feedback is very welcome. It can be sent to Ken Morison, Department of Chemical and Process Engineering, University of Canterbury, Private Bag 4800, Christchurch, New Zealand or email: [email protected].

2 An Introduction to Vi~uaIBasic for Engineers and Scientists

I I I, )

I I I I I I I I I I i

I' , 1\

~,

!. !

Example 5: Types of numbers

Option Explicit

Sub SimpleTypesO , Program to show simple data types Dim iNumber As Integer Dim IngNumber As Long Dim sNumber As Single Dim dNumber1 As Double, dNumber2 As Double

iNumber= 10 , Integers have no decimal places -IngNumber = 56000 , Long integers can-be larger than 32767 sNumber = 1.234567 dNumber1 = 1.2345678901234 dNumber2 = 1.234E+89 MsgBox iNumber

, Single precision real numbers have 7 significant digits 'Double precision number have 14 or 15 digits 'E+89 means 10 to the power of 89

MsgBox IngNumber MsgBox sNumber MsgBox dNumber1 MsgBox dNumber2

End Sub

Note that in this example that 1.234x1089 is written as 1.234E+89. You will notice that as you type in the first few letters of the data type VB displays a list of the possible options. Once you have typed enough press the tab key to accept the option (or Enter and accept and go to a new line)

Sub WrongTypesO , Program to show incorrect use of data types Dim iNumber As Integer Dim IngNumber As Long Dim sNumber As Single Dim dNumber As Double

iNumber = 10.2345 ' fractional parts will be ignored IngNumber = 56000.344 'fractional parts will be ignored sNumber = 1.23456789 ' Only the first 7 digits will be stored dNumber = 1.23456789

MsgBox iNumber MsgBox IngNumber MsgBox sNumber MsgBox dNumber + dNumber 11 0000000000# 'Only 15 digits are calculated

End Sub


3 Visual Basic Language Statements

Declaring Variables In VB and almost every other programming language we need to "declare" variables so a program knows what type each variable is and how much memory to allocate. In actual fact in VB we can manage without declaring variables but it is very sloppy programming practice and we will not do it. Declarations statements normally come at the start of the program.

The syntax for a declaration is

Dim variable As Type (,variable2 As Type] , The square brackets indicate optional text

where Type may be Integer;]300Iean, Long, Single, Double, Currency, Date, or any other valid type, e.g.,

Dim sMyNumber as Single Dim stSurname as String

Data types In a computer, data is stored as a circuit being on or off. We call this most basic unit of memory a bit (aOor I).

If we put 8 bits together using binary numbers we get a number from 000000002 to 11111111, or from o to 255 10 (255 = 2° + 21 + 22 + 23 + 24 + 2' + 26 + 2\ We call this a byte.

Each variable or character or step of the program has a location in memory. This is referred to as its memory address. You can think of it as a pigeon hole. The memory manager needs to know how much memory is required for each variable.

Integers (in Visual Basic) occupy 2 bytes of memory, i.e., from 0000 0000 0000 00002 to IIII IIII IIII 11112 but we need to keep one bit to indicate whether the integer is positive or negative. Thus there are 15 bits available and we can store integers from _2" to 21'_1, i.e., from -32 768 to 32 767.

Ifwe need a larger integer value we can use the VB Long integer which occupies 4 bytes and can range from -2 147483648 to 2 147483647.

Real numbers (or floating point numbers) are stored differently in computers. They are stored in the form 1.2535345 x 1012 as either Single precision or Donble precision. In most computers Single precision numbers use 4 bytes (32 bits). One of the bits is used for the plus or minus sign, eight are used for the exponent and 23 are used for the mantissa (the number before the exponent).

This gives a range from -3.4xlO38 to -1.4xlO·45 and from 1.4x10"s to 3.4xI038•

If this range is insufficient we use Double precision numbers which use 8 bytes and range from ±4.9xI0-324 to ±1.8xI030

'.

Single precision numbers have an effective resolution of 7 significant digits while Double precision digits have about 14 significant digits. Thus if we wanted to add 10-9 to 1.0 and get an accurate result we would need double precision as we require a total of 10 significant digits.

Single characters occupy one byte of memory, e.g., "A" is represented by 6510 or 0 I 00000 12 and "z" is represented by 12210 or 011110102. A Striug is a series of characters which may be letters or numbers.


1 ~

i

Example 4: The MsgBox function

The following program demonstrates various ways of using the MsgBox fimction.

Option Explicit

Sub MsgBoxExamplesO • Program to show uses of the MsgBox function

• First declare the variables that are going to be used in the program Dim sNumber1 As Single Dim stWord1 As String, stWord2 As String, stMessage As String

sNumber1 = 12.345678 MsgBox sNumber1 MsgBox sNumber1 + 20 MsgBox "The number is " & Format(sNumber1,"0.000")

MsgBox "Hello World"

stWord1 = "Hello" stWord2 = 'World" MsgBox stWord1 & stWord2 , & is used to join strings

• Build up a message by concatenating words and spaces" .. stMessage = stWord1 & .... & stWord2 & .... & sNumber1 MsgBox stMessage

End Sub

Exercise

Write a program that asks the user their name then responds with their name in the fonn "Hello ..... ..


The Message Box Function

The function MsgBox is very useful for displaying results on the screen and for debugging (finding the errors in) a program.

We have already used:

MsgBox sMySquareRoot

to display the value of sMySquareRoot.

MsgBox can also be used to display text (string) values or a number of different values combined together.

The ampersand n&n is used to join (concatenate) different strings so they can be displayed by the MsgBox function.

Try Example 4.

Message box can be changed with many different options. Once you have mastered the basics you can find more information under Microsoft Visual Basic Help, Index, Msgbox, or just click on the word Msgbox within a program and press Fl.

Instead ofMsgbox we are more likely to use Userforms (page 70) to display inputs, results, messages and buttons.


, \ , ,

" [

r

• !

~

I ! i

~i Example 2: Functions in Excel (revision) In a cell{say A2) enter a number. Then select another cell (say A3). Click on the Insert Function

icon l:wD (or select Insert, Function from the menu) and select, e.g., Math & Trig from the Function category list and COS from the Function name list. Click OK then enter A2 where the number is regul(lste,d 'and

Example 3: Write a user defined function We can create a "User Defmed" function using VBA. Go to VBA and in a new module, or after the last program, write the function as shown.

~~~~~~~::~~:~:;! root of a number from Excel

Important: these two names are the same

Now in Excel, use Insert Function again, but this time select the Function category "User Defined" and select the function you have just written ("SquareRoot").


Table of some data types in Visual Basic Type Use for Memory Examples Range Integer Whole numbers 2 bytes 1543 -32 768 to 32767 Long Large whole numbers 4 bytes 1542354 -2 147483 648 to

2147483647 Single Single precision floating 4 bytes 1.432523 x 10' ±3.4xl0"to ±1.4xl0-4'

point numbers Double Double precision 8 bytes 1.6534563434434 ±4.9xlO·;2' to ±1.8xl0'u,

floating point numbers Boolean True or False values 1 byte True True or False String Alphanumeric data or 1 byte per Canterbury

text character Date An eight character date 8 bytes Jan 1 1998 Jan 1, 100 AD to Dec 31,

9999 Currency Number with 19 digits 8 bytes 132 345 342.2376

including 4 decimal places

Variant An undefined or Any type changing type

The Variant type has two uses in VB. When the variable needs to have more than one type, or when the type is uncertain the Variant type can be used. For example

Sub VariantDemoO , Divide one number by another and give the result Dim sNumber1 As Single, sNumber2 As Single Dim vResult As Variant

sNumber1 = InputBox("To calculate Number1/Number2 please enter Number1") sNumber2 = InputBox("Please enter Number2") If sNumber2 <> 0 Then

vResult = sNumber1 I sNumber2 ' The result is a real number Else

vResult = "Division by zero" , The result is a string End If MsgBox "The answer is " & vResult

End Sub

Computer memory Visual Basic makes use of the RAM (random access memory) to store the program and variables. If you have a computer with 128 megabytes of RAM, you can store a maximum of, e.g., 16 million double precision numbers (and that's not counting the program memory). This seems to be a lot but RAM can be very quickly used up by complex modelling problems.

When each variable is declared with a Dim statement a small section of memory is set aside for that variable.


It I I I ' ,

I ,. ,.'

J

I I I i

i I

f ;( -

! i 1

Example 6: Other types of variables

Option Explicit

Sub OtherTypes() , This program gives examples of String, Variant and Boolean variables.

Dim stName As String Dim vJunk As Variant Dim bFlag1 As Boolean, bFlag2 As Boolean, bFlag3 As Boolean Dim daBirthday as Date

stName = "Ken Morison" MsgBox stName

, Variant examples vJunk = 10 MsgBox vJunk

vJunk = "Rubbish" 'A variable declared as variant can change types MsgBox vJunk

'Boolean examples bFlag1 = True 'Boolean variables are True or False bFlag2 = False bFlag3 = bFlag1 And bFlag2 'They can be combined with And, Or, Not, XOr MsgBox bFlag3

End Sub

Sub Overflow() , This program produces an Overflow error as the integer is greater than , the maximum size for an integer (32767)

Dim iNumber As Integer

iNumber = 32767 MsgBox iNumber MsgBox iNumber + 1

End Sub


'This number will be too big and "Overflow" will occur

13

Naming rules and conventions Names of programs, procedures and variables must conform to the following rules • They must begin with a letter • They may contain only letters, numbers and the underscore character ( _ ) • They cannot include any spaces • The cannot be longer than 255 characters • They cannot be VB reserved words, e.g., Sub, If, Goto, End, etc • They are not case sensitive but you should be consistent

Legal names include anumber2 the_number _ oC dogs

Illegal names include 2-dogs starts with a 2 and contains a minus sign dogs*cats contains a * width a VB reserved word. To fmd out if a word is reserved, move the cursor onto

the word and press FI to see if there is help available. If so it may be reserved.

In general names should be long enough to be meaningful to other people, but short enough to type easily.

Variable naming conventions Because there are so many types of variables in VB it is very useful to include the type in the name. modern convention, which is strongly encouraged, is to prefIx the name with an abbreviation for the type. This helps anyone reading the program to immediately identifY the type of the variable but the computer cannot tell the type from the prefIx. The suggested table of prefIxes is not universal. Each author seems to have their own recommendations and a universal standard has not yet developed.

Data Type IdentifIers Data Type PrefIX Example Boolean bl blFlag Byte b bCodeNumber Currency c cAmountPaid Date da daDatePaid Double d dPi Integer 1 iCounter Long (integer) Ing IngCounter Range (Excel cells) rnll; rngResults Single s sProjectedValue String st stFirstNarne Variant v vInitValue

A


I ! I I E I m

fJI \'

I i If:

Ii,

! i m

~

I I

1

1 !

i ! !

Example 7: Naming conventions

The following program contravenes naming rules and conventions. Change it so that it works and is consistent with the conventions given.

Option Explicit

Sub NameRulesO • This nonsense program breaks many rules and conventions. • Please correct it. The VB editor will help.

Dim 2Number as Integer Dim String As String Dim My Name as String Dim My-Number as Single Dim iF as Integer

2Number = 57000.34 String = "Ken" MsgBox String My Name = "Morison" My-Number = 28 iF= 2

End Sub


Option Explicit

The command Option Explicit should be written as the fIrst line of each module. It tells VB that all variables will be explicitly defmed with a Dim statement. With Option Explicit included, VB will check that every variable has been declared by a Dim statement. For example below, the check initiated by Option Explicit will fInd that sNumerl (in line 3 of the subroutine) has not been declared with a Dim statement and hence we discover a typing error.

Option Explicit

Sub VariableCheckO Dim sNumber1 As Single, sNumber2 As Single sNumer1 = 2 sNumber2 = sNumber1 "2 End Sub

Try Example 8 to see how effective Option Explicit is.

It is strongly recommended that Option Explicit be used in all modules.

Declaration may seem to be a bit of a hassle at fIrst but it is essential when developing reliable programs.

In the standard confIguration of VB Option Explicit is not automatic. To make it automatic, in VBA select Tools, Options, Editor and check the box for Require Variable Declaration. Then every time you open a new module, Option ExpliCit is already written for you.

16 An Introduction tq Visual Basic for Engineers and Scientists

r ! ,

,

I

r ,

f

I {

r

1

! i

I. I

Example 8: Using Option Explicit

Type in the following program without using Option Explicit at the top of the module. Make a number of mistakes using I (L) instead of 1 (one), 0 (capital 0) instead of 0 (zero) or Alpa instead of Alpha.

Sub DeclareDemoO AlpaO = 100.0 Beta1 = 3 * AlphaO Gamma2 = AlphaO * Betal + 2 msgBox Gamma2 End Sub

What answer does the program give?

Now insert Option Explicit at the top of the module and include some declaration lines (using Dim) after the Sub line.

Option Explicit

Sub DeclareDemoO

, Example showing variable declaration

Dim AlphaO as Single, Beta1 as Single, Gamma2 as Single ,

AlpaO = 100.0 Beta1 = 3 * AlphaO Gamma2 = AlphaO * Betal Msgbox Gamma2 End Sub

How many errors does the Visual Basic compiler pick up?


Basic Mathematical Operations VB mathematical operations are identical to those of Excel.

A exponentiation * multiplication / division + addition subtraction

They are executed in a priority order being A, * or /, then + or -. Use brackets to make the meaning clear.

For example consider the following which will give different results.

y=xAz-3 y=xz _3

y = xA(z-3) y = X(z-3)

The = symbol has a particular meaning. It means evaluate the right hand side and assign the result to the left hand side. Thus we can write

y = 3 * X

But not 3*x=y

we can also write i = i +1

which is clearly not mathematically true but will be executed as an increment in the value of i.

Built-in Functions In our first program we used three VB functions. These are built-in functions, just as in a spreadsheet or calculator. They were

InputBox Input box Sqr Square root MsgBox Message box

A complete list of VB functions can be found in Microsoft Visual Basic Help, by selecting the Contents tab, then Visual Basic Language Reference, then Functions for an alphabetical list or Indexes/Groups for keywords by topic.

The mathematics functions are Sin, Cos, Tan, Atn Basic trigonometric functions Exp exponential, eX Log naturallogaritInn Sqr Square root Rnd, Randomize Random numbers Abs Absolute value Sgn Sign of an expression Int Return the integer less than or equal to the number Fix Return the integer part of a number


1

Example 9: Mathematical operations and functions

Option Explicit

Sub MathsOpsO • Program to demonstrate simple maths functions Dim sX As Single, sY As Single, sZ As Single Dim iNurn As Integer

sX = 3.5 sY=6 iNum=4

sZ = sX A iNurn + 2' sY MsgBox "The answer is " & sZ

sZ = (-sX) A (1 I iNurn) This one doesn't work. Why not? MsgBox sZ

End Sub - ...

If the program stops with a line highlighted in yellow, you can click on the Reset icon. to reset the program.

Option Explicit

Sub BuilUn_FunctionsO , Examples of built in functions Dim sX As Single, sY As Single, sZ As Single

sX= 2.5 sY= 1.2

sZ = Sin(sX) MsgBox "The sin of" & sX & " radians is " & sZ

sZ = Log(sX) MsgBox "The natural log of" & sX & " is " & sZ

sX = -2.3 MsgBox "The sign of" & sX & " is " & Sgn(sX)

End Sub


..

19

If Statements An If statement allows the program to make decisions and perform the appropriate actions.

For example If the sun is shining Then

go to the beach (do this if the condition is true) Else

go to Computing lecture End If

(do this is the condition is false)

Some people find it useful to see this as a flow diagram

False

Go to computing lecture

The standard syntax for the If statement is

If (condition) Then statement(s)

[Else If (condition) Then statement(s)]

[Else statement(s)]

End if

sun is shining?

The parts shown in square brackets [ 1 are optional.

True

Go to beach

The conditions will usually involve a comparison between two values. The result is either true or false. There are six relational operators used as shown below.

Symbol Relation tested Examples > greater than If iCount > 10 then < less than If IngMyNumber < 0 then = equal to If stName = "Fred" then <> not equal to, '" If stName <> "Fred" then >= greater than or equal to, ~ IfiValue >= 20.0 then <= less than or equal to, :5: If iValue <= 20.0 then

When comparing strings normal rules of alphabetical order as in a dictionary are used with the added feature that digits (0-9) come before upper case letters (A-Z) which come before lower case letters (az). See Option Compare Text on page 40 for more information.

20 An Introduction to, Visual Basic for Engineers and Scientists

t .:.

Example 10: The If statement Example 1 could be enhanced by adding an If statement to catch negative numbers, e.g.,

Option Explicit Sub SquareRootO

, This program calculates the square root of a number and , displays the result. Written by Ken Morison 10 Feb 98

Dim sNumber As Single, sSquareRoot As Single

, Get a number from an inputbox, check that it is not negative , If OK get the squareroot and print it on the screen, otherwise write a message

sNumber = InputBox("Please enter a number ") If sNumber >= 0 Then

sSquareRoot = Sqr(sNumber) MsgBox "The square root of" & sNumber & " is "& sSquareRoot

Else MsgBox "I cannot calculate the square root of a negative number"

End If End Sub

Example 11: If Then Eiself As an example of If Then Eiself try this program

Sub TemperatureO , This program describes the temperature with a word given the value in ·C Dim sCelsiusTemp As Single Dim stTemperature As String sCelsius Temp = InputBox("Please enter the temperature is ·C")

If sCelsiusTemp < 15 Then stTemperature = "Cold"

Eiself sCelsiusTemp <= 25 Then stTemperature = 'Warm"

Else stTemperature = "Hot"

End If

MsgBox "The temperature is" & stTemperature End Sub

The If statement can be written as a single line as follows, but this it is no! as clear to read or as easy to alter so is discouraged.

If iNurn> 3 Then sValue = 3.3 Else sValue = 4.1


Conditions can be combined by using And or Or and/or Not. For example

If sCelsiusTemp > 25 And sHumidily > 90 Then stWeather = 'Sticky"

End If

or

If sCelsiusTemp > 25 And Not sHumidily > 90 Then stWeather = "Hot"

End If

Brackets can be used where there could be ambiguity, e.g.,

If (iA = 10 and iB = 3) Or (iA = 5 and iB = 6) Then iAB = 30

End If

Select Case The If Eiself example above could also be written using the Select Case syntax as shown in Example 12.

Select Case is often very useful when dealing with strings as shown in Example 13. This example has been written as a function so it can be used as a user-defmed function in Excel.

Variations on the syntax are included in Help and include the following:

• Get a conversion factor depending on the units Select Case LCase(stUnit) • LCase converts stUnit to lower case

Case "feet". "foot", "ft" sFactor = 0.3048

Case "yard", "yd", "yards" sFactor = 0.9144

End Select

iNumber= 9 Select Case iNumber Case 1 To5

MsgBox "Between 1 and 5" Case 6, 7,8

MsgBox "Between 6 and 8" Case Is > 8 And iNumber < 11

MsgBox "Greater than 8" Case Else • Other values.

MsgBox "Not between 1 and 10" End Select

Once a true case has been found the statements after it are executed and then program then jumps to End Select. Thus if two cases are correct, only the flTst is executed.


r "

f .-

~

f • F

f •

I · I · I • if J

J , ~

1 I E

I 1 I , ~

• ~

J j

I iid "i}' /,;'

! ! !

! i i

Example 12: Select Case

Change the previous example by using Select Case instead of the If Then ... Endif block.

Select Case sCelsiusTemp Case Is < 15

stTemperature = "Cold" Case 15 to 25

stTemperature = 'Warm" Case Is >25

stTemperature = "Hot" Case Else

MsgBox "This shouldn't happen !!" End Select

Example 13: Select Case with strings

Select Case is often very useful when dealing with strings. This procedure has been written as a function for use in Excel.

Function ConverUo_metres(Unit As String, Length As Single) As Single

, Function to convert a length to metres. The naming prefixes are dropped , so that Unit and Length are sensible names in function wizard.

Dim sFactor As Single Select Case LCase(Unit)

Case "inch'\ flinches", "in" sFactor = 0.0254

, LCase converts the units to lower case

Case ''foot'', ''feef', "ft" sFactor = 0.3048

Case "yard". "yd". "yards" sFactor = 0.9144

Case "fathoms", ''fathom'' sFactor = 1.8288

Case Else sFactor = 0 MsgBox "Sorry, I don't know that unit"

End Select

Convert_to_metres = sFactor * Length , Recall that the result of the function has the same name as the function

End Function

Note the use of indentation in the programs. Lines of code within loops are indented to make them easier to read.


ForlNext Loops Sometimes we wish to repeat a calculation a number of times in a loop. A typical syntax is shown in Example 14.

The full syntax is For Counter = StartValue To EndValue [Step Incremenq

VB statements here Next [Counterj

e.g.,

For iLoop = 20 To 10 Step-1 VB statements here

Next iLoop

The statements between For and Next are repeated for each value of the Counter.

ill the example, the iLoop after Next is optional but can be useful when looking for the end of the loop and is strongly recommended. If no step is specified the default value of I is used.

After the For/Next loop is complete the value of the Counter will be one step more than the final value.

As an example we might wish to calculate the factorial of a number. n! = 1 x 2 x ... xn - 2x n -1 x n, e.g., 5! = 1 x 2 x 3 x 4 x 5 = 120

Example 15 shows how this can be programmed.

Nested For loops We can put a loop inside another as shown in Example 16. We can this "nesting".

Exiting It is possible to jump out of For/Next loop by using an Exit For statement. For example:

• These lines of program find the first zero element in the array.

For iLoop = 1 to 100 If iArray(iLoop) = 0 then

Exit For End If

Next MsgBox "The first zero element is number" & iLoop

24 An futroduction to Visual Basic for Engineers and Scientists

f I ,

\ t

f·

Example 14: ForlNext loops

Option Explicit

Sub ForNextO , This example shows how the For Next loop works

Dim i As Integer, iCounter As Integer, iNurn As Integer

For i = 1 To 5 MsgBox "i = " & i

Next i MsgBox "After the loop, i = " & i

iNurn = 9 For iCounter = iNurn To 1 Step-3

MsgBox "iCounter = " & iCounter Next iCounter

End Sub

, This line is repeated 5 times

'Why?

Example 15: Calculate the factorial of a number using a ForlNext loop

Sub MyFactorialO , Calculate the factorial of iNumber Dim iNumber As Integer, i As Integer Dim IngFactorial As Long iNumber= 10 IngFactorial = 1

For i = 1 To iNumber ' This loop is repeated sequentially IngFactorial = IngFactorial * i ' using each value of i from 1 to iNumber

Next i

MsgBox "The factorial of" & iNumber & " is" & IngFactorial End Sub

Example 16: Nested ForlNext loops

Sub NestedForO Dim irow As Integer, icol As Integer, iNumRows As Integer, iNumColumns As Integer

iNumRows =4 iNumColumns=3 For irow = 1 to iNurn Rows

For icol = 1 To iNumColumns ' This loop is nested inside the other msgbox irow & " x " & icol & " = " & irow*icol

Next icol Next irow End Sub


Debugging The term computer bug came literally from insects which used to be attracted to the warmth of old computers causing circuit failures. Today "bug" means program error.

VB has some useful tools for finding and removing bugs.

Desk checks for debugging One of the most effective ways to debug, or understand, a computer program is to pretend to be the computer and execute the program using a sheet of paper and a calculator if necessary.

Follow the program below use a ruler or piece of paper to cover the program and force yourselfto read just one line at a time as a computer would.

Each time you come across a Dim statement write the variable and its type as a heading in a variable table on a sheet of paper. Leave room underneath for it to have various values. Whenever a value is assigned to the variable write the value on the paper and cross out the previous value.

For example:

Sub DeskCheckO • Demo sub to show how a desk check might work

Dim iNurn as Integer, iCount as Integer. iTotal as Integer

iNurn =4 iTotal = 0 For iCount = 1 to iNurn

iTotal = iTotal + iCount Next iCount

End Sub

After the program has been "run" the variable table might look like this:

When carrying out a desk check you must never use a variable unless it has already been written on the paper (from the Dim statement) and you must never give it a value if the program has not. Jfyou need to use a variable but it has not been initialised then change the program so that it gets a value, e.g., iTotal = O.

Now try Example 17.

More debugging methods are given on page 32.


j II,

Example 17: A desk check for debugging

Cany out a desk check, by writing a variable table and filling out values, to predict the expected result of this program. Then copy and run the program.

Option Explicit

Sub DeskCheckTwoO Dim sX As Single, sTotal As Single, sAverage As Single Dim i As Integer

sTotal = 0 For i = 1 To 6

sX = (i 12) A 2 sTotal = sTotal + sX

Next i sAverage = sTotal/6 Msg80x sAverage End Sub

Without running the following program use a desk check to discover the three errors in it.

Option Explicit

Sub Deskcheck30 Dim iTotal As Integer, iColumn As Integer

For iColumn = 10 To 100 Step 10 For iRow = 5 To 40 Step 5

iTotal = iTotal + iColumn' iRow If iRow > 30 Then

iColumn = 10 End If

NextiRow Next iColumn

End Sub

27

.

Arrays Very often in programming we wish to store and use a table (matrix) of information. For example we might wish to have the list of names below:

Azuma Bywater Czerney Dzenis Ezendam Fyffe

The most effective way to do this is to define an array. Each item in the array is called an element, e.g., the first element is "Azuma". Its position is referred to as its index, e.g., the index ofDzenis is 4. It may be useful to think of an array as a set of pigeon holes in the computers memory, each with a number to identify it. In VB we define an array using

Dim variable(maximum index) As DataType

e.g., Dim stSurname(10) As String

This statement defines an array with II elements, from stSurname(O) to stSurname{lO), each containing a string variable. We see here the origin of the term Dim which is short for dimension.

We could put information into the array using lines like: stSurname(O) = "Azuma" stSurname(1) = "Bywater" etc

Note that the array "index" starts at zero. This is a feature of many computer languages. Ifwe didn't want to start at element zero but instead at element one we could define the array as

Dim stSurname(1 to 10) as String

The general form of the syntax is Dim Arrayname ([Lowerlndex To] Upperlndex) [as Datatype]

The easier way of always starting with element I (rather than element 0) is to add the line Option Base 1 at the top of the program module (under Option Explicit).

Another way to get data into an array is to use the Array statement as shown in Example 19. It is more likely that we will read the array from a file or generate it within the program. More on that and the use of arrays later.


~I

{.

'. I· ,

Jl.' . , 1

Example 18: Using arrays

Option Explicit Option Base 1 'The first element of each array is element 1 rather than element 0

Sub ArrayDemoO , Program to calculate the average of an array of marks

Dim sMarks(4) As Single Dim sTatal As Single, sAverage As Single Dim iNumMarks As Integer, i As Integer

sMarks(1) = 54.5 sMarks(2) = 65.5 sMarks(3) = 34.4 sMarks(4) = 76.7 iNumMarks = 4

, Find the average mark. sTatal = 0 For i = 1 To iNumMarks

sTatal = sTatal + sMarks(i) Nexti

sAverage = sTatal / iNumMarks MsgBox "The average mark is " & sAverage End Sub

Example 19: The Array statement Another way to get data into an array is to use the Array statement, e.g.,

Option Base 1 Sub ArrayTestO Dim vDay As Variant' Note that the size is not specified here and that the type is Variant

vDay = Array("Monday", "Tuesday", "Wednesday", "Thursday", _ "Friday", "Saturday", "Sunday")

MsgBox stDay(3)

End Sub

Here we see the continuation character. To continue a statement onto another line we fmish the line with a space and an underscore _

29

Any data type can be specified in arrays. Arrays can be multidimensional. We might want to represent a table of rows and columns, e.g., to store a set of marks

Test I 55.2 45.0 87.5 24.1

To declare the array we can use:

Dim sMarks(1 to 4, 1 to 3) as Single

or if Option Base 1 is used

Dim sMarks(4, 3) as Single

Test 2 64

52.5 92 o

Test 3 73

49.5 99 35

It is normal convention for the first index to be the row number and the second to be the column number.

An array can have any number of dimensions but the overall size will be limited by the memory ofthe computer. The array sBigArray(200,200,200) will have 8x10' elements each using 4 bytes giving a total memory requirement of32 Megabytes.

To get information into an array we can assign items individually, e.g., sMarks( 1,1) = 55.2 sMarks(1 ,2) = 64.0

but this can be tedious. Normally data will be read into arrays from elsewhere.

For/Next loops are very useful when dealing with array elements. Considering the example of test marks.

Test 1 55.2 45.0 87.5 24.1

Test 2 64

52.5 92 o

Test 3 73

49.5 99 35

We can use a For/Next Loop to find out averages as in Example 20.


I

~ 'OIl

Example 20: Two-dimensional arrays

Option Explicit

Sub AveragesO , Program to calculate average test marks in a 2 dimensional array of marks

Dim sMarks(4, 3) As Single Dim irow As Integer, icol As Integer, iNumStudents As Integer, iNumTests As Integer Dim sTotal As Single, sAverage As Single

, the colon: is used to put multiple statements on one line. It should not be used often sMarks(1, 1) = 55.2: sMarks(1, 2) = 64: sMarks(1, 3) = 73 sMarks(2, 1) = 45: sMarks(2, 2) = 52.5: sMarks(2, 3) = 49.5 sMarks(3, 1) = 87.5: sMarks(3, 2) = 92: sMarks(3, 3) = 99 sMarks(4, 1) = 24.1: sMarks(4, 2) = 0: sMarks(4, 3) = 35

iNumTests = 3 iNumStudents = 4

For icol = 1 To iNumTests sTotal = 0 For irow = 1 To iNumStudents

sTotal = sToia I + sMarks(irow, icol) Nextirow sAverage = sTotal! iNumStudents Msg80x "The average mark for test" & icol & " was" & sAverage

Next icol

End Sub

Exercise: change the program to calculate the average test mark for each student.

31

More Debugging

Breakpoints The most effective means of seeing what goes on in a program is to insert a breakpoint. To do this, position the cursor on the line you want to the program to stop at. Select Debug, Toggle Breakpoint (F9) to set the breakpoint at that line. Then run the program as usual.

The program will pause before it executes the breakpoint line. The Locals Window might be automatically displayed or you can view it using View, Locals Window.

When the program is in break mode you can fmd the value of a variable by putting the mouse cursor on top of the variable anywhere in the program.

Stepping through a program The debug tool Step fito (select Tools, Step fito, shortcut F8) can be used to step through a program line by line. It may be useful to have the Locals Window open, then press F8 repeatedly to see what happens.

Debug.Print You can print values out during execution using:

Debug.Print slnputNumber

Values are printed to the Immediate Window which you can view using View, lmn)ediate Window as shown in Example 22.

MsgBox Another option is to print values in a message box using MsgBox as seen previously in Example 4.

At the same time as checking what the computer is doing you should carry out a desk check on a piece of paper to calculate what you think values should be.

interrupting a program If a VB program gets into an infinite loop or locks up you can interrupt it by using Ctrl <Break> (break is on the Pause key), or if it is rurming from Excel, Alt F4 (Exit task) might sometimes stop it.

Waming. If a function has an infinite loop in it and you test it using fisert Function in Excel it can be impossible to stop except by Control Alt Delete. Test functions by formula directly into an Excel cell rather than by using fisert Function. Save functions, and any other open files, before testing them.

For other debug methods have a look at the Debug menu together with Help in VBA.


~'. ~.

l' Example 21: Debugging with Breakpoints and the Locals Window

Write the program as shown. Insert a Breakpoint at the Next i line. Run the program.

The Locals Window shows the values of the variables. Click on G sX to see the contents of the array sX. Continuing running the program (F5 or ~) and see how the values change.

i

Sub DebuggerO , Program to assign numbers to an array and sum them Dim i As Integer, sX(5) As Single, S Total As Single

5Total=0 Fori = 1 T05

sX(i)=i'2 sTotal = sTotal + sX(i)

!Y~~£,il~3a,~eiG~§:f~,OCq:!~SMI~;w~~1i!1 Msg80x sTatal End Sub

EEl sX sTatal

Integer

5lngle(1 to 5)

Single

Example 22: Debugging with ~~~!!!~ _________ _ Change the program above by removing the breakpoint (F9 again) and close the Locals Window then include the line Debug.Print. Select View, Immediate Window and run the program.

The Debug.Print command prints the variables into the Immediate window.

sTatal = 0 Fori=1To5

sX(i) = i '2 sTotal = sTotal+ sX(i) Debug.Print "sTotal", sTolal

Next i

sTotal sTotal sTotal sTotal

5 14 30 55

33

Do While and Do Until Loops We use a For/Next loop when we know how many times a loop needs to be repeated.

We use a Do Loop when we don't know how many times a loop needs to be repeated but instead there is a condition that needs to be met before it stops looping. Try Example 23.

A DolLoop can have the structure

Do Until condition statements

Loop

When the program gets to the Loop statement it returns to the Do line if the condition has not been met. Otherwise it continues to the line after the Loop statement.

We can use Do While and Do Until in three other ways:

Do statements

Loop Until condition

Do While condition statements

Loop

Do statements

Loop While condition

All of these can be made equivalent but at times one in particular may give a more logical program flow.

As an example of a Do Loop, if we wish to add up the series

l+z+z2+z3+ ... forz<1

we do not know how many terms are required. If we are using double precision we will want to add on terms until the last term is less than about 10.15

• (Once the terms are smaller than this they will make no difference to the answer). This can be done with a Do Until loop, e.g.,

Do calculate and add on the next term

Until the next term is less than 10.15 (or until there have been too many terms)

Written in VB it might be as shown in Example 24. Notice how the fIrst Do Loop checks the input.


F r 1

~I '!:

~I

Example 23: Do Until and Do While loops

Option Explicit

Sub DoUntilExampleO Dim i As Integer, sX As Single, iCount As Integer i = 0 iCount= 0

sX = 10 Do Until sX < 1

MsgBox "Test 1: "& sX sX=sX/2

Loop

sX= 10 Do

MsgBox ''Test 2: "& sX sX = sXl2

Loop While sX >=1

sX= 10 Do While Sqr(sX) >= 2

MsgBox ''Test 3: "& sX sX = sX - 2.1

Loop

End Sub

Example 24: Sum a series using a Do Until loop

Sub DoUntilO , Program to sum the series 1 + z + zl\2 + zl\3 + .... Dim dZ As Double, dTotal As Double, dNewTerm As Double Dim i As Integer Dim blnputOK As Boolean

blnputOK = False Do Until blnputOK = True

dZ = InputBox("Please enter a positive number < 1 ") If dZ >= 0 And dZ < 1 Then blnputOK = True

Loop dTotal = 1 i = 1 Do

dNewTerm = dZ 1\ i i = i + 1 dTotal = dTotal + dNewTerm

Loop Until dNewTerm < 0.000000000000001 Or i > 10000

MsgBox "The sum of the series = " & dTotal End Sub

35

Sometimes a series can be calculated more efficiently than shown in Example 24. For example the exponential, eZ can be calculated from a Maclaurin Series

Z2 Z3 eZ =1+z+-+-+ ...

2! 3! The series should be continued until the term zn/n! is smaller than the required error in the calculation.

We notice that we can write this as i

Z I Z e= - .,

i=O,OXI I.

and to get the ith term from the previous one all we need to do is multiply the previous term by zli.

We thus get a series teIlllo = 1 termr = teIlllo x zll term2 = termr x zl2 term3 = term2 x z13, etc ....

This can be programmed much more efficiently than calculating z'li! each time as shown in Example 25. Before you run the program carry out a desk check for the first few terms so you get a clear idea of what it does.

The next example also used the random number generator. If you have two dice and throw one, how many throws of the other are required before you get one to match?

If we were to express this in English we would write

Roll one die - check and record the number Roll another die Repeat this last step until both numbers are the same

or in "pseudocode" Roll one die Record number! Repeat

Roll the other die Record number2

Loop until number2 = number 1

or in VBA using simulated die

iDie1 = Int(RndO * 6) + 1 • RndO returns a random number between 0 and 1. Do

iDie2 = Int(RndO * 6) + 1 Loop Until iDie2 = iDie1


t ! 1 1 !

Example 25: MacLaurin series using a Do Until loop

Sub MacLaurinO

Dim iLoop As Integer Dim dTerm As Double, dExpZ as Double, dError as Double, dZ As Double ,

, This procedure calculates an exponential of a number using a Maclaurin series. , el\z = 1 + z + z"212! + z"3/3! ...

dZ = Val(lnputBox("Please enter a number")) dTerm = 1 dExpZ = 1 iLoop = 1 dError = 0.00000000000001

Do dTerm = dTerm • dZ I iLoop dExpZ = dExpZ + dTerm iLoop = iLoop + 1

Loop Until Abs(dTerm) < dError or iLoop > 10000

MsgBox ("The exponent of" & dZ & " is" & dExpZ)

End Sub

Example 26: Dice throwing using a Do Until loop

Sub DiceO , A Do Loop to simulate the roll of dice until both dice are the same Dim iDie1 As Integer, iDie2 As Integer

iDie1 = Int(RndO • 6) + 1 ' RndO returns a random number between 0 and 1. MsgBox "The first of the dice is a " & iDie1 Do

iDie2 = Int(RndO • 6) + 1 MsgBox "The other die is a " & iDie2

Loop Until iDie2 = iDie1 MsgBox ''Well done. That's the same as the first"

End Sub

37

Goto A Goto statement can be used to jump to another line in a procedure. The normal syntax is

Goto LineLabel

LineLabel: • Note the colon after the line label name

Nearly always Goto statements indicate poor programming. There are much better ways of structuring programs, e.g., by using subroutines (see page 64) or with a Do Loop.

Goto statements are unlikely to appear in good programs except/or error trapping.

Error Trapping Ifpossible programs should be written so that errors do not occur. Sometimes we want to trap errors and this can be done using the On Error Goto command together with the Resume command which cancels the error trap (Example 27).

The line On Error GoTo ErrorHandler

instructs the program to go to the line labelled ErrorHandler (or any other name) when a program error occurs during execution.

The Resume statement in the line Resume Retry

resets the error conditions and continues execution from the line labelled Retry (or any other name).

The error handling lines are normally placed at the end of the program and the Exit Sub command acts like an End Sub command.

If possible error trapping should be done more elegantly as shown in Example 28.

Exit The Exit statement used in different forms to exit from a loop or a procedure before all of the statements have been executed.

Exit Do Exit For Exit Sub Exit Function

see Example 28 similar to Exit Do but in a For Next loop see Example 27 similar to Exit Sub but in a function

Exit statements are not always the most elegant to use in a program. Often an If Then Else structure may be better.

38 An Introduction to. Visual Basic for Engineers and Scientists

~:

Example 27: Error trapping

Option Explicit

Sub ErrorHandlerO Dim sX As Single, sY As Single On Error GoTo ErrorHandler

Retry: ' This line is labelled Retry

, go to the line labelled ErrorHandler:

sX = InputBox("I'1i calculate 1 O/x. Enter a value for x. First try 0 or letters ") sY=10/sX MsgBox "The answer is" & sY Exit Sub 'Exit here and do not execute the error code

ErrorHandler: MsgBox "An error occurred - " & Err.Description MsgBox "Try Again" Resume Retry , Resume execution from the line labelled Retry:

End Sub

Example 28: A more specific error handler

Option Explicit

Sub ErrorHandler20 , This program checks for all known inputs that could cause an error Dim sX As Single, sY As Single Dim stinput As String, blnputOK as Boolean

Do stlnput = InputBox("I'1i calculate 1 O/x. Enter a value for x. First try 0 or letters ") If IsNumeric(stlnput) Then' check that the input is numeric

sX = Val(stinput) , convert a string into a number If sX <> 0 Then ' check that it is not zero

Exit Do ' If OK exit the Do loop and continue after Loop End If

End If MsgBox "The input must be numeric and not 0"

Loop sY= 10/sX MsgBox "The answer is " & sY End Sub

What happens if you enter lE200 (102oo)? Perhaps a general error trap is still required.

39

String Operations

There are many useful functions for processing text strings. Most of these are the same as in Excel.

Function Returns Instr([StartingPositionj,String, A numeric value that is the position within the string where the SubString) substring begins; returns 0 if the substring is not found. lCase( Strinq) The string converted to lower case left(String, NumberofChars) The left most characters of the string for the indicated number of

characters len( String) A numeric count of the number of characters in the string -Mid(String,StartingPosition, [NumberOfChars]l

A substring taken from the string, beginning at the StartingPosition for the specified length

Right( String, The right most characters of the string for the indicated number of NumberOfChars) characters Space(NumberOfChars) A string of blank spaces for the specified number of characters Str(NumericExpression) The numerical expression represented as a string type String(NumberOfChars, Strinai

A string of the named characters for a length of the specified number of characters

Trim( String) Removes leading and following spaces UCase( String) The string converted to upper case Val( Strinq) The number value of a string expression

As examples stString = "Good morning everyone" stNewString = left(stString,4) , returns "Good" stNewString = Mid(stString,7,4) , returns "orni" stNewString = Right(stString,2) , returns "ne" stNewString = Trim(" Some gaps ")' returns "Some gaps" iPosition = Instr(stString,"nin") , returns 9

Concatenating Text

Strings and numbers can be combined into a single string using the ampersand (&) symbol

iNurn=20 stString = "Good morning" stNewString = stString & iNurn & " people" , returns "Good morning 20 people"

Always insert a space before and after &.

Option Compare Text The command Option Compare Text when included at the top of a module will cause the program to ignore the case of a string when making string comparisons, so, e.g.,

"StRinG" = "sTring" becomes True.

-


[ ~

t r

I I

i •

r

i ! , I

I I

~,

! Example 29: String operations

Option Explicit Option Base 1

Sub StringOperationsO • This program uses a variety of string functions

Dim stName1 As String, stLine As String Dim stArray(10) As String Dim iComma1 As Integer, iComma2 As Integer, iCount As Integer Dim i As Integer

stLine = "Dog, Doggy, Cat, Horse, Mouse, Snake, Student"

• Put the different animals into an array iComma1 = 0 'position of first comma iCount = 0 ' number of items found Do

iComma2 = InStr(iComma1 + 1, stLine, ",") 'Find position of next comma , If no comma is found iComma2 is set to 0

If iComma2 <> 0 Then iCount = iCount + 1 stName1 = Mid(stLine, iComma1 + 1, iComma2 - iComma1 - 1) 'extract substring stName1 = Trim(stName1) • Trim off blanks before and after the name stArray(iCount) = stName1 iComma1 = iComma2

Elself iComma 1 > 1 Then ' end of the string as iComma=O but comma was found before iCount = iCount + 1 stName1 = Mid(stLine, iComma1 + 1) stName1 = Trim(stName1) 'Trim off blanks before and after the name stArray(iCount) = stName1

End If Loop Until iComma2 = 0

, Give an appropriate message For i = 1 To iCount

Select Case Left(UCase(stArray(i)), 2) , Find the left 2 characters and make upper case Case "DO"

MsgBox 'Woof" & stArray(i) Case "CA"

MsgBox "Meow" & stArray(i) End Select

Next i End Sub

An alternative syntax uses the Split function to break up a string. The code is much more compact.

Dim vArray As Variant, vSubstring As Variant vArray = Split(stLine, ",") For Each vSubstring In vArray

Debug.Print vSubstring Next

41

I

File Operations Often the most effective ways of getting information into and out of a program is to read and write it from and to a file. Using VB we can read and write text files (also known as ASCII files) but we cannot read Word or Excel files directly. To read or write a file we need to open it first

Open Filename for {Input I Output I Append I Random} as #Filenumber

As examples

Open "c:\mydocs\engr250.dat" for Input as #1 Open "f:\engr250\results. txt" for Output as #2

The file number can be anything up to 255. When a file is opened an imaginary pointer (cursor) is positioned at the start of the file (i.e., in the top left comer).

If you want to produce text files to be read into VB, use Notepad to write and save the file, or use Word and save as type "text only". Saving as text from Excel does not produce a good text file.

We can read or write from or to the text file using the commands Input, Write or Print. These commands cannot be used with Word or Excel files. To be readable the variables in the text file must be separated by commas andlor be on separate lines. For example if a file may contains data as shown:

9034534,Jones,K,H 9102322, Smith, J, A 9018643, Brown, A, J 9400012, Morison, K, R

We can read this with the Input command

Input #1, InglDNumber, stSumame, stlnitial1, stlnitial2

The number of variables in the Input statement will usually correspond to the number of variables on each line, but this does not need to be the case. The #1 refers to the file number corresponding to the Open command.

Whenever there is an Input instruction to read a variable, the file is read from the position of the pointer to the next comma or to the end of the line. The pointer is then moved to that next comma or onto the next line.

The command Line Input can be used to read an entire line. This can be useful when trying to find particular lines within a file.

Line Input #1, stLine . read the entire line into the string variable stLine

Often we do not know how long a file is as the number oflines may depend on the number of data points recorded. The End-of-File (EOF) function is very useful especially when used with a Do Loop as seen below.


! ! ! !

Before running examples 29 to 32 you will need to create some data files. Use Notepad (under Start, Programs, Accessories on most computers). Enter the data as shown in the boxes in the examples and save as:

c:\vba\words.txt c:\vba\students.txt c:\vba\results.txt

Example 30: Reading from text files

Sub ReadWordsO , File read example Dim stWord As String, stAnotherWord As String

Close 'This is a trick in case my program failed last time before the dose at the end. Open "c:\vba\words.txt" For Input As #1

Input #1, stWord 'read a word MsgBox stWord Input #1, stWord MsgBox stWord Input #1, stWord, stAnotherWord ' read 2 words MsgBox stWord & " " & stAnotherWord Input #1, stWord, stAnotherWord MsgBox stWord & " " & stAnotherWord Input #1, stWord, stAnotherWord MsgBox stWord & " " & stAnotherWord Input #1, stWord ' This might cause an error MsgBox stWord

Close 'close the files End Sub

Example 31: Reading in a loop

Input file: words.txt

word 1 , word2, word3 word4, word5 word6, word7 wordS

The last example is much tidier within a Do Loop. We use the function EOF(), end of file to detect when we get to the end of the file.

Sub ReadLoopO , File read using a Do Loop Dim stWord As String

Close Open "c:\vba\words.txt" For Input As #1 Do Until EOF(1)

Input #1, stWord 'read a word MsgBox stWord

Loop

Close 'close the files End Sub

43

Do Until EOF(1) Input #1, etc

Loop

These statements get the computer to continue reading from file #1 until the end of file #1 is reached.

The syntax for Write and Print are

Write #2, stlnitial1; stlnitial2; stSurname Print #2, stlnitial1; " "; stlnitial2; " "; stSurname

The difference between Write and Print is that Write produces a standard output whidrcan always be read in again using an Input command, but Print gives more flexibility in formatting.

If file #1 was the file students.txt shown in Example 32 the Write statement would give us "K","H",IIJones" "J" ,"A", "Smith" "A","J", "Brown" "KII,IIR", "Morison"

and the Print statement gives the file mydoc.txt as shown in Example 32.

Ifwe Open a file for Output any previous data in the file will be overwritten. The Open for Append command is useful as it appends data to the end of an existing file, e.g.,

Open "c:\vba\results.txt" for Append as #2

Open for Append can be very useful in programs that run for a long time, e.g., for datalogging. When data is to be written, the file can be opened for append, written to and closed and then data will not be lost if the program is interrupted for any reason.

Close. At the end of the program or when files are no longer required the Close command should be used to close the files. A single file can be closed by specifYing the file to close, e.g., Close(2) which closes file #2.

Selecting Files. If the name of the file is unknown the following syntax can be used to obtain a filename via the file manager.

ChDrive "a:" ChDir"\" stFile = Application.GetOpenFilename("AII files (*.*), *.*")

Open stFile For Input As #1

Input #1, stName etc. etc.

Other file formats can be specified in the brackets, e.g., ("My Data Files (* .dat),")


l

F I ,-

r ,

r ,-

]

f;J

iJ

I

I

Example 32: Reading and writing text files

Option Explicit

Sub FileReadWriteO , File read and write example Dim InglDNumber As Long Dim stSurname As String, stlnitial1 As String Dim stlnitial2 As String

Input file: studeuts.txt

9034534,Jones,K,H 9102322, Smith, J, A 9018643, Brown, A, J 9400012, Morison, K, R

Close 'Close the file in case my progra ailed before the close at the end. Open "c:\vba\students.txt" For Input As 1 Open "c:\vba\myfile. txt" For Output As

Do Until EOF(1) 'do the following statements un' e end 0.f;..f!!,il~e-,!#..!1 _______ ~ Input #1, InglDNumber, stSurname, stlnitial1, stlnitial2 Print #2, stlnitial1; " "; stlnitial2; ""; stSurname MsgBox stlnitial1 & " " & stlnitial2 & " " & stSurname

Loop

Close 'close the files

End Sub

Example 33: Reading into arrays

Output file: myfile.txt

K H Jones J A Smith AJ Brown K R Morison

Programs read a file from the top left hand comer. They read a number or a string up to the next comma or to the end of the line.

Sub ReadArrayO

Dim sNumbers(10, 3) As Single 'provide more rows for a larger class Dim irow As Integer, icol As Integer Dim stNames(1 0) As String, stLine As String Input file: results.txt Open "c:\vba\results.txt" For Input As #1

Line Input #1, stLine , Read the header line irow= 0 Do Until EOF(1) 'We might not know the no. of students

irow = irow + 1 Input #1, stNames(irow) Foricol = 1 To 3

Test results for 1999 Smith, 55.2, 64, 73 Brown, 45.0, 52.5, 49.5 Jones, 87.5,92,99 Morison, 24.1, 0, 35

, read the name first ~_iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii_iiiiiiii~

Input #1, sNumbers(irow, icol) 'read each of the three marks Next icol

Loop Close #1 End Sub

45

4 Objects and Excel

Objects Visual Basic is an object-oriented program altbough it doesn't have all the object orientation that some languages do. It uses objects extensively especially when working witb Excel, Word and Access.

To get a better understanding of objects let's consider a simple object, a car.

A car is an Object. The car contains otber objects and has a variety of attributes, some of which we call Properties, while others are Methods, and Events.

Otber objects witbin a car include Doors Engine.. Lights

For tbe object Car we might have the properties Make EngineSize Colour (Color in Excel)

we might have methods such as Clean ChangeOil FillWithPetrol

and events might include Started Moved Stopped

In most object oriented programs we can express the information as Car.Engine Car.Make Car.Clean

And we could write Car.Make = "Ford"

or YourSize = Car.EngineSize

We could have a collection of cars and might write, e.g., Cars(l ).Make which means tbe Make of car I Cars(3).Clean which means clean car 3 Cars(2).FillWitbPetrol which means fill car 2 with petrol

Generally tbe objects tbat we use in VB are already set up for us, e.g., the objects in Excel as explained later.

One simple way to define tbe structure of an object is to define a special Type as shown in Example 34. The Type statement must come in the top of tbe module before any Sub statements.

(To define an object with methods requires a new Class Module but this is not covered in this book, nor in Microsoft Visual Basic Help.)

We can have objects of objects, or collections within collections, e.g., Cars(l).Doors("Lefthandback").Colour = "Blue" Cars(J).Doors("Leftbandfront").Colour = "Red"

46 An Introduction to Visual Basic for Engineers and Scientists I

t ! !

Example 34: Define a simple object type


Type Car stMake As String stModel As String stColour As String iSize As Integer

End Type

Sub ObjectTestO , Program to demonstrate the use of user defined Types 'Written by Ken Morison, 17 Feb 2000 Dim Cars(3) As Car 'create a collection of 3 cars type car

Cars(1 ).stColour = "Red" Cars(1 ).iSize = 1500 Cars( 1 ).stMake = "Nissan" Cars( 1 ).stModel = "Sentra" Cars(2).stColour = "blue" Cars(2).iSize = 2000 Cars(3).stMake = "Holden" End Sub

Select Debug, Toggle, Breakpoint (F9) to put a breakpoint on the last line, then View, Locals Window to see the structure produced.

This type of program can be very useful for setting up a specialised database using on VB.

Objects are not necessarily simple or uniquely defined. There can be many ways of referring to objects depending on their relationship with other objects. Consider a family and their attributes. We could write

Father("Hugh").Son("Iohn").Daughter("Heather").Husband = "Ian"

We might also write

Person( "Iohn").Daughter = "Heather" Husband("Ian").Wife = "Heather"

Here we see that one object can be expressed is different ways. It depends on the context we are in. The same applies in Excel.

47

Working with Excel Excel objects From the point of view of VBA, Excel is a large collection of objects with properties and methods. The entire "object model" contains all the attributes of Excel so is very large. Part of the Excel object model is shown in Microsoft Visual Basic Help, Answer Wizard, Microsoft Excel Objects but this is unlikely to be useful to a novice programmer.

The "grandparent" object for VBA is the Application. For VBA with Excel the Application is Microsoft Excel.

The Application has many child objects, but for us only two are important, the collection of open Workbooks and the group of WorkSheetFunctions.

If, e.g., we have open a workbook called "Mybook.xls" it could be referred to as:

Application.Workbooks("MyBook.xls")

Application

Workbooks

WorkSheetFunctions

but VBA nearly always knows that the Application is Microsoft Excel so we don't need to include "Application". Just Workbooks("MyBook.xls") is enough to refer to the workbook.

Within each workbook we will have a collection of worksheets and collections of other objects. The useful objects within each Workbook are shown. The collection of charts here are those that exist as chart sheets, not those within sheets.

Within each worksheet we have a collection of cells that is referred

Workbooks

Worksheets

Charts

Names

to as the Range object. The collection of ChartObjects includes the Worksheets charts that exist on the worksheet.

To refer to the cell, "C4" in worksheet "Sheet!" in the workbook "VBExamples.xls" the full name would be:

Range

ChartsObjects

WorkbooksC'VBExamples.xls").Worksheets("Sheet1").Range("C4")

Because you need to refer to the workbook and worksheet names, you should save the Excel workbook, and possibly change the worksheet names with their final names before you start programming. The workbook and worksheet names do not change automatically in VBA when they are changed in Excel.

To assign a value to a range, or to get a value ofa range, we use the Value property Workbooks("MyBook.xls").workSheets("Sheet1").Range("A 1 ").value = 1

Examples of this are given in Example 35.


. I •

f

t

I

~ iy

~ ~! ~

Example 35: Reading and writing data in Excel worksheets

Option Explicit Sub WorksheetExamplesO , For this example to work you need to have a file called MyBook.xls open Dim iRow As Integer, iCol As Integer Dim sX As Single, dX As Double, dY As Double

sX= 1.234 dX = 1.234 'Write values into cells Workbooks(OOMybook:XlsOO).worksheets(OOSheet1 OO).Range(OOA 1°O).Value = ooTest SheetOO

Workbooks(OOMybook.xlsOO).Worksheets(OOSheet1°O).Range(OOA3°O).Value = sX Workbooks(OOMybook.xlsOO). Worksheets(OOSheet1 00). Range(OOA4°O). Value = dX

, Get a value from a cell dY = Workbooks(OOMybook.xlsOO).worksheets(OOSheet1°O).Range(OOA3°O).Value MsgBoxdY

End Sub

Do you see the difference between the results in cells A3 and A4 ofMyBook.xls? Click on each to see their full value in the formula bar. This shows the difference between single and double precision. Excel works in double precision.

A useful part of the Application object is the collection of Worksheet Functions. This gives us access to nearly all of Excels functions even though they are not a standard part of Visual Basic. Excel has functions such as Max (maximum), LoglO, Pi and many more. To find a complete list use Microsoft Visual Basic Help, Answer Wizard and enter ooworksheet functions oo. See Example 36.

Example 36: Using the WorksheetFunctions

Option Explicit

Sub ExcelFunctionsO , This program shows two examples of worksheet functions

Dim sX As Single, sY As Single, dPi As Double sX= 2 sY = WorksheetFunction.Log10(sX) MsgBox sY dPi = WorksheetFunction.PiO MsgBoxdPi

End Sub

49

A very useful property of an Excel range is the Cells property. We can refer to cells relative to a particular cell by adding .Cells(row, column), e.g.,

Workbooks("MyBook.xls").WorkSheets("Sheet1").Range("C4").Cells(2, 1 ).Value = 1

This says that the cell at row 2 and column I relative to cell C4 (i.e., cell C5) has a value 1.

Other examples are shown here.

. . .Range("B3"). Cells(1 ,1)

;Ef ....... - ................ J-........::!;;;;;::l===::t- .. .Range("B3").Cells(1,3)

==-J~~j~=~§:::?~;~~ ... Range("B3").Cells(4,3)

=: ... Range("B3"). Cells(3, 1)

Try Example 37. Notice that in the fourth last line .value has been omitted. The default property is .value so it is not required.

The With block The With I End With syntax can make programs much tidier. Instead of typing in the full name of a cell several times we use a With statement, but there is a dot before Range.

With Workbooks("Mybook.xls").Worksheets("Sheet1") .Range("B2").Value = 3 .Range("C2").value = "metres"

End With

which we might read as "Using Sheet! in Mybook.xls ... ",

"the value of cell B2 is 3" "the value of cell C2 is metres"

The dot at the start of any line within the With block tells the computer to use whatever was specified by the With statement.

With blocks can be nested if necessary.


'I' , ,

;; i

J ;r ~j

~

[

:k \ ~!;

II~ , ~~

Example 37: The Cells property

Option Explicit

Sub UsingCelisO Dim dX As Double, dY As Double Dim iRow As Integer, iCol As Integer

, In this example the workbook is not specified so the active workbook is used.

, Insert the word Hello in the cell in row 2 and column 1 relative to cell G2 Worksheets("Sheet1").Range("G2").Cells(2, 1).Value = "Hello"

, Create a table of numbers 5 rows by 4 columns starting at cell B4 For iRow = 1 To 5

For iCol = 1 To 4 Worksheets("Sheet1").Range("B4").Cells(iRow, iCol) = iRow * 10 + iCol

Next iCol NextiRow

End Sub

Example 38 The With / End With structure

Sub WithExampleO Dim iRow As Integer, iCol As Integer Dim dX As Qouble dX = 1.234

, Write values into cells With Workbooks("Mybook.xls").Worksheets("Sheet1")

.Range("A11").value = "Test Sheet"

.Range("A14").Value = dX For iRow = 1 To 5

For iCol = 1 To 3 .Range("B13").Cells(iRow, iCol).value = iRow*10 + iCol

NextiCol NextiRow

End With

End Sub

51

Let's consider an example where we want to build up a table in Excel of function values. Sometimes the value is the result of any entire spreadsheet calculation, but we wish to obtain a set of results for different parameters.

Say instead of a complex function we use a simple example,./tx,y) = 3x2 + l + xy, and we want a table as shown below for the selected values of x and y.

In Excel we could insert the formula directly into the cells in the table, but if the function is much more complex this might not be possible.

Instead we might set up a spreadsheet as shown below with any initial values for x, y and with the formula for./tx,y). Then we could copy anx value and ay value from the table headers into the x andy cells then copy the calculated value of./tx,y) back.

The algorithm might then be:

For each combination of x and y values: copy an x value from range 04:07 to 83 copy a y value from range E3:H3 to 84 calculate f(x,y) in 85 copy the result 85 to the appropriate pOSition in table E4:H7

The program to do this could be as shown in Example 39

Creating range objects Sometimes it's tidier to create a range object variable that can be used instead of the full name ofa cell. This is very useful if we wish to refer to the same time from different parts ofa program. For example, instead of:

Workbooks("My800k.xls").WorkSheets("Sheet1").Range("C4").Value = 1 Workbooks("My800k.xls").WorkSheets("Sheet1").Range("E6").Value = 32

we can write Dim rngHomeCell as Range Set rngHomeCell = Workbooks("My800k.xls").workSheets("Sheet1").Range("C4") rngHomeCell.Value = 1 rngHomeCell.Value = 32

Here mgHomeCelI is an object that has all the attributes of cell C4. The keyword "Set" is required to assign a name to an object. Example 40 gives two examples.


• J ,

I ~

, , ! t

n ! " ., Ii

i :r· . , , ,

f? ~

Example 39: Producing a table of function values

To get this program to work you will need to create an Excel workbook called FunctionTable.xls that is set up as shown on the left.

Option Explicit

Sub FunctionTableO • This program produces a table of function values from a function in a spreadsheet

Dim iRow As Integer, iCol As Integer Dim sXvalue As Single, sYvalue As Single, sResult As Single

, Turn off cales to prevent time wasting calculations Application.Calculation = xlCalculationManual

With Workbooks("Function T able.xls"). Worksheets("Sheet1") For iRow = 1 T04

For iCol = 1 To 4 sXvalue = .Range("D3").Cells(iRow + 1, 1).Value • Get x value sYvalue = .Range("D3").Cells(1, iCol + 1 ).value • Get y value .Range("B3").value = sXvalue • Put into x cell .Range("B4").Value = sYvalue ' Put into y cell .Calculate • Calculate the sheet .Range("E4").Cells(iRow, iCol).Value = .Range("BS").Value· Put result into table

Next iCol NextiRow

End With Application.Calculation = xlCalculationAutomatic End Sub

Example 40: Using a Range object Sometimes it is tidier to declare a variable of range type as shown below

Sub RngExamplesO • This example uses object variables of type Range Dim iRow As Integer, iCol As Integer, dX As Double Dim rngHeader As Range, rngTable As Range

Set mgHeader = Workbooks("Mybook.xls").worksheets("Sheet1").Range("A 1") Set rngTable = Workbooks("Mybook.xls").worksheets("Sheet1 ").Range("A4")

rngHeader.value = ''Test Sheet"

For iRow = 1 To S ForiCol = 1 To 3

dX = iRow + iCol/10 rngTable.Cells(iRow, iCol).value = dX

Next iCol NextiRow End Sub

53

Most objects in Excel have properties and methods. Useful properties and methods of the "Application" include:

ActiveCeIl ActiveChart ActiveSheet ActiveWorkBook Calculate

Example 41 Example 39 Example 39 Example 41 Example 41

Calculation GetOpenFilename GetSaveAsFilename This Workbook (The workbook in which the VB program resides)

The full syntax for each of these is, for example, Application.~ctiveCeIl but, because the context is known to be Excel, "Application." can be omitted for some properties and methods but not the final three.

Useful methods of workbooks include: Example 41 Example 41 Example 41

Workbooks.Add Workbooks.Close Workbooks. Open Workbooks.OpenText (Open a text file as a workbook)

There are numerous properties of Ranges. They include:

Property Example Cells Specifies cells within a range by Range("F I 0:J20").Cells(2,3). Value

cell(row, column) relative to range gives value of cell HII. Column returns the number of the first iRangecol = Range("D I 0:F20").Column

column of the range gives a value of 4 Column Width returns or sets the width in points Example 43 Count return the count of cells or other Range("AI :E4").Cells.Count equals 20

objects in the range Font Sets the font Example 43 Formula Sets a formula Example 43 HorizontaiAlignment Sets or returns Alignment Range("A4:G4").HorizontaIAlignment=xICenter NumberFormat Sets or returns number format Example 43 Row similar to Column RowHeight Set or return row height in points Range("AI ").RowHeight = 20 Text returns contents of cell as text stCeIlValue - Range("E5").Text Value Sets or returns value of cell Example 35 VerticaiAlignment similar to HorizontalAlignment

This is just a small subset of available properties. For a complete list use Help, Answer Wizard, enter Range Object, then select Properties.


n 1\

r: l

r r r t

1 'I.

,f ,:~,

l

Example 41: Opening, adding, saving and closing Excel files

Option Explicit

Sub OpenSaveCloseFileO Dim stPathandName As String, stName As String Dim stSaveAsName As String, stPath As String

, Select and Open an Excel files stPathandName = Application.GetOpenFilename("Microsoft Excel Files (*.xls),*.xls")

, Open the file If stPathandName <> "False" Then

Workbooks. Open stPathandName End If

, Record the name of the active workbook within VBA stName = ActiveWorkbook.Name stPath = ActiveWorkbook.Path

• Save the file Workbooks( stName ).Save Workbooks(stName). Close

• Create new workbook Workbooks.Add stName = ActiveWorkbook.Name

• Save As stSaveAsName = Application.GetSaveAsFilename If stSaveAsName <> "False" Then

Workbooks(stName ).SaveAs (stSaveAsName) End If Workbooks. Close 'Close aU workbooks

End Sub

55

There are numerous Methods for Range in Excel including these below.

Method Example Activate Activates a single cell or sheet (same as Range("B4").Activate

clicking on a cell) Borders Sets the range border Example 43 Calculate Initiates a manual calculation Worksheets("Sheetl ").Calculate Clear Clears contents of range Range("E4:HIO").Clear Copy Copies contents of range to clipboard Range("D I O").Copy Cut Cuts contents of range to clipboard Range("D 1 O").Cut Delete Deletes all the cells in the range record a macro and see Help Find Finds information EXll.mple42 Insert Inserts a cell, row or colunm record a macro and see Help Select Selects a range or cell Range("E4:G I O").Select Show Scrolls sheets to show range Range("Z126'').Show Sort Sorts a range of cells Example 42

All these methods are very similar to the same operations in Excel. It is best to become familiar with them in Excel before VBA.

These are just a small subset of available methods. For a complete list use Help, Answer Wizard, enter Range Object, then select Methods.

Example 42 shows the methods Sort, End and Sort applied to a cell range.

Example 42: Range methods Option Explicit

Sub FindAndSortO , Find the column headed by "Name" then find the block of data below and to the right of it , Sort this data Dim rngTopLeft As Range, rngRight As Range, rngBottom As Range Dim rngBlock As Range, rngKey As Range Dim sLength As Single

Set rngTopLeft = RangeC'A 1 :G20").Find(What:="Name") Set rngRight = rngTopLeft.End(xIToRight) Set rngBottom = rngRight.End(xIDown)

Range(rngTopLeft, rngBottom).Sort Key1 :=rngTopLeft, Order1 :=xIAscending, header:=x1Yes

, Or do the same manually getting the top left and bottom right cells from the user Set rngBlock = Application.lnputBox(prompt:= _

"Select the range of data to be sorted, excluding headers", Type:=8) Set rngKey = Application.lnputBox(prompt:=_

"Select any cell in the column you wish to sort by", Type:=8) , Sort descending with no header rngBlock.Sort Key1 :=rngKey, Order1 :=xIDescending, header:=x1No End Sub


, l

t II ~

L.

r' I' j

1 1

Example 43: Formatting cells in Excel

Option Explicit

Sub FormatExamplesO , This program shows some of the techniques to format Excel cells

Dim iRow As Integer, iNumrows As Integer

With Workbooks("Mybook.xls").Worksheets("sheet2")

.Range("A 1 ") = "Page Title"

.Range("A2") = 'Written by Ken Morison" With .Range("A1:a2").Font

.Bold = True

.Size = 14

.Name = "Times New Roman" End With .Range("A:B").ColumnWidth = 15 With .Range("A 1 :B2")

.Borders.Weight = xl Medium

.Borders(xIEdgeTop).LineStyle = xlContinuous

.Borders(xIEdgeRight).LineStyle = xlContinuous

.Borders(xIEdgeBottom).LineStyle = xlContinuous

.Borders(xlinsideHorizontal).LineStyle =.xINone

.Borders(xlinsideVertical).LineStyle = xlNone End With With .Range("A4")

.Value = 1.23

.Cells(1, 2).Formula = "=A4A2"

.Cells(1, 3).Formula = "=R[0]C[-2]A2" 'See R1 C1 Reference Style in Excel Help iNumrows = 10 For iRow = 2 To iNumrows

.Cells(iRow, 1 ).Formula = "=R[-" & iRow - 1 & '1C[0]A" & iRow ' Tricky! NextiRow

End With , Here is a new use of range. See Help for more information on the syntax .Range("A4").Range(Cells(2, 1), Cells(iNumrows, 1 )).NumberFormat = "#0.000000"

End With

End Sub

Try other variations of properties and methods.

57

Interpreting macros in Excel An effective way of finding out how to program an operation is to record a macro in Excel. However the macro does not produce concise programs so some editing is nonnally required. For example if we do not know how to copy, paste, center and bold, then record a macro and do those operations in a spreadsheet.

Select Tools, Macro, Record New Macro, [OK] Then carry out the operations in a spreadsheet Select Tools, Macro, Stop Recording Then look at the contents of the module created

• This macro was recorded to find out to copy, paste, bold and centre Sub Macr010

RangeC'Cg").Select Selection. Copy Range("D1S").Select ActiveSheet. Paste Selection.Font.Bold = True With Selection

.HorizontalAlignment = xlCenter

.verticalAlignment = xl Bottom

.WrapText = False

.Orientation = xl Horizontal End With

End Sub

To edit a macro we might follow the following steps: 1. Remove any lines that just specify default values (see Excel Help for defaults)

e.g., remove .verticalAlignment = xl Bottom and the next two lines.

2. If a With block has only one line inside it remove it replace

With Selection .HorizontalAlignment = xlCenter

End With with

Selection.HorizontalAlignment = xlCenter

3. Replace "Selection" with the explicit range e.g., to give

Range("D1S").HorizontaIAlignment = xlCenter

4. Add new With blocks (Example 38) or range variables (Example 40) to tidy up the program.

5. Look up Microsoft Visual Basic Help to see alternative syotaxes and other examples.

With a small amount of editing the macro may become:

Sub Macr010 Worksheets("Sheet1").Aclivate Range("Cg").Copy Range("D16").Select ActiveSheet.Paste Range("D16").Font.Bold = True Range("D16").HorizontaIAlignment = xlCenter

End Sub


r ,

i i

~. ,~.

/If f~ I .

i ,t I!! I:~ IJ~

l

Note that we use the lines

Range("D16").Select ActiveSheet. Paste

This is because Excel pastes into a sheet and not directly into a cell.

After looking up Help for variations on the .Paste method, and using a With block, the program could be edited to the following.

Sub Macro 1 0 With Worksheets("Sheet1")

.Range("C9").Copy

.Paste Destination:=Range("D16")

.Range("D16").Font.Bold = True

.Range("D16").HorizontaIAlignment = xlCenter End With

End Sub

Example 44: Using macro recording to produce charts

Record a macro while you produce an x-y scatter plot from some data in Excel.

Then edit a copy of the program to make it as tidy and concise as possible. You are likely to use PI to get help on many of the features.

Delete the chart you produced in Excel and run your VBA program to generate a new chart.

Creating and manipulating charts in Excel While many of the charting techniques can be learnt by recording a macro in Excel, it can be difficult to make this program more flexible as not all the commands are obvious and some don't work as one might expect. Example 1 shows some alternatives for plotting data without writing them to a spreadsheet.

One of the confusions that can arise is the difference between Charts and Chartobjects. Generally charts that occupy chart sheets are referred to as Charts while charts in sheets are ChartObjects. ChartObjects are containers for charts so include a border and interior as well as a chart.

The ActiveChart is the chart in the workbook that is active. If the active chart is on a sheet, the active chart is the chart within the chartobject; it is not the entire chartobject. It is an object of type Chart. There appear to be differences in how a chart that is active is referred to, e.g., the following pairs of lines are equivalent:

ActiveSheet.ChartObjects(1 ).Chart.SeriesCollection(1 ).Values = vY ActiveChart.SeriesCollection(1 )Values = vY

The entity ActiveChart is not a member of the ChartObjects collection of a sheet, but the chart it refers to will be. If you are confused, don't worry but read this again when you have tried it.

59

A chart in a sheet has two different names. The name of the ChartObject in a sheet does not include the sheet name, and is, e.g. "Chart 2". The ActiveChart might have the name "Sheeti Chart 2". To rename a chart, so that it can be clearly identified later, one can use

stNewName="Special Chart" stOldName= Activesheet.chartobjects(1 ).name ActiveSheet.chartobjects(stOldName).name=stNewName

but one cannot rename a chart within a chart object. An ActiveChart is just that so cannot be renamed. The following works only if the active chart is in a chart sheet

ActiveChart.Name = StNewName

But the parent of the ActiveChart within a chartobject is the chartobject and a chartobject can be renamed. Thus the following syntax can be used.

ActiveChart.Parent.Name = "MyChart"

On occasions one adds a new chart to a sheet that may have other charts on it. One wants to manipulate only the latest chart so it is useful to give it a name. hI this example the old version is deleted if it is found, then a new chart is added and renamed so that it can be identified later.

Example 45: Naming and deleting Charts

Sub ReplaceChartO Dim myChartObj As ChartObject Dim vName As Variant

, Find and delete the current copy of "My Special Chart" For Each myChartObj In ActiveSheet.ChartObjects

If myChartObj.Name = "My Special Chart" Then myChartObj.Delete

End If Next

vName = ActiveSheet.Name ' Must be a variant Charts.Add ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.Location where:=xILocationAsObject, Name:=vName

ActiveChart.Parent.Name = "My Special Chart"

End Sub

It is however possible to name two charts with the same name. This leads to obvious confusion and should not be done.

The data for chart series need not be on a spreadsheet. It can be manipulated entirely by the program. Example 46 shows how.an animated chart can be produced. hI doing so the data is stored in a variant array.

60 An Introduction to, Visual Basic for Engineers and Scientists

Example 46: Creating and manipulating charts

Option Explicit Option Base 1 Sub ChartDemosO , Demonstration program to create a chart directly from VBA without using data in , worksheet cells, and animate the chart by changing a parameter , Written by P J Jordan, 29/11/99 Dim i As Integer, j As Integer, sStartTime As Single Dim vY(11) As Variant, vX(11) As Variant

, Creates a new chart on the active worksheet, with one data series ActiveSheet. ChartObjects. Delete ' Delete old charts first

j = 1 For i = 1 To 11

vX(i) = i vY(i) = SinO 110) • Cos(i)

Next i

Application.ScreenUpdating = False 'don't draw until the chart is complete Charts.Add ActiveChart. ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries 'Create one series with x and y values from arrays vX and vY ActiveChart.SeriesColiection(1 ).XValues = vX ActiveChart.SeriesColiection(1 ).values = vY ActiveChart.Location where:=xILocationAsObject, Name:="Sheet1"

, Scale the vertical axis so that it does not change during the animation , Change other default attributes With ActiveChart.Axes(xIValue)

.MinimumScale =-1

.MaximumScale = 1

.HasMajorGridlines = False End With

Application.ScreenUpdating = True 'reveal the chart

• Animate the chart by changing the y-values of the first series within a loop For j = 1 To 5

For i = 1 To 11 vY(i) = SinO 110) • Cos(i)

Nexti ActiveSheet.ChartObjects( 1). Chart.SeriesColiection( 1). Values = vY

Create a time delay of 1 second between steps in the animation sStartTime = Timer • Set start time. Do While Timer < sStartTime + 1 • Wait one second

DoEvents • Yield to other processes. Loop

Nextj End Sub

61

5 Structuring Programs

Procedures fu this section we look at

Modules Procedures Subroutines Functions

A Module is a sheet which may contain subroutines, functions, type definitions and variable definitions, e.g, Example 47.

The term procedure refers to a subroutine or function. A subroutine is a set of program instructions. It may call other subroutines or functions. A function is a set of program instructions which returns a single value to the point it was called. It is the same as Excel functions such as Average() or VLookUp(). A function can call other functions or subroutines. Functions can be used from Excel or from another procedure.

When programs get large, it is very useful to break them up into subroutines and functions. At first this seems awkward but with experience their use becomes much more sensible. If a program is more than one page long, a programmer should consider breaking it up into subroutines and functions.

Functions There are a variety of types of functions.

VB functions To find the VB functions look in Help, fudex under keywords or using View, Object Browser, VBA, Classes globals.

Excelfunctions. Excel functions can be used in VBA by prefixing the function with WorksheetFunction as described in Example 36.

Some functions from other libraries can be made available by going to Tools, References in VBA. For example if you tick atpvbaen.xls you get access to functions such as BesselJ. Select View, Object Browser to see a list of available functions and objects.

User-defined functions User-defined functions were introduced in Example 3 and used again in Example l3. fu Example 47 a function is written and used within a VBA module. At this stage we will look at the features of functions more closely.

A function returns a single result either in an Excel cell or to a variable. Any number of arguments can be passed to the function. fu Example 47 two parameters (stUnit and sLength) are passed to the function as arguments

sNewLength = ConverUo_metres(stUnit, sLength)

The first line of the function is: Function ConverUo_metres(Unit As String, Length As Single) As Single

The type of each argument is included, e.g., "Unit As String". This does not define the type of the variable Unit but puts in place a check to ensure that variable being passed is of the correct type. Similarly the type of the result of the function is defined by the final "As Single" as being Single. It is often useful to be able to see what type of variable the function returns so this style of programming is recommended.

The argument passed to the function Convert_to_metres in Example 47 have the names stUnit and sLength in the subroutine but are named Unit and Length within the function. There is no need for the names to be the same, as the memory location of the variable is passed, not the name. There can be

62 An futroduction to Visual Basic for Engineers and Scientists

• J . 1 I

1 I

?' :~\ , .

'." ~

:~' •• ' ~

any number of arguments as long as the number and order of arguments in the calling line corresponds with the number and order in the function's first line.

Arguments do not need to be declared with a Dim statement in the called Function (or Subroutine).

Example 47: Using user-defined functions within VB

Sub FunctionCaliO Dim stUnit As String, sLength As Single Dim sNewLength As Single

sLength = InputBox("Please enter the length") stUnit = InputBox("Please enter the original units")

'Calls the user-defined function Convert_to_metres sNewLength = Convert_to_metres(stUnit, sLength)

MsgBox "This is " & sNewLength & " m"

End Sub

Subroutine

Function Convert_to_metres(Unit As String, Length As Single) As Single Module , Function to convert a length to metres.

Dim sFactor As Single Select Case LCase(Unit) , LCase converts the units to lower case

Case "inch", "inches", "inti sFactor = 0.0254 Function

Case "foot", "feetll, "ft"

sFactor = 0.3048 Case lIyard", Tlyd", "yards"

sFactor = 0.9144 Case "fathoms", "fathom"

sFactor = 1.8288 Case Else

sFactor = 0 MsgBox "Sorry, I don't know that unit"

End Select

Convert_to_metres = sFactor' Length , Recall that the result of the function has the same name as the function

End Function

The function does not need to be in the same module but it needs to be in an open module or in a library.

In Example 47 the function is called from a VBA subroutine. These functions can be used in this manner and can also be used from Excel as user-defined functions as in Example 13.

All functions written in normal modules (not userform modules) are listed in Insert Function ":#1 under the Function Category called User Defined, unless they are Private Functions (see page 68).

63

If the function might be used from Excel the convention of prefixes, e.g., "st" and "s" for string and single, can be dropped so that the argument names are as meaningful as possible to a user in Excel. For example in Example 47 the first line was:

Function ConverUo_metres(Unit As String, Length As Single) As Single

The names Unit and Length appear in the Insert Function window and their first letters are always capitalised.

Subroutines Subroutines can be used in a manner similar to functions. A program with a subroutine call is shown in Example 48. In this example the memory location of cMySales is passed as the first argument to the subroutine and the answer cMine is returned as the second argument.

When the Call line is encounter in the calling subroutine, execution passes to the called subroutine. Once the called subroutine is finished control passes back to the next line of the first subroutine.

Subroutines have the advantage that any number of variables can be passed to and from them. In contrast a function can return just a single answer and cannot perform other tasks such as manipulating cells on a worksheet.

Array arguments Arrays can be passed to subroutines and functions. The syntax is slightly different

Dim sMarks(4,3) as single Dim sAverage as single

Call GetAverage(sMarks, sAverage)

End Sub

Sub GetAverage(sArrayO as Single, sAverage as Single)

End Sub


-,""

r ! ,I

Note, above, the brackets after sArrayO in the Sub GetA verage line. The brackets indicate that the argument is an array. Visual Basic checks that this is the case. Example 49 gives a full program with array passmg.

Example 48: Subroutine calling

Sub Sub TestO • This subroutine calls the subroutine CalcCommission Dim cMySales As Currency, cMine As Currency cMySales = 2000 Call CalcCommission(cMySales, cMine) 'Call the subroutine CalcCommission MsgBox cMine \ End Sub ~

Sub CalcCommission(cSalesAmount As Currency, cCommission As Currency) , This subroutine calculates the commission cCommission on the amount cSalesAmount If cSalesAmount < 1000 Then cCommission = 0

Eiself cSalesAmount <= 2000 Then cCommission = 0.15 * cSalesAmount

Else cCommission = 0.2 • cSalesAmount

End If

End Sub

Example 49: Passing arrays to subroutines

Sub ArrayPassingO Dim sMarks(4) As Single Dim iNumStudents As Integer Dim sAverage As Single

, the colon: is used to put multiple statements on one page. sMarks(l) = 55.2: sMarks(2) = 64: sMarks(3) = 73: sMarks(4) = 45

iNumStudents = 4 Call CaicAverage(sMarks, iNumStudents, sAverage) MsgBox "The averagt of the mar~as " & SAV~ End Sub \ ~

Sub CalcAverage(sArrayO As Single, iNumrows As Integer, sAverage As Single) Dim iRow As Integer, sTotal As Single ' Only variables not passed as arguments are declared

sTotal = 0 For iRow = I To iNurnrows

sTotal = sTotal + sArray(iRow) NextiRow sAverage = sTotal / iNumRow End Sub

Notice that the subroutine CalcAverage can calculate the average of any array. It doesn't have to be a set of marks, and so this subroutine could be kept and used for other programs also.

65

Structuring a program There is no single way to structure a program well. As a gnide a subroutine should be no more than 1-2 pages.

Consider as an example a problem in which you read some data from a file, analyse it and then write it to another file, e.g., to Excel. We might put together the following structure which is shown as a shell of a set of subroutines in Example 50.

Sub MainO Dim statements

Call GetData(. .. )

Call AnalyseData( ... )

Call WriteResu/ts( ... )

End Sub

----1.

Sub GetData( ... )

get all the data

End Sub

Sub Ana/yseData(. .. )

analyse the data

End Sub

Sub WriteResults(. .. )

write the results

End Sub

With a structure like this, the main subroutine (Sub Main) is very simple. It is reasonable clear just from the subroutine names what the overall program does.

Often when writing a large program it is good to start by writing the shell of a program like this. Then a large problem becomes a number of small problems.


-.

Example 50: The shell of a structured program

Sub Marks_MainO , Program to read and analyse data then write it to another file. , This program is incomplete

Dim sMarks(100, 10) As Single, sTestMean(10) as Single, sTestStDev(10) as Single Dim iNumStudents as Integer, iNumTests as Integer

Call GetMarks(sMarks, iNumStudents, iNumTests) Call AnalyseMarks(sMarks, iNumStudents, iNumTests, sTestMean, sTestStDev) Call WriteMarks(sMarks, iNumStudents, iNumTests, sTestMean, sTestStDev)

End Sub

Sub GetMarks(sArrayO As Single, iNumRows As Integer, iNumColumns As Integer) , Subroutine to read data from a file.

, Declare any variables not already defined. , The arguments, sArray, iNumRows and iNumColumns have been set up in the main , program (with different names) and do not need to be declared again.

Dim iRow As Integer, iCol As Integer

For iRow = 1 to iNumRows For iCol = 1 to iNumColumns

, get data values from a spreadsheet sArray(iRow, iCol) = ActiveWorkbook.Range("B3").Cells(iRow, iCol) etc

Next iCol NextiRow

End Sub

Sub AnalyseMarks(sArrayO As Single, iNumRows As Integer, iNumCols As Integer, _ sMeanO As Single, sStDevO As Single)

, Calculate the Mean and Standard Deviation of each test etc

End Sub

Sub WriteMarks(sArrayO As Single, iNumRows As Integer, iNumCols As Integer, _ sMeanO As Single, sStDevO As Single)

, Put names and data into a spreadsheet in this workbook etc

End Sub

67

Private and Public procedures and variables Subroutines and Functions are public to Excel unless they are specified as Private, e.g.,

Private Function Commission(cSalesAmount)

If Private, functions and subroutines can only be accessed from within the same module.

Variables are private to a subroutine or function. Any variables defined within a procedure cannot be referred to by another unless they are passed as a argument to a procedure.

Variables can be public to a module if they are defined before the first procedure. Variables can be made accessible to all modules (i.e., they are global) by using Public instead of Dim to declare it, e.g.;·

68

Option Explicit Dim sMyNumber as Single Public sNextNumber as Single

Sub MySubO Dim sAnotherNumber

etc End Sub

, This variable is public within this module , This variable is public to all modules

, sAnotherNumber can only be used within MySub

An Introduction to Visual Basic for Engineers and Scientists

I I I I I

I

1 I ! t

Example 51: Public and Private variables

Try to predict what will be the values of the variables displayed by MsgBox

Option Explicit

Dim sNumber1 As Single 'Variables declared before the first Sub are public to this module Public sNumber5 As Single 'Variables declared as Public can be used in any module

Sub PublicPrivateO Dim sNumber2 As Single, sNumber3 As Single Dim sNumber4 As Single

sNumber1 = 1.23 sNumber2 = 2.34 sNumber5 = 5.67 Call Sub1 (sNumber2, sNumber3) Call Sub2 MsgBox "sNumber3 in Sub PublicPrivate = " & sNumber3 MsgBox "sNumber4 in Sub PublicPrivate = " & sNumber4

End Sub

Sub Sub1 (sNum2 As Single, sNum3 As Single)

, Variables declared within a subroutine are private unless passed as an argument Dim sNumber4 As Single sNumber4 = 4.56

MsgBox "sNumber1 in Sub1 = " & sNumber1 'sNumber1 is public to the module MsgBox "sNumber2 in Sub1 = " & sNum2 sNum3 = sNum2 + sNumber4 End Sub

Sub2 is in another module

Option Explicit

Sub Sub20 Dim sNumber1 As Single MsgBox "sNumber1 in Sub2 = " & sNumber1 MsgBox "sNumber5 in Sub2 = " & sNumber5 End Sub

69

6 Visual Basic Forms

UserForms When using Forms we will use a number of common objects. The following prefix conventions are suggested

Ob.iect Type Prefix Example Command Button cmd cmdCancel Form fun frmCalculator Frame fra fraOptions Option Button opt optPaymentChoice Label Ibl IblEnterName RefEdit ref reflnputCell Text Box txt txtEnterName

Label

Text

Ref Edit


i. I

Example 52: Properties of the components of forms

The Properties window is very important when using forms. Try these exercises.

In VB select Insert, Userform to get a blank form. Select View, Properties Window so that the properties window is shown.

Add a Label in the top centre of the form, a text box and a button underneath the label. Notice that as you click on different items on the form you get different sets of properties.

Use the Properties Window to change the following attributes: (click on the down arrow ... or the three dots ... to see the options)

UserForm Name MyForm Caption My First Form

Label Caption Trial Form Font Times New Roman, 16pt, bold TextAlign frmTextAlignCenter

Textbox Name

\Commandbutton Name Caption Double

txtNumberl

cmdDouble (you'll see why below)

Font 12 pt

To see what the other property attributes are click on one of the attributes then push Fl.

You can run the form now from the Run menu or with F5. To close the form you will need to click on the top close button ~.

Now we will add to code to the button.

Double click on the button Double. A module will appear with the shell of a subroutine. The subroutine is an Event subroutine. It will be executed every time this button is clicked. Change it as shown below and press F5 again. Enter a number in the box and click on the Double button.

Private Sub cmdDouble_ClickO txtNumber1.value = txlNumber1.value * 2

End Sub

As you develop both the form and the code you can get leist. Select View, Code (F7) to see the code associated with a form, or select View, Object to see the form associated with the code.

71

Forms execute differently from a normal program. In a traditional program execution starts at the top and works to the bottom calling subroutines are required.

Programs based on VB forms are often event based. Subroutines are executed when an event happens. For example when the OK button is pushed a calculation may be made, when the Cancel button is pushed the form is unloaded and the program stops.

The programs associated with a form are attached to the form, while programs associated with an Excel workbook are attached to the workbook. There is a resulting sequence in which programs and forms can be run.

A procedure in a module can be run from a worksheet A form cannot be run from a worksheet A form can be run from VB or VBA A form can be run from a module

Example 53 shows how to get a form to run from a module.

Sample program lines There are numerous things one can do with a form. Textbooks cover this area quite well but some specific items are mentioned here.

To get a user input value from a text box:

Dim dX as Double dX = txtNumber1.Value , txtNumber1 is a text box in a form

To get and use an Excel cell range from a RefEdit box

Dim rngRef As Range ' declare a cell range object Set rngRef = Range(refEdit1.Value) , set the object to the cell reference entered rngRef.Value = txtNumber1 ' set the cell value equal to the number entered

Ifuserform objects are referred to from the userform code module the name of the userform is not required. If the userform is referred to from another module its name is required, e.g., the first example becomes

Dim dX as Double dX = MyForm. txtNumber1. Value

Data or headings can be loaded into a Listbox (e.g., IbxPatient) or a ComboBox using something like the code below. Microsoft Visual Basic Help is quite good; look up Answer Wizard with Listbox Control or ComboBox Control.

72

i = 0 Do

i=i+1 frmPatient.lbxPatient.Add Item ThisWorkbook. Worksheets(" data"). Range("d2"). Cells( 1, i)

Loop Until ThisWorkbook.worksheets("data").Range("d2").Cel/s(1, i + 1) = ""

An Introduction to Visual Basic for Engineers and Scientists

j

i~ ,::1

Example 53: A simple userform

In VB select Insert, Userfonn to get a blank fonn. Select View, Properties so that the properties window is shown. Use the toolbox commands to make the form look like this. Change the name of each item within the properties window so they are named as shown.

Insert a module and write

Option Explicit Sub LoadMyFormO

Load MyForm , Preload a number if you wish MyForm.txtNumber1.Text = 2 MyForm.Show

End Sub

Form Code Option Explicit Private Sub cmdCalc_ClickO

Call Calculate End Sub

Private Sub CalculateO Dim sNumber1 As Single sNumber1 = Val(txtNumber1. Text) txtNumber2.Text = Sqr(sNumber1) End Sub

Private Sub cmdClose_ClickO Unload MyForm End End Sub

Note that the line sNumber1 = Val(txtNumber1.Text)

MyForm

txtNumber2

is used to maintain compatibility with the full version of VB which strictly interprets the input from a text box as being text. The Val function is used to convert it to a number. Within VBA the folIowing line works (but not in VB)

sNumber1 = txtNumber1.Value

Running Forms from Excel If you wish a simple drawing object, e.g. a rectangle can be added to a worksheet. Using a right click on this you can add text and then a right click on its border will allow you to assign this object to a subroutine such as LoadMyFonn in Example 53. Then just click anywhere else and then back on the object to run the subroutine. To edit the object later use Ctr! and a left click together.

A special event is workbook_open. If a subroutine called workbook_open is put into the module for ThisWorkbook (double click on "ThisWorkbook" to open the module), the code will be executed on opening. This is useful for automatically popping up a userform from which the user can select options or execute commands.

73

7 Using the Full Version of Visual Basic

Visual Basic (rather than Visual Basic for Applications) is almost identical to VBA. Additional features include:

An executable file (.exe) can be produced Printing is possible There is more database access Serial communication support The ability to draw and manipulate pictures for animation, games or graphing

VB programs are either run from a form or from a main program. If you want a form to appear when you run the program, configure a form and associated code. If you do not wish to use a form, delete the form, and insert a module. The module should contain a subroutine Sub MainO which calls any other subroutines you wish to run, as shown in Example 55.

If you are running the full version of Visual Basic you can compile a project as an executable file (.exe) by using File, Make ProjectName.exe. Then you can run the .exe file anywhere in Windows.

Serial Communication via a COMport In VB right click on the form Toolbox. Select Components and check Microsoft Comm Control in the list of controls. Then see VB help for more information and examples.

Other Controls There are numerous other controls that can be used. If you can do something is MS Office there is a good change that there is a VB control for it. Start by working through the standard userform toolbox with the aid of VB Help, then right click on the toolbox and explore the possible components you can add to the toolbox. Then buy a comprehensive VB book.

Example 54: Using the full version of VB

Start up the full version of Visual Basic (6.0). Select New Project and Standard EXE.

Write the same form and form code as shown in Example 53. You don't require the first module to load the form. When it's done press F5 (run) to test it.

When it runs correctly select Project, Project! Properties to change the name of the project to say, SqrtCalculator. Compile the project using File, Make SqrtCalcuIator.exe. Save the program in a suitable folder.

Now use Microsoft Explorer to fmd the Sqrt Calculator.exe file and run it.


Example 55: A VB program without a form

In the full version of VB start a new project (File, New Project, Standard EXE).

Select Project, Add Module to add a new module.

Select Project, Projectl Properties. Under the General tab change Startup Object to Sub Main. Also change the Project Name to something else; use SquareRoot for this example.

Within the module type the following.

Option Explicit

Sub MainO • The name Main must be used. Dim sX As Single, sRoot As Single sX = InputBox("Please enter a number") sRoot = Sqr(sX) MsgBox "The square root of " & sX & " is " & sRoot

End Sub

Press F5 to test it. Select File, Make SquareRoot.exe to compile and save the project. Again choose your own filename and location for the file.

From Microsoft Explorer run the .exe file.

Printing To print results from a VBA program you need to write a file and print it using Note Pad or a similar program. Alternatively you can write the results into a spreadsheet and then print the sheet.

In the full version of VB (not VBA) you can send information directly to the default windows printer using the code in Example 56.

Example 56: Printing from VB

Sub PrintTestO • This works in the full version of VB only . • Set margin to 25 mm down and 30 mm across Printer.ScaleMode = vbMillimeters Printer.ScaleTop = -25 Printer.ScaleLeft = -30 Printer.Font.Name = "Times New Roman" Printer.Font.Size = 14

Printer. Print "Hello." Printer. Print "This is a new line." Printer. End Doc

End Sub

75

Within the full version of VB graphs and other objects can be drawn in Pictures. Example 57 gives an example of graph drawing. Graphs can also be drawn with MS Chart. See VB Help for more information.

Example 57: Drawing a graph in a form picture Insert a picture in a Form, write this subroutine in the form code, then Run.

Private Sub FomUoadO Dim i As Integer, sY As Single With picGraph

Width = 8000: .Height = 5000 'Set form size in twips . . Move 200, 200 'Move picture relative to top left .AutoRedraw = True' Turn on AutoRedraw . . BackColor = QBColor(14) 'Set background to light yellow. picGraph.Scale (-10,110)-(130, -10) 'Set custom coordinate system.

picGraph.Line (0, 0)-(0, 100) picGraph.Line (0, 0)-(120, 0) .FontSize = 8

'draw axes

For i = 100 To 0 Step -10 'draw ticks picGraph.Line (0, i)-(-2, i) 'Draw scale marks every 10 units . . CurrentY = .CurrentY + 2 'Move cursor position . . CurrentX =-7 picGraph.Print i 'Print scale mark value on left . . CurrentX = .ScaleWidth - 9

Nexti

. CurrentX = 0

.CurrentY = 40 Fori = 1 To 120

sY = 40 • Sin(i 110) + 40 picGraph.Line -(i, sY), RGB(O, 0,255)

Next i

.CurrentX = 40: .CurrentY = 110 'Move cursor position .

. FontSize = 14 'Enlarge font for title. picGraph.Print "Graph Title"

End With End Sub


· : ,

,'~ ,!

ii I~

I ,I

i. ;

8 Integrating VBA With Other Applications

Compiling Projects Normally VBA programs are available only when the file that contains them is open. There are a few different ways to save a project so that it can be used at any time.

Excel Add-ins Create a program or functions in VBA. In Excel, select File, Save As, Save as Type, Microsoft Excel Add-in. Then when the add-in is required, in Excel, select Tools, Add-ins, Browse and select the required .xla file. The programs or functions within the file are now available for use.

Workbooks in XlStartup If you create a workbook called personal.xls (or any other name) and store it in your Xl Startup folder, the procedures in it can be called from any open workbook. When you open Excel the personal workbook is automatically opened, and if you select Window, Hide (or Unhide) it will be hidden (or visible). In either case it will be visible in the VBA Project and can have other modules added to it.

Using VBA with Word and Power Point The Word object model is difficult to find. It is called Microsoft Word Objects and can be found with the sequence Help, Contents and Index, Microsoft Word Visual Basic Reference, Shortcut to Microsoft Word Visual Basic Reference, Getting Started with Visual Basic, Microsoft Word Objects. (There must be an easier way)

The PowerPoint object model can found using Help, Contents and Index, Contents, Microsoft PowerPoint Visual Basic Reference, Shortcut to Microsoft PowerPoint VB Reference, Microsoft PowerPoint Visual Basic Reference, Microsoft PowerPoint Objects.

For both applications it's easiest to select Tools, Macro, Record Macro to record a VB program and view it to see the way the objects are used. If you understand objects in Excel and you know how to use Word or PowerPoint, the objects specific to Word and PowerPoint will be easy to understand.

In Word recorded macros are associated with the Normal.dot template unless you choose to save them with a file. The choice depends on how and when you wish to use the programs,

Example 58 shows some ofthe features ofVBA with Word.

Using VBA Word and Excel together Sometimes it is useful to be able to run Excel and Word together under the control ofVBA, so you can for example transfer data from an Excel file to a series of files in Word for reporting. When you wish to integrate two applications, VBA is run within one application and objects are created for the other application.

There are a number of tricks that are included in Example 59. The Scripting.FileSystemObject is very useful here to access the file manager. This program tries to open Word but if it isn't running it will open it. Then it gets a file name that has been stored as a name in Excel and it will open this file. Once this has been done data can be transferred from Excel to Word or vice versa. The Find command can be useful here. Record a macro to see how it works.

77

Example 58: VBA in Word to produce the set offonts


Sub MyfontsO , This VB program should find, sort and write to the document , the set of fonts available on the computer in use.

Dim stNames(1000) As String Dim bFound As Boolean Dim i As Integer, j As Integer, iFontNames As Integer, vFonttype As Variant

Documents.Add Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter Selection.Font.Bold = True Selection.TypeText Text:=''Word Fonts Available - " & Format{Date, "dd mmm yy") & vbCrLf Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft Selection.Font.Bold = False Selection.ParagraphFormat.TabStops.ClearAIl 'Clear and set some tabs Selection.ParagraphFormat. TabStops.Add Position:=Centimeters ToPoints{1.2) Selection.ParagraphFormat.TabStops.Add Position:=CentimetersToPoints{5.5)

, Get list of font names sorted. iFontNames = 0 For Each vFonttype In FontNames

bFound = False If iFontNames = 0 Then

stNames(1) = vFonttype Else .

For i = 1 To iFontNames If vFonttype < stNames{i) Then 'move others and insert

For j = iFontNames To i Step-1 stNamesO + 1) = stNamesO)

Nextj stNames(i) = vFonttype

bFound = True Exit For

End If Next i If bFound = False Then stNames{iFontNames + 1) = vFonttype

End If iFontNames = iFontNames + 1

Next

For i = 1 To iFontNames Selection.TypeText Text:=vbCrLf & stNames{i) & vbTab Selection.Font.Name = stNames{i) Selection.TypeText Text:="abcdefghijkilmnopqrsturvxyz" & vbCrLf Selection.TypeText Text:=vbTab & "ABCDEFGHIJKLMNOPQRSTUVWXYZ" & vbCrLf

Next i End Sub

78 An Introduction ·to Visual Basic for Engineers and Scientists

Example 59: Opening Word and a file from Excel VBA

Sub OpenReportO , Open Word Application and a file Dim fs As Object Dim wdObj As Object Dim WordWasNotRunning As Boolean Dim stPathandName As String. stFile As String. stDirectory As String

, Check to see if word is running and if not open it

On Error Resume Next • Defer error trapping.

Set wdObj = GetObject(, "Word.Application") If Err.Number <> 0 Then WordWasNotRunning = True Err.Clear • Clear Err object in case error occurred. On Error GoTo 0 • remove error handling

If WordWasNotRunning Then Set wdObj = CreateObjectC'Word.Application")

End If wdObj.Visible = True

• If this subroutine has been run before the last file name used will be • stored as a name in the Excel workbook. , Get the file name from the names in the workbook

On Error Resume Next • If no name has been set up continue stFile = ActiveWorkbook.Names("stfile").Value stFile = Mid(stFile. 3. Len(stFile) - 3) • get rid of ="" On Error GoTo 0 'remove error handling

Set fs = CreateObject("Scripting.FileSystemObject") If fs.fileexists(stFile) Then

wdObj.documents.Open Filename:=stFile, ReadOnly:=True stDirectory = wdObj.activedocument.Path

Else stFile = Application.GetOpenFilename(,'Word files ('.doc), '.doc") If stFile <> .... And stFile <> "False" Then

Else

wdObj.documents.Open Filename:=stFile. ReadOnly:=True stDirectory = wdObj.activedocument.Path

MsgBox "no file given" Stop

End If End If

• Save the filename for future use ActiveWorkbook.Names.Add Name:="stFile". RefersTo:=stFile

End Sub

79

Using Excel Solver If you don't use Solver don't worry about these two pages but if you do these will save you a lot of time. The easiest way to produce this code is to record a Macro using Solver. Then in VBA under Tools, References check the box for SOLVER. If you can't find Solver in the list, run Solver in Excel and look again.

If you are using Solver often with new constraints or options, the first useful line might be: SolverReset

Main line SolverOk SetCell:="$D$4", MaxMinVal:=2, ValueOf:="O", ByChange:="$B$3:$B$5"

MaxMinVal

Constraints SolverAdd CellRef:="$B$3", Relation:=1, FormulaText:="=5" SolverAdd CellRef:="$B$5", Relation:=3, Formula Text:="=O"

The relations are: I <=; 2 =, 3 >=, 4 int (integer), 5 bin (binary)

Note that the FormulaText includes "=". Sometimes Solver wants a true formula rather than a value.

Options (optional) SolverOptions MaxTime:=100, iterations:=100, Precision:=0.00000001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, SearchOption:=2,_ IntTolerance:=4, Scaling:=True, Convergence:=O.0001, AssumeNonNeg:=False

To run SolverSolve userfinish:=true 'If userfinish is true Results window is not shown

80 An Introduction,to Visual Basic for Engineers and Scientists

i , , ~

~ , t 1

Example 60: Using Solver from VBA

This example sets up the entire problem from VBA and just uses a worksheet so that Solver can work.

Option Explicit

Sub SolverTestO • Select Tools, References to ensure that Solver is checked • If it is not there run Solver in Excel first to wake it up!

With Workbooks("Examples.xls").Worksheets("sheet2") Range("B3:B4") = 0 Range("B6") = "=MyFunction(b3,b4)" SolverReset ' Get rid of existing constraints SolverOk SetCell:="$8$6", MaxMinVal:=2, ByChange:="$B$3:$8$4" SolverAdd CeIlRef:="$B$3", Relation:=1, FormulaText:="=5" 'Add constraints SolverAdd CeIlRef:="$B$4", Relation:=3, FormulaText:="=3" SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.00000001, _

AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=4, Scaling:=True, Convergence:=0.00001, _ AssumeNonNeg:=False

SolverSolve userfinish:=True ' Solve but don't display result End With

End Sub

Function MyFunction(dX As Double, dY As Double) MyFunction = (5 - dX) A 2 + (2 - dY) A 2 + 4

End Function

If you are really keen to see some of the Solver program, run Solver in Excel once, then go into the VBA Editor and have a look at the modules. You will need to enter the password Wildebeest!! (complete with capital and exclamation marks).

81

Using VBA with Access VBA within Access is programmed differently than from the other applications. There is no macro recorder but instead the Access Form Wizard automatically writes VB code. Access can be programmed in at least two different ways.

When using the Design View of a form, a right hand click on an object gives a menu which includes Build Event. Click on this, then code builder to get to the module associated with the form. Also in Design View, the menu item View, Code, will get to the same module. This module is mainly used to execute actions associated with event on the form, e.g., when a field is updated.

With the database window click on the Modules tab, then New, to get a project level module in which the database can be manipulated. To use this requires knowledge of Data Access Objects (DAO) which include databases, recordsets (tables), fields and the associated operations.

I find that VBA in Access is the least well developed of the different forms ofVBA. I fmd it nonintuitive and difficult to learn. Not being able to record macros is a significant disadvantage.


.... ---------~--

Example 61: VBA and Access

Following is an example of a program that writes to Excel the first 13 fields of each record in the database in the order found. From this you can see some of the different objects used in Access. For this program to work the Reference (under Tools), Microsoft DAO 3.6 Object Library must be checked.

Sub AccessToExcelO , This program is designed to work with the Access sample database, Northwind

Dim dbsNorthwind As Database, rstEmployees As Recordset, xlObj As Object Dim strMessage As String, strPath As String, j as Integer strPath = "c:\Program files\microsoft office\office\samples\" Set dbsNorthwind = OpenDatabase(strPath & "Northwind.mdb") , AbsolutePosition only works with dynasets or snapshots. Set rstEmployees = dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset) Set xlObj = CreateObject("ExceI.Application") xlObj.Visible = True xIObj.Workbooks.Add With rstEmployees

, Populate Recordset. . MoveLast .MoveFirst

Do While Not .EOF , Copy the first 13 fields of each record to Excel. , Add 1 to AbsolutePosition value because it is zero-based. (Field 14 doesn't have a value)

For j = 1 To 13 xIObj.workbooks(1 ).worksheets(1 ).range("A 1 ").cells(.AbsolutePosition + 1, j) = _

.FieldsO).Value Nextj . MoveNext Loop

.Close End With dbsNorthwind.Close

End Sub

For more information, find a book on Access VBA, e.g., by searching http://www.amazon.com.

83

Index .exe files, 74, 75 .xla files, 77 Add-ins, 77 Arguments, 62, 64, 65, 68 AJrays,28, 30,45, 64

two dimensional, 31 Boolean, 12 Breakpoints. See Debugging Byte, 10 Cells, 50 Characters, 10 Charts, 59, 76 COM ports, 74 Command buttons, 70 Comments, 4 Compiling, 77 Conditions, 22 Continuation character, 29 Currency, 12 Data types, 10 Date, 12 Debugging, 26, 32, 33

breakpoints, 32 Debug.Print, 32, 33 desk checks, 26

Declaring variables. See Variables, declaring

Dice, 37 Dim, 10,28 Double, 10, 12 End of file, EOFO, 43, 45 Errors

trapping, 38, 39 Events. See Objects, events Excel. See Microsoft Excel Exit, 38 Exit For, 24 Factorial, 24 Files

appending text, 44 closing text, 44 input from text, 42 opening text, 42, 44 Excel, 55 reading text, 43 text, 42

Font, 54, 58 Fonts, 78 ForlNext loops. See Loops, ForlNext

Forms. See UserForms Functions, 23, 62. See also Microsoft

Excel insert function user-defined, 6, 7, 62 VB built-in, 4, 6, 18

Global variables. See Variables, Public Goto,38 Graphs. See Charts Help, 1, 6, 54, 56, 62, 77 If Statements, 20 Immediate Window, 32 Input, 42 Integer, 10, 12 Locals Window, 32, 33,47 Long, 10, 12 Loops

Do Until, 34, 35 Do While, 34, 35 exiting, 24 ForlNext, 24

Maclaurin series, 37 Macros, 58 Mathematical operations, 18 Matrices. See AJrays Memory, 12 Message Box, 4, 8, 32 Methods. See Objects, methods Microsoft Access, 3, 46, 82, 83 Microsoft Excel, 2 Microsoft Excel, 1,3,4,6,7,23,32,42,

46,47,48,50,62,77 formatting cells, 57 functions, 6 insert function, 6, 7, 63 reading from and writing to, 49 referencing cells, 70, 72 Solver, 80, 81 workbooks, 48 worksheets, 48

Microsoft PowerPoint, 3, 77 Microsoft Word, 1,3,42,46,77,78,79 Module, 3, 5, 62, 75

class, 46 Naming, 14, 15. See also Variables

conventions, 15 programs, 4

Nesting loops, 24 Objects, 46, 47, 77

events, 46

85

events, 72 methods, 46, 56 properties, 46, 54, 56, 71 type, 70

Opening Excel files, 55 projects, 4

Option Base 1,28 Option buttons, 70 Option Explicit, 16 Overflow, 13 Printing, 75 Procedures, 62 Program

structure, 66 Project Window, 3 Properties

of objects. See Objects, properties Window, 3, 70, 71

RAM, 12 Range, 14

properties, 54 Range objects, 52 Real numbers, 10 RefEdit, 70, 72 Running programs, 5 Saving, 4, 42

Excel files, 55 Select Case, 22, 23 Serial Ports, 74 Series, 34 Single, 10, 12 Solver, 80, 81

String, 10 String data type, 12 Strings, 23

joining, 8, 40 operations, 40, 41

Subroutines, 62, 64, 65, 67 Text boxes, 70 Toolbox, 3, 70 Type. See Variable, type User-defined functions. See Functions Userfonns, 3 UserFonns, 70, 71, 72, 73 Val, 73 Variable

type, 10, 12, 14,46,47 Variables

declaring, 4, 10, 16 global, 68, 69 naming, 14, 15 Private, 68, 69 Public, 68, 69

Variant, 12 Visual Basic, 1 Visual Basic Editor, 3 Visual Basic for Applications, 1 With blocks, 50 Word. See Microsoft Word Workbook

open event, 73 Workbooks. See Microsoft Excel

workbooks Worksheet Functions, 49, 62


vba guide - morison, an introduction to visual basic for engineers and scientist, uni of canterbury...

Documents

christchurch new zealand

builtin functions

excel functions

basic mathematical operations

program naming

vba functions

scientists ken

variable naming conventions