VBA Err Handling

[mchac]

I'm inexperienced with err handling and I'm trying to diagnose the source of an out of memory error that occurs when I run several procedures in succession but does not occur when I exit excel between procedures.

My question is, if I put something like 'On Error Goto ErrHandler' at the top of my code and and have some code in the ErrHandler that records the size of ranges and arrays etc for review later, does code execution get diverted to the ErrHandler any time an error occurs or does execution get diverted only if an error is occurring when the 'On Error ...' statement is processed?

Thanks in advance, sorry for the basic nature of the question.

 

[Maagaard]

First of it sounds like you are using a variable that you called as Global or Public, which means the value is saved each time you run the code. If you have for example an integer that gets to big it will deliver the message. try simply to Dim the variable instead, or in case of the integer change it to a Double.

Second if you write On Error GoTo ErrHandler, the code will jump to ErrHandler and continue the code from there.
Hope it helps!

Maagaard

 

[mchac]

Thanks Maagaard.
I've avoided global variables but there is one Public variable that parses error codes when loading webpages and I loop to load about 40k webpages iteratively. Usually there is no error but maybe that variable is being set at each iteration anyway and is hogging memory. I'll amend that and see how it goes.

Thanks re the ErrHandler.

 

[mchac]

One follow up question on this, if I create an array in Sub A then pass that array to Sub B I thought Sub B creates a duplicate array which would cause a memory use spike until Sub B is done. Is that wrong?

 

[Maagaard]

I'm not quit sure what you mean, if the duplicate array is local to sub b, it will be erased when sub b is done, hence it will only use memory while sub b is running.

 

[mchac]

Hi Maagaard. I'm guessing at what I'm about to say this since I don't yet understand how Excel allocates memory and I don't understand why I'm getting an out of memory error.

What I meant above is if in Sub A an array is created, and let's say excel is using 400mb and the array is 100 mb of that (I'm just picking numbers to illustrate), if the array is passed to Sub B then for the time Sub B is running, memory will jump to 500mb because it is duplicated. If that hits the system limit I'll get an Out of Memory error and execution will stop. If instead I declare a public array, when Sub B is called it' won't create a duplicate array so memory use will stay flat at 400 mb. As above this is a guess.

All of that said, I moved all my public variables into the respective Subs and passed them along as needed and I still get the out of memory error.

Next thing I'm going to try to do is introduce an error handler and capture details of the progress of the procedure to see if I can learn anything.

This particular part of the procedure uses borrowed code and involves parsing XML (which I'm not too familiar with) and there may be something in there that keeps getting bigger at each loop.

 

[Maagaard]

I think you are right. However an out of memory error usually applies to an variable getting to big. Not excel running out of memory. You will as an exable get an out of memory error if an integer is bigger than 32,767 where a double can hold a number up to ~1.7977 E308.

My guess is that your variables are called in a format that is to small.

 

[mchac]

I'll check the variables again.
I was reading some posts last night that said to never use Integer, to use Long because VBA interprets Integer into Long anyway. I didn't read on to understand why it does that and why Integer exists if it's superfluous. But anyway, I'll check this.
Thanks for your thoughts.