Passing array or class, ByVal or ByRef

[confusion123]

This article states objects are always passed ByRef,passing arrays are always ByRef and passing classes are like passing object variable types.

Passing Variable ByRef And ByVal

This is my code in a class:

Private pDataArray As Variant

    Private pDataArrayRows As Integer
    
Property Get DataArray() As Variant

    DataArray = pDataArray

End Property

Property Let DataArray(ByVal DArray As Variant)

    pDataArray = DArray

End Property

Property Get DataArrayItem(ByVal RowIndex As Integer, _
                           ByVal ColIndex As Integer) As Variant

    DataArrayItem = pDataArray(RowIndex, ColIndex)

End Property

Property Let DataArrayItem(ByVal RowIndex As Integer, _
                           ByVal ColIndex As Integer, _
                           ByVal Item As Variant)
    
    pDataArray(RowIndex, ColIndex) = Item

End Property

Public Sub EraseArray()

    If IsArray(pDataArray) Then Erase pDataArray

End Sub

Property Get DataArrayRows() As Integer

    DataArrayRows = pDataArrayRows

End Property

Property Let DataArrayRows(ByVal DArrayRows As Integer)

    pDataArrayRows = DArrayRows

End Property

The code works perfectly but I am confused. Am I passing a class or an array or both?

Note the keywords ByVal.

If I am passing a class (or an array) then according to the article, it should be passed ByRef only.

 

[RickXL]

Hi,

I have just been reading your other question: http://www.mrexcel.com/forum/excel-questions/917675-passing-objects-byref-byval.html

I cannot add anything to that explanation but I can do it another way.

If you create a variable, for instance:

Dim X as Long

VBA can set up a "pigeon hole" called X and make it four bytes in size so it is ready to accept a long integer.
When you say:

X = 99

in your program then the value in that pigeon hole is changed from whatever it was to 99.

If you now pass X into another Sub you can either pass just the name, X, or you can pass in the 99. The former is ByRef and the latter is ByVal. If you use ByVal then you can change the value in the Sub and its value outside will be unchanged. Effectively, a copy was made when you entered the Sub.

Objects are different. When you create an object, for instance:

Dim Y as Collection

then VBA cannot allocate the space for it. It can assign another "Pigeon hole" and label it Y but it does not know how big the collection will be, yet. So it puts the address of the place where the collection will actually start into the pigeon hole. The actual collection, the Object, will be elsewhere in memory.

Now if you pass Y into a Sub ByRef then you get a link to the object using the original address in the pigeon hole labelled Y. On the other hand, if you pass Y ByVal then you pass a copy of the ADDRESS into the Sub. The actual object is still where it always was.

This is why if you use ByVal and set the Object to Nothing then all you have done is changed the copy of the address into "blank".


Simply put, you can't pass an object into a Sub by value. You can only pass its address by value. It always works as if you have passed the object by reference.
The same goes for arrays. You may opt to pass a copy (ByVal) of its start address but for all intents and purposes, the array will be passed ByRef.

 

[confusion123]

Thanks for your comment on my other thread.

Have you any ideas on this thread?

