W dniu niedziela, 13 października 2013 08:19:04 UTC+2 użytkownik GS napisał:

I don't believe you! What language in VS are you using that you can't

Here are two listings, one is VB.NET, the second is Excel VBA.
Just put the first one inside some button sub in Visual Studio.
Put the second one in Excel module.
Both procedures do the same thing. Searching for variable from one array in another one. VB.NET procedure works almost 4000 times faster.

' VB.NET circa 55 miliseconds

Dim array1(20000) As String
Dim array2(20000) As String
Dim result(20000) As String
Dim counter As Integer
Dim mFound As Integer

Dim i As Integer

Randomize()
For i = 0 To 20000
array1(i) = Convert.ToString(Int(Rnd() * 1000000) + 1)
array2(i) = Convert.ToString(Int(Rnd() * 1000000) + 1)
Next

Array.Sort(array1)
Array.Sort(array2)

Dim oWatch As New Stopwatch
oWatch.Start()

For i = 0 To UBound(array2) - 1
mFound = Array.BinarySearch(array2, array1(i))
If Not (mFound < 0) Then
counter += 1
result(counter) = array1(i)
End If
Next

oWatch.Stop()
MsgBox("Strings found: " & counter & " , time: " & oWatch.ElapsedMilliseconds.ToString & " miliseconds.")



'VBA EXCEL circa 216 seconds

Sub VBA_Array_Find()

Dim array1(20000) As String
Dim array2(20000) As String
Dim result(20000) As String
Dim counter As Integer
Dim i As Integer
Dim mFound As Integer

Randomize Timer

For i = 0 To 20000
array1(i) = Str(Int(Rnd * 1000000) + 1)
array2(i) = Str(Int(Rnd * 1000000) + 1)
Next i

Dim timeStart As Single
timeStart = Timer

For i = 0 To UBound(array1) - 1

On Error GoTo ErrorHandler

mFound = Application.WorksheetFunction.Match(array1(i), array2, 0)
result(counter) = array1(i)
array2(mFound) = vbNullString
counter = counter + 1
ret2loop:
Next i

MsgBox "Strings found: " & counter & " , time: " & Timer - timeStart & " seconds."
Exit Sub

ErrorHandler:
Resume ret2loop
End Sub