Passing Variables between Subs .. get a ByRef argument error

SpreedsheetCrusader

Board Regular
Joined
Sep 30, 2015
Messages
130
Hi

i am trying to save some ink on a VBA code i am writing a code which is about toggling cells colors on/off by pressing a set of buttons on the sheet (e.g. button 1 toggles cells with yellow color on/off, button 2 toggles cells with green color on/off, and so forth..)

i have this piece of code at the beginning of each Subroutine dedicated to each button to set the range:

Code:
Dim lastrowlastrow = Cells(3, 6).SpecialCells(xlCellTypeLastCell).Row


Dim myrange As Range
Set myrange = Range(Cells(3, 6), Cells(lastrow, 12))

this aint going to change between subs it will remain the same so i tried declaring those two variables in a Sub and calling that Sub inside my buttons dedicated Subs see below:

Code:
Sub Vardef (Lastrow as long,myrange as range)

lastrow = Cells(3, 6).SpecialCells(xlCellTypeLastCell).Row
Set myrange = Range(Cells(3, 6), Cells(lastrow, 12))

end sub

sub ToggleYellow()

Call Vardef (Lastrow,myrange)

.
.
.
.

rest of the code

when i do the above i start getting an Error "Argument ByRef" and the code does not work, i highlight on myrange i find it = nothing

do not know what is the problem it is driving me mad, especially that i used the same technique with other projects and it worked smoothly, however this is the first time i pass a variable that is a range.


looking for your help my spreadsheets brothers and sisters.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
ByRef is the default, not ByVal.....
Thanks, - I had got confused with the more typically ( by me ) seen situation where values rather than variables are taken across at the calling line, which then of course works as ByValue, even if at the signature line of the called procedure has ByRef in it. – This comes back the bit shg explained whereby an extra set of “parems” at the calling line of, for example in my example, my Procedure Sub CallByRefByValCheck() like this:
Call ByRefByValCheck((varByRef), varByVal)
This also has the effect of doing it as ByVal, as the (varByRef) “evaluates” the variable varByRef at that point to its value I think.
_ ....

....
ByVal passes a copy of the variable or, in the case of an object, a copy of the pointer to the object.
ByRef passes the pointer to the variable. Arrays are passed ByRef
Thanks for the bit of clarity.

Thanks for the reply
Alan

P.s. Is it possible to explain out of interest what the logic ( if there is such ) to why Arrays are passed ByRef. – I think I can pass a Variant which is an Array as ByVal, - naively that sounds like it should be the same, but clearly is not, I guess??
 
Last edited:
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,141
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
A Variant which may contain a particular data type is not the same thing as that data type.

I would imagine it's to do with the internal storage and ensuring that the types match so that the offsets are correct.

If you assign to a variant, you can't pass it to a routine expecting an array; if you enclose the argument in parentheses, I believe you create an implicit local variable, which is passed ByRef (the net effect being as if you passed ByVal).
 
Upvote 0

Forum statistics

Threads
1,187,001
Messages
5,961,057
Members
438,516
Latest member
Fintrics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top