TH-cam 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!
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.
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!
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!
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.
De acuerdo con estos comentarios. Lo mejor que he visto en TH-cam. 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 TH-cam. Thank you a lot for your contents. Really appreciated
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!
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!
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.
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!
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!
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.
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 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
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.
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.
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...
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?
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.
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
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.
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!
@@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.
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.
Thank you Paul for this usefull teachings, but when I try to drop the clasess appears this error: The file 'clsArray2D.cls', is marked as a version not compatible with the current version of Visual Basic and cannot be loaded. Can this be fix? Thanks a lot.
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
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!
Hello, thanks a lot for these excellent information. I got an error trying to include the clsArray2D.cls to Visual Basic. It throws this error: the file is marked with a version that is not compatible with the current version of visual basic. It's something like this, becase I am a spanish speaker and the error is shown in spanish. Thank you very much for your helping me to solve this because I need this class a lot.
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 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?
Hello Paul, is it possible to add a function in the DatafromRange : possibility to removeHeaderRight and headerRightCount ? It could be useful ? Thanks
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.
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.
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.
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?
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.
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...
@@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.
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.
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
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?
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.
@@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?
@@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..
TH-cam 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 😊
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.
I've added dozens of functions over this class for my personal use, such as sumif, getunique....Life has never been easier!!!
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.
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.
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.
De acuerdo con estos comentarios. Lo mejor que he visto en TH-cam. 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 TH-cam.
Thank you a lot for your contents. Really appreciated
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!
This wanderfull, Paul! Your videos always teach me a lot. Thank you very much! Greetings from Paraguay.
Thanks Marcelo
Paul, absolutely fantastic.
You never ceases to amaze me.
From Portugal, a huge thanks.
Hi, you are the Best vba programmer and efective teacher that I follow. Thank you very much. Please make more videos.
Thank you Fernando.
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!
What an amazing job you have done and provided , highly appreciated !
Thank you kindly!
Brilliant! You're creating your own version of Python Pandas! :-)
This is incredible, Paul! Your videos always teach me a lot. Thank you very much!
Greetings from Brazil.
Glad you like it😀
I appreciate this work! Keep making more contents. I look forward to attend the webinar today.
Thank you I learn something new every time I watched your videos.
Glad to hear that!
Can you show how to save the functions and class in personalmacro workbook and how to access the functions in other workbooks?
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!
Thanks a lot for clsArray. It is amazing. But I try arr.Sort , it seems not to be proper
Verry impressive for the performance. i hope we will have a sort by column in the near future.
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!
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
What amazing training!!! Thank you so much!
Amazing and an excellent one Paul.Every time I see your video I learn something new
Glad to hear that!
Excelent work! is there a way to use sort with multiple columns?
Received an 'Error (404)' from the download link - could you update this excellent resource? Many thanks in advance.
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.
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.
This is incredible, Paul! Thanks for sharing the code! One doubt, How can I load the array after the filter in a listbox control?
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?
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.
Very useful code, but how can I detect when either the filter or filterbyfunction procedures return no data?
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 …
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.
Thanks Paul. That's some powerful magic you've created. Thanks for sharing it :)) Thumbs up!!
You are very welcome Wayne.
Hii Paul. Can you, please, make video about automation of filling a form in a website using vba excel?
Yet again very usefull information, thank you so much for your effort
You're welcome Anil.
Can you please make a video on how we can add/subtract larger numbers of 20 character lenght using vba?
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.
Any chance the source code is still available for this video? The link seems to have expired.
Thanks for the work. It is awesome. But the link to the source code not working. Kindly update it please.
Thank you Paul great video
Glad you enjoyed it Frik
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:-)
Another quality production. Thankyou for your work
Many thanks Anthony!
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, great lesson, thank you.
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.
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?
thank you so much for traning videoes. If you can make filter with array more then one column video, it will be better
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
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.
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 this video tutorial sir EMM. This is very helpful to my project.
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
could you please share how you create this class module from scratch. tq in advance
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.
How do you expect the lambda functions in current excel to impact this? Especially filter by
Thank you Paul for this usefull teachings, but when I try to drop the clasess appears this error:
The file 'clsArray2D.cls', is marked as a version not compatible with the current version of Visual Basic and cannot be loaded.
Can this be fix? Thanks a lot.
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).
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!
Hi Paul. Received an 'Error (404)' from the download link - could you update this excellent resource?
Hello, thanks a lot for these excellent information. I got an error trying to include the clsArray2D.cls to Visual Basic. It throws this error: the file is marked with a version that is not compatible with the current version of visual basic.
It's something like this, becase I am a spanish speaker and the error is shown in spanish. Thank you very much for your helping me to solve this because I need this class a lot.
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?
Hi, trying to copy the cls into my workbook error non compatible with visual basic current version
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.
😲Amazing!!! Thank you👍🏻
Glad you liked it!
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?
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.
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
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.
Thank you very much !!
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.
Muy útil. Gracias
Cant download the source files :(
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.
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?
Thank you....
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.
The link to source code doesn't work. It's not clear whether the array class is custom or part of VBA.
Thank you so much
You're most welcome
getting an error "the file is marked as a version not supported by current version of visual basic" , help please
Hi Paul I can't download source code - can't connect to dropbox - please help.. Jeppe
Sir what font do you use in vba
Consolas
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...
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.
i can't open the Source Code for some reason :(
sir m not done with arr .daraToRange bcz parameter is not visiable here
I don't understand your comment.
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.
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.
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.
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.
Array transpose
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!
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?
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.