If Then Else Statement (Excel VBA)

Today let’s learn how to use If Then Else statement in vba. If Then Else is a very basic control flow statement. It tells the program to evaluate conditions in the order listed and then execute a certain section of the code if a particular condition is true.

So let’s learn how to use If Then Else statement in a vba macro. I will use this simple application to explain it. This is a part of a custom calculator.

So let’s calculate the Mid estimation value for the roof. Here is how we need to calculate the value

If C2 value is 800 or less, then Mid Est is 5000
If C2 value is greater than 800 and less than or equal 1000, then Mid Est is 6000
If C2 value is greater than 1000 and less than or equal 1200, then Mid Est is 7000
If C2 value is greater than 1200 and less than or equal 1400, then Mid Est is 8000
If C2 value is greater than 1400 anand less than or equal d 1600, then Mid Est is 9000
If C2 value is greater than 1600 and less than or equal 1900, then Mid Est is 10000
If C2 above 1900, then MANUAL

We can develop the code in two different methods. Without else clause and with else clause. Below is how we can develop the code without else clause.

Dim Gross_SF As Double Dim MidEst As Variant Gross_SF = Range("C2").Value If Gross_SF <= 800 Then      MidEst = 5000 ElseIf Gross_SF <= 1000 Then      MidEst = 6000 ElseIf Gross_SF <= 1200 Then      MidEst = 7000 ElseIf Gross_SF <= 1400 Then      MidEst = 8000 ElseIf Gross_SF <= 1600 Then      MidEst = 9000 ElseIf Gross_SF <= 1900 Then      MidEst = 10000 ElseIf Gross_SF > 1900 Then      MidEst = "MANUAL" End If Range("D5").Value = MidEst

This is how we can develop the code with else clause.

Dim Gross_SF As Double Dim MidEst As Variant Gross_SF = Range("C2").Value If Gross_SF <= 800 Then      MidEst = 5000 ElseIf Gross_SF <= 1000 Then      MidEst = 6000 ElseIf Gross_SF <= 1200 Then      MidEst = 7000 ElseIf Gross_SF <= 1400 Then      MidEst = 8000 ElseIf Gross_SF <= 1600 Then      MidEst = 9000 ElseIf Gross_SF <= 1900 Then      MidEst = 10000 Else      MidEst = "MANUAL" End If Range("D5").Value = MidEst

So the program first checks whether Gross_SF is less than or equal 800. If it is true then assign value 5000 to MidEst variable and go to the end of the if then else statement. If the first condition is false then program evaluate next condition. So it checks whether Gross_SF less than equal 1000. (This is same as checking C2 value is greater than 800 and less than or equal 1000) Because in first condition we checked whether it is less than or equal 800. Program will executed to second condition only if C2 is greater than 800. If second condition is true then MidEst will become 6000. Otherwise program will evaluate next condition and so and so. If no condition is true then program will execute the section in the else part. No section will be executed if “Else” clause is not available.

According to the above requirements we need to output either a value or a string depending on the user input. Due to that reason we need to define MidEst variable as variant. If we define it as type double then program will gives an type mismatch error when the C2 value is greater than 1900.

Below are few sample outputs for different C2 values.

When C2 is 650

When C2 is 1700

When C2 is 2200

If Then Statement in VBA

In this post I will show you how to use If then statement in VBA. So let’s develop simple If then statement for sample data below.

These are scores of few students. Assume we need to color the scores which are less than 40. So let’s learn how to do that step by step.

It is a good practice to define the variables.

Dim WS As Worksheet Dim i As Integer

Next let’s assign activesheet to WS. Here we assume that user will be in the sheet where table is in when he run the macro.

Set WS = ActiveSheet

Next we need to use For loop as we have several rows. Actually this kind of programs are more Effective when we have thousands of rows.

For i = 3 To 9 Next i

Inside the for loop, we need to check whether each value is less than 40 or not. This is the point where we need help of a If then statement. We can use a if then statement like below.

For i = 3 To 9      If WS.Range("C" & i).Value < 40 Then          WS.Range("C" & i).Interior.Color = RGB(255, 0, 0)      End If Next i

Here we used RGB color system to color the cells. So once you run the program for above sample data you will get this result.

And if you want to color the cells in more lighter red color then you can use RGB values like RGB(255, 150, 150) , RGB(255, 200, 200) etc. Following is the result I got for RGB(255, 200, 200)

And here below is the complete subroutine.

Sub HighlightLowScores() Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 3 To 9      If WS.Range("C" & i).Value < 40 Then          WS.Range("C" & i).Interior.Color = RGB(255, 0, 0)      End If Next i End Sub

How to Find RGB Value of Fill Color of Any Cell

If you get a excel sheet like this, do you know how to find the RGB value of fill colour of these cells?

This excel sheet has cells with 3 different fill colors. In this post I will explain how to find RGB value of fill color of any cell you want. Let’s try to find the fill color of cells in first row(Light blue color cells). First, select one of the light blue cells. I selected the B2 cell.

Then go to the “HOME” tab and click arrow head next to “Fill Color” icon.

Then click on “More Colors...”

“Colors” window will open. You can find the RGB value under the “Custom” tab.

So the RGB value of fill color of B1 cell is RGB(173, 201, 220). You can use this method to find RGB value of fill colors manually.

Calculate absolute value in VBA (Abs function)

In our last post we learned how to calculate absolute value using Excel worksheet function. Today let’s learn how to calculate absolute value in VBA.

Here is our sample data set. As you can see we have few companies and change of their stock values.

Positive values are in green. Negative values are shown in red. If there is no change then values are in black. Assume we need to calculate the absolute value of change of the each company. We can use following vba function for that.

Abs (Number)

Number should be any numeric expression. So if we want to find the absolute value of change of “General Electric Company” then we can find it by

