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

It doesn't create a copy of the worksheet.

--
Regards,
Tom Ogilvy

"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

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

I must say, you were right! That worked.

Wow, and this whole problem was from an undeclared variable. From now on,
i'm sure going to declare all my variables ahead of time.

Thanks

"Ivan F Moala" wrote in message
om...
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

Hi Ryan,

Better yet, make sure that all of your modules have the Option Explicit
declaration at the top. This way VBA will tell you when you have not
declared a variable. From the Visual Basic Editor choose
Tools/Options/Editor and check the "Require Variable Declaration" checkbox.

By the way, your original problem, caused by using parenthesis around
the function argument when you are not retrieving the return value of the
function, will not be solved by declaring variables properly. You will still
need to either remove the parenthesis or use the return value of the
function.

--
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
t.cable.rogers.com...
I must say, you were right! That worked.

Wow, and this whole problem was from an undeclared variable. From now on,
i'm sure going to declare all my variables ahead of time.

Thanks

"Ivan F Moala" wrote in message
om...
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

Yes, you are right on that point... I now understand the whole topic of
paranthesis in functions... thank you


"Rob Bovey" wrote in message
...
Hi Ryan,

Better yet, make sure that all of your modules have the Option
Explicit
declaration at the top. This way VBA will tell you when you have not
declared a variable. From the Visual Basic Editor choose
Tools/Options/Editor and check the "Require Variable Declaration"
checkbox.

By the way, your original problem, caused by using parenthesis around
the function argument when you are not retrieving the return value of the
function, will not be solved by declaring variables properly. You will
still
need to either remove the parenthesis or use the return value of the
function.

--
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
t.cable.rogers.com...
I must say, you were right! That worked.

Wow, and this whole problem was from an undeclared variable. From now
on,
i'm sure going to declare all my variables ahead of time.

Thanks

"Ivan F Moala" wrote in message
om...
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

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