Pass a worksheet as a parameter to a function?

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

Excel 2003, and probably just a syntax issue. Googled but wasn't able to find
relevant content.

I recorded a macro to grab a bunch of sheet formatting. I have many similar
sheets that all need to be formatted the same, so I want to use the recorded
macro and just pass the worksheet name as a parameter- so after I add all the
data to each worksheet, I can format it and move on.

In my limited experience with passing values between subs/functions I know
the value has to be the same type (if a function is expecting an integer
value, I not only have to pass it an integer, but I actually have to define
the variable in the source procedure as an integer (ok, I admit I rarely use
option strict on).

So, I tweaked the recorded procedure name to be:
Sub CleanUpSheets(mWorksheet As Worksheet)

and in my main procedure I use the sheet codename (because users will be
changing the tab names), and so I use:
....
CleanUpSheets(Sheet92)

which results in a run time error 438: object doesn't support this property
or method.

I didn't define the sheet, because it is the sheet codename. Just for kicks,
I tried it anyway: Dim Sheet92 as worksheet ...but then I get a runtime error
91 when I tried to access Sheet92 in the main code.

So... what is the proper syntax (on both subs) to pass a worksheet as a
parameter, so the receiving sub knows which worksheet to format?

Many thanks,
Keith
 
I'd pass it as an object:

Option Explicit
Sub testme()
Call CleanUpSheets(CodeNameOfSheet:=asdf)
End Sub
Sub CleanUpSheets(CodeNameOfSheet As Object)
MsgBox CodeNameOfSheet.Name
End Sub

I don't think you can get more specific than that (but I'm waiting for a
correction).

There are lots of types of sheets (worksheets, macro sheets, chart sheets,
....). And each of these have a codename.
 
Here is a very simple example of passing a worksheet as a parameter:

Sub main()
Dim sh As Worksheet
Set sh = Sheets("Sheet2")
Call PutValue(sh)
End Sub

Sub PutValue(s As Worksheet)
s.Range("B9").Value = "XX"
End Sub
 
The reason your code failed is because of how you are calling it.

Either use:

CleanUpSheets Sheet1 ' No parenthesis
or
Call CleanUpSheets(Sheet1) ' with parenthesis, using Call

Otherwise your approach works:

Sub CleanUpSheets(mWorksheet As Worksheet)
MsgBox mWorksheet.Name
End Sub

Sub test()
Call CleanUpSheets(Sheet1)
End Sub
 
Dave, Tim, & Gary's Student-

Thank you all for your replies- I couldn't even figure out one way to do it,
and you all have shown me three ways to do it! I appreciate your help, and
now I'm back on track!

Best,
Keith
 
Gary''s Student said:
Here is a very simple example of passing a worksheet as a parameter:

Sub main()
Dim sh As Worksheet
Set sh = Sheets("Sheet2")
Call PutValue(sh)
End Sub

Sub PutValue(s As Worksheet)
s.Range("B9").Value = "XX"
End Sub
Click to expand...
Good, Very simple, we understand without any doubt or problem. Thank you
 
Back
Top