Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 309
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 309
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
find "Cancellation" in column "A" and copy all data from Columns B-F onto another Sheet bjohnson Excel Programming 1 September 20th 07 04:02 PM
Most efficient code to "flip" a range of values + vs - [email protected] Excel Programming 8 June 15th 07 11:08 PM
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? StargateFanFromWork[_4_] Excel Programming 7 October 29th 06 11:46 AM
Please add a "sheet" function like "row" and "column" functions Spreadsheet Monkey Excel Programming 2 November 8th 05 04:08 PM


All times are GMT +1. The time now is 08:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"