Which is the Fastest VBA Method For Reading Tables?
HTML-код
- Опубликовано: 5 июл 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: bit.ly/3FGeUDq
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Which is the Fastest VBA Method For Reading Tables?
In this video, I'm going to compare the 5 VBA methods for reading through Tables(ListObjects). We will use ListRows, Ranges and Arrays and by comparing the speed we will see which one is fastest. You may be surprised.
#ExcelVBATable #VBATable
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard) 
Наука
Hope you enjoy this video. Let me know in the comments which version you use.
Excel 2010
Excel 2010 and 2019
Arrays are the way to go especially if you want to edit data in any of the rows/columns. Next video should show how changing values in an array is faster than editing values in a range.
Array give all data in one dataset and you can play with dataset, it's fastest way of data capturing and do work, Thank you again for your video. Mr Mahendra K Tita from India.
Use assignment DataBodyRange to Range variable and For Each loop. But really impressed how array is faster and will switch to use it more often.
Ok, I saw the full video. I was going to pause it and comment "why not reading it into an array at once", haha! Nice timer class!
Your videos sped up my programs quite a bit. I wished I found you a lot sooner in my progression of learning VBA.
I first put the table into an array then put the array into a Dictionary. I use this method 99% of the time if I'm looping through another set of data for comparing. I have created subroutines to create the Dictionaries that keeps my code neat. I'll pass the Dictionary subroutine parameters such as the key, item, criteria, transposed, if columns should be summed, Include header row of Array, avoid blanks, add to or erase passed Dictionary.
The Xaxis1strecord one or more concatenated columns with a vbtab delimiter added between the values and stored as a string recorded for the key in the Dictionary.
The Xaxis2ndrecord is also one or more columns but stored into an array recorded as the item in the Dictionary.
Sub Arr2Dict(Dict, arr, Xaxis1strecord, Xaxis2ndrecord, Optional MultiCrit As String, Optional yaxisDim As Long, Optional ColSumArr As String, _
Optional HeaderArrRow As Boolean, Optional NoRemBlanks As Boolean, Optional NoErase As Boolean)
It's years since I programmed in vba but am enjoying your videos in a nostalgic sense.
I remember a sheet I had that took over 20 minutes to calculate using ranges. I converted to arrays and it to less than a minute.
I seem to remember setting the range via a shortcut method, something like rg = [tbName] though.
I used array method, learned from your tutorial. Thank you for sharing
Mahendra K Tita from India
Before I started watching Paul’s videos I read data from the worksheet now I always arrays
Hi Paul, your website & videos are my go to...just sooooo great!!! I always use arrays to read data from my worksheet (ranges & tables), and this definitely confirms it is the best way. Simply awesome 👍👍!!!
Thanks!
I see a lot of comments touting Python being a better data manipulator than VBA. Would love to see a similar video to this but also comparing other methods external to VBA.
I always use the first one. I feel like i need to know which cell the for is working on. It’s time to switch to arrays! Thanks for the video, it clearly shows the difference with live data.
thanks master for this video readind tables, i was expected tis kind material, thanks you from Santiago de Chile.
Wonderful set of tests showing just how critical it is to 'Know Your Optimal VBA Methods' - that is, of course, if you place any value on optimal user experience!
It's the price we pay for the relentless march of programming language abstraction...for everything except assembly language, we've lost control over the form of that final machine code executable...!!!
Always well presented and great value!
Thanks Nic
Hi Paul, another excellent video. I have only recently realised the power of arrays for this sort of processing and it's transformed many tasks I do. One small question on your code for the array solution: might assigning the lower and upper array boundaries to variables before the loop further improve efficiency of this code?
Thanks and keep up the great work.
Tim
great work!
Hi Paul thanks for the video. Just started learning VBA. As my knowledge improves I'll head over to your arrays playlist. I'm convinced.
Glad you like it James
Thank you for the video! This is very helpful. But I think I should just go straight to your website for further reading as usual.
Hello Paul, thank you for this video..u r truely awesome..can you please make some detailed videos to work on table data using vba and combine the same with power query
Pretty good sample for the speed of the different reading types!👍👍👍🤟😎
Array method, as always great video. Thanks Sir
Wonderful video!
Nice one though hey am a beginner and venturing into automating a tool via vba excel ;
Requirements would be to loop in thru table and compare each row value and column for either 6points ascending or 10 pts above average
Great video! In Memory best unless you have very big databases… limit on excel ( I forget the max size) but i hit the limit often in my work.
This is really good.
Paul,
As always thanks a bunch for a nice vote of confidence to arrays 😊..
A bit of a tangent of a query on arrays.. my biggest grouse with them has been the inability use them as a range in a lookup... One of my colleagues demo-ed that you can use a one dimensional one in lieu of a range but no such luck with 2d arrays... Would you have a suggestion the same?
Bests from a big fan 😁😁
Cheers
Suvadip
Thanks. I use arrays in my work, but I didn't even think about such subtleties. now my code will be less stupid.
I have implemented using the Array methodology to retrieve data and it has decreased my code execution time significantly (just like it is displayed in the video). I highly suggest using this method!
Great video! Could you please make a video comparing vlookup formula in VBA to other methods? For example using Arrays vs Vlookup.
For me it's about what type of code looks and reads better. Rather than saving 6 or 700 milliseconds! But it's good to know these things for sure
You are genius boss
Amazing
Nice work
Now i use 99% only arrays and dictionary in all my code thanks for sharing Paul. maybe can you make a future video how to place and dump colors in arrays
You can write the color ( or other format property enumeration values) as array elements and read them back.. not very efficient I would think but better than reading thru' cells/range, copying them any day. I would think that the variance would start showing as the range size becomes bigger.
Thanks Paul! Arrays are the way to go. Thanks for demonstrating and comparing methods.. very instructive. Thumbs up!!
Thanks Wayne.
Great!!!! Thanks for share. What about the faster speed of calculus iterations?
Paul, I've known about arrays from you, so thank you very much! Can you please make video about the fastest method of editing data? For example replacing characters
This is what I do... Array+instr to get to what I am looking for in each row, tag 'em and use string functions (left, right and Len) to replace sub string , store in array and paste when done.
Do not know if it will be fastest though :)
array and dictionary are a good combo
Thanx
Great video! I definitely use arrays and if I have to tables to compare to each other I’ll put one table in a dictionary and one in an array comparing the array to the dictionary.
Thanks for the feedback😀
@@Excelmacromastery thank you for doing these videos and sharing your knowledge. I use arrays and dictionaries because of what I’ve learned from watching them!
Once I had to develop a code to get one Excel sheet with lots of info into another one, but in a tabular format. The original one had blocks of information, i.e., dates in the columns, values in the rows, but each category was spaced out from the other with blank cells, and then it proceeded to repeat the date columns and the value rows for the other category. At first I started with a code that would get each cell of the "table" and put it into tabular format in another table. I did some tests and estimated around 1 hour to run everything. After some thoughtful consideration, I realized I basically needed to copy the whole block and transpose it to achieve the tabular format for each category. So I did it using an array for each block. It took, and I kid you not, less than 5 seconds to go through everything.
That's an amazing difference in speed. Thanks for sharing.
Thanks. The myTimer object is interesting. How is it done?
Super
Hi. How about reading data to an array then copy direct the whole array to a range?
Amazing... Bessings
I love arrays and find them to be transforming the way I code, but what's the row limit of an array variable, though?
Isn't it somewhere around 60k rows if you're using Excel 2016?
Wow interesting, will check this... I somehow think not (unless ofcourse you have a large number of columns in the mix as well)... Will confirm
👍
Thanks for the video! I don't see the source code link. Does that source code include the class module for your timer? I would love to use it.
Im sure Paul will update the link. There was actually another video where he share the timer, but it did not have the estarttime and elasttime enums. I tried to locate it but unfortunately no luck, sorry.
I will add the link shortly😀
Does the speed the same if its not in table?
I use array to process data for many years. It's the most efficient way...
1) Load All Data in one read
2) Process data with a 2D array (even you have one columns)
3) Paste the data in the sheet in one write
With this example ( 50000x3), it take 0.25 sec for me (it depend to your computer) with pasting of the data... (30ms without the pasting)
for exemple:
Sub ReadTable()
Dim myTimer As Double
'---
myTimer = Timer
Call forTable
'---
Debug.Print Timer - myTimer
End Sub
Sub forTable()
'--- Variable
Dim rng_R As Range
Dim vnt_Data As Variant
Dim temp As Variant
Dim i As Long
Dim j As Long
'--- Initialisation
Set rng_R = ThisWorkbook.Worksheets(1).Range("a1")
vnt_Data = Range(rng_R, rng_R.End(xlDown).Offset(0, 2)).Value
'--- Loop
For i = 1 To UBound(vnt_Data, 1)
For j = 1 To UBound(vnt_Data, 2)
temp = vnt_Data(i, j)
Next j
Next i
'--- Paste new Data
Range(rng_R, rng_R.End(xlDown).Offset(0, 2)).Value = vnt_Data
End Sub
Oh noooo. I must rewrite my "listrowfor" method code by a new "array" code. Thank you Paul.
Yes because why "waste" 800 milliseconds of precious time ! 🤣
How file Excel vba form activation in file?
The performance of arrays is great buy in the real world data is not always nice and clean.
The first time I tried loading a range into an array, the code would abort with no message, as I had never seen VBA do. Debugging line by line revealed the problem was in the line where the array was being assigned the range. A cell with a formula error was making it crash.
I know, arrays are ruling, but it a bit hard for me to use them. :)
I prefer to use range for and sometimes each.
THANK YOU, SIR!
If you haven't a large amount of data then ranges are fine.
@@Excelmacromastery I'm on approach to use VBA with big data. So i need more effective instruments for this.
In Arrays How can we get values with Columns Names? Is there anything like Ado Queries to Select Specific Column's Value with Criteria
arr = shSales.ListObjects("tbSales").Range.Value will put the headers into the same array. You can then do a for loop to go through the columns then the rows. Alternatively, you can create a separate array for the column names by doing arrH = shSales.ListObjects("tbSales").HeaderRowRange.Value. LBound(arr,1) and UBound(arr,1) will get you the min/max rows. LBound(arr,2) and UBound(arr,2) will get you the min/max columns.
There isn't anything like this for arrays. The disadvantage of arrays in vba is that they have very little functionality. That's why I created this class ruclips.net/video/ZYxa-Q-nSX0/видео.html
@@Excelmacromastery Yes I have Already Watch it multiple times and and used your 2d Array Class it is very Brilliant Work. 👍
Great as usual but no link to file this time...
Just added now
I used to use For n = 1 to whatever. Now I'll be using arrays 🙂I was always worried that arrays would clog up the memory. Would you use Set arr = Nothing, or will it clear itself?
You only use "Set = Nothing" for objects. The array will automatically be deleted when the array goes out of scope or you assign the variable to something else.
@@Excelmacromastery ArrayName = Empty
Excuse me, I don't understand what is "shSales". Please, could you please explain? You are the best!
The code name of the worksheet.
Why is it that Excel takes different processing time for the same macro for the same dataset each time we run it? I have a macro that takes on average 3 minutes to run, but sometimes it even takes 5-6 minutes to run.
If you have other apps running they can alow your machine down. That said I wouldn't expect such a discrepancy until you were running a resource hungry app
I didn't see the link to download the source in the description
It's there now
as expected, arrays rule.
I don’t understand what this code does. What is its purpose?
not downloading the file by given link.
what is the main reason the array is faster 🤔❓ Is it the definition of the array (arr = shSales ....) or is it the reading out of the array with the for loop ?
That would be as well interesting for the other cases 🤭
------
i for myself use arrays, but to define it... i do a oldschool loop to fill my array with data. Not that elegant like you 😅😅😅
Thanks 👍👍👍
It's both. Assigning the array is fast as is read through the array. The array is simply a group of adjacent memory values.
Hi Paul, do you do consulting? Can I contact you by email?
Sorry, I'm not available for consultation work at this time.
is the workbook missing?
I've added it now.
To me it looked like you read only the first column of the table, not the complete table.
It's too fast. I have to slow down the video to keep up.
LInk dont work..... sorry!
👍