VBA imploding?? Memory leak?

[robgordon]

Hi

I have an application that will run at a clients site for an indeterminate amount of time, sometimes days, sometimes hours. But at some point it will crash.

What I've determined so far, by stepping through the code, is that VBA statements are executing without debugging, but most importantly without actually actioning!

Let me try to explain.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Within a function I use the following statement …
<o> </o> Sheets(sWorksheetName).Select

where sWorksheetName refers to a string parameter that stores the name of the Sheet I want to move to.

But here is the part that is causing my client and myself angst. Inconsistently it does not activate the sheet!!!
<o> </o>
1) The code runs without bugging out.
2) I can hover my mouse over the variable and see that the correct string has been stored in the variable.
3) But the sheet specified does not get focus.

To note also, if I pass via sWorksheetName a worksheet that does not exist, then I get a debug statement correctly informing me the sheet does not exist. In other words, VBA is still somewhat functioning.

The only way around the issue is to reboot the PC. The program will then work for some time before bugging out again.
<o></o>
I’ve searched and searched the net. I don’t think I’ve got a memory leak. Any ideas at all would be so appreciated.
</o>

 

[baodinh]

Try inserting an extra line Sheets(sWorksheetName).Activate to ensure the sheet is activated.

I assume you've already defined the variable sWorksheetName as string? For example Dim sWorksheetName as String.

If the above still does not work, you should post a copy of the workbook and macro so we can see why it does not work.

 

[Norie]

Why are you selecting/activating the worksheet in the first place?

It's probably not needed, and it could actually be at the root of the problem.

How are you determining it isn't doing what you want?

Is it based on the code following that line, perhaps incorrect values being used for variables, data going to the wrong worksheet?

Those are typical problems that can actually be caused by using Select/Activate.

Each time when the code doesn't work is there a common factor?

eg another workbook open by the user, perhaps one that's not part of the 'process' or even one that is

By the way, I'm pretty sure it's not a memory leak.

Then again we've not seen any of the code.