How to read Excel Array to vb.net Arrays?

  • Thread starter Thread starter archidamos
  • Start date Start date
A

archidamos

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 (e-mail address removed) napisał:
Unfortunately I can manage how to read such array in Visual Studio.
Click to expand...


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?
Click to expand...

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ł:

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
Click to expand...

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
 
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
Click to expand...
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 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
Click to expand...

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 inanother 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
 
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 ?
Click to expand...

What is XLL?
 
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
 
Archidamos said:
What is XLL?
Click to expand...


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.
 
W dniu piątek, 18 października 2013 01:10:13 UTC+2 użytkownik GS napisał:
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].
Click to expand...

Plz show me how to do it.
Thanks

Archidamos
 
W dniu piątek, 18 października 2013 01:10:13 UTC+2 użytkownik GS
napisał:
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].
Click to expand...

Plz show me how to do it.
Thanks

Archidamos
Click to expand...

There's an example posted in this forum, but I don't go back far enough
on this machine to find the topic title. I'll look on my other machine
later and post back. (My power is off today due to replacing the main
electrical panel, so no internet at home!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
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!
Click to expand...

You can do that even faster in C++.
 
GS said:
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!
Click to expand...

You can do that even faster in C++.
Click to expand...

Yeah, I know that! The topic here, though, is VBA vs VB.NET and so not
much point dragging other langs into it!<IMO>

--
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 piątek, 18 października 2013 01:10:13 UTC+2 użytkownik GS napisał:
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!
Click to expand...
LOL, I changed the code to 3x 500000 arrays and ran it again.
Took less then 2 seconds on my old Core Duo machine :)

http://demo.rbx.pl/array2.exe this is it, it's exe but it's clean.

Archidamos
 
W dniu piątek, 18 października 2013 01:10:13 UTC+2 użytkownik GS
napisał:
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!
Click to expand...
LOL, I changed the code to 3x 500000 arrays and ran it again.
Took less then 2 seconds on my old Core Duo machine :)

http://demo.rbx.pl/array2.exe this is it, it's exe but it's clean.

Archidamos
Click to expand...

How does this help people using VBA in Excel? As Witek states, C++ is
even faster than VB.NET, but there's no point dragging that into this
discussion since it also has nothing to do with Excel programming!

Still looking for my example code...

--
Garry

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

Topic:
Find matches in 2 cols using Collection vs Dictionary

Post Date:
1/17/2012

Look for the reply w/subject line "Improved performance"

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
W dniu piątek, 18 października 2013 01:10:13 UTC+2 użytkownik GS napisał:
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!
Click to expand...
LOL, I changed the code to 3x 500000 arrays and ran it again.
Took less then 2 seconds on my old Core Duo machine :)

http://demo.rbx.pl/array2.exe this is it, it's exe but it's clean.

Archidamos
Click to expand...

How does this help people using VBA in Excel? As Witek states, C++ is
even faster than VB.NET, but there's no point dragging that into this
discussion since it also has nothing to do with Excel programming!

Still looking for my example code...
Click to expand...

c++ can be used to write UDFs for Excel.
It is the fastest way If you do not need dumped static data but you
prefer formula which will be recalculated every time when something changes.

I use VBA to handle events only if I do not use VSTO.
All formulas I use are in C++.
 
c++ can be used to write UDFs for Excel.
It is the fastest way If you do not need dumped static data but you
prefer formula which will be recalculated every time when something
changes.

I use VBA to handle events only if I do not use VSTO.
All formulas I use are in C++.
Click to expand...

This may be good for you, me and a few others.., but not the regular
Excel user since this requires a licensed version of Visual Studio.
Albeit we can assume Archidamos has said software, how does that
benefit the regular user who doesn't have the programming background.
Better to, perhaps, use Olaf's dhRichClient.dll and just set a ref to
the component you want to use. At least he provides this free (along
with samples/demos). In fact, I also have (from Olaf) a version of the
FilterMatches function I pointed the OP to that does the exact same
thing using the dhRichClient component. If I recall, it does the 2x
500000 comparisons and returns a 3rd list in less than 2 seconds (or
was that less than 1 second?).<g>

--
Garry

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