If then else

“If then” statements do the same kind of job as Excel’s if funtions assuming you’re familiar with those. In VBA they make your macro do something if a particular condition is met. If the condition is not met, then your macro does something else.

    We’ve already created some examples for you of if then statements working in conjunction with “Goto” statements. Also there’s an example where we’ve shown you how to get “User forms” working. Here’s a third example. To understand it fully you need to see our definitions for “Variables“, “Input box” and “Message box“:
    Sub IfThenElseStatement()
    'This macro demonstrates an If Then Else statement
    'Establish the variable Result
    Dim Result As String
    'Display input box to collect up Result
    Result = InputBox("See whether you can enter 'x' (lower case) below:")
    'Start If statement
    If Result = "x" Then
    'This is what happens if the condition is met
    MsgBox ("Well done, you got it right!")
    Else
    'This is what happens if the condition is not met
    MsgBox ("You got it wrong")
    'End the if statement
    End If
    End Sub
    Note you don’t always need the else part of the statement to get something working. You can see that from the example where we’ve got the user form working. Here’s an example that uses if and then but not else and still works. This time it makes use of a “Loop” which runs util the input is correct:
    Sub IfThenStatement()
    'This macro demonstrates an If Then statement - Else is not required for this one
    'Establish the variable called Result
    Dim Result As String
    'Display input box to collect up Result
    Result = InputBox("See whether you can enter 'x' (lower case) below:")
    'Start If statement. Runs if result has been incorrect
    If Result <> "x" Then
    'Display message box
    MsgBox ("Nope, sorry you're going to have to try harder than that")
    End If
    'Start loop
    Do Until Result = "x"
    'Collect up Result
    Result = InputBox("Please try again - enter 'x' (lower case) below:")
    'End loop
    Loop
    'Display message box with feel-good message
    MsgBox ("Well done - you got it right!")
    End Sub

Indent

I also stands for “Indent”. “Code” junkies will indent their code at certain points – typically when you start something new that is self contained within your macro like a “Loop” or an “If then else” statement. Along with “Comments” it’s just a way of making your code clearer and making it look like you know what you’re doing. We’re the first to admit that in places we’ve been a bit sloppy with our indentation and you will be able to do better. You can see us indenting in the pictures of the examples above.

Input box

I stands for “Input box”. Input boxes allow you to collect up data for use in your “Macro“. See under “Message box” for an example of input boxes at work. You can create your own tailored input boxes with custom “User forms“.

Macros coverage from the financial modelling course

You’re looking at the macros glossary from our course material covering the use of macros in Excel financial modelling.

...join the conversation...

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