But I think this is truly excessive to copy a whole worksheet
everytime

When passing objects to procedures, ByVal does NOT copy the whole
worksheet. Objects are ALWAYS passed by reference; the ByVal and
ByRef specifies indicate whether the address of the object is
passed by value or by reference. Using ByVal doesn't cause "more"
data to be passed to the called procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ryan H." wrote in message
.cable.rogers.com...
I was able to fix it by changing the function into:

Function Foo(ByVal wsheet as Worksheet)

But I think this is truly excessive to copy a whole worksheet
everytime I
use this function. I'm sure there is another way.



"Ryan H." wrote in message

.cable.rogers.com...
no, Im not using any chart sheets... I changed it from sheets
to
worksheets
though to see if it works or not... just tried a bunch of
different
combinations... nothing worked...



"Chip Pearson" wrote in message
...
Ryan,

Is it possible that you have chart sheets in the workbook?
If
so, wksheet won't be a worksheet. If you have chart sheets,
use

For Each wksheet In myBook.Worksheets


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Ryan H." wrote in message

et.cable.rogers.com...
I just tried that and I still get a "byref argument type
mismatch", which is
a compile error

Let me just mention that wksheet comes from a for each
statement as follows:
For Each wksheet In myBook.Sheets





"Chip Pearson" wrote in message
...
Ryan,

If you are not taking the return value of the Foo
function,
don't
enclose the parameter in parentheses. E.g.,

Foo wksheet


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Ryan H." wrote in message


e.rogers.com...
Hi,

I have a function that accepts the following
parameter:

Function Foo(wsheet as Worksheet)
...
...
End Function

when I call the function as in the following:

Foo(wksheet)

I get a ByRef error. Why can I send worksheet as a
parameter? I
think it has
something to do with references. The only way I see
out of
this
is to make
the variable global, which offcourse is bad. How can
this
be
done?

Thanks