VBA Macros for Solving Problems

1
VBA Macros for Solving Problems in Water
Chemistry Chad Jafvert Purdue University
The Educational Objectives of the Tools Students
will Design and create VBA Macros (i.e.,
programs) that solve analytical and numerical
problems in aquatic chemistry and related fields.
(Levels 5 (Synthesis) 3 (Application) in
Blooms Taxonomy of Educational Objectives) This
computer-based learning tool consists of the
instructions and example programs that students
can apply and mimic in designing their own
solution algorithms.
The Ingredients Excel (Microsoft Office) loaded
on any PC
2
Development Time ? Development Time (for
students) 1 to 2 50-min lectures to convey the
tools to the students, 5 min to 40 hrs for
students to write a successful VBA macro.
Educational Outcomes The computer skills,
program language backgrounds, and programming
phobias of students entering graduate
environmental engineering and science programs
are wide-ranging, yet these students always have
in common the ability to perform mathematical
operations in spreadsheets like Excel. With very
little additional instruction, they are using VBA
in Excel as a programming language and solving
rather complex mathematical problems with
software they are comfortable with, that is
ubiquitous in nature (including on their own
PCs), and that has immediate graphical output.
3
Web Address where Instructions and Examples can
be Downloaded http//bridge.ecn.purdue.edu/jafve
rt/Macros.html
Downloads (demos) Instructions An Acrobat file
contains the instructions for writing VBA macros
in Excel and further explanations on each of the
sample spreadsheets below. (15 pages) Introductio
n to VBA.xls An Excel spreadsheet that contains
an example VBA macro that shows how to (i)
dimension variables and arrays, (ii) write 'For .
. . Next loops, (iii) read and write to the
spreadsheet, (iv) format common mathematical
expressions, (v) etc. Functions.xls A simple
spreadsheet than contains some user-defined
functions (volume of a cylinder, roots to the
quadratic equation, and the sin of an angle
measured in degrees). pKa.xls A spreadsheet
that calculates buffer composition and draws the
pC-pH diagram of a simple acid.
4
Downloads (cont.) Case 3 p60 in Morel
Hering.xls A spreadsheet that solves a system
of three equations and three unknowns by
Newton-Raphson iterations. The 'recipe' for the
problem is the same as 'Case 3' on page 60-63 in
the text "Principles and Applications of Aquatic
Chemistry" by Morel and Hering. Stratified
Lake.xls A spreadsheet that solves a system of 2
simultaneous ODE's with Euler's method. The
example case is the transport of TCE in a
stratified lake - presented on pages 551-574 in
"Environmental Organic Chemistry" by
Schwarzenbach, et al. The 2 ODE's are eqs 15-30a
and 15-30b (p. 569) in the text. 1D
Diffusion.xls The central difference solution to
the 1-D diffusion equation with an impulse input
at the center of the media. The numerical
solution is calculated with a VBA macro and is
compared to the analytical solution calculated
directly on the spreadsheet. A second macro
calculates the mass under the concentration
profile with Simpson's Method.
5
A Very Simple Example VBA program that reads and
writes In Excel, Type 3 in cell B4, then click
on Tools, Macro, Macros. Provide a name for
your macro, click on create Type the following
lines and click on Run, Run Sub
6
Now you are ready to solve 3 simultaneous
equations
By finding the roots with the Newton-Raphson
method
First, solve for the functions and the partial
derivatives with initial guesses, invert the
matrix, and do the math to solve for the new
guesses, all on the spreadsheet.
Next, replace the old guesses with the new
guesses with a simple read and write VBA
program. Repeat (iterate) until the problem
converges.