Division of Labour

One of the good things about using VBA with Excel is that you get two languages to play with: Excel formulas and VBA. We can use these in a complementary way, to effect a ‘division of labour’. Specifically, there are many built-in Excel functions, which it would be pointless (and less efficient) to re-implement in VBA.

Suppose that we need a VBA function to calculate an Order Price, as the back-end to a data-entry form:

Function Order_Price(productid As String, quantity As Long) _
                                              As Currency
        'pre: productid is in Catalogue
        'pre: quantity > 0

To calculate the price, we need to look up the Unit Price (UP) for the given productid (PID), in a Catalogue that is held on a worksheet.



There is already a formula function to do just this: VLOOKUP. The lookup value and the VLOOKUP formula go in two named cells, PID and UP respectively:



Now, all the VBA function needs to do is set the value of the PID cell, let the VLOOKUP do its work, and then get the value out of the UP:

    Dim unitprice As Currency

    Range("PID").Value = productid
    unitprice = Range("UP").Value

    Order_Price = unitprice * qty
                 'could apply delivery, discounts, etc
End Function

Notice that the Range function is used with names, not cell addresses. Firstly, this makes the code more readable. More importantly, it makes the code much more robust, since any structural changes to the worksheet are taken care of by automatic re-definition of the names “PID” and “UP”. Having literal cell addresses (“E2”, “F2”) in our code would be a maintenance nightmare. Anyway, you name ranges in Excel as standard practice, don’t you?

Note also that Value is not the default member of Range (there isn’t one), unlike form controls. So we can’t write just ‘Range(“UP”)’, we need the ‘.Value’.

You might wonder whether the VLOOKUP formula is guaranteed to evaluate before VBA moves on to the ‘unitprice = …’ statement. However, it always seems to work in practice, so my guess is that the worksheet calculations take place before control returns to the VBA.


2 Responses to “Division of Labour”

  1. 1 made a post August 12, 2014 at 2:25 pm

    Hey there! Do you use Twitter? I’d like to follow you if that would be okay.
    I’m absolutely enjoying your blog and look forward to new posts.

  1. 1 Excel Worksheet Functions « Roy MacLean’s VBA Blog Trackback on March 16, 2009 at 7:02 pm

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

February 2009
« Jan   Mar »

%d bloggers like this: