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