VBA - Full Speed Code Problems

[Neutralizer]

Hi All,

I have a reasonably long piece of code that conducts a lot of operations particularly on ranges.
In short, it loops through all worksheets in a workbook and runs a lot of evaluate("index(functions))") on the .areas of a range object. This range object is made up of .specialcells that are constant text values. It's purpose is to perform operations on the text within each of these .areas and to store details of this in a (many rows) x (5 columns) array, which is then dumped into a new worksheet - or else into a specified worksheet if there is one that matches the sheet name I have given it. As far as I can tell, wherever possible I have used fully qualified range references.

The problem I have now is that this code works fine on small sheets, or if I run the code for just one larger worksheet, or, if I put in stops and run it in sections - thus slowly loop through all the worksheets in a workbook.
But if I just let the beast have at it, it starts dumping the change details array all over the shop in the other worksheets, erasing values from cells all over the place etc.

I'm assuming this is something to do with execution rate, so how can I make sure an operation (for instance dumping all the change details to a sheet has finished), before it moves on to the next thing (for instance getting the next worksheet)?

I have played with doevents (based on the limited info in the help file) but it doesn't seem to be helping....

Any help would be much appreciated.


Edit:
One thought I had was that I can restructure away from a sub (which writes the change details to the sheet each time it is called for each ws in workbook) and make it a function that returns the array information, which is stored up in a variable in the calling sub and then just dumped once at the end...

Edit 2:
Another thought, at the moment it does a lot of:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Is one of those preferable over the other? (they look about the same to me...)

[Neutralizer]

No one has any ideas what types of activities might be causing the issues when running code at unrestricted speed as opposed to manually processing it line by line, or code section by code section?

Conceptually, is there anyway that I can ensure that the activites related to a segment of code have completed, prior to allowing the code to move on?
(That is within VBA itself, rather than being able to check output ranges or the like...)

[watersev]

hi Neutralizer,

basing on the story above the sheets/ranges/celles references are incorrect. The code can not jump to the next code line until the current one is completed.
For each ... Next construction works a bit faster.

[Neutralizer]

Hi,

If that were the case though, it wouldn't work on the small testing sheets that I use, but it works fine.

It's weird, it works fine at full speed on small sheets, but as soon as you move to a realistic example of what I need to work on (5000 rows out to CK columns for instance), it starts just blanking out cells and causing all sorts of problems...

[watersev]

can you post the file and code in question?

[Neutralizer]

not the file unfortunately, it's proprietary information.
I'll clean up the code over the weekend and put it up (it's painful to work through at the moment, I've just got used to it); hopefully someone on here will have an idea as to why it's going wrong.

[Neutralizer]

I just had a thought:
Is there some sort of inherent limit on the size of a VBA range object? (in particular, a limit on the number of discontinuous addresses that can be stored in a single range object?)
Similarly, is there any limit on the number of operations that can be carried out within a 'For Each Next' structure?

It's worth mentioning, the code runs all the way through to completion without error according to VBA, it's not getting out of range errors or anything that it's telling me about...

[watersev]

it would be helpful to see the full code at least

[Neutralizer]

it would be helpful to see the full code at least

Yep, understandable, i'll just make it somewhat more readable for you. It sucks badly in that department at the moment.

[Neutralizer]

Hmmm, I know this was meant to have been fixed in Excel 2007 (I'm using Excel 2010 64 bit), but this is exactly the issue I'm seeing:
http://www.standards.com/OhMyWord/VB...ollection.html

More likely than a bug though is that I've stuffed up somewhere, I must be missing an index argument or something, I'll keep checking for that.

I moved from using .cells approach because I thought using .areas (combined with evaluate and index) would be faster, but maybe that was a bad call
I have some of the old .cells code, I'll try implementing that for some of the functions and see if it eliminates the problem.

If it persists, well, I'll be back with the code on here!

[MarvinP]

Hi,

You are describing a problem with timing when you say things go everywhere. I think better placed DoEvents that allow things to fully open or longer operations to complete before more code is executed is needed.

I'd also be looking for the correct range references with a period before the .cell or .range but it seems you have that correct.....

DoEvents is my best guess.....

[Neutralizer]

Thanks for the help gents, finally found the problem.

I repeatedly create a named range variable, which holds a range address as a string. The variable is then used in a bunch of evaluate() expressions.
For one of the lines where i was assigning the range objects address to the named range variable, I hadn't used External:=True, so this particular one was acting relative to which worksheet was active whilst all the others were working correctly, as they were always explicitly linked to a certain sheet.

Stupid mistake. Really obvious in hindsight, just didn't see it through the fog of war
Probably a good lesson that I need to keep the code somewhat neater during development, makes it easier to spot such stuff ups.

It's just shear chance that on the small sample workbook I was using for development, I always started the code from the same sheet and the addresses used on that sheet did not cause any problems on the other sheets in that workbook, as those particular addresses had nothing in them on the other sheets. Wasn't until I ran it on larger workbooks that the interference became obvious and, stupidly, I went running off down the path of assuming it was something to do with size. On these larger workbooks I never stepped far enough through the code in debugging fashion to come across cells that were affected, as it wasn't until some 60,000 cells in that it started causing havoc.

Just a classic case of look very carefully at what's *actually* written in the code, not what you *think* was written!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.