Preventing VLOOKUPS From Executing on Data Load

[dflak]

It seems that using code such as

Please Login or Register  to view this content.

Doesn’t turn off ALL calculations.

I have an application where I read in two tables from an external file, each into its own tab. If that’s all I did, the tables load in seconds.

I now add another table in a third tab. It refreshes data from a data source. in a matter of seconds.

Now I add two columns to the third tab. One does a VLOOKUP against the first table and the other does a VLOOKUP against the second table. In the next column I take the mathematical difference between these two columns and finally one more column to see if the result is zero.

As I said, to load the data without the formulas takes about 5 seconds. With the formulas loading the table takes about 13 minutes. I suspect that this is because the VLOOKUPS are being executed as the data are being loaded even though calculations are set to manual.

So I rewrote the code to remove the columns with the formulas, import the data and then add the columns back in with the formulas. This program takes about 5 seconds for the data to load and 2 minutes (plus or minus about 5 seconds) for the formulas to get calculated.

OK, so I have something that works, but do I have to go through all this trouble? Is there some other means that I am overlooking that will hold off execution of the VLOOKUPS until the data load is complete?

The data are proprietary so I can't post the program.

Here is the code howerver.

Please Login or Register  to view this content.

[Sintek]

Hi dflak

Two tables from an external file, each into its own tab

Why copy the table...why not just copy the sheet...with all formula in tact....Or does your setup not allow for this...

I suppose not seeing the file it is not possible to suggest an alternative...

[dflak]

The source data files: I do a snapshot each day of a download from our database and I save that snapshot to a date-stamped tab. So today's download went to 205019 and yesterday's download went to 200518.

I tell the program in question to open up this spreadsheet and copy in those two tabs. I need to find the difference in a certain metric for parts between them. The snapshots have no formulas.

The third tab (the one with the formulas) is read from a database - it is a list of all our parts. So I look up the metric for each part for yesterday's download and the day before and take the difference. Most parts have zero difference so I filter them out and reduce the number of records from about 18,000 to about 400 for further analysis. I can handle the 400 .

[romperstomper]

If you assign a formula to a cell using the Formula property, it will calculate, regardless of what you set the Calculation property to. It only makes a difference if you then use a Fill operation to populate the rest of the cells, since Excel will simply put the result of the initial calculation into every cell. Your problem here is that the table is automatically assigning the formula to each row of the table in turn, which requires it to calculate.

[dflak]

Most of what I was able to find elsewhere on the web is some variant of what I wound up doing. It's a bit of a PITA but it's not like I have to do it EVERY day. Now that I got this one programmed, it should last for a long time.