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.