I Made this VBA Code 2,880,952% faster

Рет қаралды 24,257

Ай бұрын

Want to improve your Excel VBA skills fast? Go here: ▸courses.excelmacromastery.com/
Want to download the source code for this video? Go here: shorturl.at/ltxLZ
Subscribe to the channel here: bit.ly/36hpTCY
I Made this VBA Code 2,880,952% faster
A user recently sent me Excel VBA code that was taking 6 hours to run and asked if I could somehow improve the speed. In this video I show how I made this code run in mere milliseconds with a few changes. If you want to avoid having slow VBA code in the future then this video is a must watch.
💻🔧 #VBA #VBAFast #VBASpeed
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)

Пікірлер: 101

@Excelmacromastery Ай бұрын

Let me know in the comments below if you would like more videos like this👇

@mikheilchigogidze4224 Ай бұрын

This is great. Thanks a lot! Can you please do a video, on how would you schedule an excel macro? Macro to run without opening an excel.

@jastonkong1409 Ай бұрын

Your work is very inspiring. You keep me interested in learning VBA. Thank you so much for such selfless sharing! Also, never in a million year i would have thought that simple line of clearing contents could cause that much trouble! lol

@sweetdreams6697 Ай бұрын

I loved it

@luiss.l7904 Ай бұрын

There is still one thing to work on: combined = data(i,2) & data (i,3) e data(i,4) has a N level of complexity This concatenation operation is done N(loop size) times unecessarily. Use worksheet function to vectorially create a single array with the already concatenated values of all rows.

@markd.9538 Ай бұрын

This idea would have come in very handy for me about 4 years ago. I had to write complex (similar to this, but but non-trivial) scripts that ran across invoice support templates that checked and updated columns to the customer's very specific demands. The code had to look across about 120 columns of data, and the spreadsheets contained hundreds of thousands of rows. In the end it all worked but we had the same problem: the constant updates to the sheet during the process slowed it down to a crawl. Come due day, it would eat a huge chunk of the day for the person running and checking it. If I ever have to implement (or refactor) anything like this in the future, I'm going to be doing it your way! Thank you for the great advice here.

@HSkraekelig Ай бұрын

Paul, I've been using your web site for hints and tricks for years now. I probably would have stopped at the first fix, but the second is 200% more brilliant. Don't change what doesn't need changing! More please.

@Excelmacromastery Ай бұрын

Glad you liked it

@Info-God Ай бұрын

The most efficient combination. Dictionaries+Arrays.

@barrygeldham390 Ай бұрын

Very interesting Paul. Just a rather pedantic point, 2.8 million percent is not 2.8 million times faster, only a mere 28,000 times faster. Still quite impressive.

@RobertWoodman 25 күн бұрын

That was quite impressive, Paul. I use your KZfaq site often for ideas, tips, and tricks.

@eshaich Ай бұрын

Pro as usual. btw I believe you can do it even faster with the "evaluate" VBA function. no loops and it does the calculation for all the range ows at once.

@BrittKinsler Ай бұрын

Excellent video and looking forward to more! Thanks!

@hammeedabdo.82 Ай бұрын

Thank you Paul, we need more videos like this.

@Excelmacromastery Ай бұрын

Glad you like it

@janezklun Ай бұрын

Thank you Paula for such useful videos, yes more

@stevegarside5026 Ай бұрын

Fantastic coding and described in a way that people can understand. Thanks for sharing!

@Excelmacromastery Ай бұрын

You're welcome

@Tocotronic000000 Ай бұрын

Fantastic tutorial. We need more like this. Keep up the excelent Work. Greetings from germany

@Excelmacromastery Ай бұрын

Glad you like it

@Elesario Ай бұрын

