VBA and Excel for Engineers and Scientists – Part 2
By Randy Post, P.E., G.I.T.
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.