which option faster and why?

thanks for the info.

But, why does having the 1.3Meg file open change how fast the VBA program runs. Its almost instantaneous when the 1.3Meg file is closed.

Hi,

In option1, try adding these two lines at the beginning of your vba code and see if it makes the code run faster:

Application.ScreenUpdating=False
Application.EnableEvents = False

Thanks and Best Regards,
Abi

ok.

I have two files. One is an excel file that is 1.3Meg in size. It has many sheets and macros in it.

The other file is basically a subset of the first file. I have copied the code this code

Please Login or Register  to view this content.

into the file and i run this under two circumstances.


1) The 1.3Meg file is not open. Run time is instantaneous....miliseconds.
2) With the 1.3Meg file open. Run time is several seconds....like 25 secs to run...not kidding on the time.


Adding the code below did nothing to change the time....which makes sense the screen does not change when its running anyway.

Application.ScreenUpdating=False
Application.EnableEvents = False


very strange..................any ideas?

screen updating on / off stops the flickering

Its considered quicker as a result

welchs101,

I would say its because of the number of operations that the macro is writing to the sheet. In option 1, each cell is being written to individually. In option 2, a column is inserted, the formula is written, then copied down (the copy down is only one operation), then that range is copy/pastespecial (as values), then the column deleted. So only about 5 or 6 operations compared to an operation in every single cell.

To speed up option one, you could make it so that the names are updated within the macro and all put down at once instead of one at a time, resulting in only one operation to the sheet. The following code will do that:

Please Login or Register  to view this content.

That should speed up the macro a bit

Hope that helps,
~tigeravatar

thanks all for the info.

tigeravatar, i will try your new code and let you know what i find out.

I do think its strange that its instantenous without the 1.3meg file open and takes 30sec or more with the file open. Note: This 1.3meg file has nothing to do with the actual macro....its just open and sitting there.

Thanks again to all who responded.

What kind of formulas do you have in the 1.3 meg file?

the ones i can think of are

1) countifs
2) sumifs
3) match and index
4) lots of vlookups

Why did you ask this question?

tigeravatar,

I just ran the code you posted in #8 and that ran "instantaneously"............having the 1.3meg file open or closed had no impact. This is what i am looking for.

So exactly what was i doing different in the other code?

So i think your reading all the values into an array........is this right?

Then your putting all the values from the array into the sheet..........is this right?


Also, i dont understand this

Please Login or Register  to view this content.

i think this is somehow putting the array into the sheet but i dont know how. can you explain briefly.

THANKS AGAIN!

Originally Posted by welchs101

So i think your reading all the values into an array........is this right?

Yes, this is correct. All of the items in column A are loaded into the array named arrProducts. The code to get the trimmed, lefthand portion of the item is then operated for each item within the array without writing it to the sheet yet. Internal operations like this are much faster than external operations (those that write to the sheet).

Originally Posted by welchs101

Then your putting all the values from the array into the sheet..........is this right?

Yes, at the end of the code (explained in the next point) it ouputs the entire array, all at once, to the sheet so that only a single external operation is performed.

Originally Posted by welchs101

Also, i dont understand this

Please Login or Register  to view this content.

i think this is somehow putting the array into the sheet but i dont know how. can you explain briefly.

The items started in A2 and need to be returned to A2 (and down) because the desired outcome of the macro is to replace existing data with the cleaned data. In order to do that, the code "selects" A2 (its not really a selection, it just uses that as a starting point), and then resizes the range its writing to to be a number of rows equal to the number of items in the array. Now that the range to output to is the correct size, it dumps the array data into that range.

Hope that helps,
~tigeravatar

thanks!

What would happen if you did not have the "resize" operation in the code. What if you had just

Please Login or Register  to view this content.

Why do you need the resize?

thanks again

welchs101,

Best way to learn is from first-hand experience, so you should give that suggestion a try and see what the results are.

However, what will happen is that only A2 will receive the first value of the array. And then, with no more cells to populate, nothing else will happen. That's why you need the range.resize operation.

~tigeravatar

thanks for your help!