An alternative solution would be to set up a temporary column with a function that can detect whether the item is the first of the group or not, and then use that column to filter the data and clear the visible cells in the first column. Then it's simple to remove the filter and the helper column. It would work best with the calculation mode set manual and triggering the calculation for only the point prior to filtering. In the example provided it's unlikely to be faster, but it might scale better (haven't tested). The reason I suggest this is because moving data between the Excel and VBA engines can be slow, so sometimes you get better performance directly from the Excel calculation system (which is multithreaded), and you're only pushing a few commands at it from VBA's side.

@user-xx5et3fj7b Ай бұрын

That was splendid indeed! Thank you very much!

@Excelmacromastery Ай бұрын

Thanks

@dantealighieri8031 Ай бұрын

You make excel coding very enjoyable to learn. i dont even know half of what you did but it was intriguing.

@Excelmacromastery Ай бұрын

Thanks. Glad you like it.

@Info-God Ай бұрын

Dictionaries are a just like that: an array organised as you look at a ... dictionary. Try to organise a set of data as a ... dictionary....unique items.

@RobGijrath Ай бұрын

That is very efficient. Please show us more videos like this.

@Excelmacromastery Ай бұрын

Thanks Rob

@benlinford_work Ай бұрын

Love it, Paul!

@Excelmacromastery Ай бұрын

Thanks Ben

@kapibara2440 Ай бұрын

Smart and clean. Great work Paul! Your videos are valuable 🎉

@Excelmacromastery Ай бұрын

Thanks

@Excelmacromastery Ай бұрын

Thanks

@nadermounir8228 Ай бұрын

Brilliant video Thank you very much for your effort and for showing us a real world example. I am currently enrolled in your course (just started)

@Excelmacromastery Ай бұрын

Thanks. Glad you like it.

@stevec1835 7 күн бұрын

Awsum, thanks for taking the time to make these videos. They are so helpfull...

@iincitr Ай бұрын

Amazing. Thank you.

@Excelmacromastery Ай бұрын

You're welcome

@sudheerallamuri7132 Ай бұрын

Thank you Sir.. I like your Vedios ❤❤

@brianminder2561 Ай бұрын

Excellent! I vote for more.

@Excelmacromastery Ай бұрын

Thanks Brian

@joeburkeson8946 Ай бұрын

Easier & faster what's not to like? Another fine tutorial with real world application, thanks.

@Excelmacromastery Ай бұрын

You're welcome

@rajuhubli2 Ай бұрын

Great video sir

@Excelmacromastery Ай бұрын

Thanks

@egozMaster Ай бұрын

you should remove the With statement as well

@dermotbalson Ай бұрын

My preference is never to overwrite any original data, both because making an error may lose it forever, and also because in business, work needs to be checked, and you can't do that without the original data.

@notaras1985 Ай бұрын

There's zero reason the data will be lost

@mylife1131 Ай бұрын

Harika video için teşekkür ederim Paul.

@Excelmacromastery Ай бұрын

You're welcome

@Bhuvan_C_Paranji Ай бұрын

Hi Paul, I have one issue from Power Bi data, Its taking long time to execute. please give me any idea to lessthan 1 minute to get the power bi data with filter options.

@sandeepkothari5000 Ай бұрын

SURE, WE LIKE YOUR VIDEOS immensely & wany more

@Excelmacromastery Ай бұрын

Thanks Sandeep

@sriranga188 Ай бұрын

Yes we need more videos

@Excelmacromastery Ай бұрын

Thanks

@daves4026 Ай бұрын

Bravo

@strannostrannovasrr Ай бұрын

Is it possible to dump only 1 column from a multi column variant array on a worksheet at once? I mean without using a for-next-loop?

@Excelmacromastery Ай бұрын

You can only do it with the first column. Resize the output range to the size of this column. Range(rowCount,1) = arr

@sasifexcelsolutions4509 Ай бұрын

Hi, thanks for this, question, how would I force only xlsb when the user want to save as a file, I want the dialog picker or with 365 only show xlsb to show, this was possible before 365 but don't know how to do it in 365

@kofigyebiadusei2205 Ай бұрын

When will make an introductory video for us who are beginners. Thank you

@Excelmacromastery Ай бұрын

Good suggestion.

@stevetatterton Ай бұрын

Do you need to use LBound of the arrays when it will be 1 unless you specify otherwise, which you don't do here as it's not needed? Or is iterating from LBound to UBound seen as more elegant? I would find 1 easier to use as the For statement is then less cumbersome.

@Excelmacromastery Ай бұрын

Using lbound is a defensive measure in case a zero based array used. But you'll get away using 1 in most cases.

@JithinDevan Ай бұрын

Thank you Paul - is there any way to speed up below requirement? I need to list all filenames from a folder and their last modified dates into a table. The folder has over 20,000 files and it’s taking ~5 mins to run the macro.

@sinus2220 Ай бұрын

You can call Shell and run DIR command from there.

@Excelmacromastery Ай бұрын

I think that would be fast. You can easily send to a file

@Back1Ply Ай бұрын

Power query has from folder connector that lists files and last modified date

@JithinDevan Ай бұрын

Thank you all for suggestions

@PIESvcs Ай бұрын

When I see this, I'm reminded that the Data, Advanced Filter allows Unique data items to be filtered in place or copied to another location (using header rows, I think). I am wondering if this may be faster using the native Excel operability. One question I had (and benefit to using Paul's method) is if we want to know the number of occurrences for each Unique entry, an additional array may be useful, for example, if all of those monitors were to be assigned their serial numbers, assigned to users, maintained and depreciated. I needed to do this for network hardware in 1988 using QuickBASIC before VBA.

