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@yahoo.com>
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
wanbt 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
--- In ExcelVBA@yahoogroups.com, "mickey11030" <mickey11030@...> wrote:
>
> Paul,
> You have slain the evil genie and your reward should be riches beyond
> counting, the Nobel Prize in Computer Science, or beautiful women fawning
> over you, but you'll have to settle for my most sincere thanks.
>
> I have usually opened Excel, and then used Alt+F11 to open VBA. I now see
> when you do that you get the sheet module for the open sheet.What is the
> easiest way to get the module for the workbook?
>
> Thanks again,
> Michael Trombetta
>
> --- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@> wrote:
> >
> > Where did you place this macro?
> > Would it happen to be in a SHEET module?
> > I bet you put it in the FRUIT sheet module... here's why I think this:
> >
> > I tested it, and it what you're seeing is referred to as the "scope"
> > of the
> > variable/macro.
> >
> > When a variable is defined, or a macro created, it is only defined for
> > the
> > MODULE where it is defined.
> >
> > So, if you put this in the Fruit module,
> > even though you "select" the Vegs sheet, the .range property STILL
> > refers to the
> > sheet where the macro is defined.
> > Which is the Fruit sheet.
> > Put a value like:: "Fruit2" in cell A2 (of sheet "Fruit")
> > You'll see that the macro is actually reading the value of the fruit
> > sheet, even
> > though the Vegs sheet is selected.
> >
> > Think of it this way:
> > the Range property actually REQUIRES a sheet object to refer to.
> > It actually CANNOT work without it..
> > However, if you don't supply one, there ARE defaults available.
> > If the macro is written in a SHEET module, then the "default" the sheet
> > where it
> > is defined.
> > If the macro is written in a "standard" module, then the default is
> > taken as the
> > currently selected sheet.
> >
> > Personally, I don't like selecting sheets.
> > It just causes extensive display "flickering".
> > I almost ALWAYS provide the sheet like you did with:
> > VegName = Sheets("Vegs").Range("A2")
> >
> > Additionally.. The Range Property has LOTS of attributes...
> > colors(foreground/background), font, borders...
> > basically, anything you can change with regard to a cell is stored as a
> > range
> > attribute.
> > It just happens that the "default"Â attribute of the Range property is
> > "Value".
> >
> > If you had not defined VegName as a String, then you could have used:
> > Set VegName = Sheets("Vegs").Range("A2")
> >
> > and VegName.Value would be the value of the cell and
> > VegName.ColumnWidth would be the width of the column...
> >
> > I don't like relying on defaults.
> > Therefore, I ALWAYS like to specifiy the object as well as the Value
> > attribute, like:
> >
> > VegName = Sheets("Vegs").Range("A2").Value
> >
> > hope this helps,Â
> > Paul
> > -----------------------------------------
> > â?oDo all the good you can,
> > By all the means you can,
> > In all the ways you can,
> > In all the places you can,
> > At all the times you can,
> > To all the people you can,
> > As long as ever you can.â? - John Wesley
> > -----------------------------------------
> >
> >
> >
> >
> > ________________________________
> > From: mickey11030 <mickey11030@>
> > To: ExcelVBA@yahoogroups.com
> > Sent: Tue, December 13, 2011 9:47:59 PM
> > Subject: [ExcelVBA] Help! There is an evil genie in my VBA
> >
> > Â
> > To illustrate my puzzle, I've set up a workbook with 2 sheets, named
> > Vegs and
> > Fruit. the Vegs sheet has Beets Corn Spinach in A1, A2 and A3. The Fruit
> > sheet
> > has Apple Banana Cherry in B1, B2 and B3.
> >
> > I wrote the following code:
> >
> > Sub Test()
> > Dim FruitName As String
> > Dim VegName As String
> >
> > 1 Sheets("Vegs").Select
> > 2 VegName = Range("A2")
> > 3 Sheets("Fruit").Select
> > 4 FruitName = Range("B1")
> > 5 VegName = Sheets("Vegs").Range("A2")
> > End Sub
> >
> > I initially displayed the Fruit sheet, and when stm 1 was executed, the
> > Vegs
> > sheet was displayed. But after stm 2 was executed, VegName was empty,
> > i.e., "".
> > That is the first puzzle - VegName should be Corn.
> > When stm 3 was executed the Fruit sheet was displayed. When stm 4 was
> > executed
> > FruitName was Apple. That indicates to me that stms 1 and 2, which are
> > similar
> > to stms 3 and 4, should work. To put icing on the puzzle, after stm 5,
> > which is
> > equivalent, I think, to stms 1 and 2, is executed, VegName finally had
> > the value
> > Corn.
> > I also noticed when I hover the mouse over Range("A2") in stms 2 or 5
> > nothing
> > displays, but when I hover the mouse over Range("B1") in stm 4, a
> > tooltip
> > displays Range("B1")="Apple".
> >
> > What in the world is going on?
> > Thanks.
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
------------------------------------
----------------------------------
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