Make Your VBA Code Run 1000 Times Faster (Part 2)

[@Excelmacromastery]

I hope you enjoy this video. Let me know what you think of these techniques in the comments. You can download the code from the description.

[@gunasekar4098]

Hi Paul, kindly post any easy way to understand about array method and U bound vs L bound code video.

[@Excelmacromastery]

I have a video on arrays here: kzbin.info/www/bejne/gKukfaaahtudqJo

[@danteencinas7020]

To quote a famous Star Wars character, "Impressive... MOST impressive!!!" Thank you for sharing your knowledge Paul!

[@ryanquinn1257]

These are so valuable. I wrote code that worked well on a subset of a hundred orders but then when kicked up to 10,000 it took an hour. New to VBA but not new to programming (still pretty novice to advanced/production uses) so this helps understand what VBA does faster.

[@danielbarton1694]

Top stuff Paul as usual. I'm continuing to learn an incredible amount from your videos and from your VBA course which I can highly recommend to all your viewers. Keep up the great work.

[@Excelmacromastery]

Thanks very much Daniel.

[@ganeshs1360]

Combining Part-1 and Part-2, you have actually made your code run 2000 times faster. Awesome work once again.

[@donaldli4755]

Ganesh S not 10*^6 times faster? Sarcasm tho right?

[@danteencinas7020]

Fantastic, not just the content, but the manner in which the content was presented. Thanks again Paul.

[@Excelmacromastery]

Thanks Dante

[@stuartdiprose8382]

Always interesting and well explained. And usually a revelation of two thrown in as well.

[@frikduplessis3869]

Again a fantastic video Paul, I love the bottleneck technique with the timer brilliant 👍👍 and thank you for sharing and helping

[@Excelmacromastery]

Thanks Frik.

[@nicor1501]

Fantastic Paul, thanks a lot for this jewel!! I applied your solution on one of my template and the code runs in less than 2 seconds to process 600.000 rows ! Thank you again

[@Excelmacromastery]

Great to hear Nico.

[@nicor1501]

@@Excelmacromastery Hi Paul, me again. I've just found out that there seems to be an issue with the "Transpose" function in the sub "WriteDictionaryToWorksheet". I've extended the scope of data to 100.000 rows with "CreateData 100000" and ran the "Main" sub. The "Output" sheet displays "N/A" from row 34.465 onward. Is the 'Transpose' function limited with the memory usage ?

[@AndreyDelay]

Thank you for your hard working )) VBA is my first language and from time to time I have to use my skills at work , but I didn't spend enough time to learn it in beginning of my way. Your lessons are the best on you KZbin and help me to improve my VBA, It would be very nice if you were record at least one long video with big close project if it is possible)) thanks from Russia once again))

[@Excelmacromastery]

Thanks. glad you liked it.

[@akrishnan47]

Dim myTimer As New clsTimer giving error user define is not defined , can u help in this

[@jmathew6988]

Yes. This tutorial was very helpful. I have watched Part 1, but this one is a Gem as it discussed the lesser know techniques. Moreover, collections, dictionaries and arrays are the most commonly employed objects when coding in VBA for large data sets. Thank you Paul for this teaching. This is exactly what I was searching for and it practically helped my cause. Thanks again!

[@Excelmacromastery]

You're welcome J

[@BenLinfordUK]

This isn't quite so glamorous but this happens to be some industrial strength VBA. Thanks Paul. Invaluable concepts packed into 16 minutes of video.

[@Excelmacromastery]

You're welcome Ben.

[@murilobruza]

I've found your website and KZbin channel recently and I'm really impressed with your practical examples and the techniques presented. The method for measuring and analyzing which part of the code to optimize first is a really great tool. Thank you very much for your videos!

[@benjamingmsousa]

Very nice. What is the addin you are using? At minute 11:08 there are more features when you right click.

[@joaocustodio2094]

Very, very useful tips. Thanks Paul.

[@todorowael]

Great tutorial! Thank you.

[@jurijdmitrenko5753]

