### 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.

Catalogue

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:

Lookup

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. 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