15. Manipulating variables with if then statements

Perhaps it was no surprise that you can use the Excel functions you are used to and perform operations on variables. Perhaps this will be no surprise as well: you get If statements in VBA.

If-then-else statements exist in VBA

You can create if-then statements in Excel VBA macros

Perhaps you’re one of the people who appreciates the power of If functions as one of the most essential of Excel functions. Assuming that’s true, it’s going to kind of feel like coming home when you realise that you get the same thing with if-then statements in Excel VBA. Lovely.

Comparison with Excel's If function

If-then-else

Here we’ve:

  • Downloaded this example standard Excel If function spreadsheet
  • Clicked on cell B6
  • Clicked on Excel’s insert function (“fx”) button on the taskbar.

This is what pops up:

Image of Excel's If function

Notice the structure for the standard If function:

  • First there’s a logical test
  • If the logical test is true then we proceed with whatever we’ve got in the first box
  • If the logical test is anything else but true, then we proceed with whatever we’ve got in the second box.

Notice the if-then-else structure. That’s what we get working in Excel VBA. You’ll see that working shortly because we’ve got an example coming up for you.

You also get And, Or, Not in VBA

And Or statements in Excel VBA macros

Maybe you’ve played with Excel’s And/ Or functions. It might be comforting to know that you get those ones in VBA as well!

For the same money (think of it as a bonus, but maybe it isn’t the best you’ve ever had) you also get something called “Not” in VBA. We’re yet to use that one in Excel financial modelling but never mind – it exists.

Coming up next: an example where we can see all of this happening together

To summarise what we’re learning about variables so far:

  • You can perform basic operations on variables
  • You can use the Excel functions you are used to in VBA (in VBA they are called “Worksheet functions”)
  • You can manipulate variables using the familiar if, then, else logic
  • Plus you get to do a few other things to variables with And, Or, Not.

Perhaps it would help to look at an example where you can see some of this happening. That’s what we’ve got coming up in the next lesson, at the same time as we look at the usefulness of the “GoTo” statement in controlling program flow.

About this online financial modelling course material

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

3 comments so far:

...join the conversation...

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

  1. The Excel functions ‘If’, ‘And’ and ‘Or’ are very familiar to me. Admittedly ‘Not’ is less so, but nevertheless I had expected this module to focus on using functions in VBA rather than worksheet cells

    Reply
  2. Interesting but, not that easy.

    Reply
  3. Had to stop a while. Looks very interesting though. Hope I can use one of the codes to change part of cell name string.

    Reply