16. The usefulness of “GoTo” in controlling VBA program flow

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:

    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
    '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
    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!

1 comment

Leave a Reply to Ian Martin Cancel reply

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

  1. Clearly explained – as always. I feel that there’s quite a lot to do in the last four lessons to achieve a useful level of proficiency.