Abs(Range("B2").Value)

As we need to find absolute value of several cell values we can use For loop as follows.

Sub CalculateAbsValue() Dim i As Integer For i = 2 To 8      Range("C" & i).Value = Abs(Range("B" & i).Value) Next i End Sub

After you run above macro you will get following result.

And this post explains how to use worksheet ABS function.
How to Use Excel ABS Function

How to Use Excel ABS Function

We often need to find absolute value of a number or absolute difference of two values. Fortunately we have inbuilt Excel function to calculate that. It is call “ABS” function. So in this post I will teach you how to use Excel ABS function.

Here is our sample data.

Now let’s find the the absolute value of value in B4 cell. You can find it using this formula.
=ABS(B4)

When you click enter, result will be shown like this.

Now take your cursor to bottom right corner of the C4 cell. Then cursor will changed to + mark. Click the left mouse button and drag until C8 cell. Function will added to all the relevant cells.

Now let’s learn how to find absolute difference of two values. Let’s consider following example.

First let’s find difference of C4 value and D4 value. We can use following formula for that.
=D4-C4

When you click enter, value will calculated like this.

Next fill all the cells in column E with formula like we did earlier.

As you can see, we have both positive and negative values. Assume we need to find absolute weight difference for two months. We can use ABS function for that.
=ABS(D4-C4)

When you click enter value will be calculated like this.

Next fill rest of the cells in the column F with formula.

Now we have absolute difference of weights of two months for each person in column F.

If you want to learn how to find absolute value in vba then read this post.
Calculate absolute value in VBA (Abs function)

Hide And Unhide Worksheets Using VBA

Sometimes we need to hide or unhide sheets in Excel. If you develop an advance application with lots of excel databases and VBA forms, then you may need to hide the database from the users. So then they can only alter data with userforms. In this post first I will explain you how to hide or unhide worksheets manually. Then I will teach you how to do it using VBA.

Assume we have an Excel workbook like this. So we have 3 sheets in our file.

If we want to hide one of the sheets manually we can do it as follows. First right click on the worksheet name you want to hide. Then click on “Hide”.

As I click on the “Sheet 1”, it will hide the “Sheet 1”

And if you need, you can hide several sheets at once. What you need to do is select all the sheet names you want to hide while holding down Ctrl key in your keyboard. (However you can’t select all the worksheets in the workbook to hide. Because there should be atleast one visible sheet in the workbook.) Then right click on one of the worksheet name you want hide and click on “Hide”.

This will hide all selected sheets.

Next let’s learn how to Unhide sheets manually. You can do it as follows. First right click on one of visible worksheets. Then click on Unhide.

Then it will show list of hidden sheets like this.

Select the worksheet you want to unhide and then click OK. Selected worksheet will become visible.

Now let's look at how to hide a worksheet using VBA. Assume you have worksheet call Sheet1. And you should have at least one more sheet in the workbook in addition to Sheet1. You can hide Sheet1 like this.

Sub HideSheet() Worksheets("Sheet1").Visible = False End Sub

And if you want to unhide that sheet using VBA then you can do it as follows.

Sub UnhideSheet() Worksheets("Sheet1").Visible = True End Sub

However if you use above method to hide sheets, then users have ability to unhide them manually if they want. But there is another method you can hide sheets which doesn't allow manual unhide. If you use that method then worksheets can be unhidden only using a above unhide code. Here is the code.

Sub VeryHidden() Worksheets("Sheet1").Visible = xlVeryHidden End Sub

So assume we have 3 sheets like this.

And we run above code.

As you can see the Sheet1 is now in hidden state. But you can’t unhide it manually as it doesn’t show hidden sheet.

So you need to use above Unhide code to make it visible.

How to Read, Write, Delete and Move excel comments

In this post let’s look at how to automate Excel cell comments using VBA. If you want to manually enter a cell comment, then you can do it as follows. First right-click inside a cell. Then click on “Insert Comment”.

Then Excel will create a box like this.

Now you can enter your comment.

So if you have a spreadsheet with lots of data, how do you identify the cells which have comments? It is easy. Cells with comments will have red color triangle shape in top right hand corner of the cell. In this example there is a comment in D3 cell.

If you want to read the comment you can take your cursor on top of that cell. Then it will show the comment.

Now let’s look at how to read this comment using VBA. If the comment is in cell D3 then you can read it as follows.

Sub ReadComment() Dim StrComment As String StrComment = Range("D3").Comment.Text Debug.Print StrComment End Sub

If you run above macro, at the beginning comment will be assigned to string variable call “StrComment”. Then it will be printed in immediate window.

Next let’s learn how to write a comment to a specific cell. Below is the code to write a comment to a cell. This will add comment “This is sample comment” to cell G5.

Sub WriteComment() Range("G5").AddComment "This is sample comment" End Sub

Also note that you can replace “Range” keyword with “Cells”. So instead of Range(“D3”) you can write Cells(3,4) and instead of Range(“G5”) you can write Cells(5,7)

So if you want to move comment from B2 cell to C2 cell then you can do it as follows.

Sub MoveComment() Dim StrComment As String StrComment = Cells(2, 2).Comment.Text Cells(2, 3).AddComment StrComment End Sub

Also if you are developing a dynamic application, it is important to check whether there is a comment in the cell before try to read the comment. Because otherwise excel will produce runtime error if program try to read a comment from a cell where there is no comment. You can check whether there is a comment in cell A1 using following code.

Sub CheckForComment() If Cells(1, 1).Comment Is Nothing Then      MsgBox "No comment" Else      MsgBox Cells(1, 1).Comment.Text End If End Sub

If there is no comment it will give you “No comment” message. If there is a comment then macro will show the comment in a message box.