19. Make Excel VBA macro dialog boxes

We’ve already been using message and input boxes in some of the examples we’ve been running with you – that was deliberate. We’re hoping that maybe there’s been a bit of learning by osmosis. Perhaps you’ve had a chance to see the code that we’ve been using and what the boxes are doing.

Example message and input boxes

An example of message and input boxes at work

Here’s the code we were working with when we ended up looking at goto statements. You can download the example here: a IRR macro. There’s a lot going on. We’re


    Sub IRRMacro()
    'This macro calculates the IRR of an investment
    'Establish variables
    Dim MoneyIn As Double
    Dim MoneyOut As Double
    Dim Years As Double
    Dim InternalRateOfReturn As Variant
    'Create a location for the later GoTo statement
    EnterMoneyIn:
    'Collect up inputs
    MoneyIn = InputBox("Enter the amount of money you are putting in to the investment: ")
    MoneyOut = InputBox("Enter the amount of money you expect to get out at the end of the investment: ")
    Years = InputBox("Enter the time-frame for your investment (in years): ")
    'Check whether any of the inputs are less than 0
    If MoneyIn < 0 Or MoneyOut < 0 Or Years < 0 Then
    'Notify the user that they have entered invalid values
    MsgBox ("All your inputs need to be +ve numbers - please start again ")
    'Use GoTo statement to send the user back to the beginning
    GoTo EnterMoneyIn
    'The next two lines are optional for what we really want to achieve here - you could delete or comment them out and the macro would still work
    Else
    MsgBox ("Ooo that's a lot of money you're putting in - do you think you'll be happy with the expected returns? ")
    'Close If statement
    End If
    'Calculate IRR of investment
    InternalRateOfReturn = (MoneyOut / MoneyIn) ^ (1 / Years) - 1
    'Format IRR as % to 1 decimal place
    InternalRateOfReturn = FormatPercent(InternalRateOfReturn, 1)
    'Output the result in a message box
    MsgBox "The IRR of your investment is " & InternalRateOfReturn
    End Sub

Message boxes

The message box

If you run the above macro you’ll see the message boxes at work. The clue’s in the title: the box gives the user a message. Here are two lines in the code that produce message boxes:


    MsgBox ("All your inputs need to be +ve numbers - please start again ")
    MsgBox ("Ooo that's a lot of money you're putting in - do you think you'll be happy with the expected returns? ")

Notice the brackets (). For a simple message box like this the brackets are optional – the message box will work fine without them. You can see this at the bottom of the example where the brackets are not used but the macro still works just fine:


    MsgBox "The IRR of your investment is " & InternalRateOfReturn

We’re furiously hoping that you’ll be happy with the idea that you can copy something similar into macro code of your own.

You can do more with message boxes

Our message boxes above are about the simplest imaginable. We’d like to show you how to do more with message boxes.

Input boxes

The input box

Again, appropriately named. This one requests an input from the user. Here’s where we’re asking for inputs in the code, linking those straight away to the macro’s variables:


    MoneyIn = InputBox("Enter the amount of money you are putting in to the investment: ")
    MoneyOut = InputBox("Enter the amount of money you expect to get out at the end of the investment: ")
    Years = InputBox("Enter the time-frame for your investment (in years): ")

Create your own boxes: user form controls

As well as using the standard message boxes and input boxes, you can also create your own custom dialog boxes with something called user forms. That’s what we look at next. It’s a (very) big subject all of its own, but we’d like to at least show it to you and get you started.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering macros.

4 comments so far:

Leave a Reply to Ian Martin Cancel reply

Your email address will not be published. Required fields are marked *

  1. This is fabulous.

    Reply
  2. The extra work on message boxes was useful, so perhaps that could be urged more strongly in that part of the basic lesson.

    Reply
  3. Many thanks indeed for the series of macro lessons which i found it to be priceless in importance.
    Really! Your kind great effort is highly appreciated.

    Reply