Excel Worksheet Functions

In an earlier posting, I talked about the possible division of labour between VBA code and Excel formulas. An alternative approach is to call Excel worksheet functions directly from your VBA code, without the need for input cells and formula cells on a worksheet.

The Excel VBA library defines a global property WorksheetFunction, which returns the singleton WorksheetFunction object. This object offers methods corresponding to a selection of worksheet functions (about 180). These include the financial functions (PMT, etc), statistical and mathematical functions. There are also the Database functions (DSUM, etc), which could be useful.

Where a function would normally (in a formula) take a range reference or name, it might require a Range object. This could well be constructed using the Range function – e.g. Range(“A2:B6”). Not that you should be using literal range addresses in your VBA code, of course. Either you’ll be using range names, or the address string is going to be computed in some way. The latter offers interesting possibilities.

Where an argument must be a Range object, this is indicated in the function signature (in the Object Browser details): Arg1 As Range. Some functions can take either values or ranges – for example, SUM. Here the arguments show as untyped.

So in our Division of Labour example, we could have called the VLOOKUP within our code, rather than using on-worksheet cells. This would simplify the worksheet structure, and keep the VLOOKUP out of the hands of ordinary users (if we think that’s necessary). On the other hand, there’s a case for making things visible on the worksheet: we can see the last used lookup value, it’s easy to test, and so on.

A couple of things I want to investigate. Firstly, the Database functions are specified as (for example) DSUM(Arg1 As Range, Arg2, Arg3) As Double. This suggests that the Criteria (Arg3) can be an array, rather than a Range. Secondly, the array function Transpose(Arg1) is available, which looks like it could be useful – I’ll try to come up with an example. More in a couple of days.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

March 2009
« Feb   Apr »

%d bloggers like this: