How to pass sheet reference to a procedure

I have a worksheet that will calculate the cost of various services based on
the quantities and other information entered on a worksheet. Because this
worksheet can be copied multiple times in the workbook, I added a button to
the worksheet that will call a vba procedure that will contain the decision
logic and the cost calculations. I would like to use named cells so that the
logic is self-documenting for maintenance purposes.

Due to the number of inputs to the procedure I don't want to pass the values
entered as arguments. The inputs will not be modified only the costs cells
so I was thinking of passing the sheet using ByVal. I can't find anything
that says you couldn't do this in a book. Is this possible? Any gottcha's
in doing this?

Hi djd,

djd wrote:
> Due to the number of inputs to the procedure I don't want to pass the
> values entered as arguments. The inputs will not be modified only
> the costs cells so I was thinking of passing the sheet using ByVal.
> I can't find anything that says you couldn't do this in a book. Is
> this possible? Any gottcha's in doing this?

I would use ByRef (default, so you can leave it out entirely) since you want
to pass a reference to an object (Worksheet):

Sub demo()
DoCalcs Worksheets("Sheet1")
End Sub

Sub DoCalcs(rwsSource As Worksheet)
MsgBox rwsSource.Name
End Sub

There aren't any gotcha's that I can think of.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]