Visual Basic for Application: Library (String)

Working with String:

To check text string existence in string.
- Search text or letter from left to right.
  InStr(1, String, text or character)
- Search text or letter from left to right.
  InStrRev(String, text or character, -1)
Example:
Option Explicit
Sub GetLocationTextInString()
    Dim StrText As String
    Dim iL As Integer, iR As Integer

    StrText = "C:\Users\UserName\Desktop\MyBook.xlsx"

    iL = InStr(1, StrText, "\")
    iR = InStrRev(StrText, "\", -1)

    Debug.Print iL, iR
End Sub
The result:
   iL = 3 (Position 1st "/" Character from left).
   iR = 26 (Position 1st "/" Character from right).
To find text string existence in range.
- Find text string in range by default setting.
  Set StrFind = ActiveSheet.UsedRange.Find("MyText")
- Find text string in range customize.
  Set StrFind = ActiveSheet.UsedRange.Find("MyText", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True)
  Items can be set: Value,Whole,Order,Case sensitive and etc.
  (Microsoft Reference - Range find)
Example:
Option Explicit
Sub FindTextStringInRange()
    Dim RngFindDeft As Range
    Dim RngFindSetg As Range

    Dim StrText As String

    StrText = "myText" 'Changed this string

    Set RngFindDeft = ActiveSheet.UsedRange.Cells.Find(StrText)
    Set RngFindSetg = ActiveSheet.UsedRange.Find(StrText, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True)

    If Not RngFindDeft Is Nothing Then
        Debug.Print RngFindDeft.Row, RngFindDeft.Column
    End If
    If Not RngFindSetg Is Nothing Then
        Debug.Print RngFindSetg.Row, RngFindSetg.Column
    End If
End Sub
To cut text string.
- Get left text from string. (Microsoft Reference - Left function)
  Left(String Text, Number of Characters)
- Get right text from string. (Microsoft Reference - Right function)
  Right(String Text, Number of Characters)
- Get mid text from string. (Microsoft Reference - Mid function)
  Left(String Text, Start Number, Number of Characters)
Example:
Option Explicit
Sub CutTextString()
    Dim StrText As String
    Dim StrLeft As String, StrRight As String, StrMid As String

    StrText = "C:\Users\UserName\Desktop\MyBook.xlsx"

    StrLeft = Left(StrText, 8)
    StrRight = Right(StrText, 11)
    StrMid = Mid(StrText, 10, 8)

    Debug.Print StrLeft, StrRight, StrMid
End Sub
The result:
   StrLeft = C:\Users (8 Characters from left).
   StrRight = MyBook.xlsx (11 Characters from right).
   StrMid = UserName (Start 10 Characters from left, 8 Characters length).
To split text string.
- Split string by any character. (Microsoft Reference - Split function)
  SplitText = Split(String Text, "Any Character")
Example:
Option Explicit
Sub SplitText()
    Dim StrText As String
    Dim SplitTxt As Variant
    Dim i As Integer

    StrText = "C:\Users\UserName\Desktop\MyBook.xlsx"

    SplitTxt = Split(StrText, "\")
    For i = LBound(SplitTxt) To UBound(SplitTxt)
        Debug.Print SplitTxt(i)
    Next i
End Sub
The result:
   SplitTxt(0) = C:
   SplitTxt(1) = Users
   SplitTxt(2) = UserName
   SplitTxt(3) = Desktop
   SplitTxt(4) = MyBook.xlsx
To get text string total length.
- Get total length any string text. (Microsoft Reference - Len function)
  Len(String Text)
To trim text string.
- Remove space before and after string text.(Microsoft Reference - Trim function)
  Trim(String Text)
To replace text string.
- Replace text string partial or replace all. (Microsoft Reference - Replace function)
  Replace(Text String, "What to replace", "Replace with")
Example:
Option ExplicitSub ReplaceTextString()
    Dim StrText As String

    StrText = "C:\Users\UserName\Desktop\MyBook.xlsx"
    Debug.Print Replace(StrText, "UserName", "MyName")
End Sub
The result:
   Before = C:\Users\UserName\Desktop\MyBook.xlsx
   After = C:\Users\MyName\Desktop\MyBook.xlsx
To test character in range.
- To get what character at certain position in range (Result is text).
  Range("A1").Characters(i, 1).Text
- To test font Strike through or not in range (Result True or False).
  Range("A1").Characters(i, 1).Font.Strikethrough
- To get font Color index for the character in range (Result Color Index).
  Range("A1").Characters(i, 1).Font.ColorIndex
Note: Change i with number of character position
To joint or concatenate text string.
(Microsoft Reference - Concatenate function)
- Use symbol "+" to joint.
  "Base Text" + " Joint Text"
- Use symbol "&" to joint.
  "Base Text" & " Joint Text"
The result: Base Text Joint Text
To converts a text string to all uppercase letters.
- Use of Ucase function. (Microsoft Reference - UCase function)
  UCase("text string")
To converts a text string to all lowercase letters.
- Use of LCase function. (Microsoft Reference - LCase function)
  LCase("TEXT STRING")
To converts a text string to all proper case, 1st letter in each word uppercase.
- Use StrConv. (Microsoft Reference - StrConv function)
  StrConv("text string", vbProperCase)

Library (String)

Working with String:

No comments:

Post a Comment