Hello thank you for good tips, your videos very help me. But I have question. What is the best way how to write data from Dictionary to Worksheet, if in ITEMS I don't have basic data. So I cannot use directly pasting how you used in your video. I have there variable as Class Modul (in this Class modul I have created next 6 user variables). Thank you in advance :-)

[@averagebodybuilder]

I agree that early binding would increase the speed for the dictionary object. But what I have found is that late binding allows me to distribute my excel apps with less requests for support

[@akulanarayana5284]

Sir, I am from India most useful for every one and your videos are different/ unique from other videos and voice of your teaching is extremely good. Thank you to so much. I support and share your videos.

[@Excelmacromastery]

Thanks Akula, I appreciate it.

Hi and thanks for this excellent video. In my 64 bit system vba says the timer code should be updated and I have no clue? Do you have any suggestions.

[@TheJoeFletch]

Another great video! I learned so much! In the sub "WriteDictionaryToWorksheet", when writing back to the sheet, if the data is filtered, then really odd things happen; the first row of the data in the "dict" variable is pasted into the visible rows. Is it possible to write data back to a sheet that is filtered?

[@christianclarke1270]

The best most underrated content on KZbin thank you so much for the upload!

[@Excelmacromastery]

Glad you enjoy it!

[@bsk8643]

Can you please help to do video on how to generate reports from ALM using VBA code pls.. My code is taking too long to run and to update the cells in the excel .. Please help .. your videos are helping me alot ..

[@wayneedmondson1065]

Hi Paul.. nice one.. more great advice and tips from the master! Thanks for sharing. Thumbs up!!

[@Excelmacromastery]

Glad you enjoyed it Wayne. Thanks for your ongoing support.

[@EricHartwigExcelConsulting]

Really great video Paul! I plan to apply these techniques in further refining my code writing skills. Thank you so much!

[@Excelmacromastery]

Thanks Eric

[@benponderin]

Your work is an inspiration to me. Well done

[@TechTinkTronics]

Wow, thank you for this! VBA is awesome in the way that there are multiple ways to execute code more efficiently. Now I can time my code execution with precision.

[@houstonvanhoy7767]

Very nice video - again - Sir Paul. I do have a question. I noticed that you used Control-F in the VBA editor to find text, then manually replaced text. I am accustomed to using Control-H in Excel to perform find and replace. Does that also work in the VBA editor? I searched this, and found conflicting answers. Thanks in advance.

[@planxlsm]

1:37 Time code

[@RogerFPrida]

Hi Paul! I wanna thank you for all the help your videos gives us. Your book looks awsome and I'm pretty sure it would be such an amazing help in the project I'm currently on. Maybe some day in the future I'll be able to purchase it. Mean while I'll learn a lot with your videos.

[@chetanj2367]

Already we saw 100 times faster and now 1000 times. Impressive .

[@Excelmacromastery]

Glad you like it.

[@srinivasraogampala4420]

Actually I got inspired by Part1 video. That is awesome and great video.

[@Excelmacromastery]

You're welcome.

[@akshayc7152]

Excellent Paul. I am totally a fan..thank u.. Is there a way we can optimize slicers attached toa normal pivot and also a way to calculate the time for the pivot to update after selection of the slicers?

[@udaymanna5398]

Hey thanks for yet another informative video ,I have one requirement for that I need to read data from text file,extract the required contents only and output to CSV..now iam storing the extracted contents in excel and outputting to csv from excel.So can you pls suggest any efficient ways like storing the data array or collection .. I need to have dynamic rows and columns

[@abdullahboat466]

Welcome back ...MacroTimer so superb!!!

[@Excelmacromastery]

More to come!

[@adamonjourney3726]

Hello Paul, thank you a lot for your efforts... could you please make the download link work again? Maybe it´s broken... Thanks...

[@adamonjourney3726]

.. and where to get the clsTimer code, please?

[@albertbatfinder5240]

