There are definitely some tricks to getting our user form working. Already we’ve:
- Created a user form with buttons and code attached
- Thought about how we should structure the user form macro code.
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.
'Create a location for a GoTo statement later. Enables us to re-start this routine if invalid inputs are entered EnterMoneyIn:
'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 UserForm1.Show 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 UserForm1.Show
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:
- Add option buttons, command buttons or other buttons and input boxes to the user form
- View your user form macro code (created when you add something to a user form)
- Structure the user form macro code – get each piece of your user form code doing exactly what you want.
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!