Hi folks,
as you know Excel is quiet slow when operating on arrays comparing to Visual Studio.
So I have an idea to make the work faster by:

1. Write Excel range to disk as array

Dim MyArray As Variant ' create array
Dim path As String
path = ActiveWorkbook.path & "\array.txt" ' set path & filename
MyArray = Selection ' selected area will be written as array

Dim free As Integer
free = FreeFile ' free file number

Open path For Binary As free
Put #free, , MyArray ' save array to disk
Close #free

2. Then open this array in Visual Studio for faster operations.

Unfortunately I can manage how to read such array in Visual Studio.

Can you help me?

Thanks in Advance

Archidamos

W dniu sobota, 12 października 2013 10:13:36 UTC+2 użytkownik napisał:

Unfortunately I can manage how to read such array in Visual Studio.


Of course it should be I can't manage :)

A

Why all the extra steps? Why not just 'dump' the range into an array
and work with it in memory?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

W dniu sobota, 12 października 2013 21:03:18 UTC+2 użytkownik GS napisał:
Why all the extra steps? Why not just 'dump' the range into an array
and work with it in memory?

Because of speed.
I made the test which compared the speed of seeking the strings from one array in another array.
This operation in Excel memory took about 30 seconds.
Exactly the same operation in Visual Studio took 52 miliseconds :)
It's 576 times faster. Some operation are 3 or 4 thousant faster.
When working with large amount of data it counts.
Excel is great software but VBA is slow.

A

W dniu sobota, 12 października 2013 21:03:18 UTC+2 użytkownik GS
napisał:
Why all the extra steps? Why not just 'dump' the range into an array
and work with it in memory?

Because of speed.
I made the test which compared the speed of seeking the strings from
one array in another array. This operation in Excel memory took about
30 seconds. Exactly the same operation in Visual Studio took 52
miliseconds :) It's 576 times faster. Some operation are 3 or 4
thousant faster. When working with large amount of data it counts.
Excel is great software but VBA is slow.

A

I don't believe you! What language in VS are you using that you can't
capture the range directly into an array? Show me the code you used for
your VBA tests, and described the layout of your data. Better yet, post
a link to your Excel file...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Well this has nothing to do with VB.NET vs VBA! It just proves that
..NET's Array.BinarySearch function (a Framework feature usable by all
dotnet languages) works better than Excel's Match function. Try using
your VB.NET code on an automated instance of Excel where you substitute
its Array.BinarySearch function with Excel's Match function and see
what I mean.

Besides, that's certainly not how I would do it. I can iterate 2 500000
element arrays seaching for duplicates, put those found into a 3rd
array, then dump the result back into a worksheet in less than 8
seconds using pure VB[A]. Try doing the same with pure VB.NET and let
me know how you make out!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

wrote:
Hi folks,
as you know Excel is quiet slow when operating on arrays comparing to Visual Studio.
So I have an idea to make the work faster by:

1. Write Excel range to disk as array

Dim MyArray As Variant ' create array
Dim path As String
path = ActiveWorkbook.path & "\array.txt" ' set path & filename
MyArray = Selection ' selected area will be written as array

Dim free As Integer
free = FreeFile ' free file number

Open path For Binary As free
Put #free, , MyArray ' save array to disk
Close #free

2. Then open this array in Visual Studio for faster operations.

Unfortunately I can manage how to read such array in Visual Studio.

Can you help me?

Thanks in Advance

Archidamos


what do you mean by "open array in Visual Studio"?

what language and what technology are you using when working in Visual
Studio?
Are you working on files dumping data to txt file first?

Why do not you use XLL ?

W dniu niedziela, 13 października 2013 16:26:24 UTC+2 użytkownik witek napisał:

what language and what technology are you using when working in Visual
Studio?
Could be C# or VB.NET
Are you working on files dumping data to txt file first?
Unfortunatelu yes :)


Why do not you use XLL ?

What is XLL?

Archidamos wrote:

What is XLL?



You can write UDF functions (and more) in C++, compile them to DLL
(called XLL) and use them as built-in functions.
it is 30 - 300 times faster Comparing to vba udfs


Go to Add-in click browse and you can see that allowed extensions are
*.xla, *.xll ....


whatever is not formula (i.e. events) must be done in .net but you do
not have to deal with calculation and pasting outputs to excel.