Speed

[Bjwbell]

How can i speed up this Function?

Public Function InString(text As Variant, String1 As String) As Integer
Dim y As Integer
Dim x As Integer
Dim i As Integer
If String1 = "" Then
    InString = 0
    Exit Function
End If
For i = 1 To UBound(text)
DoEvents
    x = InStr(1, text(i), String1)
    If x <> 0 Then
        y = y + 1
    End If
Next i
InString = y

End Function

Edited to add code tags

[Edited by Bjwbell on 11-27-2000 at 11:23 PM]

[kedaman]

Well what slows down the process for you there is doevents, you have to put it somewhere where it fires more seldom, like using a double loop:

Public Function InString(text As Variant, String1 As String) As Integer
    Dim y%, i%, imax%
    If Len(String1) Then Exit Function
    For i = 1 To UBound(text)
        imax = i + 5000
        If imax > UBound(text) Then imax = UBound(text)
        For i = i To imax
           y = y - CBool(InStr(1, text(i), String1))
        Next i
        DoEvents
    Next i
    InString = y
End Function

[kedaman]

On a second trought, if you are using vb6 you could also pass text as a string array instead of variant, i'm not sure if thats much speed gain, but it's probably faster to access it than a variant all the time

[Bjwbell]

How do you pass the text as a string array?

[Edited by Bjwbell on 11-28-2000 at 12:27 AM]

[HarryW]

I thought that would happen. Change i for j in the inner loop.

[HarryW]

Just to clarify, use this code:

Public Function InString(text As Variant, String1 As String) As Integer
    Dim y%, i%, imax%, j%
    If Len(String1) Then Exit Function
    For i = 1 To UBound(text)
        imax = i + 5000
        If imax > UBound(text) Then imax = UBound(text)
        For j = i To imax
           y = y - CBool(InStr(1, text(j), String1))
        Next i
        DoEvents
    Next i
    InString = y
End Function

[Bjwbell]

I did just did thats why i edited my post just now

[Edited by Bjwbell on 11-28-2000 at 12:40 AM]

[kedaman]

Hmm pretty stupid of me, well it worked in java though..
hehe "next j" there harry, you forgot that

And heres how you declare the parameter a string array:

Public Function InString(text() as string, String1 As String) As Integer

[Bjwbell]

Well here's what i came up with.

Public Function InString(text() As String, String1 As String) As Integer
Dim y%, x%, i%
If String1 = "" Then
InString = 0
Exit Function
End If
For i = 1 To UBound(text)

y = y - CBool(InStr(1, text(i), String1))

Next i
InString = y

End Function



[Edited by Bjwbell on 11-28-2000 at 01:18 AM]

[HarryW]

Oh yeah *D'oh*

[kedaman]

am i missing something here? well uh i don't know whats going on if you keep editing your posts