Get this lesson’s code on my GitHub.
An if-then-else statement is pretty much exactly how it sounds: if X happens, then do something; else do something else; followed by “end if”.
Function fFunction()
Dim x as Integer
Dim y as Integer
x = 0
y = 1
If x > y Then
Debug.Print "X is less than Y!"
Elseif x = y Then
Debug.Print "X and Y are the same!"
Else
Debug.Print "Y is less than X!"
End If
End Function
You can also do things like toggle boolean variables/properties:
Function fFunction()
If ActiveDocument.Saved = True Then
ActiveDocument.Saved = False
Else
ActiveDocument.Saved = True
End If
End Function
And here is a shorter one-line version of the above, and you can use this shortcut to toggle any boolean properties in this way:
ActiveDocument.Saved = Not ActiveDocument.Saved
Understanding how if-then-else statements work is really important to what we’re going to set up here. If-then-else is one of the ways in which you tell the computer how to make the decisions you need it to make while running. Sometimes this is a great place to use pseudocode to outline the various choices and make them easier to write out in VBA code.
Try to not nest your decisions (loops/ifs/etc) too many times in because, the more loops and if statements you nest, the more confusing it gets to sort out later when nobody has looked at it in months.
The use of ‘ElseIf’ and ‘Else’ are both optional. You can tie requirements together with ‘and’, ‘or’, or use the negative value with ‘not’:
Function fIfNot()
Dim x as integer
If not x = 4 Then
'do things here
End If
End Function
And here’s a demonstration of the use of ‘and’ and ‘or’:
Function fIfAndOr()
Dim x as integer
Dim y as integer
If x + 1 > 4 and y > 5 Then
'do things here
Elseif x + y < 5 or x - y < 5 Then
'do things here
End If
End Function
You can use a one-line if statement, but there is no ‘End if’ at the end of it:
Function fFunction() Dim x as integer x = 3 If x = 3 Then Debug.Print "X is not a million!" End Function
Select Case
I don’t use it much, but it’s still relevant. Here is the syntax for select case, another method of telling the computer how to make the decisions during the function:
Function fCaseSelect()
Dim vX As Variant
Dim sX As String
vX = InputBox("How many chocolates do you have?")
Select Case vX
Case " "
End
Case Is < 0, 0, 1 To 2
sX = "You have so few pieces of candy."
Case 2 To 3
sX = "Have a moment to yourself."
Case 3 To 4
sX = "Man, are you having a good day or what?"
Case 4 To 6
sX = "Maybe you can trade up!"
Case 6 To 10
sX = "Hope you're sharing!"
Case Is > 10
sX = "Wow, is it Halloween?"
End Select
Debug.Print sX
End Function
Full Code
These decision-making pieces of code will help us when we need the function to, for example, select the correct template. Here’s a real piece of code I use which has a lot of the things I just discussed in it. It calls a certain function and references a certain template name depending on what’s stored in the variable ‘sJurisdiction’. The “Stage2” is the parent folder they’re in:
Public Function pfSelectCoverTemplate()
'============================================================================
' Name : pfSelectCoverTemplate
' Author : Erica L Ingram
' Copyright : 2019, A Quo Co.
' Call command: Call pfSelectCoverTemplate
' Description : parent function to create correct transcript cover/skeleton from template
'============================================================================
Dim sFDAQuery As String
Call pfCheckFolderExistence 'checks for job folder and creates it if not exists
sFDAQuery = "Food" & "*" & "and" & "*" & "Drug" & "*" & "Administration"
Call pfCurrentCaseInfo 'refresh transcript info
If ((sJurisdiction) Like ("*" & sFDAQuery & "*")) Then
pfCreateCover ("Stage2s\TR-Company4-FDA.doc")
ElseIf sJurisdiction = "Company2 USBC" Then pfCreateCover ("Stage2s\TR-Company2-Bankruptcy.dotm")
ElseIf sJurisdiction = "Company1 Oregon" Then pfCreateCover ("Stage2s\TR-Company1Oregon-Template.dotm")
ElseIf sJurisdiction = "Company1 Nevada" Then pfCreateCover ("Stage2s\TR-Company1Nevada-Template.dotm")
ElseIf sJurisdiction = "Company1 Bankruptcy" Then pfCreateCover ("Stage2s\TR-Company1Bankruptcy-Template.dotm")
ElseIf sJurisdiction = "Non-Court" Then pfCreateCover ("Stage2s\TR-noncourt.docx")
ElseIf sJurisdiction Like "District" & " " & "of" & " " Then pfCreateCover ("Stage2s\TR-Bankruptcy.dotm")
ElseIf sJurisdiction = "Company3 BK" Then pfCreateCover ("Stage2s\TR-Company3-Bankruptcy.docx")
ElseIf sJurisdiction = "Company3 NJ" Then pfCreateCover ("Stage2s\TR-Company3-NJ.docx")
ElseIf sJurisdiction = "Company2 NH" Then pfCreateCover ("Stage2s\TR-Company2-NH.dotm")
ElseIf sJurisdiction = "Company2 OC" Then pfCreateCover ("Stage2s\TR-Company2-OC-CA.dotm")
ElseIf sJurisdiction = "Company2 NH" & "*" Then pfCreateCover ("Stage2s\TR-Company2-NH.dotm")
ElseIf sJurisdiction = "Company2 NH" Then pfCreateCover ("Stage2s\TR-Company2-NH.dotm") '1.3.2
ElseIf sJurisdiction Like "Massachusetts" Then pfCreateCover ("Stage2s\TR-Mass.dotm")
Else: pfCreateCover ("Stage2s\TR-WACounties.dotm")
End If
Call pfCommunicationHistoryAdd("CourtCover")
Call pfClearGlobals
End Function
Now, the last thing I will point out about this function is that you will notice the ‘if’ or first part of the if-then-elseif-else statement takes up two lines. If you do NOT put the ‘if’ part of the statement on two lines, it will not process the rest of the statement as part of itself. It will think the next line is a different ‘if’ statement and error out because the ‘new’ one doesn’t start with If. But as you can see, you can put elses and elseifs all on the same line.
One more article and we’ll start getting into the fun stuff; next up is loops and then we’ll be learning how to parse JSON strings!
Disclaimer
**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness, or timeliness.