VBA Err Handling

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Maagaard

Board Regular
Joined
Apr 16, 2013
Messages
70
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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

Board Regular
Joined
Apr 16, 2013
Messages
70

ADVERTISEMENT

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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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

Board Regular
Joined
Apr 16, 2013
Messages
70
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top