VBA Array with Filter, Sort and Slice (2021)
HTML-код
- Опубликовано: 8 июл 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/3bCWkj8
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
VBA Array with Filter, Sort and Slice (2021)
The Array is VBA is super fast which is why we use it. But it has limited functionality. In this video I'm going to show you a brand new Array class that allows you to easily sort, filter, search, reverse, retrieve rows and columns and more.
At the end of the video, you will see an amazing FilterBy Function that allows you to create custom filter functions just like you can do in most modern programming languages.
#VBAArray #VBAArrays #ExcelVBAArrays
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + C 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)
Table of Contents:
00:00 - Introduction
00:11 - Why use Arrays?
02:30 - The problem with the standard VBA array
02:43 - Adding the array class to your code
06:26 - Inserting Rows
07:51 - Removing rows
08:32 - Searching the Array using IndexOf
10:35 - Basic Filtering
11:35 - Filter using custom functions
13:49 - Create a compare function easily
14:59 - IndexOf with a custom function 
Наука
RUclips should add an additional button because a thumbs up is a large understatement for this kind of quality content! Thank you sir, I have learned a lot from your videos, highly appreciated!
Wow, thanks Gert!
Agreed .. he is precise and knows how to deliver in an easy language . Eagerly awaiting his next video. It's like he knows exactly my problem and he guides me through his vids.. keep it up.
Same with me 😊
I've added dozens of functions over this class for my personal use, such as sumif, getunique....Life has never been easier!!!
There are probably millions of VBA coders struggling with these array manipulations, and you got just what the doctor ordered.
I wish Microsoft would add your classes by default so that all could benefit from your excellent work!
Thanks Ori.
Wow, I'm impressed by the amount of work You've put in this class and now You're sharing it with others, awesome!
This is what is - Sharing Wealth...and what can be a greater wealth than knowledge....may be with only exception of 'Health' wealth :) Hats off to you, Paul!
This is now my go to VBA channel.
What amazing training!!! Thank you so much!
I appreciate this work! Keep making more contents. I look forward to attend the webinar today.
Paul, absolutely fantastic.
You never ceases to amaze me.
From Portugal, a huge thanks.
This wanderfull, Paul! Your videos always teach me a lot. Thank you very much! Greetings from Paraguay.
Thanks Marcelo
Super cool functions. I wrote an array manager class without knowing a bunch of stuff and although it works well, it's certainly not as slick as yours!
Brilliant! You're creating your own version of Python Pandas! :-)
Fantastic video!
Eagerly awaiting the workbook source code download so I can go through it in more detail.
Thank you for creating and sharing this video!
Unmatched. This saved a tons of time and effort for my VBA application. It would be nice we can get a unique array of items given the column index.
Verry impressive for the performance. i hope we will have a sort by column in the near future.
The content that this channel provides is just amazing. Following previous videos I switched the majority of data manipulations to arrays because they were much faster. They worked wonders but changing them was hard work, even just adding/removing a row or filtering data required a lot of code, BUT NOT ANYMORE :D!! Keep up the great work. Huge thanks!
Great to hear Alexandru!
What an amazing job you have done and provided , highly appreciated !
Thank you kindly!
De acuerdo con estos comentarios. Lo mejor que he visto en RUclips. Calidad y cantidad. Y eso que no es mi idioma. Pero vale la pena el esfuerzo porque es lo mejor que hay por lejos de VBA en RUclips.
Thank you a lot for your contents. Really appreciated
Ho avuto la possibilità di studiare manuali e frequentare diversi corsi promossi da programmatori su VBA e posso affermare che Paul Kelly è un eccellete docente!. Grazie a Te Paul Kelly ho compreso concetti profondissimi sul linguaggio di programmazione VBA come le Classi, Interfacce, Collection, Dictionary, Array ecc.., Che Dio ti benedica e sia propenso a farti raggiungere le "alte vette" professionali che abitano nel tuo cuore di insegnante.
Translated "I have had the opportunity to study manuals and attend several courses promoted by programmers on VBA and I can say that Paul Kelly is an excellent teacher !. Thanks to you Paul Kelly I understood very deep concepts on the VBA programming language such as Classes, Interfaces, Collection, Dictionary, Array etc .., May God bless you and be inclined to make you reach the professional "high peaks" that live in your heart as a teacher."
Thanks Giovanni, I appreciate it.
Amazing and an excellent one Paul.Every time I see your video I learn something new
Glad to hear that!
This is incredible, Paul! Your videos always teach me a lot. Thank you very much!
Greetings from Brazil.
Glad you like it😀
Hi, great lesson, thank you.
Thanks Paul. That's some powerful magic you've created. Thanks for sharing it :)) Thumbs up!!
You are very welcome Wayne.
Another quality production. Thankyou for your work
Many thanks Anthony!
Thank you very much !!
Fabulous Class Module! Thank you for making my work even easier! Now I see a simple solution to combine this with your Dropdown Search class method to do searches on multiple columns from multiple textboxes.
You're very welcome Peter!
Yet again very usefull information, thank you so much for your effort
You're welcome Anil.
thank you very much
Thank you Paul great video
Glad you enjoyed it Frik
😲Amazing!!! Thank you👍🏻
Glad you liked it!
Muy útil. Gracias
Hi, you are the Best vba programmer and efective teacher that I follow. Thank you very much. Please make more videos.
Thank you Fernando.
Awesome, I have a bunch of functions i use for similar functionality that add to as need it. But I like the completeness and polish of this, think I'll use this class instead.
Thanks Dave.
Thank you....
It is a bit of overkill, but It is pretty awesome. Thanks for sharing!
Great job, Paul! Many thanks
Is it possible to initiliaze array once (as the data range will not be changed), on workbook open for example?
Thank you so much
You're most welcome
This is incredible, Paul! Thanks for sharing the code! One doubt, How can I load the array after the filter in a listbox control?
This is fabulous thank you so much. What is the simplest way to format the columns using this method? I have been trying to format the source and destination and they are not affecting the end results. I have strings that resemble dates and keep formating that way when the array is output to the destination.
Thanks a lot for clsArray. It is amazing. But I try arr.Sort , it seems not to be proper
Can you show how to save the functions and class in personalmacro workbook and how to access the functions in other workbooks?
How do you expect the lambda functions in current excel to impact this? Especially filter by
Can this be used to filter on dates in the array? > as well as =? If so, would this need to be hard coded like your example or could it be a variable?
There is an amazing video! Thank you Paul.
By the way, I got a question is these builts-arrays filters faster than the other video you did about using advanced filtering function of vba?
Depends on what you're doing. They have different applications.
Advanced Filter is extremely fast to run but if your code runs it multiple times it tends to slow down a lot.
The Array isn't quite as fast for the same filter but it is more than fast enough in most situations and doesn't slow if you run operations multiple times.
Hi, trying to copy the cls into my workbook error non compatible with visual basic current version
Can you please make a video on how we can add/subtract larger numbers of 20 character lenght using vba?
Excellent Paul ! and it is very nice to you to share the code. Maybe you could implement Sort Method that allows a Compare fonction :o. It will be perfect.
Of course the compare methode should return -1, 0 or 1.
That's a good idea Benjamin. I may update the sort in the future.
Thank you I learn something new every time I watched your videos.
Glad to hear that!
Thanks for this video tutorial sir EMM. This is very helpful to my project.
thank you so much for traning videoes. If you can make filter with array more then one column video, it will be better
Excelent work! is there a way to use sort with multiple columns?
hey Paul. I have been trying to get around with .rowCount and .rowEnd method for the class. What is the real difference? As far as I can see, they both give the same result
Received an 'Error (404)' from the download link - could you update this excellent resource? Many thanks in advance.
Hi Paul, I've tried to add "clsArray2D.cls" to my own project but it's being rejected. It seems it's not compatible with my VBA version. Could you help me on this please?
thanks a lot. Great video. I do have my own array class but it is nowhere as refine as yours. I will try it for sure. But my company had the great idea to switch to google suit instead of ms office so I might not be using it for very long. I Can’t believe it...
You're welcome. Sounds rough. Maybe time to move:-)
Very useful code, but how can I detect when either the filter or filterbyfunction procedures return no data?
This is amazing, but I have a situation, it would be awesome if you can give me some advice. I have to write data from an array to a Range, but sometimes the user has filters activated over the sheet, so when I run the writedataToRange the info its printed in a wrong way. Thank you so much for your work.
Any chance the source code is still available for this video? The link seems to have expired.
That's really great stuff your doing here. I very much appreciate your lessons and learn a lot from it. Especially this video push me ahead. Just one thing remained open as you didn't talk about sorting an array. Would you give another short explanation on that? Or just add a simple command line as a reply to this comment how to sort an array, like: arr2 = arr1.sort(2)?
I can't figure out myself.
Would be great.
Let me know if you like this Array class in the comments. Don't forget to download the source code with tons of examples from the video description.
Table of Contents:
00:00 - Introduction
00:11 - Why use Arrays?
02:30 - The problem with the standard VBA array
02:43 - Adding the array class to your code
06:26 - Inserting Rows
07:51 - Removing rows
08:32 - Searching the Array using IndexOf
10:35 - Basic Filtering
11:35 - Filter using custom functions
13:49 - Create a compare function easily
14:59 - IndexOf with a custom function
I liked !!! only if as recive the email with the code , i tried in the past to download other code but mismatch , maybe my email has a problem
a link who needs my email is already a promise of spam to me
@@bogdanexit1 The do you have the infinite wait cursor? I disabled the adblocker and the download worked (mine was Ublock Origin).
I used your example to create a filter class, worked perfectly, except for one problem. In clsCompareApple, you show the first argument to iCompareFunction_Compare as ByVal, which causes a compile error. It should be ByRef. This feature made my code a lot simpler. Thanks!
You're welcome
Hii Paul. Can you, please, make video about automation of filling a form in a website using vba excel?
Hello Paul, is it possible to add a function in the DatafromRange : possibility to removeHeaderRight and headerRightCount ? It could be useful ? Thanks
Yes, absolutely. You can easily alter the code to do this.
getting an error "the file is marked as a version not supported by current version of visual basic" , help please
could you please share how you create this class module from scratch. tq in advance
I don't seem to be able to download the code (Edge and Firefox). Would appreciate it if someone could help. Great videos by the way.
In Column 4 I have the following: 01/05
When using Sub TestFilterBasic() the information is returned as 05/jan, which is exactly Day and Month (dd/mm) in Portuguese.
How do I return exactly 01/05?
Hi Paul. Received an 'Error (404)' from the download link - could you update this excellent resource?
Very good stuff here. I tend to use list objects rather than arrays or ranges. I notice you state the performance of arrays is faster than ranges. Does the same apply with list objects on large datasets?
An array is always faster than operations that requires called to the worksheet.
Hi Paul, thank you for the channel, you've got some great content! I'm running 365 in a Windows 11 VM on top of Windows 10. When I drag and drop the array class it gives me an error dialog stating, "The file is marked as a version not supported by the current version of Visual Basic, and won't be loaded." I opened the .cls with Notepad, I assume the error comes from the first directive VERSION 2.0 CLASS. I've been writing VBA for quite some time, but you've got me here, I don't understand your code before Option Explicit. I Googled for an explanation but didn't find anything. Do you have a resource you can point me to so I can enlighten myself? Also, I copied and pasted your code into a class module and commented out everything before Option Explicit, no errors remain, do you feel the code will still run properly? I've noticed other oddities running Win11 VM on top of Win10, I have a hunch it may just that. Your comments are appreciated!
Cant download the source files :(
Hi Paul I can't download source code - can't connect to dropbox - please help.. Jeppe
The link to source code doesn't work. It's not clear whether the array class is custom or part of VBA.
Paul, I'm having trouble downloading the code for this and all your other videos. (All I see is a pulsating circle ... no download activity). Is there a problem with web page or do I need to become a member somewhere. Thanks in advance for the help and for your fantastic videos.
You need to turn off any pop up blockers like uBlockOrigin
Thank you so much - now i have no excuse to not use Arrays in future. Thanks Paul
Happy to help!
Array default is case insensitive?
Very nice functionality! Will try that out for sure.
Is this Array class capable of getting the value of a Column/Row intersecting cell? I use this a lot in my project with ListObject tables.
For example: arr.getValue(row:="7", col:="Sales") would get me the value=10. If the array would start on Fruit column, arr.getValue(row:="Pear", col:="Sales") would get me 98.
Thanks Son. You can get a cell using the *Value* property but only using the number indexes e.g. _arr.value row:=5, column:=3_
Using the text is a neat idea.
Hi,
Thank you for the great content as usual!
Would like to ask if it is possible to pass the array to another workbook? I got the error: "Object doesn't support this property or method" when I tried to write data to a range from another workbook.
Thanks!
Hi Joel,
Yes you can write to any worksheet in any workbook. What line do you get the error on?
@@Excelmacromastery Thanks for your quick response! I managed to find out why; I had some wrong referencing of workbooks.
One other thing to highlight is that for Error trapping, it should not be set to "Break on All Errors". Otherwise, there will be issues with the "Do.. While" loop inside the "CheckDimensions" functions in the class clsArray2D. Just to highlight!
One unfortunate thing in VBA is the best way to check if something exists is often by getting and suppressing an error. Then as you say if you are debugging using "break on all error" it will stop on the line.
It's not the end of the world but definitely inconvenient.
Thanks for the fantastic video! This is exactly what I want 👍👍 I tried to download but the link to source code doesn’t work any more. Is there another alternative? Thank you
I removed pop up blockers and still 404 error …
How does the IndexOf function compare when attempting to find a single string? If I have a string I am meant to find in an array of 5000 items, currently I am cycling through the 5000 item array, one at a time, to locate the string (if it exists). I then repeat this 1500 times (once for each item in another array). If I find the item I am looking for, I return the current array number and move to the next item in the list of 1500. Although this functions, due to the sheer number of times I am repeating the same action, I am looking to optimize this. IndexOf looks promising (even if it only saves a little time).
I cannot test this myself because the links to the classes do not appear to work. **** As I typed this I attempted Microsoft Edge browser. Edge worked, Chrome didn't.
Is this a standard MS class or an add on you are providing? Very cool indeed.
It's a class module that I created.
@@Excelmacromastery That's awesome that you created it and are sharing it. Many likes.
i can't open the Source Code for some reason :(
I need to Use 5000 cell values stored in a column in one workbook as filter criteria for another workbook with multiple columns. Please help solve this official work.
Thanks for the work. It is awesome. But the link to the source code not working. Kindly update it please.
Thanks for all the work involved. I work with VBA arrays all the time, so I was interested to see what functions you created, and that they are somewhat different to those I use. My work, like that of many analysts, doesn't involve changing the data so much as summarising, filtering and making sense of it.
So I often need to get a unique set of IDs from a data set, and then collate totals for each ID. I use a dictionary for this, which is extremely fast. When I sort, I create a sorted linked list using quick sort, so I am only sorting and rearranging one column, which is much faster than sorting all the data columns continuously, when you have thousands of rows of data. After the sort, I can quickly create a new array from the old, using the sort order in the linked list.
A small point - I notice you add ".value" when accessing array values. Perhaps you do this for clarity, but it isn't needed, being implied if omitted.
Hi Dermot, That's a really nice way to do the sorting. I used an existing quicksort algorithm which has been around a while. I might try your method and compare the speeds it would interesting to see the speed difference.
"I notice you add ".value" when accessing array values" - did you mean Range rather than array?
The value is the default in most cases. In some cases, like adding the range to a collection, it will add the actual range rather than the value.
No clsArray2D option comes up for me. Any ideas?
Also do you recommend Array instead of range for large data sheets? Almost 7000 rows. I tried array and its running slower with a bunch of if statements
You need to download the clsArrsy2D code from the description.
Arrays are faster than Ranges. You may be doing something wrong to make them slower.
Hi Paul, very interesting video. Unfortunately I cannot download the workbook. Is the link broken? When I click on "Get the Code" it never finishes loading.
Solved ... when I switched to Chrome the download worked. Thanks :)
Glad you got it sorted out
sir m not done with arr .daraToRange bcz parameter is not visiable here
I don't understand your comment.
Hey Paul, I find an issue in the code, which is inconsistent with your code. That the iCompareFunction's first argument is ByVal in your video, but ByRef in the code you share with us.
And it leads to some issues when doing a multi-condition filter.
I am totally new to the VBA field, I am not sure whether this is a problem, anyway just want you to know that,
And thanks for sharing this wonderful video with us.
Hi Jun. It should be ByRef or the filter will run very slow.
@@Excelmacromastery Oh, that is the reason, now I got it, thank you, Paul.
The links don't seem to work.
Make sure you don't have a pop up blocker turned on or try a different browser. It's been downloaded by 30 people at this time so should work for you.
Sir what font do you use in vba
Consolas
I am a near- to complete-novice at vba. I see your "cls..." autopopulates. Is this new vba code? Do i have an older version, or do the classes get created as you name them? Like i said, novice.
Edit: ugh. From my small amount of research it looks like you won't be able to sum this up in a yt reply. And i think i just answered it by looking at your class module video...
Do you mean if you type cls it fills out the class name? You can use Ctrl + Space to autocomplete.
@@Excelmacromastery I figured it out. I have never used or created class modules before (and I hadn't watched the tutorials on classes before this), so I didn't understand them. I still don't really, to be honest.
Great video, but the link to the source code doesn't, just forever looks like a page trying to load something.
Turn off any popup blockers. That's the most likely cause.
You're a real wizzard! How do I get hold of the clsArray2D class? The link to download the content does not seem to work. Put my email in and selected "Get Code", but nothing happens.
Hi Francois, Make sure to turn off any pop up blockers in your browser.
This download link does not work for me. No matter what pops or blocks, Paul. But fine, that you can get it for yourself :)
Now, magic happened. I got it twice. Thanks twice!
Array transpose
I can't download the files.. after keying in my email, nothing happened
Send me an email and I will take a look for you.
Hi! I haved the same problem, what i did is trade the browser(opera) to the Microsoft Edge, after this the page worked.
@@LuffyFA thank you .. I will try that too. His class filter module is exactly what I'm looking for. Arrays are fast. It helped me alot at my work. Here is a suggestion for the next video .. how to make dashboard with graphs using arrays and advanced filter .. dynamic.. hahahaha..
Disable Ublock Origin or your other adblocker and reload the page, it worked for me.
oh, please, please, please... monstercampaigns isnt opening... i neeeeed this... you make so easy, i can't believe it...
Remove any popup blockers
@@Excelmacromastery oh, tyvm,,, you should charge, you'd be millionaire,,, lol,,, i have yet 300 people to look at my video and i advertise on FB...
thanks so much for your channel and help. I joined to get access to all the cool stuff
cls2Array2D is cool. helped solved a buch of problems.
But i can't seen to alter an individual element of the new array
Lets say A1:B4 = {1,2;3,4;5,6;7,8}
DIM arr as New clsArray2D
DIM result as double
arr.data = Range("A1:B4").value
result = "hello"
arr.data(1,1) = result
but the data @ arr.data(1,1) never gets altered
what gives?
typo - should have been DIM result as string. in any case still doesn't work
When you retrieve the array you are getting a copy of it. VBA copies arrays when moving between variables except when passing as a parameter which must be ByRef.
I cannot remember if there is a class property for updating an individual position but if not you can easily add one that updates the class array.
The opt in form does not work. Permanent sleep.
It's probably caused by a pop up blocker like UBlock Origin. Try using an incognito window and see if that works. If not, send me an email.
@@ExcelmacromasteryI tried another browser, got past that problem, next screen said I would receive an email shortly with the download, that was about 7 hours ago, no email yet. How long does this usually take?