Adding the keyword ByVal (and yet it still works, though it's an array) confuses me.

 

[mikerickson]

There are two assignment statements, Let and Set. Let is the default, so if

myVariable = 2

were written fully, it would be

Let myVariable = 2

Let is used for values and Set for objects.

Let is a ByVal operation
Set is a ByRef operation

Does that explaination help?


It looks like you are trying to create a custom class so you can assign an array as a property of that class, manipulate the class and those manipulations will be automatically reflected in the array. I'm not sure if that can be done in VBA, but I will ponder it all day at work.

 

[confusion123]

There are two assignment statements, Let and Set. Let is the default, so if

myVariable = 2

were written fully, it would be

Let myVariable = 2

Let is used for values and Set for objects.

Let is a ByVal operation
Set is a ByRef operation

Does that explaination help?


It looks like you are trying to create a custom class so you can assign an array as a property of that class, manipulate the class and those manipulations will be automatically reflected in the array. I'm not sure if that can be done in VBA, but I will ponder it all day at work.

Thanks for your comment.

I understand the difference between Let and Set.

What my code does (and it works) is to use a class to pass an array, instead of declaring it as a public variable.

If I add a Let Property using Insert -> Procedure -> Property, it automatically adds the keyword, ByVal.

I know the default for passing arguments is ByRef, so if the keyword, ByVal, is omitted, it "should" pass ByRef.

However, as the article by Chip Pearson states, classes and arrays are passed ByRef.

But my code explicitly has the keyword, Byval, yet still works.

So essentially does VBA ignore the keyword when passing an array?

 

[mikerickson]

I haven't read the Pearson article, but I suspect that he might have said that they are passed ByRef by default. Specifying ByVal would override those defaults.
Classes are objects, so passing one to a routine ByVal doesn't make much sense to me.
But I've successfully used both ByRef and ByVal to pass arrays and have gotten the expected results.

My comment about Let/Set was in response to your Property Let routine

Property Let DataArray(ByVal DArray As Variant)
    pDataArray = DArray
End Property

It doesn't matter if DArray is passed to the routine ByVal or ByRef. The Let statement makes pDataArray a copy of DArray, regardless of how it is passed to the Property Let routine. The implied Let (and its ByVal nature) make the routine as a whole ByVal

 

[DocAElstein]

Hi,
_ I am not sure if this may be just of very minor passing interest here. I have found in practice that an Array can be passed to a Function either as An Array() ByRef or as A Variant ByVal. There seemed to be some parallel here to why some Application.Functions worked when the corresponding Application.WorksheetFunction.Functions did not.

_ I confess i never got near to understanding exactly what was going on there. Someone suggested to me it was due to the complex way VBA or computers in general stored a data field by offsets to the initial Storage location. Somehow a more indirect “Call” was not capable of supporting this complicated offset structure. My understanding however does not go beyond this.
_ I am not sure if the Function example has any minor relevance to the things discussed in this thread

Alan

p.s.
_ Some of the Pointer ideas seem to be similar to some ideas being discussed just now in this thread.
http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html

 

[RickXL]

If you recognise the two kinds of data you can visualise how it works. Think of memory (RAM) as a long shelf.

Finite amounts of data (e.g. Long, Integer, Single, Double, Byte) can be given a named location on the shelf. The shelf space can be assigned as well. So a list of Dim statements in a macro can often have names and labels assigned straightaway.

However, when you Dim something that has an unknown length then that is no longer possible. So to carry on with its "setting up" process it leaves a place holder which is the address of the place where the actual data will start.

When you pass one of these variable length variables to a Sub VBA just passes the address. Whether that address is ByRef or ByVal does not really matter because (usually) you would not change an address in VBA. The object you see is the same in either case.

In fact, if you think about it, if you passed a WorkSheet ByVal would you expect it to pass a copy? Would you want to see another Tab appear for the copied sheet? Probably not.

However, things are never entirely straightforward. If you use strings, strings are of variable length so VBA will store this in two parts Address and Data. Strings can be passed ByVal. So when you pass a string ByVal then VBA must make a copy of it for you. Obviously, this will slow down your program. I think Arrays work the same way as strings.

You can see the process like this:

Sub Test()
    Dim x As String
    x = "qwer"
    Debug.Print StrPtr(x)
    Call mySub(x)
End Sub
Sub mySub(ByRef c As String)
    Debug.Print StrPtr(c)
    c = "22"
End Sub

StrPtr will display the address of the string. If you use ByRef, as above, then both print statements will show the same address but if you change it to ByVal then the two addresses will be different. So a copy was made and one address points to one and the other address points to the other. You can also time it as well. The ByRef method is faster than the ByVal method. Again, this points to the fact that a copy is being made.

As for comparing str="" with str=vbNullString, the first method creates the named space on the shelf, it creates the address in the space, it creates the actual data space then it places a null character in it. However, if you use vbNullString then you still get the named space on the shelf but the content is basically a null address. The actual string is not created. So vbNullString should be quicker than "".

I hope this helps.

(Alan: By the way, the thread you linked to has not been live since 2014.)

 

[DocAElstein]

There are two assignment statements, Let and Set. Let is the default, so if

myVariable = 2

were written fully, it would be

Let myVariable = 2

Let is used for values and Set for objects........

_ . I seem to be in a minority of about 1 in still using Let. Since only relatively recently getting into Object Orientated Programming it helps me distinguish and to try to understand the reasoning behind things that can be Let, things that must be Set and code lines that do not require either.
_. In the other Thread I referenced I stumbled on a “sort” of way of being able to both Set and Let something. It involves using a Variant - It can be Set or Let.
_. Not quite sure what this is telling me.. , other than maybe a Variant is a really big, inefficient “Place” allowing for all variable types, and at least the “Start” , Pointer / Address thing for an Object. ( The Pigeon Hole assigned to house the further address should a specific Object be created (...”...place holder which is the address of the place where the actual data will start….“) )...etc.... And I guess my Let or Set is really applying to the Variant.. which "......can be anything...!"

[color=blue]Sub[/color] LetSetVarObj() [color=darkgreen]'   mikerickson    http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4411927[/color]
10  Rem 1) Declaring the Variables
20  [color=blue]Dim[/color] Var_Obj [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Before Start this is Empty. At Start it is a Variant Variable with Empty in it of value Empty[/color]
30  [color=blue]Dim[/color] Obj [color=blue]As[/color] [color=blue]Object[/color] [color=darkgreen]'Before Start this is [color=blue]Empty[/color]. At Start it is an Object of value Nothing[/color]
40
50  Rem 2) [color=blue]Set[/color] Both ( to nothing.."
60  [color=blue]Set[/color] Obj = [color=blue]Nothing[/color] [color=darkgreen]'This does not appear to change the state of the Object[/color]
70  [color=blue]Set[/color] Var_Obj = [color=blue]Nothing[/color] [color=darkgreen]'This now is still a Variant Variable but has an Object "in it" of value Nothing[/color]
80      [color=darkgreen]'    Set Var_Obj = CreateObject("Excel.Sheet") 'This would become a Variant Variable with an Object "in it" and ThisWorkbook in that.  Then the Let at line 110 also works[/color]
90  Rem 3) Try to = Empty
100 [color=darkgreen]'Let Obj = [color=blue]Empty[/color] 'Runtime Error 91. Object Variable not Set[/color]
110 [color=blue]Let[/color] Var_Obj = Empty [color=darkgreen]'Becomes again a Variant Variable with Empty in it of value Empty[/color]
120
130 Rem 4   [color=blue]End[/color] "empty Stuff" as good practice:.....
140 [color=darkgreen]' ..Just to be on the safe side   "......apparently there is some memory leakage in VBA where allegedly objects which are not set back to Nothing keep this memory space flagged as being in use, even after the program finishes.)......[/color]
150 [color=darkgreen]'    sijpie    http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4411537[/color]
160 [color=blue]Set[/color] Obj = [color=blue]Nothing[/color] [color=darkgreen]'This does not appear to change the state of the Object[/color]
170 [color=blue]Set[/color] Var_Obj = [color=blue]Nothing[/color] [color=darkgreen]'This now is still a Variant Variable but has an Object "in it" of value Nothing[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

 

