How to Search String

Functions retrieves all the instances of the searched string and returns the number of instances detected, also a reference array will give you ‘additional information about the locations of the searched string. This is done using Instr and Chr function.

Public Function InStrAll( _
ByVal nStart, ByVal sString1, ByVal sString2, _
ByVal nCompare, ByRef arrRes)
Dim nBookMark, nFound, nCount
Dim i
nCount = 0 : nBookMark = nStart
Do
‘— searching the position found.
nFound = InStr(nBookMark, sString1, sString2, nCompare)

If nFound > 0 Then
nCount = nCount + 1
‘— resizing the array with preserve to store new location
ReDim Preserve arrRes(nCount – 1)
arrRes(nCount – 1) = nFound
‘— setting the next start point to search
nBookMark = nFound + 1
End If

Loop While nFound > 0

InStrAll = nCount
End Function