Module: Strings in Category General VBA/VB6 : String Handling from Total Visual SourceBook

Manipulate, parse, clean, capitalize and count strings using VBA and VB6.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modStrings module.
ArrayToString Procedure Convert the passed array to a delimited string.
CaseConvert Procedure Convert a string to upper case, lower case, or proper case. The built-in StrConv() function accomplishes the same thing as CaseConvert. Use this function if you want to change or enhance the functionality in StrConv().
SmartCapitalize Procedure Capitalize words but not small words that are not the first word of the phrase (e.g. the, by, of, etc.). Also, handles capitalization of letters after Mc and O' (e.g. McDonald or O'Donnell).
CleanAmpersand Procedure Clean strings with ampersands by doubling the characters. This is useful when placing text with an ampersand character in the caption property of a control. To avoid interpreting the & character as a hotkey indicator, this function adds an ampersand character for each ampersand.
CleanQuotes Procedure Covert double-quote (") characters in a string to apostrophes ('). This is useful when using strings as SQL strings.
ContainsAlpha Procedure Determine if there are alpha characters other than "-" in the supplied string.
CountDelimitedWords Procedure Get the number of words in a delimited string.
CountOccurrences Procedure Get the number of times a phrase exists in a string.
CountWords Procedure Get the number of words separated by a variety of separators defined in WordsToArray.
CountWordsSimple Procedure Get the number of words separated by spaces.
EncloseString Procedure Wrap the specified string in selected characters. Useful for adding HTML tags to text.
FillString Procedure Generates a string of repeated characters or strings. Unlike the built-in String function, this function allows you to generate strings of repeated strings, instead of just strings of repeated characters.
GenRandomPassword Procedure Generate a random string of letters for use as a password. Optionally formulates the string so that every other character is a vowel, which makes the word easier to remember and pronounce. To avoid using easily guessed passwords, or ones which are difficult to crack with a dictionary-based password cracker program, your users should use a meaningless password.
CreatePassword Procedure Create a random string of characters from a string of allowable characters with numbers and special characters.
GetDelimitedWord Procedure Get word intIndex in delimited string strText.
GetFirstWord Procedure Get the first word in delimited string strText and puts the rest in strRest.
GetLastWord Procedure Get the last word in delimited string strText, puts the rest in strRest.
InsertString Procedure Insert one string into another at the specified location.
IsCharLetter Procedure Determine if the passed character is a letter (a-z, A-Z, à-ÿ, À-Þ).
IsCharNumeric Procedure Determine if the passed character is numeric.
NumberSuffix Procedure Convert an integer into a string with a trailing suffix.
NumberSuffixOnly Procedure Get the suffix for a number.
PadLeftString Procedure Left pad a string to intStrLength characters for right justification.
PadRightString Procedure Right pad a string for left justification.
StringToArray Procedure Use the Split function to create a one-dimensional array from a string.
StringToCollection Procedure Split a string into a collection of items using the specified delimiter.
StripChars Procedure Remove the specified character from a string.
TrimNull Procedure Get the passed string terminated at the first null character. If there isn't a null, the entire string is returned.
ValidateZipCode Procedure Validate and clean the supplied zip code. Note that this is a format check only. It doesn't check to see if the actual zip code exists.
WordsToArray Procedure Get the number of words separated by a variety of separators. Expects each word to be separated by one space. Multiple spaces between words cannot be parsed by this routine.
WordsToArraySimple Procedure Convert the passed string to an array of words, delimited by the specified character.
WrapTextToArray Procedure Wrap the input string into an array.
CleanNonAlphaNumeric Procedure Strip non-alphanumeric characters from the specified string.
TrimSpacesTabsCrLf Procedure Trim leading/trailing spaces, carriage returns and line feeds.
ReplaceLineBreaks Procedure In HTML files, text can be spread across multiple lines with indentations and line breaks. Eliminate breaks and combines the lines into one line with just one space where the previous line break existed.
ExtractTextSection Procedure Extract a portion of the text based on beginning and end tags. This is useful for parsing sections out of an HTML file.
ReadFile Procedure Read the contents of a text file.
' Example of modStrings
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modStrings()
  ' Comments: Examples using the modStrings module to perform string manipulation in VBA and VB6.
  '           See the results in the Immediate Window.

  Dim intCounter As Integer
  Dim intCount As Integer
  Dim strValue As String
  Dim strRemainder As String
  Dim strWithNullTerminator As String
  Dim astrValues() As String
  Dim varArray As Variant
  Dim col As New Collection

  ' Example of ArrayToString()
  ' Loads an array with the names of the tables in the current database and converts the array to a delimited string.
  ' It then displays the delimited string in the immeidate window.
  ReDim astrValues(0 To 9)
  astrValues(0) = "Zero"
  astrValues(1) = "One"
  astrValues(2) = "Two"
  astrValues(3) = "Three"
  astrValues(4) = "Four"
  astrValues(5) = "Five"
  astrValues(6) = "Six"
  astrValues(7) = "Seven"
  astrValues(8) = "Eight"
  astrValues(9) = "Nine"

  ' Convert the array to a delimited string
  strValue = ArrayToString(astrValues(), ",")

  ' Display the delimited string
  Debug.Print "ArrayToString(): returned " & strValue

  ' Display the delimited string
  Debug.Print "ArrayToString_JoinFunction(): returned " & strValue

  ' Example of CaseConvert()
  strValue = "NOW IS THE TIME"
  Debug.Print "CaseConvert(): input value of '" & strValue & "' with mode of 1 returned " & CaseConvert(strValue, 1)

  strValue = "Now Is the Time"
  Debug.Print "CaseConvert(): input value of '" & strValue & "' with mode of 2 returned " & CaseConvert(strValue, 2)

  strValue = " now is the time"
  Debug.Print "CaseConvert(): input value of '" & strValue & "' with mode of 3 returned " & CaseConvert(strValue, 3)

  ' Example of SmartCapitalize
  strValue = "let's invite the o'donnell and mccarthy families to dinner."
  Debug.Print "SmartCapitalize(): input value of '" & strValue & "'" & vbCrLf & "         Returned: '" & SmartCapitalize(strValue) & "'"

  ' Example of CleanAmpersand(): Cleans the ampersands from the string.
  strValue = "R.Crumb&Associates"
  Debug.Print "CleanAmpersand(): input value of '" & strValue & "' returned " & CleanAmpersand(strValue)

  ' Example of CleanNonAlphaNumeric():
  strValue = "Here's my #1 Test!"
  Debug.Print "CleanNonAlphaNumeric(): input value of '" & strValue & "' returned " & CleanNonAlphaNumeric(strValue, "_")

  ' Example of CleanQuotes(): Cleans the quotes in a string.
  ' This string is set to Now Is The "Time"
  strValue = "Now is the " & Chr$(34) & "Time" & Chr$(34)
  ' The results are Now Is The 'Time'
  Debug.Print "CleanQuotes(): input value of '" & strValue & "' returned " & CleanQuotes(strValue)

  ' Example of ContainsAlpha(): Ensures a string contains no letters
  Debug.Print "ContainsAlpha() '12s34' does " & IIf(ContainsAlpha("12s34"), " ", "not ") & "contain alpha characters."

  Debug.Print "ContainsAlpha() '1234' does " & IIf(ContainsAlpha("1234"), " ", "not ") & "contain alpha characters."

  ' Example of CountDelimitedWords(): Counts the words in the string.
  strValue = "now,is,the,time,for,all,good,men"
  ' Returns a value of 8
  Debug.Print "CountDelimitedWords(): there are " & CountDelimitedWords(strValue, ",") & " words in the string '" & strValue & "'"

  ' Example of CountOccurrences(): Counts the occurrences of "ow" in the supplied string.
  strValue = "How now, brown cow"
  'Returns a value of 4
  Debug.Print "CountOccurrences(): there are " & CountOccurrences(strValue, "ow") & " occurrences of 'ow' in the string '" & strValue & "'"

  ' Example of CountWords(): Counts the number of words in a sentence
  strValue = "How now brown cow."
  Debug.Print "CountWords(): there are " & CountWords(strValue) & " words in the string '" & strValue & "'"

  ' Example of CountWordsSimple(): Counts the number of words in a sentence
  strValue = "How_now_brown_cow."
  Debug.Print "CountWordsSimple(): there are " & CountWordsSimple(strValue, "_") & " words in the string '" & strValue & "'"

  ' Example of FillString(): Creates a string of repeated characters
  strValue = "-"
  Debug.Print "FillString(): returned " & FillString(strValue, 10)

  ' Example of GetDelimitedWord(): Test retrieval of words from a delimited string.
  strValue = "Table,Query,Form,Report,Macro,Module"
  Debug.Print "GetDelimitedWord(): word 3 of '" & strValue & "' is: " & GetDelimitedWord(strValue, 3, ",")

  ' Example of GetFirstWord(): Test retrieval of the first word from a delimited  string.
  strValue = "Table,Query,Form,Report,Macro,Module"
  Debug.Print "GetFirstWord(): the first word of '" & strValue & "' is " & _
    GetFirstWord(strValue, strRemainder, ",") & ". The remainder of the string is " & strRemainder

  ' Example of GetLastWord(): Test retrieval of the last word from a delimited  string.
  strValue = "Table,Query,Form,Report,Macro,Module"
  Debug.Print "GetLastWord(): the last word of '" & strValue & "' is " & _
    GetLastWord(strValue, strRemainder, ",") & ". The remainder of the string is " & strRemainder

  ' Example of IsCharLetter()
  Debug.Print "IsCharLetter(): 'x' is " & IIf(IsCharLetter("x"), " ", "not ") & "a letter."

  Debug.Print "IsCharLetter(): '1' is " & IIf(IsCharLetter("1"), " ", "not ") & "a letter."

  ' Example of IsCharNumeric()
  Debug.Print "IsCharNumeric(): 'x' is " & IIf(IsCharNumeric("x"), " ", "not ") & "a numeric value."

  Debug.Print "IsCharNumeric(): '2' is " & IIf(IsCharNumeric("2"), " ", "not ") & "a numeric value."

  ' Example of NumberSuffix(): test retrieval of number suffixes
  Debug.Print "NumberSuffix(): value returned for 1 is " & NumberSuffix(1)
  Debug.Print "NumberSuffix(): value returned for 19 is " & NumberSuffix(19)
  Debug.Print "NumberSuffix(): value returned for 1234 is " & NumberSuffix(1234)
  Debug.Print NumberSuffix(1)

  ' Example of NumberSuffixOnly(): test retrieval of partial number suffixes
  Debug.Print "NumberSuffixOnly(): for 9, returned: " & NumberSuffixOnly(9)

  ' Example of PadLeftString(): Test left padding of the input string
  strValue = "now is the time"
  Debug.Print "PadLeftString(): " & PadLeftString(strValue, "X", 20)

  ' Example of PadRightString(): Test right-padding of a string
  strValue = "Now is the time"
  Debug.Print "PadRightString(): " & PadRightString(strValue, "X", 30)

  ' Example of RemoveNulls(): Test removal of null characters from a string.
  strWithNullTerminator = "Now is the time " & Chr$(0)
  Debug.Print "RemoveNulls(): returned " & TrimNull(strWithNullTerminator)

  ' Example of StringToArray(): Creates a delimited string of values, converts it to an array and displays the array contents in the Immediate Window.
  Erase astrValues
  ' Load a string for the StringToArray
  strValue = "apples,oranges,pears,kiwis,grapes,tangerines"

  ' Convert the string to an array using the Split Function
  varArray = StringToArray(strValue, ",", -1, vbTextCompare)

  ' Display the contents of the array
  Debug.Print "StringToArray(): run on '" & strValue & "' " & "returns: "
  For intCounter = 0 To UBound(varArray)
    Debug.Print varArray(intCounter)
  Next intCounter

  ' Convert the string to a collection
  Set col = StringToCollection(strValue, ",")
  ' Display the contents of the collection
  Debug.Print "StringToArray(): run on '" & strValue & "' " & "returns: "
  For intCounter = 1 To col.Count
    Debug.Print col(intCounter)
  Next intCounter

  ' Example of StripChars(): Test stripping of characters from a string.
  strValue = "Microsoft VB6 Project"
  Debug.Print "StripChars(): input of string of '" & strValue & "'" & " stripped of 'o' returns: " & StripChars(strValue, "o")

  ' Example of ValidateZipCode(): Prompts the user for a zip code and checks it.
  strValue = "22182"
  Debug.Print "ValidateZip(): input value of '" & strValue & "' returned " & ValidateZipCode(strValue)

  ' Example of WordsToArraySimple(): Creates a string of words, converts the string
  ' to an array and displays the contents of the array in the immediate window. Pass ' ' (space) as the delimiter.
  Erase astrValues
  ' Load a string.
  strValue = "You run VBA code by calling the Function or Sub procedure that contains the code."
  ' Convert the string to an array. These characters are considered separators: ,.?:;!^&*-+=|\/ and ' ' (space)
  intCount = WordsToArraySimple(strValue, " ", astrValues())
  ' Display the contents of the array
  Debug.Print "WordsToArraySimple(): input string of '" & strValue & "' returns: "
  For intCounter = 0 To intCount - 1
    Debug.Print astrValues(intCounter)
  Next intCounter

  ' Example of WordsToArray(): Creates a string of words, converts the string
  ' to an array and displays the contents of the array in the immediate window.
  ' These characters are considered separators: ,.?:;!^&*-+=|\/ and ' ' (space)
  Erase astrValues
  ' Load a string.
  strValue = "You/run,VBA.code?by:calling;the!Function^or&Sub*procedure-that+contains=the|code."
  ' Convert the string to an array
  intCount = WordsToArray(strValue, astrValues())
  ' Display the contents of the array
  Debug.Print "WordsToArray(): input string of '" & strValue & "' returns: "
  For intCounter = 0 To intCount - 1
    Debug.Print astrValues(intCounter)
  Next intCounter

  ' Example of WrapTextToArray()
  strValue = "This is a test of a long paragraph to demonstrate how the WrapTextToArray() function operations. This function takes a string " & _
           "and word-wraps it to the passed array. You can specify the number of characters per line."
  ' Wrap the text to the array in lines no longer than 40 characters
  intCount = WrapTextToArray(strValue, 40, astrValues())

  Debug.Print "WrapTextToArray(): returned "
  For intCounter = 0 To intCount - 1
    Debug.Print "   " & astrValues(intCounter)
  Next intCounter

  ' Example of InsertString()
  strValue = InsertString("This is a test", "better ", 10)
  Debug.Print "InsertString(): new string is ' " & strValue & "'"

  ' Example of GenRandomPassword()
  Debug.Print "GenRandomPassword(): returns " & GenRandomPassword(20, True)

  ' Example of CreatePassword
  Debug.Print "CreatePassword(): returns " & CreatePassword(20)

  ' Example of EncloseString()
  Debug.Print "EncloseString(): returns " & EncloseString("Customers Table", "[", "]", False); ""
End Sub

Private Sub Example_modStringsReadFile()
  ' Comments: Examples using the modStrings module to read file contents and perform string manipulation in VBA and VB6.
  '           See the results in the Immediate Window.

  Const cstrFile As String = "C:\Total Visual SourceBook 2013\Samples\sample.html"

  Dim strText As String
  Dim strTitle As String
  Dim strHeader As String

  ' Read the contents of the text file without trimming anything
  strText = ReadFile(cstrFile)

  ' Extract sections from the text file
  strTitle = ExtractTextSection(strText, "", "")
  strHeader = ExtractTextSection(strText, "

", "

") Debug.Print "Read contents of " & cstrFile Debug.Print "Title: " & strTitle Debug.Print ' The header is split over several lines so there's a way to clean it to just get the text Debug.Print "Header: " & strHeader Debug.Print Debug.Print "Cleaned: " & ReplaceLineBreaks(strHeader) End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS