Efficient use of sheet "codenames"

[Robert Crandal]

Suppose I define the following subroutine in one of my VBA modules:

Public Sub DoCalculations()
invoiceDate = Sheet1.Range("A1").Value
invoiceTotal = Sheet1.Range("A2).Value
Sheet1.Range("A30).Value = invoiceTotal + 20
End Sub

The above subroutine is limited to using Sheet1, so how
I can I modify this subroutine to accept any sheet codename
as a parameter??? Please keep in mind, I prefer to use
sheet codenames rather than the names on my sheet
tabs, since users can change the names on tabs at any time.

So, I want to modify the subroutine definition as follows:

Public Sub DoCalculations (ByVal SheetCodeName as String)
'
' Revised code here
'
End Sub

The revised routine above will accept a sheet codename in string
format, then I want the same code above to be executed on
an arbitrary/given sheet referenced by codename.

I hope that makes sense...

thank you

 

[Chip Pearson]

Declare the parameter As Worksheet. E.g,

Sub AAA()
BBB Sheet3
End Sub

Sub BBB(WS As Worksheet)
Debug.Print WS.Name, WS.Range("A1").Text
End Sub

As you'll see by the output generated by BBB, you are passing Sheet3
(code name) that doesn't necessarily have the same tab name.

If you attempt to use a sheet that doesn't exist (e.g., code name
Sheet5), you'll get a compiler error, not a runtime error.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Chip Pearson]

I should have mentioned also that you can change the code name of the
worksheet to make it more meaningful. Just bring up the Properties
window and then select the worksheet in the Project window. Change the
Name property of the selected worksheet. This changes the code so you
can use more meaningful names like shBudget rather than just Sheet1.
This change of names has nothing to do with the tab name of the
worksheet, so it won't be affected if the user renames a sheet.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Robert Crandal]

So, when I call BBB, I must call it as follows:

BBB Sheet3 ' works okay

On the other hand, I get an error message if I call it as follows:

BBB (Sheet3) ' error message - Object doesn't support this property or
message.

I just think it's weird that an error occurs if I place parentheses around
Sheet3. I wonder
why that causes an error?

Anyways, thank you so much for your help. Your solution above DOES work
for my needs!

 

[Chip Pearson]

BBB (Sheet3) ' error message - Object doesn't support this property or
message.

When you enclose the sheet parameter within parens, you are causing
VBA to evaluate the item to its default value, and a worksheet has no
default value. Since there is no default value, you get the "object
doesn't support..." error.

You'd get the same result regardless of whether you pass by code name
or by sheet name. E.g,

BBB (Worksheets("Sheet1"))

will cause the same error.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Rob van Gelder]

Here's an example:

For each sheet, set cell A1 to something different.


Sub test()
Debug.Print MyFunction(Sheet1)
Debug.Print MyFunction(Sheet2)
Debug.Print MyFunction(Sheet3)
End Sub

Function MyFunction(wks As Worksheet) As String
MyFunction = wks.Range("A1").Value
End Function


I support the use of codenames in VBA. It allows the user/developer flexibility in naming the sheet what they want.

Cheers,
Rob