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
----------------------------------
Tidak ada komentar:
Posting Komentar