An Interesting vba Excel quirk

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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).
 
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
 
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.)
 
Dave Peterson said:
I was surprised that JE's suggestion worked--I wouldn't have guessed that it
would. (Thanks JE for the suggestion.)
Click to expand...

Late vs Early binding.

And as I wrote, I agree that the better option is using the CodeName.
 
I understand the theory, well, after the fact.

I just didn't think it would have worked <bg>.
 
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!).
 
Back
Top