Excel Example

Now that you know how to get at values within a worksheet you can start to think of ways of making VBA and Excel work together.

In nearly all cases you have a choice of whether to implement what happens as part of the VBA program or make use of the spreadsheet to do the same job. Of course if you end up doing everything in VBA then there is little point in having the spreadsheet attached at all!

For example, take the VBA program in Chapter One, Getting started with Excel VBA.

It used message and input boxes to get the data from the user and then worked out the answer, the regular repayment needed to pay off a loan. The working out was done within VBA and the data never got anywhere near the spreadsheet.

This program could just as easily have been written using Visual Basic or C# and of course this was the point being made!

In real life however VBA programs are generally make more use of the application that they are tied to. For example, the loan program could easily be re-written so that the data was stored in the spreadsheet and the calculation performed in the spreadsheet.

Starting with a new workbook insert a module using the command Insert,Macro,Module and enter the following code.

Sub loan()
MsgBox "This works out the cost of a loan."
With Workbooks("Book1").Worksheets("Sheet1")
.Range("A1") = InputBox( _
     "How much do you want to borrow ")
.Range("A2") = InputBox( _
    "What is the interest rate per month")
.Range("A3") = InputBox( _
   "What is the payback time in months")
.Range("A4") = "=Pmt(A2, A3, -A1)"
.Activate
MsgBox "Monthly repayments " & .Range("A4")
End With
End Sub

The program starts off in the same way as the original but now it stores the input values in A1, A2 and A3 of Book1, Sheet1. Notice the way that the With command is used to specify Book1.Sheet1 as the default for all of the references.

The fourth command starting .Range is interesting because this inserts a formula into A4. Notice that the formula has to be enclosed in quotes to stop VBA from trying to work it out before it is stored in the cell!

If you run the program and have a look in cell A4 you will find that it contains the formula =PMT(A2,A3,-A1) just as if you had typed it in.

Of course an alternative is to do exactly that - store the formula in the cell manually before the program is run. This would mean that the program was making assumptions about what was already stored in the worksheet but this is the way programs usually interact with spreadsheets. Usually you build a spreadsheet that does as much of the job as possible and then write a program to extend what it does. However as this example demonstrates you can always write a program that generates the complete spreadsheet.

The main advantage of this approach is that after the program has run the data and formulae are still in the spreadsheet and can be updated in the standard way. That is after running the program you can type in some new values into A1, A2 or A3 and the repayment will be immediately recalculated - which is of course the power of the spreadsheet and the advantage we gain by using it rather than a VBA function.

The line .Activate makes Sheet1 the active sheet  and just to make sure that the user knows a message box also tells them the answer.

Of course in a real application the spreadsheet would have been prepared with titles and formatting to make the presentation better. It might even have had the formula already inserted into cell A4 which would have relegated the VBA program to the task of getting the data and perhaps showing the results. This is not an uncommon role for VBA to play when the spreadsheet is more complicated. By guiding the user to supply the appropriate values and checking them it can make filling in the “data” part of a spreadsheet foolproof without losing any of the spreadsheet's advantages.

<ASIN:0764540726>

<ASIN:0596007663>

<ASIN:0789736829>

<ASIN:1598633945>

<ASIN:0470046430>

<ASIN:0789739380>

<ASIN:0789743140>