How to use If Else and Case functions in excel VBA ?

To understand how to use If else and case statements, i have create a small program code, which checks your personality based on your birth date numbers. So have fun while learning :-).


Sub checkpersonality()

‘Define all variables we are going to use in below code
Dim bdate As Integer, bmonth As Integer, byear As Integer, total As Integer
Dim calca As Integer, calcb As Integer, calcc As Integer, calcd As Integer, calctotal As Integer
Dim calce As Integer, calcf As Integer, yourbnum As Integer

‘On error, e.g. if incorrect dates are entered the below code will take you to the error message: in the code and exit the execution
On Error GoTo

‘Use InputBox function to collect the day, month and year of the user
bdate = InputBox(“Enter your Day of birth (DD):”)
bmonth = InputBox(“Enter your Month of birth (MM):”)
byear = InputBox(“Enter your Year of birth (YYYY):”)

‘Calculate total from your birth date, learnt this from astrology website 🙂
total = bdate + bmonth + byear

‘below are few more calculations on the number from above total
calca = Left(total, 1)
calcb = Right(Left(total, 2), 1)
calcc = Right(Left(total, 3), 1)
calcd = Right(total, 1)
calctotal = calca + calcb + calcc + calcd

‘below is use of If Else statement to get a final number from all calculations
calctotal >= 10 Then
calce = Left(calctotal, 1)
calcf = Right(calctotal, 1)
yourbnum = calce + calcf
Else: yourbnum = calctotal
End If

‘After all the calculations above, we will get a final number as output. This number will decide what is your personality trait and based on that a message box will pop-up from below 1 to 9 cases.
Select Case yourbnum

Case 1
MsgBox “Your Birth number is ” & yourbnum & ” and You are The originator” & vbCrLf & vbCrLf & “1s are originals. Coming up with new ideas and executing them is natural. Having things their own way is another trait that gets them as being stubborn and arrogant. 1s are extremely honest and do well to learn some diplomacy skills. They like to take the initiative and are often leaders or bosses, as they like to be the best. Being self-employed is definitely helpful for them.” & vbCrLf & vbCrLf & “Lessons to learn: Others ideas might be just as good or better and to stay open minded.” & vbCrLf & vbCrLf & _
“Famous 1s: Tom Hanks, Robert Redford, Hulk Hogan, Carol Burnett, Wynona Judd, Nancy Reagan, Raquel Welch, Prassann Daphal”, vbInformation

Case 2
MsgBox “Your Birth number is ” & yourbnum & ” and You are The peacemaker” & vbCrLf & vbCrLf & “2s are the born diplomats. They are aware of others needs and moods and often think of others before themselves. Naturally analytical and very intuitive they don’t like to be alone. Friendship and companionship is very important and can lead them to be successful in life, but on the other hand they’d rather be alone than be in an uncomfortable relationship. Being naturally shy they should learn to boost their self-esteem and express themselves freely and seize the moment and not put things off.” & vbCrLf & vbCrLf & _
“Famous 2s: President Bill Clinton, Madonna, Whoopi Goldberg, Thomas Edison, Wolfgang Amadeus Mozart”, vbInformation

Case 3
MsgBox “Your Birth number is ” & yourbnum & ” and You are The life of the party” & vbCrLf & vbCrLf & “3s are idealists. They are very creative, social, charming, romantic, and easygoing. They start many things, but don’t always see them through. They like others to be happy and go to great lengths to achieve it. They are very popular and idealistic. They should learn to see the world from a more realistic point of view.” & vbCrLf & vbCrLf & _
“Famous 3s: Alan Alda, Ann Landers, Bill Cosby, Melanie Griffith, Salvador Dali, Jodi Foster”, vbInformation

Case 4
MsgBox “Your Birth number is ” & yourbnum & ” and You are The conservative” & vbCrLf & vbCrLf & “4s are sensible and traditional. They like order and routine. They only act when they fully understand what they are expected to do. They like getting their hands dirty and working hard. They are attracted to the outdoors and feel an affinity with nature. They are prepared to wait and can be stubborn and persistent. They should learn to be more flexible and to be nice to themselves.” & vbCrLf & vbCrLf & _
“Famous 4s: Neil Diamond, Margaret Thatcher, Arnold Schwarzenegger, Tina Turner, Paul Hogan, Oprah Winfrey”, vbInformation

