PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

STRINGS IN VBA

INTRODUCTION

STRINGS is the specific term given to the normal Text in the programming languages.

Strings are very important component in any program as this is the only part of the language through which the program communicate with the user.

We ask the user for the input through a STRING.

We process the user input.

We return the output to the user through a STRING.

So we can understand the importance of Strings in VBA.

Many times we need to handle strings very carefully for which we need to know several types of manipulations so that we can make our application can communicate effectively.

” STRINGS ARE THE ONLY WAY THROUGH WHICH OUR APPLICATIONS COMMUNICATE WITH THE USER”

So, we’ll learn many types of STRING MANIPULATIONS in this article.

WHAT IS A STRING IN VBA

Simple text is a String.

String is a collection of characters. As we know programming languages considers the characters as the basic unit. So when characters combine STRINGS are formed.

e.g.

“WELCOME TO GYANKOSH.NET” is a String.

IF THEN ELSE construct

starts with the IF instruction followed by the CONDITION which we want to decide.

After condition comes the keyword THEN after which comes the instructions which should be executed

if the condition is true.

There are few optional keywords also.

IF WE WANT TO CHECK MORE THAN ONE CONDITION MAKE USE OF ELSEIF.

For the NON APPLICABILITY of the given condition make use of ELSE which will have the instructions to be executed if the condition is not true.

If is ended by END IF.

*If it doesn’t have many instructions there is not need of END IF.

EXAMPLE 1-ENTERING THE STRING/TEXT INTO CELL

OBJECTIVE

ENTER THE TEXT “WELCOME TO GYANKOSH.NET” INTO THE CELL D3 USING VBA.

Make use of VBA to enter the given text into cell positioned at location D3.

PLANNING THE SOLUTION

In this case we simply need to send the String to the cell.

Steps:

1. Select the cell.

2. Put the string into the cell.

CODE

‘ gyankosh.net
‘ putting a custom text/string in a specified cell

Sub stringtocell()

Range(“d3”).Value = “WELCOME TO GYANKOSH.NET”

End Sub

EXPLANATION

There is just one statement in the code.

Range(“d3”) refers to CELL D3

.VALUE refers to the value of D3.

So we put the value of D3 as String “WELCOME TO GYANKOSH.NET”

RUNNING THE CODE AND OUTPUT

EXAMPLE 2- JOIN THE TEXT GIVEN IN THE CELLS D3 , D4 AND D5

OBJECTIVE

JOIN THE TEXT GIVEN IN THE CELLS D3,D4 AND D5 USING VBA

PLANNING THE SOLUTION

We’ll get the data from the cells and concatenate it. Then again send it to cell D6.

1.Retrieve text present in D3 D4 AND D5.

2.Concatenate the data.

3. Put the data back in D6.

CODE

‘ gyankosh.net
‘ join the text in three given cells and output in fourth cell

Sub jointext()

Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim temp4 As String

temp1 = ActiveSheet.Cells(3, 4).Value ‘ANOTHER WAY OF REACHING A CELL
temp2 = ActiveSheet.Cells(4, 4).Value
temp3 = ActiveSheet.Cells(5, 4).Value

Range(“d6″).Value = temp1 + ” ” + temp2 + ” ” + temp3 ‘CONCATENATING

End Sub

EXPLANATION

We declare temp1, temp2 , temp3 and temp4 as strings for holding the values temporarily.

We retrieve the value with a new method.

Activesheet.cells(row, column).value

All the values are in the temp variables.

we put the value in d6 and concatenate the temp1, temp2 and temp3.

RUNNING THE CODE AND OUTPUT

EXAMPLE 3- REMOVING ANY SPECIFIC CHARACTER/S FROM STRING IN VBA

OBJECTIVE

REMOVE CHARACTER c FROM THE GIVEN STRING-CAT CAUGHT A MOUSE

PLANNING THE SOLUTION

Whenever we need to inspect any string, we need to go character wise. As we need to eliminate c, we need to check every character sequentially.

Get the input from the cell to a string.
Examine the string one by one.
If the character is c , skip it, if others, leave them as such.
Return back the string.

CODE

‘ gyankosh.net
‘ REMOVING CHARACTER C FROM THE GIVEN STRING

Sub replaceCharacter()

Dim temp1 As String
Dim temp2 As String
temp2 = “”

temp1 = Range(“F3”).Value ‘getting value from cell

Dim I As Integer

For I = 1 To Len(temp1)
If Not UCase(Mid(temp1, I, 1)) = “C” Then ‘scanning character if its equal to c or not
temp2 = temp2 + Mid(temp1, I, 1)

End If
Next I

Range(“H3”).Value = “The given string after removing c is ” + temp2 ‘Sending the text back to cell.

End Sub

EXPLANATION

We declare temp1 and temp2 as string variable and get the input from the cell. temp2 is kept for putting in the characters, which we don’t need to skip.

Start the loop with variable i =1 upto the length of temp1.

Apply if UPPER CASE of MID(TEMP1,I,1) which gives the ith character, is equal to c or not. If yes, skip it, and if not, just attach it with the temporary string temp2.

Output is taken by putting the value back to cell H3.

TUTORIAL HOME

CONTENTS

STRINGS IN VBA

INTRODUCTION

WHAT IS A STRING IN VBA

EXAMPLE 1-ENTERING THE STRING/TEXT INTO CELL

OBJECTIVE

PLANNING THE SOLUTION

CODE

EXPLANATION

RUNNING THE CODE AND OUTPUT

EXAMPLE 2- JOIN THE TEXT GIVEN IN THE CELLS D3 , D4 AND D5

OBJECTIVE

PLANNING THE SOLUTION

CODE

EXPLANATION

RUNNING THE CODE AND OUTPUT

EXAMPLE 3- REMOVING ANY SPECIFIC CHARACTER/S FROM STRING IN VBA

OBJECTIVE

PLANNING THE SOLUTION

CODE

EXPLANATION

RUNNING THE CODE AND OUTPUT

WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL