VBA and Excel for Engineers and Scientists – Part 2

Wrap-up and Future Articles

As I mentioned at the beginning of the article, my intent was to let the 1st installment in this series on VBA and Excel for Engineers and Scientists be an introduction to the toolbars, the VBE and your very first VBA program. In this article, I hope that I gave you a crash course in the VBA language itself. We looked at code containers (modules), then two smaller types of "containers” (subroutines and functions) and finally the smallest of containers, variables. We examined the different types of variables, how to declare them and how to manipulate them with math operators and some of VBA’s built-in functions. We also quickly touched on VBA’s string functions for manipulating text.

We looked at flow control statements such as If statements, For loops etc. Next, we looked at various help resources available and introduced the object browser which will come in handy during the 3rd article in this installment and we touched on the all important topic of debugging your code. Finally, we examined how to create your own custom VBA functions that can be called as formulas in a worksheet.

In the third article in this series on VBA and Excel for Engineers and Scientists, we will start to put together the concepts we have learned so far and accomplish some of the things that you really wanted to learn when you started reading this series of articles. Things like how to repeat a calculation multiple times for a range of inputs or how to perform a Monte Carlo simulation. In order to accomplish that we will have to look at what objects are, and how to work with them. We’ll focus on the Range object which is how we will be manipulating cells in a spreadsheet but we will also mention other Excel objects like the Workbook, Worksheet and Chart objects. Best of luck luck with your VBA programming. I welcome any comments or corrections you might have on this article, and I’ll do my best to address any any Excel/VBA questions you send my way.

References

1. Samtani, N.D. and Nowatzki, E.A. (2006), Soils and Foundations Reference Manual. Federal Highway Administration (FHWA) Publication Nos. FHWA NHI-06-088 and FHWA-NHI-089, NHI Course No. 132012, December 2006.
2. Walkenbach, J. (2004a). Excel 2003 Power Programming with VBA. Wiley Publishing, Indianapolis, Indiana.
3. Walkenbach, J. (2004b). Excel VBA Programming for Dummies. For Dummies Publishing.

Recommended Reading

I don’t have the 2007 versions of these books yet, but if your’re serious about creating spreadsheet applications with Excel and VBA, these are must-haves. And yes, I get a small commission if you click on the links and buy through Amazon.