String Manipulation

[vchapran]

I'm building a string for a message box, combining the return values from many functions, which validate the statuses of controls, allowed values for some variables and so on. Actually, everything was done before me, and the validation is pretty complicated. Sometimes the final string contains duplicates, i.e. the same substring presented in the final string several times. For example "You are required manager approval to send this order. Highlighted Fields require data. Highlighted Fields require data. The Quantity exceedes permitted value. Highlighted Fields require data".
I know how I can avoid inserting an additional substring (it's not acceptable since it requires rewriting many functions), if it exists already in a string, but I'd like to delete duplicates from the final string. It seems to be more safe and simpler. I have an idea to loop through the string with InStr function, changing Start parameter after it finds a substring and then use Replace function if it finds second occurance of the substring. But maybe there is more simple way?
Any idea would be greatly appreciated.
Thank you.
Vlad

[Iouri]

Hi Vlad, may this will help you.

That will count occurances of substring in the string. Then you can delete if occurences > than 1.

Function InstrCount(Source As String, SearchString As String) As Long
If Len(Source) Then
InstrCount = UBound(Split(Source, SearchString))
End If
End Function

'another way
Dim i As Long, count As Long
i = InStr(source, search)
Do While i
count = count + 1
i = InStr(i + 1, source, search)
Loop

'the same with Replace function

The trick is to replace each substring with another string that is one character longer, and then determine
the difference between the obtained string and the original string. This number is equal to the number of
substitutions done, and therefore equal to the number of substrings found. (Note that the code is more
concise but a little slower than the previous approach).

count = Len(Replace(Source, Search, Search & "*")) - Len(Source)


Iouri Boutchkine
[email protected]

[shree]

A slight variant of your idea:


option Explicit
private Sub Command1_Click()
Dim Str1 as string
Dim Str2 as string
Dim ArrStr() as string
Dim i as Integer
Str1 = "You are required manager approval to send this order. Highlighted Fields require data. Highlighted Fields require data. The Quantity exceedes permitted value. Highlighted Fields require data"
Str2 = ""
ArrStr = Split(Str1, ".")
for i = 0 to UBound(ArrStr) - 1
If InStr(1, Str2, ArrStr(i)) = 0 then Str2 = Str2 & ArrStr(i) & "."
next
MsgBox Str2
End Sub

[vchapran]

Thanks. Using Split did not come to my head. It's exactly I was looking for. All other variants I knew, but did not like.
Vlad