How I Made 2 VBA Applications Run Ultrafast

Рет қаралды 52,981

9 ай бұрын

👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- 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)

Пікірлер: 53

@Squeeonline 9 ай бұрын

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.

@AnilKumar-vi8oe 9 ай бұрын

Love to see you back again, keep bringing these

@Excelmacromastery 9 ай бұрын

Thanks

@baphnie 9 ай бұрын

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.

@Excelmacromastery 9 ай бұрын

Not to loop but to delete individual rows.

@peterhuntervogel 9 ай бұрын

You're missing the point: the time is spent deleting even a single row in a list of "thousands of rows."

@sunildhyani904 9 ай бұрын

😊😊😊😊😊😊😊😊@😊😊

@alterchannel2501 9 ай бұрын

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 !!!

@Excelmacromastery 9 ай бұрын

Thanks. I plan to keep going.

@MrStuartAllan 9 ай бұрын

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?

@hcandts 9 ай бұрын

Great (as always) you shed new light on what we have done by habit

@madmaxhaskovo 9 ай бұрын

Nice video, but may I ask for a link to that Timer class? Thanks in advance!

@kukuhwahyurinaldi6288 9 ай бұрын

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🙏

@BenLinfordUK 9 ай бұрын

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. 🤩🙏

@Excelmacromastery 9 ай бұрын

That is a coincidence. Thanks for the feedback Ben.

@michaelangellotti5741 9 ай бұрын

Your work is excellent. Thanks for sharing.

@Excelmacromastery 9 ай бұрын

Glad you like it

@fitosferenc 9 ай бұрын

I am using this method: Sort, Filter, Delete visible lines exept header, filter off: ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp

@LoneIcon 9 ай бұрын

In your 2nd topic, i think using the Dictionary method is the best option in an instance when "Count FC" inidcator isnt present.

@Bhavik_Khatri 9 ай бұрын

Excellent tutorial!

@Excelmacromastery 9 ай бұрын

Thanks

@munshirasimraja5713 9 ай бұрын

Thanks for wonderful technique that save a lot time.😊

@Excelmacromastery 9 ай бұрын

You're welcome.

@SolutionsByPVV 9 ай бұрын

Thank you, Paul! I would like a tutorial on VBA interaction with messengers: Telegram, Viber, WhatsApp. Your best practices on this topic.

@iliesmeziani 8 ай бұрын

thank you very much for this video which is interesting. please can you share with us the excel file of this course. best regards

@josephdaquila2479 8 ай бұрын

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.

@renziair 13 күн бұрын

I think this can also be done using Union and delete all the 0's at once. Sorting won't be required.

@ZoltanGrose 9 ай бұрын

I’m wondering if the external workbook references is what was making the Delete slow if the workbook isn’t open…

@ousmanetall1286 9 ай бұрын

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.

@mathijs9365 9 ай бұрын

Create table. Load in power query en load back in Excel.

@SolutionsByPVV 9 ай бұрын

Paul, link please file from this tutorial to download.

@thiyagarajan1989 6 ай бұрын

Can you Upload Sample Data- Excel Sheet ...Which will help many people....

@jonelatendido9836 5 ай бұрын

Please give the files, Or Just the code in a texf files, it would help a lot..

@edysaputratech3645 9 ай бұрын

How to scrape data from web with login

@CB27 9 ай бұрын

1st example, why not simply Data/Filter/Autofilter for zeros and then delete the filtered rows?

@Excelmacromastery 9 ай бұрын

It is slower than the method I used.

@CB27 9 ай бұрын

@@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.

@TravisFX 7 ай бұрын

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?!!

@Excelmacromastery 7 ай бұрын

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.

@ManojYadav-nt2rm 10 күн бұрын

it looks like you dont like the for loop in your code.

@DimaEarth 9 ай бұрын

Заметил русское название файла))

@tonynichols1638 9 ай бұрын

@ManojYadav-nt2rm 10 күн бұрын

this is not the great solution without toucing the data you shld make the code run faster

@TP-om8of 9 ай бұрын

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?)

@quench100 9 ай бұрын

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.

@Excelmacromastery 9 ай бұрын

The solution required keeping the formatting of the records and because sorting was faster.

@TP-om8of 9 ай бұрын

@@quench100 How about Power Query. It would be fine befire more. You opened the VBA editor.

@quench100 9 ай бұрын

@@TP-om8of I've never used Power Query, I'm using Excel 2013.

@mathijs9365 9 ай бұрын

Yep. Power Query is an ETL programm. Perfect for cleaning data.