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
-----------------------------------------
"Do 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@yahoo.com>
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
----------------------------------
Tidak ada komentar:
Posting Komentar