Microsoft Access, Office/VBA, VB6 Modules royalty-free source code library

Microsoft Access Modules royalty-free source code library

Microsoft Access VBA and VB6 Module Library

Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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 List

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 Code for Using Module: Strings

' 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

Overview of 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!