Return to Index
Operations Research Models and Methods
Computation Section
Do it Yourself

Visual Basic for Applications (VBA) is powerful tool for expanding the capabilities of Excel. The language is free to the owner of Excel and provides almost all the capabilities of a fully featured computer programming language. Its greatest advantage over other languages is that it allows direct control over Excel worksheets. With a little effort one can program almost any numerical task to be accomplished with Excel alone. You will significantly expand your own capabilities if you learn to program in VBA.



The materials on this site can be used in several ways to learn or use VBA. First the source code for each add-in is part of the add-in and can be viewed through the VBA editor. The source codes are password protected, but the password is found elsewhere on this site. The problem with this approach is the complexity of most of the add-ins and the author's programming style may make it difficult for you to find parts that may be useful.

Second, several of the add-ins provide functions that can be used for your own applications. For example, functions involving random variables may be handy for probability analysis and Monte Carlo simulation. When the Random Variables add-in is installed, the functions are available as user-defined functions and can be used just like the Excel built-in functions. These functions are described in the documentation of the add-ins and are reviewed in this section. Separate pages are provided for functions in add-ins under the Add ORMM menu and under the Add OMIE menu

Finally functions and subroutines can be copied from the source code of the add-ins and pasted into your own macro modules. They can be changed to suit your needs and used for a variety of applications. To this end we have compiled a number of subroutines and functions into a collection and stored them as macros in the workbook paj_components.xls. The file can be downloaded from the link at the left or from the Excel add-ins page of the site and opened like any Excel data file. We have not made this into an add-in so the subroutines and functions are stored on module sheets. When opening this file, the computer will ask if you want to enable the macros. You must answer yes if you plan to use them.

When using the results of this section please note that I provide no guarantee that the materials provided are entirely bug free. Although I have used most of the components before, translating them into the this new form may have introduced errors. Please forgive my programming style. I program with the "what works" philosophy. If you find a better way to perform the operations and functions provided, please use your way. My components are just a beginning, although in some cases they are the results of many previous experiments. I am programming using the VBA version for Microsoft Office X for the MacIntosh. This is not the same as those used for Windows, but I have found that the add-ins that I have written work for both Mac and Windows operating systems and in all the versions of Excel since Excel 1997. My programs do not take advantage of some features available in the later versions of Excel.

When learning VBA it is good to have a book. I use Microsoft Excel 2000 Bible by Walkenbach. Microsoft has provided a good Help facility with VBA.


Return to Top

tree roots

Operations Research Models and Methods
by Paul A. Jensen
Copyright 2004 - All rights reserved