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



On 14-Dec-2009 09:16, Robert Crandal wrote:
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