@richardgreves6161 Ай бұрын

tried to download the code and I get Web server is returning an unknown error

@Info-God Ай бұрын

Type it. This way you learn.

@pupazzetti2680 Ай бұрын

Yet you could make it even faster by using `Value2` (as you have previously taught in other videos). Just for curiosity, how much faster?

@Excelmacromastery Ай бұрын

It would only make a difference if you had a large number of writes.

@rahul7rock Ай бұрын

Dictionary and Array is a deadly combination as always

@Excelmacromastery Ай бұрын

Absolutely

@wizardofaus8473 Ай бұрын

More videos like this please. Also, more videos not like this please. Basically, I would like more videos, as long as they are you teaching VBA. Thank you Paul

@Excelmacromastery Ай бұрын

You're welcome.

@nivrin13 Ай бұрын

How do you calculate the speed of the macro?

@Excelmacromastery Ай бұрын

Using the Windows api timer

@Info-God Ай бұрын

Define TStart and TEnd, then the difference is the TLapse

@YO-BIZZY Ай бұрын

MATT PARKER OPTIMISES CODE

@mback3713 Ай бұрын

Try using Collection instead of Dictionary. ;)

@Excelmacromastery Ай бұрын

Why? They won't be faster.

@nivrin13 Ай бұрын

Collection allows double entries, dictionary doesn't, so it's already filtering the data

@mback3713 Ай бұрын

@nivrin13 um.... collection will error if you try to retrieve an element from it that is not in the collection. you use `on error resume next` then in your loop check the collection `_ = col[key]`, then switch to do something `if err.number then` and don't forget to `err.clear` after failing to retrieve a value. it works... a little hokey, but is faster than `if key in dict`

@mback3713 Ай бұрын

@Excelmacromastery collection is "native" and doesn't require an import - and is more lightweight and faster than dictionary. It is a little "hokey" as you need to use error handling (the native "Err" object) to detect if a key/value pair exists in the collection, but... try it... sounds like you may be a little surprised at the result.

@nivrin13 Ай бұрын

@@mback3713 Alright. I actually wanted to use the collection method in the past. This is a better approach. Will save it. Cheers!

@saketmishra1470 26 күн бұрын

Paul, please help me to make my code as faster you have shown here

@Hughahugha361 Ай бұрын

The code is nearly as fast as you deleting comments...

@Excelmacromastery Ай бұрын

I haven't deleted any comments😀

@user-gj3kz7cm3x Ай бұрын

How to improve it further: use literally anything but VBA.

@junkertom7766 Ай бұрын

Tell me you are a clueless VBA hater without telling that you are a clueless VBA hater.

@user-gj3kz7cm3x Ай бұрын

@@junkertom7766 it is literally ranked the worst programming language. Personally I don’t think that is fair because it isn’t a real programming language.

@markd.9538 Ай бұрын

@@user-gj3kz7cm3x Ranked... by who? Its a tool for a purpose. Yeah there's better languages out there; yeah its ugly; yeah it has poor object typing, and weak object security in parts... and yeah its not "fun" in the programming sense - but it has exceptionally good fit-for-purpose libraries when you have to crunch spreadsheets and data for insane business bosses who need to work in Office formats and need their little/big problem solved ASAP. Not everyone can be a L33t coder. Sometimes, spreadsheets pay the bills. VBA is ubiquitous - and exceptionally useful - in MANY environments.

@laurentbosc2896 Ай бұрын

@@user-gj3kz7cm3x ok and what else ? in the real World Vba is probably one of the most used. So this is a very simple view to tell that. And it should ont be taken alone but as one element of the Excel toolbox. With Power Query and power pivot, it gives powerful possibilities very easily, without any extra installation. This is why it is very used and so popular.

@laurentbosc2896 Ай бұрын

And it is a programming language, literally speaking… not comparable to others ok, but it is powerful for other reasons as explained