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.