How I Made 2 VBA Applications Run Ultrafast
Вставка
- Опубліковано 6 жов 2024
- 👉 Ready to master VBA?
Check out my full courses: courses.excelm...
Subscribe to the channel here: bit.ly/36hpTCY
How I Made 2 VBA Applications Run Ultrafast
In this video, I take two extremely slow Excel VBA applications and I make them run in under a second. First of all, I will show you how long they take
to run and then I will show you the steps to make them increase their speed exponentially. If your VBA applications are running slowly then this video is for you.
#ExcelVBA #VBASlowCode #VBACodeFast
Dictionary Playlist: • Excel VBA Dictionary
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) - Наука та технологія
You're my teacher n. 1 for VBA. Please don't stop posting. even if excel has improved a lot, with new formulas, power query and power pivot, we still need our good old VBA !!!
Thanks. I plan to keep going.
It's interesting to a see a professionals take on this. The first problem, I would have just filtered the column searching for 0, then deleted all rows, then removed the filter. On datasets of a few hundred rows, this works very fast.
Love to see you back again, keep bringing these
Thanks
It's been a while Mr Paul Kelly, since a last video about Vba. I still need learn a lot from U.. please don't get bored to teach us..
Thanks🙏
Great (as always) you shed new light on what we have done by habit
Brilliant video! I always learn something new when I watch one of your videos. If you are able to, could you share the Timer code?
If it takes you 4 minutes to loop over 10 rows of data, you don't need a better algorithm; you need a new computer.
Not to loop but to delete individual rows.
You're missing the point: the time is spent deleting even a single row in a list of "thousands of rows."
😊😊😊😊😊😊😊😊@😊😊
I am using this method: Sort, Filter, Delete visible lines exept header, filter off: ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
Nice video, but may I ask for a link to that Timer class? Thanks in advance!
I find it interestinf you seem to get a performance increase by disabling events even if you don't have any actions assigned to those events.
Your work is excellent. Thanks for sharing.
Glad you like it
thank you very much for this video which is interesting. please can you share with us the excel file of this course. best regards
Thank you, Paul!
I would like a tutorial on VBA interaction with messengers: Telegram, Viber, WhatsApp. Your best practices on this topic.
In your 2nd topic, i think using the Dictionary method is the best option in an instance when "Count FC" inidcator isnt present.
Lovely, Paul, and pretty ironic as I put together a very similar routine yesterday, except I captured an array of the values across all columns within the records with the same 'famCode'. Then I looped across the rows in each column of that array, storing the first value as the string for comparison and comparing each subsequent value within that column of the array against it. If it didn't equate to the comparison string, I mapped out that column in the array to the equivalent range in the worksheet, applied formatting to highlight and exited out of that column to resume looping.
Very much inspired by your Array-Dictionary combination techniques for speed.
🤩🙏
That is a coincidence. Thanks for the feedback Ben.
Thanks for wonderful technique that save a lot time.😊
You're welcome.
I think this can also be done using Union and delete all the 0's at once. Sorting won't be required.
Mr. Paul, I find your videos very helpful in my tasks.
Can I ask a question? My dataset column 'D' has integer values in it, along with zeros in some cells. How can I force the Find function to just look for '0' in whole cell, instead of looking for '0' in figures say 76,000?
Create table. Load in power query en load back in Excel.
Excellent tutorial!
Thanks
Hi Paul, I am from Senegal. I really appreciate your sharing knowledge. However, I would like to know how I can't subscribe through payment by card from my country to get more of your lessons.
Can you Upload Sample Data- Excel Sheet ...Which will help many people....
I’m wondering if the external workbook references is what was making the Delete slow if the workbook isn’t open…
Paul, link please file from this tutorial to download.
1st example, why not simply Data/Filter/Autofilter for zeros and then delete the filtered rows?
It is slower than the method I used.
@@Excelmacromastery ah yes, I fell into the trap of thinking what's easiest when doing it manually through the UI. Execution wise, I can see how your method would be faster.
Please give the files, Or Just the code in a texf files, it would help a lot..
How to scrape data from web with login
Hey man.. So call me late to the party, but is vba seriously dead with subscr online ver of MS365? Even the paid version? Am lookin to finally upgrade my old excel version...Reading all about the benefits of buying the subscription MS 365 office vs the old one time buy/install version. No create or run any vba at all? How is that possible with millions of vba files out there. I hear office 365 excel desktop ver still does vba...But whats the point...u cant give it to someone using 365 online?!!
MS365 I'd subscription based with updates where as Office 2021 is once off payment.
Both are desktop versions of office. 365 includes an online version of Excel.
Online isn't replacing desktop. It's just an alternative way of editing an Excel file.
it looks like you dont like the for loop in your code.
Заметил русское название файла))
this is not the great solution without toucing the data you shld make the code run faster
Mn
Take home message: MS Excel is a dinosaur.
(You could have read the data into an array and done the deletion there, on the first one too. Why didn’t you?)
I had a sheet with 16,000 rows with a similar problem. I tried both methods: delete via array, and delete after sorting. Sorting is way faster, especially when the cell contents are formulas.
The solution required keeping the formatting of the records and because sorting was faster.
@@quench100 How about Power Query. It would be fine befire more. You opened the VBA editor.
@@TP-om8of I've never used Power Query, I'm using Excel 2013.
Yep. Power Query is an ETL programm. Perfect for cleaning data.
=MIN(FILTER($C$2:$C$1048576,$B$2:$B$1048576=b2,""))
but its always good to know a new VBA approach.