VBA imploding?? Memory leak?

robgordon

New Member
Joined
Mar 8, 2011
Messages
1
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-com:eek:ffice:eek:ffice" /><o:p>Within a function I use the following statement …
<o:p> </o:p> 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:p> </o:p>
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:p></o:p>
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:p>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

baodinh

New Member
Joined
Feb 27, 2011
Messages
36
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Forum statistics

Threads
1,144,616
Messages
5,725,325
Members
422,614
Latest member
nicklasx

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