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

Hi Ryan,

If you are not retrieving the return value from the function you need to
call it without the parenthesis around the argument:

Foo wksheet

Otherwise, the parenthesis evaluate the argument, which in the case of
objects returns their default value, so passing (wksheet) is actually
passing the name of the worksheet, not a reference to the worksheet.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"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

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

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

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

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

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

In your

For Each wksheet In myBook.Worksheets

you will need to Dim wksheet as Worksheet
i.e. same as your Foo function declaration.......

"Ryan H." wrote in message t.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