13. Perform operations on VBA macro variables

If you’re used to building basic formulas in Excel then the kinds of operations you can perform on VBA variables won’t surprise you at all.

Performing operations on variables

Here are some of the things you can do with variables. If you’ve been working in Excel then they should seem pretty familiar:

  • Add: +
  • Subtract: –
  • Multiply: *
  • Divide: /
  • To the power of: ^
  • Join two text strings together: &.

Example code: performing operations on variables

An example of how to perform operations on variables

Here’s some example code where we’re performing an operation on variables. The operation is happening towards the bottom where you see the code: ((MoneyOut / MoneyIn) ^ (1 / Years) – 1). There are a few other things happening as well:

  • Establishing variables (see our important notes on different kinds of variable)
  • Using input boxes to collect up inputs
  • Formatting the output as a %
  • Displaying the output using a message box.

Here’s the code you need to copy and paste into your own spreadsheet, or you can download the example here: IRR macro. Run the macro and try using the inputs 40, 120 and 5 – it should work and give you an IRR of around 25%:

    Sub IRRMacro()
    'This macro calculates the IRR of an investment
    'Establish variables - "Double" is a number with a decimal point
    Dim MoneyIn As Double
    Dim MoneyOut As Double
    Dim Years As Double
    'Variant is Excel's default and, in this case, helps when we come to format IRR as a % later. You could delete the line below and the macro would still work
    Dim InternalRateOfReturn As Variant
    'Use input boxes to 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): ")
    '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

It’s the line ((MoneyOut / MoneyIn) ^ (1 / Years) – 1) where the operation is being performed. If you’re unsure of the maths involved, see our notes on calculating IRR where we talk about calculating IRR using algebra.

Try running the macro again. What if  (big hint) you enter a negative number as an input. Notice that the macro stops working? What could help us here? That’s why you need to keep on with this course material…

More functions you can use with VBA macro variables

There are a few other things you can do with variables apart from simple mathematical operations. Next we go on to:

  • See how we can use the Excel functions we are familiar with inside VBA
  • Look at if-then statements in Excel VBA
  • Create an example that tries to pull together much of what we’ve been trying to demonstrate with macro variables.

About this online financial modelling course material

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

1 comment

...join the conversation...

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

  1. These modules make extensive, if somewhat exaggerated, use of comments in the code. It shows how this can hel to overcome criticism that use of VBA in models makes their functionality obscure.