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.
4 1458
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:
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.
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.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Joerg Toellner |
last post by:
Hi Group,
Dont know how to solve my problem with my little brain by myself. Maybe my
way is not the best at all and maybe not C++ style...but i don't no to do
better at the moment. So excuse my...
|
by: Neil Zanella |
last post by:
Hello,
When an "std::map<int, int> foobar;" statement appears in a C++ program
followed by a statement of the form "foobar;" where nothing has ever
been inserted at position 123 into map foobar,...
|
by: publictom |
last post by:
I just happened to read item 47, "Control Flow", in Exceptional C++ by
Herb Sutter (which is based on Guru of the Week 12), just after reading
item 33, "Use Inlining Judiciously", in Effective C++...
|
by: vp |
last post by:
Can I safely assume that all static variables are initialized as NULL
or zero, depending on the types of the variables, no matter on which
platform that app is compiled ?
Thanks for your help,
...
|
by: Andreas Boehm |
last post by:
Hi *.*,
does the standard meanwhile define something about initializing
variables by the compiler? I think, it is a side-effect of the OS used,
if undefined global (static) variables are...
|
by: Madhav |
last post by:
Hi all,
I did not understand why do the global vars are
initialized to NULL where as the block level variables have random
values? I know that the C standard requires this as was mentioned in a...
|
by: DaveC |
last post by:
I always used to initialise variables at declaration, then a couple of
colleagues started telling me it was bad practice and that the compiler
should be left to spot the use of uninitilised...
|
by: Kislay |
last post by:
Which of the following is correct regarding the storage of global
variables :
1. Global variables exist in a memory area that exists from before the
first reference
in a program until after the...
|
by: Ranganath |
last post by:
Hi,
Why is there a restriction that only integral types can be made static
constant members of a class?
For e.g.,
class B {
private:
static const double K = 10;
};
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |