Remove Characters From Left or Right of a String

String manipulation is very important in any programing language. It is same for VBA as well. In this post let’s learn an another very important string manipulation technique. So today’s lesson is about how to remove characters from left or right of a string.

Let’s assume we have a string type variable call “MyString”.

Dim MyString As String MyString = "abcde123"

This string has 8 characters. So if we want to remove 3 characters from right side how do we do that? We can simply use left function to do the job. Here is how.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Left(MyString, 5) Debug.Print NewString

MyString has 8 characters. If we want to remove 3 from right, that means we need first 5 characters from left. So if you run above code, you will see the value of NewString in the immediate window as follows.

Similarly if we want to remove characters from left then we can use right function for that. Following code shows how you can remove first 2 characters from left.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Right(MyString, 6) Debug.Print NewString

If you run above code you will see this result in immediate window.

So what if, if you want to remove characters from variable length string. Then we can’t use constant value with left or right functions. But we can solve this with “Len” function. We can calculate the length of the string using “Len” function. Then we can subtract amount of characters we want to remove. Following code will remove first 2 characters from right side of a string.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Left(MyString, Len(MyString) - 2) Debug.Print NewString

This is the result you will get.

And following example shows how to remove first 3 characters from left of a variable length string.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Right(MyString, Len(MyString) - 3) Debug.Print NewString

And you will see this result in immediate window.

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