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
> -----------------------------------------
> “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@...>
> 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