Gerhard Boenisch, created 02/28/2002, modified 05/21/2002; boenisch@bgc-jena.mpg.de

Max-Planck-Institut fuer Biogeochemie

Paleoclimatology and Global Ecology Groups

VBA Programming Seminar, Excel Class 2/28/2002, Access Class 4/11/2002, Q&A 4/25/2002

By Gerhard Boenisch

About This Webpage

This webpage makes the files used in the seminar available to interested people, but does not repeat the instructions and explanations provided during the classes. Only because this is not standard knowledge for Excel users, it is briefly explained how to open and use the Visual Basic Editor in Excel.

Files Used During The Classes

To obtain the files used during the classes, right click the links below and choose "Save Target As ..." from the pop-up menu. Since there are still users of Access 97 and Access 2000, the Access demonstration file is also provided in Access 97 format. In order to read the data from the ASCII file into Excel or Access and to read the data from the Excel file into Access, the paths must be edited in the program to reflect the location where you saved the files on your disk.

Special Instructions How To Open The VBA Editor In Excel

This page does not summarize or explain the programs available in the Excel file Excel-VBA used during the talk, but demonstrates the steps neccessary to write programs in Excel.
To obtain the file used for demonstration during the class, click the Excel-VBA file and choose to save it to a location of your convenience.

In the following instructions I unfortunately did not manage to be able to capture the pop-up menus, you have to read the text beside the figures.
For starters a warning: Never open a file you do not know and have not specifically requested from someone else with "Macros Enabled". It could be your last action on this computer. ALWAYS DISABLE macros when opening unknown files!
In order to start writing programs in MS Excel, choose View - Toolbars - Visual Basic
Click the highlighted icon to switch to design mode
Click the highlighted icon to open the toolbar
Click the highlighted icon , click on the sheet where you want to place the upper left corner, hold down the mouse button and drag to where you want the lower right corner of the command button to be to place a command button
Right-click the command button and choose View Code from the pop-up menu
This opens the Visual Basic Editor. Another way to open the Visual Basic Editor is to choose Tools - Macro - Visual Basic Editor from Excel.
You can now rename the command button to something useful (changing "CommandButton1" highlighted in the lower left window in the figure on the left), and assign the command button a task by entering Call "procname" below the Private Sub CommandButton1_Click() line in the upper right window in the figure on the left.
Now choose Insert - Procedure from the menu of the Visual Basic Editor, type the name of the procedure into the name field (leave the other choices as shown) and press OK. Do not insert a second procedure called "restruct1" into this Excel program. Use a different name.
Type the code of your program into the new procedure or copy it from an example.
After finishing the code, select Debug - Compile VBAProject from the menu of the Visual Basic Editor. If no error message occures, congratulations, you have a working program. In case of error messages, correct them (HaHa!)!
Now press F8 to debug the program, watch as the variables appear in the lower right window in the figure on the left and how they change, when you run through your code line by line by repeatedly pressing F8. To stop the program, press the icon that looks like the stop button of a CD player or a tape recorder in the menu of the Visual Basic Editor. Correct the code if necessary and repeat the debug process. When you are convinced your program is behaving nicely, return to Excel, close the Toolbar by pressing and exit the design mode by pressing (see figures at the top of this page).
Now you can run the program by pressing the command button.
Good luck!