The GoTo statement does exactly what it says on the tin: it sends your program wherever you want. The statement is really useful in controlling program flow and it’s really easy to create. If you look carefully you can see it working in the example below.
An example that manipulates variables
An example that tries to pull it together
Here’s some example code that tries to pull together some of what we’ve been learning. If you look closely at the example, you might be able to identify where we’re:
- Performing relatively simple operations on variables (InternalRateOfReturn = (MoneyOut / MoneyIn) ^ (1 / Years) – 1)
- Defining different kinds of variable (Dim…)
- Using If Then statements (including an optional Else), as well as an “Or” statement
- Controlling program flow with a GoTo statement (GoTo…)
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
You can copy the code into your own spreadsheet or download the example here: a IRR macro.
Getting GoTo to work
Getting the GoTo statement working
If you look at the example code above, you should be able to notice that there’s only two things we need to do to re-route this program:
- Establish a location within the program. The line “EnterMoneyIn:”, which includes a colon, does just that.
- Send the program to the location. The line “GoTo EnterMoneyIn” does that in this example.
Do you feel like we’re learning something about macros?
If you compare the code example on this page to some of the others we created earlier using Excel’s macro recorder, we think you’ll agree that we’re coming up with something that’s looking neater, more elegant and could conceivably be checked by another user. We’ve come a long way! Perhaps the warnings we gave out when we first got into macros are seeming a bit harsh now that we’ve improved the way we’re doing things!