
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
STRINGS IN VBA
INTRODUCTION
” STRINGS ARE THE ONLY WAY THROUGH WHICH OUR APPLICATIONS COMMUNICATE WITH THE USER”
WHAT IS A STRING IN VBA
EXAMPLE 1-ENTERING THE STRING/TEXT INTO CELL
OBJECTIVE
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
RUNNING THE CODE AND OUTPUT
EXAMPLE 2- JOIN THE TEXT GIVEN IN THE CELLS D3 , D4 AND D5
OBJECTIVE
PLANNING THE SOLUTION
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
RUNNING THE CODE AND OUTPUT
EXAMPLE 3- REMOVING ANY SPECIFIC CHARACTER/S FROM STRING IN VBA
OBJECTIVE
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
RUNNING THE CODE AND OUTPUT
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.