There is an anecdote from the early days of Microsoft where Bill Gates was annoyed when his programmers (who had come from other languages) complained about the speed of Altair Basic, for which he and Allen had written the interpreter. He ran in-house classes to show the programmers how the innards worked, and how such knowledge could drastically speed up their code. That’s exactly what we’re seeing here! Great work, sir! (Though sadly there won’t be billions of bucks in it for you😜)

[@Excelmacromastery]

Very interesting to read Albert. I hadn't heard that before.

[@yashchawla8360]

I'm failing to write dictionary same way while using class modules. Can we use this with class modules to

[@JanBolhuis]

As always, your videos are great, very interesting and educational. Thank you. Still a lot of study to go.

[@Excelmacromastery]

Thanks Jan.

[@muralikannanramamoorthy364]

Hello Paul, can you pls upload a video of macro which fastly deals with multiple workbooks and doing data manipulations.

[@MrMallesh1]

Again Awsomeeeeeee video , always your video's comes with Something New in it. Thanks for your effort !

[@Excelmacromastery]

You're welcome😀

[@allenscroft]

Thank you. Where can I find the macro timer? Tried googling but unsuccessful.

[@Excelmacromastery]

MicroTimer

[@adamwstbrook]

I absolutely loved Part 2 and Part 1! I have an application that I first created a few years ago and it's expanded to include more features over the years. I've learned a ton of alternative methods from you that can greatly increase its efficiency, particularly early binding (I'll just have other users of this application set up their references in advance) and using arrays without loops. One other thing I learned from another video of yours (was it Part 1?) is instead of using the clipboard to set the range to value2 as a variable. My app currently breaks when I forget and copy something just as the app needs to paste. The way app is written now it's pretty complex, thus daunting to make more efficient while ensuring I didn't break anything, but I'm definitely eager to see the difference once I make the time to do it. I'll definitely be applying the various lessons you've taught to speed it up!

[@Excelmacromastery]

Thanks for the feedback Adam. It's great the hear about your practical use of the material.

[@gunasekar4098]

Hi Paul again wonderful video. I asked one week back, kindly post loop condition code like do loop or for loop or any others method similar way from one excel to other excel or within same excel.

[@Excelmacromastery]

Glad you enjoyed the video - I don't understand your suggestion

[@gunasekar4098]

@@Excelmacromastery please post excel vba loop video, For next loop and do while loop.. something..

[@sktneer]

Hi Paul, Did you try writing the dictionary keys and items to the sheet using the Transpose approach when there are more than 65537 unique items in the dictionary?

[@mike_case]

Great video Paul. Thank you very much :) Good job.

[@Excelmacromastery]

Glad you liked it Michal!

[@amarnadhg6896]

Great video, thanks

[@Excelmacromastery]

Glad you liked it!

[@fnscooter]

I'm wondering about the whole worksheet to array to collection to dictionary to worksheet. Was that just for demonstration purposes? I would think that in a real application, you would eliminate some of those steps.

[@rolfjansson9136]

As usally an excellent video. If I have more than one Item in the dictionary, can I still write it out with the transpose? Or should I use a "For each" loop, or put it in an array and use resize by that size?

[@Excelmacromastery]

This method just works where the dictionary item is a simple data type.

[@MrMallesh1]

Hi Paul, I have added all rows to collection , from collection to dictionary and dictionary to worksheet, its not working. Can you whats wrong in my code. Thanks

[@thearchibaldtuttle]

Another mastery video!

[@Excelmacromastery]

Thanks Archibald. Glad you enjoyed it!

[@robin2701]

Great content! I’m putting the vba handbook course to good use. I’m currently building an application that populates SAP fields.

[@Excelmacromastery]

Thanks for the feedback Robin. Glad you are finding the course so useful.

[@Victor-ol1lo]

This is really a good one.... Thanks for sharing and Thumbs Up!!!

[@Excelmacromastery]

Thanks Victor.

[@igesantos9703]

Hi Paul, thank you much for this new technique. One thing I noticed in my code, it bug when I passing a dictionary into a range with blank key or item.

[@Excelmacromastery]

Thanks for sharing!

[@PernambucoMeuPaisOficial]

