> I can't recall having variables randomly go-to-null on me,
What happens is that Excel will normally maintain the values of static
variables between macro executions. However, it sometimes seems to reset
the whole VBA environment, resulting in the static variables needing to be
reinitialised.
It certainly does this when there is a code change, but it also does it at
other times on occasions. I've never been clear about when these other
occasions are. Simplest thing is to assume that they can be reset and
program accordingly.
> I also knew someone would dislike globals,
I use some globals, but try to avoid them. They don't cause VBA problems,
as such, but can be more difficult to debug when something goes wrong. It
depends a lot on how large your code is and across how many modules. The
more modules you have, the harder it is to control your global variables.
> On the Sheets collection usage, I knew that probably
> wouldn't necessarily be the "best" way to specify the object.
This one is worth getting a handle on. I reckon it makes the code easier to
read, and it certainly makes it run faster.
Regards, Dave S
----- Original Message -----
From: "noskosteve" <noskosteve@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Monday, January 02, 2012 11:50 AM
Subject: [ExcelVBA] Re: Help! There is an evil genie in my VBA
>
> Thanks Dave,
>
> I can't recall having variables randomly go-to-null on me, but I'll add a
> reminder to my personal hints document to keep in mind if I run across an
> intermittent problem. So far, all my problems have been 'planned in'.
> (;-)
>
> I also knew someone would dislike globals, but it's the way my brain
> works. Passing variables in my applications gets to be a pain for me and
> I haven't noticed problems in my VBA....
>
> On the Sheets collection usage, I knew that probably wouldn't necessarily
> be the "best" way to specify the object. Each time I need to do coding I
> need a serious refresher and frequently refer to old code. One area I
> will admit weakness is referring to some objects - especially when there's
> more than one way, and it showed up here.
> It's a neat language, but also terribly complex, yet fun ... especially
> when I am doing things outside of Excel on serial and USB ports with it.
> One thing I have always disliked about VBA is the way it has defaults
> that can easily cause you to get into this (the op's) kind of trouble.
> Option Explicit is the best thing going, but it doesn't extend to object
> referencing.
> Sometimes it seems that VBA thinks it is smarter than we are. (;-)
>
> --
> Cheers for 2012, Steve N. USN (Vet) MOT (Ret) Ham (Yet)
> --
> I served during the cold war, so you can continue to be served a cold one.
>
>
>
>
> --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...>
> wrote:
>>
>> A big problem with static variables - particularly when you're using them
>> globally - is their lifetime. Excel will lose the contents of static
>> variables on a seemingly random basis.
>>
>> You need to ensure that your code can cope with that. I tend to have an
>> extra variable at the same scope. I make it a Boolean and check it in an
>> "initialise" sub that I call from the top of every other sub. If it's
>> false, I know that Excel has thrown away all the values, so I set them up
>> again, and set this one to true.
>>
>> In general, though, I avoid static and global variables as much as
>> possible.
>>
>> Getting references to worksheets, etc, seems to be pretty quick, and it's
>> just as easy to do them each time the code runs, rather than trying to
>> keep
>> them in static variables.
>>
>> Also, I like to use references to worksheets, etc, rather than multiple
>> Sheets("xx") calls. I simply set them up at the top of the methods and
>> use
>> them to explicitly specify which item I'm working with.
>>
>> Regards, Dave S
>>
>> ----- Original Message -----
>> From: "noskosteve" <noskosteve@...>
>> To: <ExcelVBA@yahoogroups.com>
>> Sent: Monday, January 02, 2012 9:01 AM
>> Subject: [ExcelVBA] Re: Help! There is an evil genie in my VBA
>>
>>
>>
>> Yes, Variable scope and lifetime are important VBA concepts that needs a
>> focused approach. I, as an Advanced Beginner, constantly wrestle with
>> this.
>>
>> I will be corrected if I am wrong, but, as an FYI for the future when you
>> want to be sure you are referring to the correct object, I believe you
>> should (also) be able to *explicitly* specify the sheet (object)
>> something
>> like this:
>>
>>
>> > > 1 Sheets("Vegs").Select
>> > > 2 VegName = Sheets("Vegs").Range("A2")
>>
>> Of course, the variable VegName is available only in sheet "Vegs" code.
>> If desired, you also have the option of declaring variables as Public
>> (Global) (I do this in a regular module), then have them available in any
>> module.
>> --
>> 73, Steve
>>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1416 / Virus Database: 2109/4116 - Release Date: 01/01/12
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar