do vlookups in loops slow things down? IF so how to speed up

[miless2111s]

Background:
I have a complex worksheet that I am turning into code so that I can attempt to protect the calculations with VBA obscuring. This has resulted in a macro which loops around 1000 times and has a significant number of vlookups built in. It gathers information from the looks up, performs calculations as required and then outputs the results to the display sheet which has c. 1000 rows in at the moment however this will eventually grow to around 2000.

The vlookups are looking into 5 or more distinct tables of data:
Dates 2x2000 columns x rows
Rows 2x80
Data 10x 250
Colours 12 x possibly lots but usually less than 50
Definitions 10 x400I

Issue:
Running the code works a charm and does the job however it takes rather a long time to run (around a minute) and so far I've only dealt with 50% of the calculations. As I will be setting the macro to run when the workbook is closed and saved this delay will be uncomfortable and slows the whole process down.

Help needed:
Is there some way to either speed up the Vlookup process or a technique to pre-load all the information into the code and then the code uses something other than vlookup to fetch the required information?

Many thanks

Miles

 

[miless2111s]

Aladin

Now that I'm back at my PC I can reply more fully as I fear that my previous comments may not have help your understanding of the issue I face. I have, over many years, developed a worksheet solution (with a couple of little macros to automate the updating of vital pivot tables etc) which worked brilliantly for me and output the thousands of answers needed for my solution across a great many rows and columns. It is a good tool and not something that I have seen many people do. So much so that after many people telling me I should market it I decided to do just that and licence copies of the workbook. During the research for this I found that whilst password protecting sheets and even making them very hidden this turns out not to be a lot of use with all the Excel ******* solutions available on the internet etc. I am not especially interested in giving this away or seeing someone else capitalize on my hard work so some more digging found some techniques for obscuring code. Sadly options such as cloud based solutions where the calculations are stored on a hidden location are not possible as companies and clients don't usually like putting this sort of data into the cloud.
With this in mind I am now engaged in coding all non pivot table operations which were previously done in worksheet calculations so that the method and techniques can be largely hidden. Having coded up half of the operations required using vlookups this code takes 60 seconds to run which is too long when one of the selling points of the tool is that it saves time It is also too long to wait whilst a program just sits there apparently doing very little!
I have shared the test blocks of code where people have kindly suggested other ways of getting information or speeding up the code so that I can compare techniques without having to rewrite significant volumes of code so that people can see if I am making rookie mistakes.
Does this help explain the situation better?
Regards
Miles

 

[Aladin Akyurek]

Ok, thanks for the update.

 

[miless2111s]

Having done some more detailed testing of the full code pack and discovered very little difference between loops which only had one calculation and no lookups and loops where I had many sub loops and multiple vlookups I found very little difference in individual run times. As a last desperate measure I created a new test code to see how long a simple loop to add a value to 16 columns and 1030 rows of cells. This turns out to be a fairly significant amount of time (some 63 seconds compared to a run time of 85 seconds for the whole thing).

So new question - is there anyway to speed up the process of putting values into cells? I'll raise a new question so that other people can search more easily for the answer (here)

 

[miless2111s]

right, I put some modifications into the writing section of the code to check if the contents of a cell needed to change and only write if it did and it did indeed speed up somewhat when there were few changes to be made. However even when there were no changes to be made (i.e. run the code and run it again with no changes) it was taking 48 seconds to run so there is some opportunity to improve there so keep your brilliant ideas coming

 

[HackSlash]

If this is a product you intend to sell I would suggest porting your code to VB.NET so you can compile it. It will run faster, be easier to obfuscate, and it will look like a real software product.

 

[miless2111s]

If this is a product you intend to sell I would suggest porting your code to VB.NET so you can compile it. It will run faster, be easier to obfuscate, and it will look like a real software product.

HackSlash I don't think that I have the skills to do this yet but if this works in some small way I will be able to pay someone to do this. One thing that I wonder that VBA may give the product is a simpler route onto people's machines. Anything .exe etc needs to go through months of scrutiny by the IT department etc however a macro enabled workbook may not have to go through as much? Maybe??

Out of interest what does porting this take?

Miles

 

[miless2111s]

Update - on the other thread about outputting massive amounts of data Chihiro made a suggestion that yielded a huge performance increase:

FYI - Your slow speed may be do to other calculation/formula present in your workbook as well. I'd set "Application.Calculation = xlCalculationManual" at start of code and reset to "xlCalculationAutomatic" at end.

with the addition of an error handle to switch calculation and screen updating back on in the event of an error crashing the code.

ErrHandle:
    If Err.Number > 0 Then
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End If

This one change dropped the time taken from 80 seconds to 0.8 seconds for the full code run!

 

[HackSlash]

HackSlash I don't think that I have the skills to do this yet but if this works in some small way I will be able to pay someone to do this. One thing that I wonder that VBA may give the product is a simpler route onto people's machines. Anything .exe etc needs to go through months of scrutiny by the IT department etc however a macro enabled workbook may not have to go through as much? Maybe??

Out of interest what does porting this take?

Miles

On the contrary, I would never buy an excel macro. Especially if the author tried to hide the code from me. If I was buying a macro I would need to read every line of code before I could approve its use. A compiled program doesn't have this level of scrutiny. More importantly, you have to support every version of excel that your customers could be running. It might not work on every version. How will you test it? Do you own every version of excel from 2003-2016? There are too many things that can go wrong with this.

If you want to get in to software development you should download Visual Studio:

https://www.visualstudio.com/downloads/

Make a project and paste your code in there. You will have to rewrite the beginning part from the perspective of crafting an excel workbook from VB.NET. Once you can manipulate excel from VB.NET you will be in business because you fill find many of the commands are the same.

Here is an example so you can see the code. You should find that it looks familiar:

http://www.tutorialspoint.com/vb.net/vb.net_excel_sheet.htm