Passing Variables between Subs .. get a ByRef argument error

[SpreedsheetCrusader]

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:

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:

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]

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??

 

[RoryA]

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

 

[DocAElstein]

Thanks Rory