Multiple Apps

Store in an array the number of occurences (and positions of) strings within strings

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

xCav8r

Description:

When given a string to search and a string to search for, this function will return the number of occurences of the string being sought in addition to the starting and ending position of each instance. 

Discussion:

Want to know how many times the letter "a" or the word "Frank" is in a string? Want to know the position of these strings? Then this is the pre-2000 friendly code for you. 

Code:

instructions for use

			

'--------------------------------------------------------------------------- ' NB: I have avoided abbreviating variable and procedure names to make ' things easier to grasp for beginners and non-native English speakers. ' Feel free to use Find and Replace to swap these names with something ' you prefer. ' ' This demonstration module has been commented with the beginner in ' mind. '--------------------------------------------------------------------------- Option Explicit '--------------------------------------------------------------------------- ' Insert the string to search below between the quotation marks Const mcstrSearchThisString As String = "How much do those shoes cost?" ' Insert the string to search *for* below between the quotation marks Const mcstrSearchForThis As String = "os" '--------------------------------------------------------------------------- Private Sub TellMeHowManyTimesAndWhere() '--------------------------------------------------------------------------- ' Desc : This sub procedure calls the PositionsInString function using ' the module level constants defined above as the arguments, ' printing the results to the immediate window. You do not need ' to adjust the values of these strings to make it work. ' ' When it gets the results of the function, it stores them ' in a temporary array to count the total number of instances ' and print the starting and ending positions of each instance ' to the immediate window. ' ' Remarks : The purpose of this procedure is to demonstrate how the ' PositionsInString function *might* be used. It is included ' here soley for that reason, and it is not required for the ' function to work. '--------------------------------------------------------------------------- ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' an array in which to store the results of the PositionsInString function Dim avarStringPositions As Variant ' a counter that will be used to step through the values in the array Dim intCounter As Integer ' a string to store a comma separated list of the locations Dim strStringPositions As String ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Load the array by feeding the values defined in the constants above ' to the function called PositionsInString. ' ' NB: These constants could be replaced by any string enclosed by quotation ' marks. avarStringPositions = PositionsInString(mcstrSearchThisString, _ mcstrSearchForThis) ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Loop through the array and extract each value to a comma separated list For intCounter = LBound(avarStringPositions, 2) To _ UBound(avarStringPositions, 2) If intCounter = 0 Then strStringPositions = avarStringPositions(0, intCounter) _ & "-" & avarStringPositions(1, intCounter) Else strStringPositions = strStringPositions & ", " _ & avarStringPositions(0, intCounter) & "-" _ & avarStringPositions(1, intCounter) End If Next intCounter ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Add a period to the end of the string (end of sentence) strStringPositions = strStringPositions & "." ' output to immediate window for example Debug.Print "There are a total of " & intCounter _ & " occurrence(s) of " & Chr(34) & mcstrSearchForThis & Chr(34) _ & ": " & strStringPositions End Sub Public Function PositionsInString(SearchThisString As String, _ SearchForThis As String) As Variant '--------------------------------------------------------------------------- ' Desc : When given a string to search and a string to search for, ' this function returns the starting and ending positions ' of each instance of the sought string within the searched ' string. It returns these values in a two dimensional array ' where the first argument contains the number of occurences ' and the second argument contains the starting and ending ' positions, respectively. ' ' Arguments : SearchThisString : The string which will be searched ' SearchForThis : The string that will be sought ' ' Remarks : Since this functions doesn't use Split or Join, it will work ' work Office 97. '-------------------------------------------------------------------------- ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Integer used to determine if string was found and where Dim intPositionOfSoughtString As Integer ' Integer used to identify the starting point of each search Dim intStartPosition As Integer ' Variant array to house the starting and ending positions of sought string Dim avarStringPositions As Variant ' Integer used to redimension array Dim intCounter As Integer ' Integer used to house the length of the sought string to calculate end Dim intStringLength As Integer ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - intStringLength = Len(SearchForThis) ReDim avarStringPositions(0 To 1, 0 To 0) ' The first search will start from this position intStartPosition = 1 ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Loop until all occurences have been located Do ' Record the location of the sought string, if 0 then not found intPositionOfSoughtString = InStr(intStartPosition, _ SearchThisString, SearchForThis, vbTextCompare) ' If found, store the initial and final position of the sought string If intPositionOfSoughtString <> 0 Then ' Redimension the array as necessary ReDim Preserve avarStringPositions(0 To 1, 0 To intCounter) ' Store the initial position of the sought string avarStringPositions(0, intCounter) = intPositionOfSoughtString ' Store the final position of the sought string avarStringPositions(1, intCounter) = intPositionOfSoughtString _ + intStringLength - 1 ' Change the starting position for the next search intStartPosition = intPositionOfSoughtString + 1 ' Augment the counter so the array is properly redimensioned intCounter = intCounter + 1 End If Loop Until intPositionOfSoughtString = 0 ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Pass the values from the array to the funciton PositionsInString = avarStringPositions End Function

How to use:

  1. Open your document.
  2. Press Alt + F11 to open the Visual Basic Editor (VBE).
  3. Insert a standard module. (Insert -> module)
  4. Paste the code in the window to the right. (F7 to view; CTRL + V to paste)
  5. Save the file.
 

Test the code:

  1. From the VBE, make sure the Immediate Window is visible (CTRL + G)
  2. With your cursor anywhere in the module, press F5. (If your cursor is in the TellMeHowManyTimesAndWheresub procedure, then you won't have to do the next step.)
  3. Select TellMeHowManyTimesAndWherefrom the dialog box that appears, and then click Run.
 

Sample File:

SearchStringInString.zip 11.8KB 

Approved by mdmackillop


This entry has been viewed 93 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express