fanjy,
Yes, that is one way. Here is another:
Sub TestFunction()
Dim strSample As String
Dim lngIndex As Long
Dim varRtn
strSample = "abcabcabcabc"
varRtn = fcnGetSubStringPosits(strSample, "b")
If Not IsArrayEmpty(varRtn) Then
For lngIndex = LBound(varRtn) To UBound(varRtn)
Debug.Print varRtn(lngIndex)
Next lngIndex
End If
lbl_Exit:
Exit Sub
End Sub
Function fcnGetSubStringPosits(strMain As String, strSub As String) As Long()
Dim arrParts() As String
Dim arrPosits() As Long
Dim lngIndex As Long, lngCalc As Long
arrParts = Split(strMain, strSub)
For lngIndex = 0 To UBound(arrParts) - 1
ReDim Preserve arrPosits(lngIndex)
arrPosits(lngIndex) = Len(arrParts(lngIndex)) + 1 + lngCalc
lngCalc = lngCalc + Len(arrParts(lngIndex)) + Len(strSub)
Next lngIndex
fcnGetSubStringPosits = arrPosits
lbl_Exit:
Exit Function
End Function
Public Function IsArrayEmpty(varArray As Variant) As Boolean
Dim lngLB As Long, lngUB As Long
If Not IsArray(varArray) Then
IsArrayEmpty = True
Exit Function
End If
On Error Resume Next
lngUB = UBound(varArray, 1)
If (Err.Number <> 0) Then
IsArrayEmpty = True
Else
Err.Clear
IsArrayEmpty = False
lngLB = LBound(varArray)
If lngLB > lngUB Then IsArrayEmpty = True
End If
lngLBl_Exit:
Exit Function
End Function