VBA Variables Already Initialised

[peridian]

Hello,

This is a bizarre one. I've got some Macro code in Excel, and variables are getting initialised to the last value they were when the code was last run! Before the code even reaches them!

I thought I was imagining things, but I've had it happen three times now. I've tried closing Excel and re-opening, but it still happens. The only thing that works is if I restart the PC.

If I put a break point before a line where a variable gets set for the first time, when I hover my mouse over the variable, there is already a value assigned to it (the example was a Long that I declare, then two lines later set to 2; it was already set to 4 on the line before the assignment).

I know with older languages you had to be careful of remnant values in memory when declaring variables, but I didn't think that was the case with VBA.

Here are the versions:

MS Office Excel 2003 (11.8169.8172) SP3
Windows XP Pro Version 2002 SP2

Has anybody else experienced this occurring? Does this mean I always have to initialise variables explicitly?

Regards,
Rob.

[jeffstl]

As long as you are dimming your variables out at the start of your code, just make sure that when your code is all done executing that you do something like this to the ones your having problems with:

Set MyVariable = Nothing

This will make sure it clears everything out from memory and if your dim statements are in place at the start of your code, they will get re-created.

This is putting it simply though as it might be tougher then it sounds to do this depending on how your logic is working and where exactly your dim statements are placed.

If you try this and still have the issue see if we can narrow it down atleast.

[Killer42]

This is a bizarre one. I've got some Macro code ...

It's certainly an odd-sounding problem you've come up with. I can think of a couple of questions that might help pin things down, but it would probably help if you could show us the code in question.

Anyway, here's some thoughts on debugging...

• Are they global variables? At what scope are they declared?
• Are they static variables?
• Are you sure some other code isn't being run when the sheet is loaded or something?
• As well as the breakpoint, can you try setting a watch on the variable to determine every time the value changes? Perhaps you can find where the value is coming from.
• Maybe it's just a bug in Excel. If all else fails, I suppose you could just clear the variables when your code starts. This is an ugly workaround, but if you can't solve the problem then you need to avoid it.

[peridian]

Thanks for your responses.

I do always use Option Explicit, so I always declare my variables and their data types. I had thought VBA always initialised to a default value, and that variables were cleared out after the program had finished. I'm surprised I'd have to do explicit clearing of variables.

They are Global variables however, which may have something to do with it. As far as I am aware, this is the only code that runs in this single spreadsheet, and I have no others open.

Interesting that you mention about break points, an additional side effect that occurred was that when I initially debugged the script, I placed break points. As you know, closing and re-opening Excel causes those break points to be cleared out.

Only when I did this as per my post, when I ran the code on re-opening, the code broke and went into debug mode at the points where the breakpoints were before I restarted the program. Even though I had not placed them the second time around.

Oddly enough, one of my colleagues has reported a similar problem with the break points when using VBA in Access.

For the time being, I ran the code and it worked, and unfortunately I have a deadline to meet, so for now I'm going to leave it. When I get the chance, I will come back to the file and see if the problem keeps on occurring still. If it does, I'll take some more time to look into this thoroughly. If I figure it out, I'll come back and post the resolution.

Thank you for your comments.

Regards,
Rob.

[Edit]: Typo.

[Killer42]

Good luck.

It starts to sound like some sort of fundamental change (or bug) in VBA, perhaps introduced as part of a service pack or Windows Update patch or something.