How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
|
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
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? |
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
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. |
How to read Excel Array to vb.net Arrays?
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]. Plz show me how to do it. Thanks Archidamos |
How to read Excel Array to vb.net Arrays?
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]. Plz show me how to do it. Thanks Archidamos 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 |
How to read Excel Array to vb.net Arrays?
GS wrote:
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! You can do that even faster in C++. |
How to read Excel Array to vb.net Arrays?
GS wrote:
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! You can do that even faster in C++. 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 |
How to read Excel Array to vb.net Arrays?
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! 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 |
How to read Excel Array to vb.net Arrays?
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! 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 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 |
How to read Excel Array to vb.net Arrays?
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 |
How to read Excel Array to vb.net Arrays?
GS wrote:
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! 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 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... 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++. |
How to read Excel Array to vb.net Arrays?
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++. 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 |
How to read Excel Array to vb.net Arrays?
W dniu piątek, 18 października 2013 21:25:46 UTC+2 użytkownik Archidamos
http://demo.rbx.pl/array2.exe this is it, it's exe but it's clean. Should be http://demo.rbx.pl/arrays2.exe sorry. Archidamos |
How to read Excel Array to vb.net Arrays?
W dniu piątek, 18 października 2013 22:47:13 UTC+2 użytkownik GS napisał:
Found it! Find matches in 2 cols using Collection vs Dictionary Thanks GS! It looks quiet interesting. Will check it ASAP. A |
All times are GMT +1. The time now is 05:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com