Case 5
MsgBox “Your Birth number is ” & yourbnum & ” and You are The non-conformist” & vbCrLf & vbCrLf & “5s are the explorers. Their natural curiosity, risk taking, and enthusiasm often land them in hot water. They need diversity, and don’t like to be stuck in a rut. The whole world is their school and they see a learning possibility in every situation. The questions never stop. They are well advised to look before they take action and make sure they have all the facts before jumping to conclusions.” & vbCrLf & vbCrLf & _
“Famous 5s: Abraham Lincoln, Charlotte Bronte, Jessica Walter, Vincent Van Gogh, Bette Midler, Helen Keller, Mark Hamil.”, vbInformation

Case 6
MsgBox “Your Birth number is ” & yourbnum & ” and You are The romantic” & vbCrLf & vbCrLf & “6s are idealistic and need to feel useful to be happy. A strong family connection is important to them. Their actions influence their decisions. They have a strong urge to take care of others and to help. They are very loyal and make great teachers. They like art or music. They make loyal friends who take the friendship seriously. 6s should learn to differentiate between what they can change and what they cannot.” & vbCrLf & vbCrLf & _
“Famous 6s: Albert Einstein, Jane Seymour, John Denver, Meryl Streep, Christopher Columbus, Goldie Hawn”, vbInformation

Case 7
MsgBox “Your Birth number is ” & yourbnum & ” and You are The intellectual” & vbCrLf & vbCrLf & “7s are the searchers. Always probing for hidden information they find it difficult to accept things at face value. Emotions don’t sway their decisions. Questioning everything in life, they don’t like to be questioned themselves. They’re never off to a fast start, and their motto is slow and steady wins the race. They come across as philosophers and being very knowledgeable, and sometimes as loners. They are technically inclined and make great researchers uncovering information. They like secrets. They live in their own world and should learn what is acceptable and what not in the world at large.” & vbCrLf & vbCrLf & _
“Famous 7s: William Shakespeare, Lucille Ball, Michael Jackson, Joan Baez, Princess Diana”, vbInformation

Case 8
MsgBox “Your Birth number is ” & yourbnum & ” and You are The big shot” & vbCrLf & vbCrLf & “8s are the problem solvers. They are professional, blunt and to the point, have good judgment and are decisive. They have grand plans and like to live the good life. They take charge of people. They view people objectively. They let you know in no uncertain terms that they are the boss. They should learn to exude their decisions on their own needs rather than on what others want.” & vbCrLf & vbCrLf & _
“Famous 8s: Edgar Cayce, Barbra Streisand, George Harrison, Jane Fonda, Pablo Picasso, Aretha Franklin”, vbInformation

Case 9
MsgBox “Your Birth number is ” & yourbnum & ” and You are The performer” & vbCrLf & vbCrLf & “9s are natural entertainers. They are very caring and generous, giving away their last dollar to help. With their charm, they have no problem making friends and nobody is a stranger to them. They have so many different personalities that people around them have a hard time understanding them. They are like chameleons, ever changing and blending in. They have tremendous luck, but also can suffer from extremes in fortune and mood. To be successful, they need to build a loving foundation.” & vbCrLf & vbCrLf & _
“Famous 9s: Albert Schweitzer, Shirley McLaine, Harrison Ford, Jimmy Carter, Elvis Presley”, vbInformation

Case Else
MsgBox “I didn’t knew this, You are not HUMAN !”, vbCritical
End Select
Exit Sub

‘ you have an option to change the below message, anything that you want to display on error
MsgBox “You entered something wrong !” & vbCrLf & vbCrLf & “You need to enter your Birth Date ?”, vbCritical

End Sub

In above code, you have seen vbCrLf used between text messages. Here Cr means carriage return, while Lf means line feed, usign vbCrLf between text, it will create a new line for next text.

You can also use vbNewLine instead of vbCrLf, more easy to remember :-).

Below are the screen shots of the above program in action.

  1. Open new excel file
  2. Copy paste above code from sub to End sub in new VBA module
  3. Save the file as Personality Test
  4. Press Alt + F8 to run the code
  5. Select checkpersonality macro, if you have multiple codes in same sheet
  6. Run
  7. we will take example of my birth date. 23-01-1984. First input your date of birth in first input box in DD format. In my case it will be 23.
  8. In second input box, enter your birth month in MM format. In my case it will be 01.
  9. In third input box, enter your birth year in YYYY format. In my case it will be 1984.
  10. Based on your birth date, out program will calculate the personality and show the below output.
  11. If you don’t enter anything in the input box or click cancel on input box, then it will show the error message.


Thank you for reading. Happy Learning !

Please leave a reply if you like the article or if you need any help on excel

%d bloggers like this: