Microsoft Excel and Visual Basic for Applications or VBA make a powerful combination and a useful tool for scientists and engineers. In the first article in this series, I attempted to give you a basic understanding of VBA and an introduction to the Visual Basic Editor or VBE environment. We also produced our first code (the obligatory "Hello World"). In this long awaited second article in the series, I will dive into the meat of the VBA programming language, including code containers, data types, variables, math operators, built-in functions, flow control, and even debugging.. In the end, you should be able to code some fairly complex things including custom worksheet functions that you can use in your spreadsheet just like a regular formula. Also in this article I give you a sample spreadsheet with some useful utilities that you can use right away. One that is particularly useful is a general interpolate/extrapolate function. Enjoy!

VBA and Excel for Engineers and Scientists – Part 2

By Randy Post, P.E., G.I.T.
Editor, GeoPrac.net

Introduction

In the 1st installment in my series on VBA and Excel for Engineers and Scientists, I provided an overview of the benefits of developing spreadsheet applications in VBA and Excel. I also discussed other programs that use VBA, introduced you to the Macro Toolbar and Macro Menu, and the Visual Basic Editor (VBE) and you wrote your first program, the obligatory “Hello World”.

In this article, I hope to provide you with a crash course in the programming language basics so we can move on to solving some problems that are a little more interesting and practical to an engineer or geologist! In the first few sections I’ll cover various types of containers for your code, Subroutines vs. Functions, variables and data types, functions, and math operators and functions. Then in the last section or two, we will look at creating your own excel functions. I’ll even give you some free VBA code for easily performing linear interpolation and extrapolation. Let’s get to it!

Note: Most of this article was written with Excel 2003 in mind, but I have since started using Excel 2007. I have found that there are no really significant changes to the way VBA operates in 2007 so the content of this article should work for either version. If I’m wrong, let me know! I have also developed for Excel 2000 and even Excel 97, and the topics covered should be relevant to those versions as well.

TrackBack URI for this entry

Comments (1)

Subscribe to this comment's feed

Ted said:

Very nice. I have avoided vba and macros up to this point but I think I'll give it a try.

Monday June 16, 2008 10:13AM

Show/Hide comment form

Name

Email

Title

Comment

Write the displayed characters

Add Comment