[DocAElstein]

@ RickXL
Hi Rick XL

..... recognise the two kinds of data.....
......... Think of memory (RAM) as a long shelf.

......Finite amounts of data (e.g. Long, Integer, Single, Double, Byte) can be given a named location on the shelf. The shelf space can be assigned as well. So a list of Dim statements in a macro can often have names and labels assigned straightaway.....

...... something that has an unknown length then that is no longer possible. So to carry on with its "setting up" process it leaves a place holder which is the address of the place where the actual data will start..........

I find that a nice simple to understand clarifying statement. Thanks ( It does support the ideas discussed in the other Thread i referenced....and ...

........(Alan: By the way, the thread you linked to has not been live since 2014.)

?? ..... It has about 8 Replies from me and sijpie starting from Fri 22 Jan 2015....??
same link again:
http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html
and from Fri 22 Jan 2015
http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4405322
_............................................

The address Codes were a very nice demo , showing the different address for a String and a “Copy” of that String, thanks. I tried and confirmed your results.
_ 1) I tried a slightly different version of your codes, and got some interesting results.

[color=blue]Sub[/color] TestByValByValByRefByValByValStr() [color=darkgreen]'  RickXL   http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382[/color]
10  [color=blue]Dim[/color] x [color=blue]As[/color] [color=blue]String[/color]
20  [color=blue]Let[/color] x = "qwer"
30  Debug.Print StrPtr(x) [color=darkgreen]'Address  194810764[/color]
40  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  194809764[/color]
45  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  194809764[/color]
50  [color=blue]Call[/color] mySubByRef(x)    [color=darkgreen]'Address  194810764[/color]
60  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  194810764[/color]
65  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  194810764[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=blue]Sub[/color] my[color=blue]Sub[/color]ByRef(ByRef c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print StrPtr(c)
    c = "22"
[color=blue]End[/color] Sub
Sub mySubByVal(ByVal c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print StrPtr(c)
    c = "22"
[color=blue]End[/color] Sub

_1a) Is this telling me..
_1a)(i) I can only make 1 copy ?
And
_1a)(ii) Once i call ByRef all further calls go By Ref regardless ?
Or
_1a)(iii) Have I missed out some “Pointer reset” command between calls...................

_.....................
__________2 ) I did notice if i comment out your
c = "22"
then the results look ( slightly) more sensible...

[color=blue]Sub[/color] TestByValByValByRefByValByValStr() [color=darkgreen]'  RickXL   http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382[/color]
10  [color=blue]Dim[/color] x [color=blue]As[/color] [color=blue]String[/color]
20  [color=blue]Let[/color] x = "qwer"
30  Debug.Print StrPtr(x) [color=darkgreen]'Address  280460428[/color]
40  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  280460668[/color]
45  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  194807404[/color]
50  [color=blue]Call[/color] mySubByRef(x)    [color=darkgreen]'Address  280460428[/color]
60  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  280461508[/color]
65  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  194807404[/color]
70  [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address  280461508[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=blue]Sub[/color] my[color=blue]Sub[/color]ByRef(ByRef c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print StrPtr(c)
    [color=darkgreen]'c = "22"[/color]
[color=blue]End[/color] Sub
Sub mySubByVal(ByVal c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print StrPtr(c)
    [color=darkgreen]'c = "22"[/color]
[color=blue]End[/color] Sub

_...very strange...
_2a) do you have an explanation of that.. I mean how does that line, c = "22" , “freeze” the pointer, as it were. Is it a sort of “Trick”

_..I note that after the first ByRef Call a new fresh address is given for a ByVal Call, after that it uses the same addresses...., i guess this bit is sort of OK... no reason it should not use the same address for ByVal calls ( just a bit wired it uses a different the first time around after a ByRef - although if i did infinite experiments i might find it picks randomly the addresses for a ByVal, sometimes the same, sometimes not... maybe....)


Thanks
Alan