VBA and Excel for Engineers and Scientists – Part 3

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

Introduction

VBA in Excel is a fantastic tool for scientists and engineers. It gives you the power of writing your own programs or applications while using a more familiar environment (excel) with some powerful visualization capabilities and with no additional cost (above buying MS Office of course). In the first part of this series, I gave an overview of the benefits of developing spreadsheet applications in VBA and Excel, discussed other programs that use VBA, introduced you to the Macro Toolbar and Macro Menu, and the Visual Basic Editor (VBE) and showed how to write your first VBA program, the obligatory "Hello World". In the second installment, we dived more into the meat of the VBA programming language with a crash course in code containers, variables, flow control, debugging, VBA's built in functions, and writing your own functions to use in your code or in a formula on a worksheet.

Now that you have some basic familiarity with the language, we need to understand the basics of objects and how they are used in Excel. Then we can learn how to read and write values from individual cells and groups of cells using the most common object in Excel, the Range Object. We'll use a practical if trivial example of a bearing capacity calculation to show how you can implement a Monte Carlo type simulation using your new VBA skills. Finally, as an added bonus, we will learn how to use VBA move and copy files and directories on your computer and to read from and write to text files.

Objects in Excel and VBA

I haven't found a good definition of "Object Oriented Programming" that makes sense to a non-programmer type like me.  But objects do play an important role in VBA programming. Perhaps the best way to demonstrate the idea of objects is with a simple example.

Listing 1: Simple Object Example

'Traditional programming methods
CustomerName = "Randy"
CustomerAge = 28
CustomerEmail = "foo at bar.com"
MyResult = DoFancyEmailMarketing()
Call DisplayResultsSubroutine(MyResult)
 
'Object oriented programming method
Set oCust = New CustomerObject 
oCust.Name = "Randy"
oCust.Age = 28
oCust.Email = "foo at bar.com"
oCust.DoFancyEmailMarketing()
oCust.DisplayResults
 

Some examples of objects within Excel and VBA are:

• Charts, Chart axes, Chart series, Chart series data points
• Text boxes, Inserted pictures, Click buttons
• A Workbook (XLS/XLSX/XLSM/XLSB File), a Worksheet (one tab)
• A range of cells, a single cell
• Even Excel itself is an object to VBA

What makes an object?

Properties

The first thing to know about objects is that they can have properties. You can think of these as like variables contained within the object (like .CustomerName or .CustomerAge in the example above). They can be Read/Write, or Read Only and they can even be other objects. For example, if you are working with a chart object, oChart, the Legend property (oChart.Legend) returns a Legend Object.

Procedures and Methods

Objects also have Procedures and Methods. A Procedure is sort of like calling a function or subroutine within the object (like oCust.DoFancyEmailMarketing). Another example would be Excel.Calculate which tells Excel to recalculate all cells (useful if you have your Calculation option set to manual). A Method is a procedure that acts on another object.

How do you find the object you need?

The Object Browser (F2 Shortcut) can help you when you're looking for a particular object. The easiest thing is to simply search for the object you're looking for.

Figure 1 - Object Browser

Once you find it, click on ? Help button to read about the object you have highlighted. It also comes in handy to see what kind of Object to expect from a particular Method. You can also use it to traverse the hierarchy of a particular object. Below is the object hierarchy for the ChartObject (for Excel 2003 anyway…2007 should be similar).

Figure 2 - Chart Object Heirarchy from Excel 2003