Step Six - Input

Now that you can display a message the next step is to find out how to get information back from the user - i.e. let them input something.

This makes use of another VBA command, InputBox, but pay attention because it is a little more tricky than MsgBox.

The command

A = InputBox("How old are you")

will display an input box on the screen with the message - How old are you - to prompt the user to enter a numeric value.

When the user presses the enter/return key the input box vanishes and the program continues. The value that the use has entered is stored in the variable A.

This raises the question of what is a variable?

If you are used to the idea of storing data in a spreadsheet then think of a variable as a named cell - but, and this is important, a VBA variable doesn’t have anything to do with any worksheets you may have open.

When you create a variable VBA sorts out the problem of where to store the data and looks after it for you without involving a spreadsheet. You can create a variable simply by using its name and you can call a variable almost anything you like. In this case it would be more reasonable to call the variable Age rather than A - but it’s up to you what you call a variable.

Once the value is stored in a variable you can feel free to make use of it. For example, the program

Sub hello()
 A = InputBox("How old are you")
 MsgBox A
End Sub

asks the user for their age and then displays it in a message box.

Notice that this is a little more sophisticated than you might realise. The MsgBox command doesn't display the letter A but the contents of the variable A i.e. a number. This is the same distinction as "A1" i.e. the text consisting of the letter A and the number 1 and =A1 used to signify the contents to the cell A1.

There are some problems that you need to know about that concern the type of data - i.e. numeric, string, date, etc. that a user might enter but we will look this sort of “real world” problem later.

For the moment be warned that you might find that some programs that you think ought to work don’t because of data typing problems.

But to show you that you really have learned something useful let's write a small but fairly complete program.

Excel Example

There isn’t a great deal that we can do with VBA as introduced so far but it is possible to give a small example program that gives you the flavour of what you can expect.

One of the problems with building spreadsheets for other people to use is that you have to make sure that they know what data needs typing in and where.

Using VBA you can actually prompt them for information in the order that you want it, use it and show them the answers.

Notice that in this early example we are guilty of completely ignoring the spreadsheet component of Excel and this wouldn’t be the case in a real VBA program.

Starting with a new or completely empty module enter the following

Sub loan()
 MsgBox "This program works 
            out the cost of a loan."
 Amount = InputBox("How much do you 
                        want to borrow ")
 Interest = InputBox("What is the 
                interest rate per month")
 Months = InputBox("What is the payback 
                         time in months")
 pay = Application.Pmt(Interest, 
                         Months, -Amount)
 MsgBox "Your monthly payment is " & pay
End Sub

Notice that we have had to split lines to make them fit on the page - you should type complete lines in. Examine the screen dump if you are in any doubt:

If you run this program you will see a message box telling you what the program does and then three input boxes getting relevant information. Finally a message box gives you the monthly payment.

Don’t take this example too seriously because there are no error checks to make sure that you have entered sensible values in the correct form - however these would be very easy to add.

You should be able to understand most of this program without any extra explanation however the line that does the calculation is beyond what has been introduced.

What this does is to use the worksheet PMT function to work out the payment on the loan. You have to use the function in the form

Application.Pmt(

to let VBA know that this is a function supplied by the spreadsheet and not an internal VBA command. Yes this is your first reference to Excel in a VBA program - more of which in the next chapter.

Another puzzle might be the way the final message box joins the text message and the numeric result - this is an example of the data type problems mentioned at the end of Step Six. Don’t worry about it for the moment.

Finally notice the way variables have been introduced to store all of the data collected by the input boxes and for the monthly payment results.

Although this is a very simple example you can begin to see that a VBA program can interact with both the user and, to an extent, the spreadsheet. This is one of the big advantages of VBA - you can specify exactly what is to happen and when.

Later you will discover how to build complete dialog boxes with buttons, text boxes and so on. In this way you can make an easy to construct Excel spreadsheet look like a fully programmed application developed using C++/C#  or whatever.

The good news is that it is only a short step from a simple example such as this one to useful applications!

<ASIN:0764540726>

<ASIN:0596007663>

<ASIN:0789736829>

<ASIN:1598633945>

<ASIN:0470046430>

<ASIN:0789739380>

<ASIN:0789743140>