Problems with slow code, can this be coded differently to speed it up.

**molesy** · 06-15-2014, 05:13 AM

Hi

I have a small project that i am developing and its coming along OK. The problem I have is the clunky code I think, it takes to long to populate, its not too bad on my i7 processor but when I move it to my tablet its not quick enough.

I will explain the purpose of the application.

I have a sheet that holds all the data called "MatsData" and a userform called "frmcontract"

There are two listboxes "Listbox1" & "Listbox2" The category name from cell "C" in "MatsData" populates into the "Listbox1" whichever category that is chosen in "Listbox1" is transferred to "Listbox2". When you have completed choosing the categories you hit the + button to create the document; this is where it slows up, I know the code looks complex and it does for me. I had this code written by a friend and he is unavailable to help, I cannot work it out due to my limitations I am relatively new to vba and only pick things up from you good guys, I would appreciate if someone could shed some light on this. I have attached the file.

Thanks

**Richard Buttrey** · 06-15-2014, 06:12 AM

Hi,

First things first. Are you sure that VBA is the most efficient way of doing this. I don't believe it is. Only resort to VBA when standard functionality won't give you what you want. And even if you do need VBA avoid loops wherever possible. This is often the case when loops are merely extracting information after evaluating each cell in a range. This can usually be avoided by using a Data Filter to identify all such cells in one hit.

Your macro is doing a lot of formatting and also a lot of looping. Formatting with VBA could probably be avoided by using conditional formatting and looping could probably be avoided by using filtering.

The essence of what you're doing is picking items from the MatsData sheet. Why not use standard excel functionality with say a validation drop down Yes/No cell, or mark each row you want to use. Then just use VBA to extract those items to your Materials and Workmanship sheet.

**molesy** · 06-15-2014, 07:01 AM

Hi Richard

Thanks for your reply; the simple answer is that i really do not know where to start with your suggestions. I know this might be cheeky but could you do a very small sample to show me what you mean.

thanks again