If Then Statement in Excel Vba
The If statement in VBA (Visual Basic for Applications) is essential for executing actions based on specific conditions defined by the user. This guide will walk you through the basic syntax and provide an example program to determine if a number entered by the user is positive or negative.
Syntax of the If Statement
This is the syntax of the If statement:
IF condition1 THEN
'What will the happen if the condition is met
ELSE
'What will the happen if the condition is not met
END IF
Explanation: Explanation: The If statement evaluates a specified condition. If the condition is true, the code within the If block is executed. If the condition is false, the code within the Else block 1 (if present) is executed.
Our program will take an input from the user and tell whether it’s a positive number or a negative number.
Example Program: Checking Positive or Negative Number
Click on Developer tab and select View Code.
A new window (Visual Basic Editor) will open which would have a dialog box in the center.
Write the following line of code in the dialog box.
Sub Find_Negative()
On Error GoTo catch_error
Dim number As Integer
number = InputBox("Enter the number: ")
If number < 0 Then
MsgBox "Entered number is negative!"
Else
MsgBox "Entered number is positive!"
End If
Exit Sub
catch_error:
MsgBox "Oops, Some Error Occurred"
End Sub
The On Error GoTo catch_error statement is an error handler. If an error occurs during execution (e.g., the user enters non-numerical input), the code jumps to the catch_error label.
After writing the code close the window by clicking on the cross(x) icon on the upper right side of the screen.
Explanation of the Code
This code prompts the user for numerical input. It then checks if the entered number is less than zero. If the number is less than zero, a message box displays indicating that the number is negative. Otherwise (if the number is zero or greater), a message box displays indicating that the number is positive.
Hence we display a message box saying that the number is negative. However if the user instead of entering a number enters a letter or a special character (like @) than we display a message box saying that an error has occurred.
This is the result if the user has entered a positive number.
This is the result if the user has entered a negative number.
This is the result if the user has entered a special character or a letter.
That’s it; you have now successfully used the If statement.
This basic example demonstrates how to check if a number is positive or negative, handle user input, and manage errors effectively.
Stevie
OMG, just stumbled upon this article about the If statement in Excel VBA and it’s like a light bulb went off in my head! 🤯💡 I mean, I always knew conditions were a thing in programming, but seeing it laid out like this makes so much sense. It’s like telling a story: “If this happens, do this; otherwise, do that.” Simple, yet so powerful! 🚀
Quick question though: in the code you’ve given, it’s checking if the number is negative or positive, but then you mentioned something about checking if the number is divisible by 2? 🤔 I’m a bit confused here. Is it about positivity/negativity or divisibility by 2? Or am I missing something?
Also, I could really use some help here. I’m trying to modify this to check if a number is even or odd instead. How would I tweak the code for that? I’m kinda new to VBA and trying to learn through practice. Any help would be massively appreciated! Thanks in advance! 🙏👩💻 #LearningVBA #CodingNewbie #HelpMePlease