Faça seu código VBA rodar 1000 vezes mais rápido (Parte 2)

[@Llb890]

Powerful knowledge

[@haragorn88]

More awesome tricks, thanks for Sharing Paul. Who knew you could write out the dictionary like an array. I assume this only works when you have a unique value in the item, not an array or class object. Does the same trick work for collections too? For Each instead of For i great tip too. Cheers.

[@Excelmacromastery]

Thanks Craig. It doesn't work for Collections but it does work with the ArrrayList - see bit.ly/3eF0hUl

[@serdip]

Mind blown AGAIN!!! Thanks so much for these fantastic tips to dramatically improve VBA execution times. Just a couple of quick questions, if I may: 1.) Since WorksheetFunction.Transpose() will bomb if fed an array greater than 64K, how would such a case be handled? In my own development projects I rolled my own Transpose function. 2.) When populating a Dictionary, I use objDictionary.Item(vntKey) = vntItem as opposed to objDictionary.Add Key:=vntKey, Item:=vntItem Do you know if one method is more efficient than the other? Thank you!

[@Excelmacromastery]

Glad you liked it. I haven't tested these Dictionary Add methods for speed. It would be interesting to compare them.

[@Lutfitince]

Great content!

[@Excelmacromastery]

Glad you like it.

[@ilyastrojnov7627]

Thx for video! But in old version excel function transpose can't work with 6+ cells, now it fixed? And i think, what append dict keys and items in new arr and then paste this arr in sheet maybe little faster. Sorry for my bad eng)

[@Excelmacromastery]

Yes, for more than 64k we would need to create our own transpose code.

[@victor_wang_1]

Hi, isn't worksheetfunction.transpose a bad practice if we're expecting more than 2^16 rows? Perhaps the dictionary keys/items should be manually added to an array that doesn't need transposing?

[@Excelmacromastery]

Yes. An array is probably the best way.

[@antonshc]

What tools are you using? I found only "MZ-Tools"

[@Excelmacromastery]

That's the only one I use.

[@johnwayne8059]

As we all know you, again a great video! Thanks for your tips, simply great!😉🤟

[@Excelmacromastery]

You're welcome John.

[@Krishna-wj5bn]

Hi Paul, I downloaded the workbook from the description and when i run the vba with data size of 199999(which is the raw data) i get #N/A values starting from row 3392 till the end which is 199999 in both the columns. can you also help in getting email column as well in output ? also can we read from array to dictionary skipping the collection part ? Thanks !

[@tsaodon1786]

Great video, I am curious to have seen that you've used microtime as compared to the timer you used in one of your previous videos, so why you changed to use mircotime? is it because of accuracy or something else ?

[@Excelmacromastery]

I explain in the video. Microtimer can time in milliseconds so we need less data to see the differences in speed.

[@BenjaminHouot]

Awesome, as usual

[@Excelmacromastery]

Thanks Benjamin.

[@aliibrahem6313]

Great, thank you.

[@Excelmacromastery]

You are welcome!

[@Phsoco]

What are your thoughts on Office.js as a potential replacement for VBA in the future?

[@Excelmacromastery]

See my last video which is on Office Scripts: kzbin.info/www/bejne/pZnKqHqDoaaMbq8

[@karurkannan23]

I generate random numbers -1 to 1 in 10 cells in the row, in which the sum of the random number is always equal to +values need Please help

[@ricos1497]

Would: With shtoutput .cells etc . cells etc End with Work faster in your write dictionary example? I've found speed improvements using with over specifying the sheet name for some reason.

[@Excelmacromastery]

With is faster than not using it. However, what slows VBA is multiple reads or writes to the worksheet.

[@grahamparker7729]

Welcome back...

[@Excelmacromastery]

😄

[@KcKc-bh6lu]

What would be faster between using Range and Cells?

[@dd1965]

Try it yourself

[@jockwalker24]

