Introduction to Visual Basic Programming with Special ...

Introduction to Visual Basic Programming with Special Reference to Fisheries

April 8th-11th

Synopsis:

This is an introductory course intended for graduate students and those who are interested in expanding their options for dealing with data analysis and modeling problems in fisheries science. The course will cover essential programming concepts using the Visual Basic Computer language.

Topics & Examples:

? Using Visual Basic for Applications (VBA).

? Build your own functions to use in spreadsheets.

? Working with vectors and arrays.

? Subroutines and basic models for fisheries stock assessment.

? Non-linear parameter estimation and likelihood profiling.

? Monte Carlo methods in spreadsheets.

Using Visual Basic to Build Applications:

? Documenting programs and using pseudocode.

? Declaring local and global variables, objects.

? Using consistent naming conventions.

? Reading and writing data files.

? Building applications and compiling executables.

? Building multi-species population models.

? Debugging Projects.

REQUIRMENTS

-Preferable a notebook computer with Microsoft Excel (office 97 or newer). -Visual basic 5.0 or 6.0 -Good sense of humor and doughnuts!

1 Course Outline

1.1 Using Visual Basic for Applications (VBA)

-VBA can be used in all Microsoft Applications, including Powerpoint, Word, Access and Excel.

Using Functions in Excel Introduction to functions and function arrays. Building Functions in Excel Building user defined functions, and when

to use function arrays. There are a limited number of functions in Excel, and we can build our own functions to carry specific analysis. Vectors and Arrays Defining Vectors and Arrays in VBA, and how to use them efficiently. Looping and conditional statements. Building Models and Programs in VBA Using Subroutines to organize code. Declaring global and local variables, passing variables between subroutines. Non-linear Parameter Estimation Fitting models to data, or estimating model parameters given the data. Parameter Uncertainty Bootstrapping, or re-sampling the data. Using Monte Carlo methods to construct parameter distributions.

1.2 Using Microsoft Visual Basic

Introduction to the Visual Basic Interface Using the interface efficiently. Program Design Mapping and using pseudocode. Naming Conventions Using consistent naming conventions, global and lo-

cal variables External Data Files Create or read external data files for your program. Debugging Programs Using breaks, the immediate window and watch

window. Error handling.

ii

Contents

1 Course Outline

ii

1.1 Using Visual Basic for Applications (VBA) . . . . . . . . . . . ii

1.2 Using Microsoft Visual Basic . . . . . . . . . . . . . . . . . . . ii

2 Using Visual Basic for Applications

2

2.1 Using Functions in Excel . . . . . . . . . . . . . . . . . . . . . 2

2.2 Building Functions in Excel . . . . . . . . . . . . . . . . . . . 2

2.2.1 Passing Arguments . . . . . . . . . . . . . . . . . . . . 3

2.2.2 Example: Random Normal Distribution . . . . . . . . 4

2.3 Vectors and Arrays . . . . . . . . . . . . . . . . . . . . . . . . 5

2.4 Building Models in VBA . . . . . . . . . . . . . . . . . . . . . 6

2.4.1 Example: Stock Recruitment Model . . . . . . . . . . . 7

2.5 Non-linear Parameter Estimation . . . . . . . . . . . . . . . . 8

2.5.1 Integration Example: Pella-Tomlinson Model . . . . . . 9

2.6 Parameter Uncertainty . . . . . . . . . . . . . . . . . . . . . . 12

2.6.1 Bootstrapping Your Data . . . . . . . . . . . . . . . . 13

2.6.2 Sampling Importance Resampling . . . . . . . . . . . . 16

2.6.3 Example: Depletion Estimator . . . . . . . . . . . . . . 17

3 Building Applications with Visual Basic

20

3.1 The Visual Basic Design Interface . . . . . . . . . . . . . . . . 20

3.2 Designing VB Projects . . . . . . . . . . . . . . . . . . . . . . 22

3.3 Variable Declaration and Naming Conventions . . . . . . . . . 23

3.3.1 Variable Scope . . . . . . . . . . . . . . . . . . . . . . 25

3.3.2 Variable Types . . . . . . . . . . . . . . . . . . . . . . 26

3.4 Working with External files . . . . . . . . . . . . . . . . . . . 26

3.5 Debugging and Error Handling . . . . . . . . . . . . . . . . . 28

1

2 Using Visual Basic for Applications

Visual Basic for Applications, or VBA, can be used in most Microsoft Office Products, however this manual specifically refers to Microsoft Excel. VBA is a programming environment that allows the user to define their own specific functions, or create/record macros for carrying out repeated tasks.

2.1 Using Functions in Excel

In Excel, functions can be used to carry out specific calculations based on values specified in the spreadsheets. There are many functions available to the excel user, for example: =Sum(A1:A10) will sum all of the numbers in the column A and rows 1 . . . 10. The function itself is simply a computer program that loops over all the numbers in rows 1 through 10 and adds them up, then replaces the contents of the cell with the value of the function. The "Range" A1:A10 is referred to as the arguments, or simply the range of numbers we want to sum. The word Sum is the name of the function, and we call this function by placing a = sign in front of sum. The actual computer instructions are stored in a Dynamic Link Library or dll. Note that in Excel or Visual Basic Language, function names and variables are not case sensitive.

Function "arrays" differ from standard functions in that they may return numerous results. For example a frequency distribution returns the number of observations that are within a bin range. One of the more common array functions used in Excel is the Table Function. Function arrays can also be programmed, but are implemented in the spreadsheet environment by typing in the function name and pressing Ctrl Shift Enter.

2.2 Building Functions in Excel

Creating your own function in excel is fairly strait forward. User defined functions are written in the Visual Basic Editor which can be located under the Tools . . . Macros menu, or simple press Alt-F11.

Functions must be written in a Module. To create a new user defined function, under the Insert menu select Module. For a simple demonstration lets create a function that returns a random number between 0 . . . 100.

2

In the module enter the following code:

Function Junk() Junk = 100 * Rnd()

End Function

If we now return to the spreadsheet view, and select an empty cell, we can call the new function Junk by entering =Junk() into the cell and press enter.

To create a function array, we would highlight a range of cells, and in the formula bar type =Junk() and press Ctrl-Shift-Enter. Note that in each cell the same random number is copied into each cell. If, however, this range of cells is copied and pasted elsewhere on the spreadsheet, the function is called again and new random numbers are drawn.

2.2.1 Passing Arguments Much like the =sum() function, we can pass arguments to a function, such as numbers to add together, divide etc. These arguments can either be Optional or Required. To write a function with required arguments the function would start with:

Function Junk(x as Integer)

where x is treated as an Integer in program. If we are passing Optional arguments the function would start with:

Function Junk(Optional x as Integer)

In this case if x is not specified then the computer assumes the value of x = 0. It is necessary then if no argument is presented that the user writes code that is robust to the optional argument. For example suppose your function returns 10/x, if x = 0 then the function is undefined. Making the code robust might include a statement such as "If x = 0 Then x = 1", this will insure a divide by zero error does not occur. The following example illustrates the use of optional arguments.

3

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

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

Google Online Preview   Download