byref errors

R

Ryan H.

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
 
R

Rob Bovey

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 *
 
C

Chip Pearson

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
 
R

Ryan H.

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
 
C

Chip Pearson

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
 
R

Ryan H.

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...
 
R

Ryan H.

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.
 
I

Ivan F Moala

In your

For Each wksheet In myBook.Worksheets

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

Ryan H.

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
 
R

Rob Bovey

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 *
 
R

Ryan H.

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

Chip Pearson

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
 
Top