Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
Hello,
Im my workbook I have a sheet named "Departments". In the code for this sheet I have the following sub DTest msgbox "Hello from DTest" end sub My code in Module 1 includes the following Option Explicit Public Departments as worksheet pubilc sub setup Set Departments = activeworkbook.sheets("Departments") end sub sub Test1 setup Departments.DTest end sub When I try to complie this code, VBA insists on interpreting Departments.Dtest as a method of the object variable Departments rather than a reference to the routine DTest found on the code for the sheet named Departments. So an error is generated. Casn anyone think of a way around this preblem? Thanks -- -regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
The code doesn't live in the worksheet--it lives in the worksheet module
associated with that worksheet. You won't be able to call it the way you suggested. You could do this (I wouldn't): Application.Run Departments.CodeName & ".dtest" ==== But you can make your life a lot simpler. Instead of using a variable named Departments, you can rename the codename of the "Departments" worksheet. Open your workbook go to the VBE hit ctrl-r to see the project explorer expand your project click on the Departments object you'll see something like: Sheet1 (Departments) The name in ()'s is the name the users see on the worksheet tab. The name in front is called the CodeName. hit F4 to see the properties for that worksheet. Change the (Name) property (at the top of the alphabetic list) to Departments Then you can use: Departments.dTest If you don't rename the Codename, you can still use: Sheet1.dTest But renaming the codename is a nice because you can use a mnemonically significant name. And your becomes: Option Explicit Sub aaa() Departments.dtest End Sub You don't have the Public line, the setup procedure or any thing else. GeorgeJ wrote: Hello, Im my workbook I have a sheet named "Departments". In the code for this sheet I have the following sub DTest msgbox "Hello from DTest" end sub My code in Module 1 includes the following Option Explicit Public Departments as worksheet pubilc sub setup Set Departments = activeworkbook.sheets("Departments") end sub sub Test1 setup Departments.DTest end sub When I try to complie this code, VBA insists on interpreting Departments.Dtest as a method of the object variable Departments rather than a reference to the routine DTest found on the code for the sheet named Departments. So an error is generated. Casn anyone think of a way around this preblem? Thanks -- -regards -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
Since you define Departments in the module as a worksheet object
variable, the compiler looks for a Worksheet method named Dtest, and doesn't find one. One workaround would be to define Departments in the module as an Object: Public Departments As Object so that the Dtest method won't be checked until run-time. Another (IMO better) way would be to forget the variable altogether and change the "Departments" worksheet's code name to Departments (via the VBA Properties window). In article , GeorgeJ wrote: Hello, Im my workbook I have a sheet named "Departments". In the code for this sheet I have the following sub DTest msgbox "Hello from DTest" end sub My code in Module 1 includes the following Option Explicit Public Departments as worksheet pubilc sub setup Set Departments = activeworkbook.sheets("Departments") end sub sub Test1 setup Departments.DTest end sub When I try to complie this code, VBA insists on interpreting Departments.Dtest as a method of the object variable Departments rather than a reference to the routine DTest found on the code for the sheet named Departments. So an error is generated. Casn anyone think of a way around this preblem? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
I'd like to thank both of you guys for your very helpful info. Here's what
I've discovered, based upon your suggestions 1) Each worksheet has both a Name and a (Name) property. If this isn't wierd enough, these properties correspond to the reference to the sheet seen in the VBA project explorer in the *opposite* way from what one would expect, e. g. the sheet described in the project explorer window as Sheet1 (Departments) has Name = Departments and (Name) = Sheet1 go figure! 2) If you select a particular sheet in the project explorer window and then right-click and then select VBA properties, you *don't* get the display needed to change Name or (Name). To get this display you need to press F4, as Dave mentions. I'm grateful to Dave for pointing this out to me. I never would have guessed to do this. I think Msft should make this operation more intuitive. Where is it discussed? 3) Contrary to JE's suggestion (as I understood it), if you change both the Name and the (Name) of a sheet to Departments, and if you also declare a global variable Departments of type worksheet, then a reference to Departments.sub1 will cause VBA to interpret this a a field of the object variable Departments and flag an error. JE's suggestion that Departments be declared simply a variable of type object appears to work, but I'm not sure whether or not there are disadvantages to doing this FYI, here's how I've decided to handle the situation For a worksheet Sheet1 (Departments) I will use F4 to change it to Departments_(Departments) Then the complier will understand that Departments_.sub1 refers to a subroutine on the code module associated with Departments_(Departments) and Departments.cells(1,1) represents the upper left cell on the worksheet referenced by the VBA worksheet variable Departments, which has been set by the code Set Departments = ActiveWorksheet.Sheets("Departments") Once again, thanks to you both for your help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
I was surprised that JE's suggestion worked--I wouldn't have guessed that it
would. (Thanks JE for the suggestion.) But personally, I'd drop the: dim Departments as worksheet set departments = worksheets("departments") just so you could use: departments.cells(1,1) You could have also used: departments_.cells(1,1) But I'd just change the codename to Departments--There's nothing wrong with using the same name for the codename and sheet name. It might make the code easier to understand, too. And there's nothing wrong with using variable names like: dim PriceList as range set pricelist = worksheets("prices").range("PriceList") (It's kind of the same philosophy.) GeorgeJ wrote: I'd like to thank both of you guys for your very helpful info. Here's what I've discovered, based upon your suggestions 1) Each worksheet has both a Name and a (Name) property. If this isn't wierd enough, these properties correspond to the reference to the sheet seen in the VBA project explorer in the *opposite* way from what one would expect, e. g. the sheet described in the project explorer window as Sheet1 (Departments) has Name = Departments and (Name) = Sheet1 go figure! 2) If you select a particular sheet in the project explorer window and then right-click and then select VBA properties, you *don't* get the display needed to change Name or (Name). To get this display you need to press F4, as Dave mentions. I'm grateful to Dave for pointing this out to me. I never would have guessed to do this. I think Msft should make this operation more intuitive. Where is it discussed? 3) Contrary to JE's suggestion (as I understood it), if you change both the Name and the (Name) of a sheet to Departments, and if you also declare a global variable Departments of type worksheet, then a reference to Departments.sub1 will cause VBA to interpret this a a field of the object variable Departments and flag an error. JE's suggestion that Departments be declared simply a variable of type object appears to work, but I'm not sure whether or not there are disadvantages to doing this FYI, here's how I've decided to handle the situation For a worksheet Sheet1 (Departments) I will use F4 to change it to Departments_(Departments) Then the complier will understand that Departments_.sub1 refers to a subroutine on the code module associated with Departments_(Departments) and Departments.cells(1,1) represents the upper left cell on the worksheet referenced by the VBA worksheet variable Departments, which has been set by the code Set Departments = ActiveWorksheet.Sheets("Departments") Once again, thanks to you both for your help -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
In article ,
Dave Peterson wrote: I was surprised that JE's suggestion worked--I wouldn't have guessed that it would. (Thanks JE for the suggestion.) Late vs Early binding. And as I wrote, I agree that the better option is using the CodeName. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
I understand the theory, well, after the fact.
I just didn't think it would have worked <bg. JE McGimpsey wrote: In article , Dave Peterson wrote: I was surprised that JE's suggestion worked--I wouldn't have guessed that it would. (Thanks JE for the suggestion.) Late vs Early binding. And as I wrote, I agree that the better option is using the CodeName. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
An Interesting vba Excel quirk
Just to add, I'm surprised that you could refer to the code in the worksheet's
module by using the worksheet (even as an object). I thought that I'd have to use the .codename to get there. I learned something today (again!). Dave Peterson wrote: I understand the theory, well, after the fact. I just didn't think it would have worked <bg. JE McGimpsey wrote: In article , Dave Peterson wrote: I was surprised that JE's suggestion worked--I wouldn't have guessed that it would. (Thanks JE for the suggestion.) Late vs Early binding. And as I wrote, I agree that the better option is using the CodeName. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter Quirk | Excel Discussion (Misc queries) | |||
an interesting Excel question | Excel Discussion (Misc queries) | |||
Formatting custom currency quirk | Excel Discussion (Misc queries) | |||
another interesting thing... | Excel Worksheet Functions | |||
a VLOOKUP quirk | Excel Worksheet Functions |