Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient use of sheet "codenames"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient use of sheet "codenames"
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] On Sun, 13 Dec 2009 13:16:30 -0700, "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient use of sheet "codenames"
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] On Sun, 13 Dec 2009 13:16:30 -0700, "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient use of sheet "codenames"
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient use of sheet "codenames"
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] On Sun, 13 Dec 2009 14:25:46 -0700, "Robert Crandal" wrote: 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" wrote in message .. . 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient use of sheet "codenames"
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
find "Cancellation" in column "A" and copy all data from Columns B-F onto another Sheet | Excel Programming | |||
Most efficient code to "flip" a range of values + vs - | Excel Programming | |||
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? | Excel Programming | |||
Please add a "sheet" function like "row" and "column" functions | Excel Programming |