|
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
byref errors
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 |
| All times are GMT +1. The time now is 12:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com