I have been utilizing your resources for 5+ years. I'm a stress engineer, so sifting through tons of raw data and outputting something useful with little individual input is the name of the game. Your stuff has been without question my favorite resource. Clear. Concise. Exactly what is needed.
Really Good. Starting from what an average skilled VBA user would try, highlight the pitfalls and errors, then going to the right solution and finishing with the neat ultimate optimized professional way to do it. Great. Thank you Paul.
Excellent, as always! Best cristal clear VBA tutorials ever! I've learned a lot form you! Thank you for that! Please, share us more of your knowledge! AI can automate tasks, but human makes them personalized. So, for Excel dependent jobs, VBA can help a lot if deeply understood and learned.
Hi Paul, I appreciated that longer video format. It helped me locate where I'm at in my Excel VBA Array journey, and the clear explanations helped me to move to the next level. Nice tutorial; I'm a fan of your work!
Nice video, I'm working more with VBA nowadays and the knowledge I got from you helped me a lot, though I haven't adapted to not bubble up my variable declarations yet 😅
Thanks Paul - as always your videos contain very useful information. I like the longer content video above. It provides a more 'start to finish' approach to code logic. Best regards, Bill
Hi, Paul. Great tutorial as always. However i ve encountered with difficulties when sorting an array at the very last step. I fail to sort it with one line worksheetfunction approach (No method is found).Other youtube tutorials also provide some longer solution on array sorting. Is there any trick that i ve missed?
Thanks Mr. Paul Great way to master , step by step - so basic fundamentals are very clear as we move forward with clutter free powerful codes - Your Mastery, always shared brilliantly to make us Masters as well ! Thanks a Lot for sharing your treasure 🙏
Hi Paul, you are amazing, i also download your ClsArray2D class module, and take so much benefits, thank you, now am also able to make my own function for arrays current region, or clear existing data,,. I also download this one , thank you so much for your free help😊
@@Excelmacromastery if you need an example idea for the course I have 2: getting the names of files in folders that have subfolders and getting the charges on a credit card charges history that sum up to the credit card current balance
Can we use arrays to copy all file names from a folder and paste it in an excel range? I am currently using "Copy Path" menu item to copy all file names which are there in a folder and pasting them in excel because it is simpler and time saving than running a macro which reads each file one by one and copies its name.
As always your tutorial was very informative. One issue; I get a runtime error when calling the worksheet sort function. This function seems to be unavailable in my workbook. Do you know why?
I would suggest the GetCurrentRegion function should call another function to remove heading lines but setting the current region property outside of calling that function. Then the inner function can be used to remove headings from any region even if current region isn't to be applied.
Paul, thanks for nice and detail comments. I have one question, why when I use "marks = shMarks.range("A1").Value" I receive error 424 (Object required). And it works when I write "marks = Sheets("shMarks").range("A1").Value".
Hi Paul, Realy enjoyed the webinar, Thanks. I did run in a problem with the code, trying to filter I get the eror " unable to get the sort property of the worksheetfunction class" on line "outputArray = WorksheetFunction.Sort(outputArray, 4, -1)" . any ideas?
Hi Mr KELLY. Hope you're fine. I guess you've heard about Office script. My concern is to know if VBA is will still be used in the 5 or 10 next years?!!! Is Office Script is replacement!! I've got those worries since I read somewhere that Microsoft will definetively discard VBScript from Windows 11. Do you have clear and trustfull informations about that topic?
i didn't watch the video but i have a trick about array i don't know if you are aware of. its how to redim preserve an array's first dimension, we know that we can only redim the second dimenssion , so the trick is to fill your Array in reverse order what in first dimension go into the second and what in the second go into first dimension, so then you redim preserve the second dimension after that you transpost the array into a variant using worksheetfunction.transpose to get your initial copied range.
@@omaraissa318I have also the same problem, I use meta AI, but AI say, Copy this Array to new blank array and then resize it 😂 Thank u But still I am not completely understand of your comment I will screenshot it 😊
Thanks for sharing valuable information. I am already using array but it is still taking time to process big datasets. I have checked the number of iterations is more than 30+ million. Please can you tell me is there any way through which I can use GPU or multi-thread? I have done a lot of research regarding this and someone told me to use opencl but unable to find the library. I have tried to find some third-party tools also for parallel processing but I haven't found any tool yet. Please can you help me that would be great.
Hi, would you like to elaborate on the problem statement a bit.. for example, would 30 million iterations mean that you are accessing data from multiple spreadsheets? What type of validations/logic are you processing? Can you employ 'divide and conquer' approach? Finally, have you looked at using Excel's ETL backbone, power query as a solution? I do not know the performance criteria you have so if you can throw some more light on the same that will help .. Cheers, Suvadip
I don’t think vba is the best tool for massive datasets. From my experience once you’ve done the usual disabling calculations, events and screen updating and are doing most of the work in arrays thats about as fast as you’ll get (advanced filter can be faster for large filtering operations). I believe vba is single threaded (worksheet formulas can use more) this can make large parallel operations slow I also doubt it takes advantage of the gpu much if at all. I think python may the better tool to use for large data analysis
one issue I was facing when working with copying arrays is that if you have a column (or more) that is containing string values that starts with "0", the destination range needs to be formatted as text (.numberformat = "@"), otherwise the leading 0s will be truncated.
If your numbers are of fixed length you can always just add zeros to the front and take the right of the required length, then number value of that Always a pain on bank details and phone numbers Tho it’s more annoying working with sort codes as some of the time it converts 6 digits separated by hyphens to dates
@@johnbutler2750 your solution for a simple copy paste will add a lot of extra effort to implement, especially if you have more columns that are not always being pasted into the same cells
@@alexandrumarcel3696 meant it as a more general solution, either use a custom function to apply it when called in a larger macro, or as a nested formula to apply to an entire column then copy-paste as values (depending on how often it’s used)
Nice video but I have a feeling that you've covered this topic multiple times already in previous videos. As a long time fan of your content I consider myselft a master on arrays at this point :) I'd prefer more videos on Classes with some real world examples....
4:16 you r not pro ! Pro vba wud do this Dim totrow as long totrow= [Counta(Sheet1!A:A)] now automatically got total rows Dim Marks() as variant Redim Marks(totrow) as long for i= 1 to totrow ''' either throw marks array values '''' or use in any cmd. Msgbox Marks(i) Sheet1.Range("B"&i).value= Marks(i)*10 Next i Msgbox "Done !"
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
Let me know in the comments if you like this longer video format😀
Yes is beautifull ,easy to follow you and learn a lot!
I have been utilizing your resources for 5+ years. I'm a stress engineer, so sifting through tons of raw data and outputting something useful with little individual input is the name of the game. Your stuff has been without question my favorite resource. Clear. Concise. Exactly what is needed.
Glad you like it so much😀
@@Excelmacromastery Paul, you are my hero. Learned so much stuff alone from your articles. Thank you very much!!
Paul, your tutorials are fundamental. Yes, I like them.
Thanks
Really Good. Starting from what an average skilled VBA user would try, highlight the pitfalls and errors, then going to the right solution and finishing with the neat ultimate optimized professional way to do it. Great. Thank you Paul.
really good to separate each procees in read, process and write the data
It's a pure pleasure to work with your tutorials.
Thanks
Excellent, as always! Best cristal clear VBA tutorials ever! I've learned a lot form you! Thank you for that! Please, share us more of your knowledge! AI can automate tasks, but human makes them personalized. So, for Excel dependent jobs, VBA can help a lot if deeply understood and learned.
Thank you
Hi Paul, I appreciated that longer video format. It helped me locate where I'm at in my Excel VBA Array journey, and the clear explanations helped me to move to the next level. Nice tutorial; I'm a fan of your work!
Great to hear Nathan!
This is, without a doubt, awesome!
Amazing video as always Paul. The longer format is quite good as well. Thanks.
Thanks
Nice video, I'm working more with VBA nowadays and the knowledge I got from you helped me a lot, though I haven't adapted to not bubble up my variable declarations yet 😅
Thanks Daniel
Thanks Paul - as always your videos contain very useful information.
I like the longer content video above. It provides a more 'start to finish' approach to code logic.
Best regards,
Bill
Thanks Bill
Hi, Paul. Great tutorial as always. However i ve encountered with difficulties when sorting an array at the very last step. I fail to sort it with one line worksheetfunction approach (No method is found).Other youtube tutorials also provide some longer solution on array sorting. Is there any trick that i ve missed?
Thanks Mr. Paul
Great way to master , step by step - so basic fundamentals are very clear as we move forward with clutter free powerful codes -
Your Mastery, always shared brilliantly to make us Masters as well ! Thanks a Lot for sharing your treasure 🙏
Glad it was helpful!
thanks Mr. Pau.
Please, we need more videos with projects about real-time application in excel through websockt.
Thank you paul for this nice video. I am already taken your VBA effective course
You're welcome
Hi Paul, you are amazing, i also download your ClsArray2D class module, and take so much benefits, thank you, now am also able to make my own function for arrays current region, or clear existing data,,.
I also download this one , thank you so much for your free help😊
You are amazing. Thank you. Greetings from Turkey.
Thanks
I used VBA as an enhancement tool, but not frequently.
I couldn't ask for a better reference resource.
A M A Z I N G work!!
i adore these types of videos
Glad you like them
Excelente tutorial paso a paso y muy útil, muchas gracias
Thank you again clean and valuable info. Any plan to video about Excel with Typescript or any other modern code?
I have videos on office scripts and Python on this channel.
I like anything you do!
Thanks 😊
I'll be wanting for the tutorial. Even I don't speak English 🫣. But I speak Vba. Tks so much. It looks great
Hi Paul, Excellent video!
I cant seem to get LastRow to work. I get an error Run time error1004. all the sheet names are correct Any ideas?
Simply masterful! Do you have any courses or classes in recursive functions?
Thanks. I haven't covered recursion.
@@Excelmacromastery if you need an example idea for the course I have 2: getting the names of files in folders that have subfolders and getting the charges on a credit card charges history that sum up to the credit card current balance
Can we use arrays to copy all file names from a folder and paste it in an excel range? I am currently using "Copy Path" menu item to copy all file names which are there in a folder and pasting them in excel because it is simpler and time saving than running a macro which reads each file one by one and copies its name.
As always your tutorial was very informative. One issue; I get a runtime error when calling the worksheet sort function. This function seems to be unavailable in my workbook. Do you know why?
It's only available for Excel 2021 and 365
I would suggest the GetCurrentRegion function should call another function to remove heading lines but setting the current region property outside of calling that function. Then the inner function can be used to remove headings from any region even if current region isn't to be applied.
I don't mind the longer format, but would like shorter ones as well
Is there something wrong with using xlDown as oppose to xlUp?
It will stop at a blank row if searching from the top.
Love this video! Do you program in Access VBA? Please would you do one for Access if you do? Thanks!
Glad you like it. I haven't used Access in a long time .
Thank you very much! It helps me so much!😀
You're welcome
Paul, thanks for nice and detail comments.
I have one question, why when I use "marks = shMarks.range("A1").Value" I receive error 424 (Object required).
And it works when I write "marks = Sheets("shMarks").range("A1").Value".
shMarks should be the code name of the worksheet
Hi Paul, Realy enjoyed the webinar, Thanks. I did run in a problem with the code, trying to filter I get the eror " unable to get the sort property of the worksheetfunction class" on line "outputArray = WorksheetFunction.Sort(outputArray, 4, -1)" . any ideas?
You need Excel 2019 or later to use the sort function.
@@Excelmacromastery Thanks Paul, that explains it. Nice webinar, again. Like the lenght.
Thank you. I learned a lot.
Hi Mr KELLY. Hope you're fine. I guess you've heard about Office script. My concern is to know if VBA is will still be used in the 5 or 10 next years?!!! Is Office Script is replacement!! I've got those worries since I read somewhere that Microsoft will definetively discard VBScript from Windows 11. Do you have clear and trustfull informations about that topic?
Office scripts is not a replacement for VBA.
The VBScript update, as it stands now, will only affect regular expressions in VBA.
Hello Paul. I'm Kofi from Ghana. Please when will you make a VBA introductory video. It will be very much appreciated. Thank you
how much time it will take if the data is 10k plus?
Really amazing work, yes we like it. I watched it 2 times 😂
You're very welcome
i didn't watch the video but i have a trick about array i don't know if you are aware of. its how to redim preserve an array's first dimension, we know that we can only redim the second dimenssion , so the trick is to fill your Array in reverse order what in first dimension go into the second and what in the second go into first dimension, so then you redim preserve the second dimension after that you transpost the array into a variant using worksheetfunction.transpose to get your initial copied range.
The problem is that Transpose has a limit of 64000 rows for arrays.
@@Excelmacromastery thank you i learned something
@@omaraissa318I have also the same problem, I use meta AI, but AI say, Copy this Array to new blank array and then resize it 😂
Thank u
But still I am not completely understand of your comment
I will screenshot it 😊
Thanks for sharing valuable information. I am already using array but it is still taking time to process big datasets. I have checked the number of iterations is more than 30+ million. Please can you tell me is there any way through which I can use GPU or multi-thread? I have done a lot of research regarding this and someone told me to use opencl but unable to find the library. I have tried to find some third-party tools also for parallel processing but I haven't found any tool yet. Please can you help me that would be great.
Hi, would you like to elaborate on the problem statement a bit.. for example, would 30 million iterations mean that you are accessing data from multiple spreadsheets? What type of validations/logic are you processing? Can you employ 'divide and conquer' approach?
Finally, have you looked at using Excel's ETL backbone, power query as a solution? I do not know the performance criteria you have so if you can throw some more light on the same that will help ..
Cheers,
Suvadip
I don’t think vba is the best tool for massive datasets. From my experience once you’ve done the usual disabling calculations, events and screen updating and are doing most of the work in arrays thats about as fast as you’ll get (advanced filter can be faster for large filtering operations). I believe vba is single threaded (worksheet formulas can use more) this can make large parallel operations slow I also doubt it takes advantage of the gpu much if at all.
I think python may the better tool to use for large data analysis
one issue I was facing when working with copying arrays is that if you have a column (or more) that is containing string values that starts with "0", the destination range needs to be formatted as text (.numberformat = "@"), otherwise the leading 0s will be truncated.
If your numbers are of fixed length you can always just add zeros to the front and take the right of the required length, then number value of that
Always a pain on bank details and phone numbers
Tho it’s more annoying working with sort codes as some of the time it converts 6 digits separated by hyphens to dates
@@johnbutler2750 your solution for a simple copy paste will add a lot of extra effort to implement, especially if you have more columns that are not always being pasted into the same cells
@@alexandrumarcel3696 meant it as a more general solution, either use a custom function to apply it when called in a larger macro, or as a nested formula to apply to an entire column then copy-paste as values (depending on how often it’s used)
Amazing! Thank you!
You're welcome.
Nice video but I have a feeling that you've covered this topic multiple times already in previous videos. As a long time fan of your content I consider myselft a master on arrays at this point :) I'd prefer more videos on Classes with some real world examples....
Thanks. I have covered a lot of this before. However, this video shows a step by step logic from beginning to end.
Дякую за урок.
Nice. What about Typescript for Excel ?
I have some videos on Office Scripts which using the TypeScript language for Excel.
Thanks for your knowling
You're welcome
amazing !
Just the best!!!
does anyone know how to do "make a multiple line as comment" shortcut like the one in 14:35?
See the second answer here: shorturl.at/HaQN1
Thanx
Welcome
I feel so alone and unworthy, because I know VBA like a pro, but no one needs VBA anymore... It makes me depressed...
Learn a modern language.
Should not be difficult for u.
4:16 you r not pro !
Pro vba wud do this
Dim totrow as long
totrow= [Counta(Sheet1!A:A)]
now automatically got total rows
Dim Marks() as variant
Redim Marks(totrow) as long
for i= 1 to totrow
''' either throw marks array values
'''' or use in any cmd.
Msgbox Marks(i)
Sheet1.Range("B"&i).value= Marks(i)*10
Next i
Msgbox "Done !"
😄😄 maybe watch the full video?
🙂_______👍💯
.
Thanks
Too long
Too complicated
Stick to the goal.