Select Page

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

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

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

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

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