Get the user form working

There are definitely some tricks to getting our user form working. Already we’ve:

Now we need to:

  • Make sure that the code in the main body of our macro is doing what we want it to
  • Make sure the components of the macro link/ fit together properly (that the user form pops up at the right time) and
  • Think about the variables that are being used.

Code for the main body of the macro

Code for the main body of the macro

The code in the main body of the macro needs to calculate returns for us (in three different ways) – ready for output based on the user’s selection once they’ve hit the command button. Place the code below in the main body of the macro in module 1. That will do the job for you. It’s calculating returns three ways.

    Sub Returns_macro()

    'Create a location for a GoTo statement later. Enables us to re-start this routine if invalid inputs are entered

    'Collect up inputs
    MoneyIn = InputBox("Enter the amount of money you are putting in to the investment: ", "Money In")
    MoneyOut = InputBox("Enter the amount of money you expect to get out at the end of the investment: ", "Money Out")
    Years = InputBox("Enter the time-frame for your investment (in years): ", "Time in years")
    TargetRate = InputBox("Enter your target/ hurdle rate of return (as a fraction e.g. enter 0.1 for 10% :", "Hurdle rate")

    '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
    'Close If statement
    End If

    'Check whether target hurdle rate has been entered as a valid value
    If TargetRate < 0 Or TargetRate > 1 Then
    'Notify the user that they have entered invalid values
    MsgBox ("Target rate of return needs to be a number between 0 and 1 representing a % e.g. for 10% enter 0.1. Please start again ")
    'Use GoTo statement to send the user back to the beginning
    GoTo EnterMoneyIn
    '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)

    'Calculate money multiple
    MoneyMultiple = (MoneyOut / MoneyIn)
    'Format money multiple to 1 decimal place
    MoneyMultiple = Format(MoneyMultiple, "#,##0.0")

    'Calculate NPV of investment
    NPV = (MoneyOut / (1 + TargetRate) ^ (Years)) - MoneyIn
    'Format NPV to 1 decimal place
    NPV = Format(NPV, "#,##0.0")

    'Display user form
    End Sub

Getting the user form to pop up

Calling the user form

We want the user form to ‘pop’ up at the right time. This is an important little piece of code (inserted towards the bottom of the code we just showed you) that achieves that for us.

    'Display user form

Assigning variables

Assigning variables

We’ve already looked at the topic of macro variables. Remember variables are temporary stores where we keep the results of our work for a short period of time. In all our examples to date though, we’ve had one little macro with variables. Now we’ve got something more complicated (each button on the user form gives us the opportunity to start a new macro running). We need to make sure that any variables/ stores we create are available for later use. Here’s how to do that:

  • Move your variables up above any other code
  • Declare your variables as “Public” variables available to other parts of your macro.

Here’s an example where that’s been done:

You can copy and past the code from here. Note the variables are assigned before the first line beginning “Sub [macro name]”.

    'Establish variables. Declaring variables above "Sub" and using the word "Public" means the variables now become available to other sections of code (modules) accessed by the user form. Strictly speaking not all these variables have to be available to all modules for this program.
    Public MoneyIn As Double
    Public MoneyOut As Double
    Public Years As Double
    Public TargetRate As Variant
    Public InternalRateOfReturn As Variant
    Public MoneyMultiple As Double
    Public NPV As Double

Download the complete user form example

Userforms: download the complete working example

Here you can download the complete working example: user form macro.

Extra for experts

User forms: extra for experts

If you’ve downloaded the example above you’ll notice that to complete our work we’ve created a button in tab 1 that runs the macro. If you’ve been following all our course material on macros you already know how to run a macro automatically.

We’ve also used the tool box to add a few more instructions on the user form (the words “Tell us how you would like to measure returns”). See whether you can do that yourself (on the tool box you need the first row, second one in).

We’ve also added a title to the user form (“measuring returns”). Again, try and see whether you can figure out how to do this. Click on the user form, “View” “Properties window” and look under the “Caption” property.

Summary – user forms

We said when we got into this that user forms were a big topic! Let’s reflect for a moment on all the pieces of the puzzle that have had to come together to create a working custom user form. It’s a check list, if you like, for creating a user form. You get started with user forms by inserting a new form. Then you:

The above list has gotten us most of the way there but to finish the job just now we’ve had to:

  • Get the right code working in the main body of our macro
  • Call the user form at the correct point
  • Declare variables in the correct way (above the first line of the rest of our code).

To ‘finesse’ our macro (under “Extra for experts” above) we’ve:

  • Got the macro running automatically from a button in the first tab of the spreadsheet
  • Added a label to the user form with a few more instructions
  • Accessed user form properties to add a label to the user form.

Of course it’s a big topic but with user forms you get plenty of flexibility (that makes them powerful). You can have some fun creating them and jumping around the room celebrating when they really work!

About this online Excel course material

You’re looking at a free online course extract covering different kinds of Excel VBA macro dialog boxes and user forms.

1 comment

...join the conversation...

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

  1. Brilliant course, useful for all sorts of programming i should imagine and expecially for getting more out of Excel.