Hi Paul, when I started watching I though this was the answer to my slow running vba/excel sheet hybrid. But I don't think I could make it work. I have user forms that someone would use to capture data (2 weights of products every 10 minutes). When the person has keyed these in and pressed complete on the form it drops these values onto an excel sheet, with the batch number in column A, then weights in B and C. Next time they capture weights they are dropped in on the next line. The problem comes when I try to retrieve the the data. I use another sheet (on the same workbook) and use the index function. I enter the batch code in cell A1 then cells below have the formula in to look up the batch code enter the 2 weights. Then the next line down has the same code but looking down to the next line that the batch code is shown. Any ideas? It all works but takes a long time when I set calculate to on. Its all a bit hard to discribe but I think if there was a way for vba to find the data rather than a formula within a cells (long formula in over 200 different cells).

[@mbayembengue6902]

Thanks

[@realhuahin]

Is the excel vba handbook index zero?

[@Excelmacromastery]

??? I'm not clear on your question Daniel.

[@realhuahin]

Excel Macro Mastery hi Paul, sorry it’s a joke. there are some books written on programming that intentionally begin at page zero - like an array

[@byteseq]

8:23 bad practice to set i = 1. It does not matter here as i is not used for anything but the loop but i=0 would have been better

[@Excelmacromastery]

"bad practice to assume i = 1","It does not matter here" - which is it?😀

[@byteseq]

@@Excelmacromastery I have no idea how my "set" became "assume". My point is i should follow the number of items found in the loop and not be set to 1 before the loop 🤪 It does not matter much in this case since i is not used after the loop.

[@markarmon2883]

Cool new video.

[@Excelmacromastery]

Thanks Mark

[@lietus0]

I understand logic of using ByRef, however when I ran my code I got same result as with and without it. No time savings observed.

[@Excelmacromastery]

The speed depends on a number of factors e.g how much data you are passing.

[@nicadi2005]

*But what is the original code trying to accomplish?!*

[@Excelmacromastery]

Why is that relevant?

[@nicadi2005]

@@Excelmacromastery "Why is that relevant?" - Because the initial solution might not have been sufficiently thought through, to begin with. What is the point of optimizing a solution that, in itself, might NOT reflect the most efficient way of achieving the desired goals?! (If there was no actual point to the original code - other than to serve as an example for the application of the various optimization techniques - then fair dues; ignore the above. ;-)

[@adamwstbrook]

​@@nicadi2005 I agree that it's not relevant. Whatever the original code is trying to accomplish, if you're using late binding, loops, etc. in your own application, you can speed up your code using the techniques taught in this video. The key is to focus on how you can also use these techniques in your own macros, not his.

[@nicadi2005]

@@adamwstbrook "I agree that it's not relevant." - As I already stated, I can understand the irrelevancy in this case, if the initial solution was simply set up to serve as an example background to the techniques presented in this video. However, generally speaking, I find that the best approach to optimization problems is to first look at the overall solution adopted and make sure there are no glaring redundancies in that. For example, a solution might resort to first translating all data to a different structure, apply the changes to that, then translate it all back; the questions to ask would be: do we actually need to do the forward-and-back translations, or could we just find a way to apply the changes to the current format as it is? If that's not possible, then could we only do the translation on the data subset that is meant to be changed, rather than the whole lot? etc. etc.

[@Sancarn]

Good video, is this an example sent in by someone on here? I've seen the equivalent of that write dictionary example before. It makes me despair how poor many VBA programmers are, so I'm glad there's someone like yourself going through this more advanced stuff :) I would always advise strongly against early binding. Fully understand that it increases speed but it simply makes your code less portable, which is a big thing... I'd always suggest using one of the dictionary libraries created by the community. E.G. www.vbforums.com/showthread.php?788247-VB6-Hash-table Not only is it faster than the dictionary class provided by the scripting runtime (as it runs on assembly code) but it's also 100% portable. Much better than early binding! :)

[@Excelmacromastery]

I agree with what you say but there are always exceptions. For some people who are using the code themselves or with one or two others - they want speed but don't care about portability. I always use Late Binding when distributing code as early binding always leads to issues on different computers.