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.

 

[DocAElstein]

@ RickXL
Hi Rick XL.........( 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
_............................................
......
......

EDIT: - I meant to say it has replies from the last few days... that is to say... Fri 22 Jan 2016

 

[RickXL]

Alan,

I think it is reusing space if it can. I tried this:

Sub TestByValByValByRefByValByValStr() '  RickXL   http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382
    Dim x As String
    Let x = "qwer"
    Debug.Print StrPtr(x) 'Address  479669860
    Call mySubByVal(x)    'Address  535500540
    Call mySubByVal(x)    'Address  476610300
    Call mySubByRef(x)    'Address  479669860
    x = "111111"
    Call mySubByVal(x)    'Address  625821292
    Call mySubByVal(x)    'Address  625752244
    Debug.Print StrPtr(x) 'Address  479669860
End Sub
Sub mySubByRef(ByRef c As String)
    Debug.Print StrPtr(c)
    c = "22"
End Sub
Sub mySubByVal(ByVal c As String)
    Debug.Print StrPtr(c)
    c = "22"
End Sub

My interpretation would be like this:

Debug.Print StrPtr(x) 'Address 479669860 - New address created for the main program.
Call mySubByVal(x) 'Address 535500540 - Data copied to new address for ByVal.
Call mySubByVal(x) 'Address 476610300 - Data copied to new address for ByVal.
Call mySubByRef(x) 'Address 479669860 - Original address used for ByRef.
x = "111111"
Call mySubByVal(x) 'Address 625821292 - Data length changed so new copy made.
Call mySubByVal(x) 'Address 625752244 - Data length changed so new copy made.
Debug.Print StrPtr(x) 'Address 479669860 - Original address unchanged.

If you run the code repeatedly, you can see that sometimes the same address is re-used. When it becomes free, because the Sub has completed, the address becomes available for re-use. So it is still making a copy but it may sometimes put it in the same place.

Running repeatedly shows that the addresses can be re-used for different things for different runs.

 

[xenou]

Just to get back to an earlier question:

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

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

Arrays are always passed ByRef so using the keyword ByVal or ByRef is the same in that case.


Note: haha and yet I am wrong - see next post by Mike Rickson.

 

[mikerickson]

Arrays are always passed ByRef so using the keyword ByVal or ByRef is the same in that case.

Consider this code.
The argument for PassByVal and PassByVal is a Variant and not an array, but they do pass an array. And the ByRef/ByVal acts as expected.
The compiler won't let me write ByVal anArray() As String, but VBA will pass an array ByVal.
(Or are we reaching the boundary of the term "work-around". )

Sub test1()
    Dim myArray() As String
    ReDim myArray(1 To 3)
    myArray(1) = " one": myArray(2) = "two": myArray(3) = "three"
    
    Call PassByRef(myArray)
    
    MsgBox UBound(myArray) & myArray(1) ' "2 first"
End Sub
Sub PassByRef(ByRef anArray As Variant)
    ReDim Preserve anArray(1 To 2)
    anArray(1) = " first"
End Sub

Sub test2()
    Dim myArray() As String
    ReDim myArray(1 To 3)
    myArray(1) = " one": myArray(2) = "two": myArray(3) = "three"
    
    Call PassByVal(myArray)
    
    MsgBox UBound(myArray) & myArray(1) ' "3 one"
End Sub
Sub PassByVal(ByVal anArray As Variant)
    ReDim anArray(1 To 2)
    anArray(1) = " first"
End Sub

 

[DocAElstein]

Hi xenou

Just to get back to an earlier question:
Arrays are always passed ByRef so using the keyword ByVal or ByRef is the same in that case.

_. I guess that ties up sort of with what i mentioned in Post #7. : VBA does not let me syntaxly pass an Array() to a Function( ByVal Array(), _____ ) - it always errors for me and says that a data field must be taken ByRef. I expect that is often not noticed, as i did not for a while, as I always just passed the Array by the default Function(Array(), _____ ) which i overlooked was default ByRef

But i think you can pass an Array by value..... in a work around sort of a way, as mikerickson showed.... and like i will maybe try to show in with my Function idea using a Variant to "house" the Array, and pass that ByVal.....

Alan

 

[DocAElstein]

Hi mikerickson

Consider this code.
The argument for PassByVal and PassByVal is a Variant and not an array, but they do pass an array. And the ByRef/ByVal acts as expected.
The compiler won't let me write ByVal anArray() As String, but VBA will pass an array ByVal. .......

_ . That was a nice demo and a better explanation of something similar to what I had also said..

..... I have found in practice that an Array can be passed to a Function either as An Array() ByRef or as A Variant ByVal........

I think this is my Function equivalent of what you were saying..

[color=darkgreen]'[/color]
[color=blue]Sub[/color] test3() 'Pass an Array to a Funktion ByRef
[color=blue]Dim[/color] myArray() [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]' ###[/color]
[color=blue]ReDim[/color] myArray(1 [color=blue]To[/color] 3)
myArray(1) = " one": myArray(2) = "two": myArray(3) = "three"

[color=blue]Dim[/color] myArray2() [color=blue]As[/color] [color=blue]Variant[/color]
[color=blue]Let[/color] myArray2() = FunkPassArrayByRef(myArray())

MsgBox [color=blue]UBound[/color](myArray) & myArray(1) [color=darkgreen]' "2 first" ' Changed because myArray() Taken  ( ByRef )[/color]
MsgBox [color=blue]UBound[/color](myArray2) & myArray2(1) [color=darkgreen]' "2 first" ' Result returned from Function[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
[color=blue]Function[/color] FunkPassArrayByRef(ByRef anArray() [color=blue]As[/color] [color=blue]Variant[/color]) [color=blue]As[/color] [color=blue]Variant[/color] '' ### Only variant can return a collection ( Array ). A data field is expected to be returned, which always is as Variant definiert
[color=blue]ReDim[/color] [color=blue]Preserve[/color] anArray(1 [color=blue]To[/color] 2)
anArray(1) = " first"
[color=blue]Let[/color] FunkPassArrayByRef = anArray()
[color=blue]End[/color] [color=blue]Function[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] test4() 'Pass an Array to a Funktion ByVal
[color=blue]Dim[/color] myArray() [color=blue]As[/color] [color=blue]String[/color]
[color=blue]ReDim[/color] myArray(1 [color=blue]To[/color] 3)
myArray(1) = " one": myArray(2) = "two": myArray(3) = "three"

[color=blue]Dim[/color] myArray2() [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] myArray2() = FunkPassArrayByVal(myArray())

MsgBox [color=blue]UBound[/color](myArray) & myArray(1) [color=darkgreen]' "3 one" ' Not changed because copy of myArray() Taken  ( ByVal )[/color]
MsgBox [color=blue]UBound[/color](myArray2) & myArray2(1) [color=darkgreen]' "2 first" ' Result returned from Function[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

[color=blue]Function[/color] FunkPassArrayByVal(ByVal VarWithMyArrayInIt [color=blue]As[/color] [color=blue]Variant[/color]) [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'VarWithMyArrayInIt can "house" An Array of String Type Elements[/color]
[color=blue]ReDim[/color] [color=blue]Preserve[/color] VarWithMyArrayInIt(1 [color=blue]To[/color] 2)
VarWithMyArrayInIt(1) = " first"
[color=blue]Let[/color] FunkPassArrayByVal = VarWithMyArrayInIt
[color=blue]End[/color] Function

Alan

 

[DocAElstein]

Hi RickXL

.......I think it is reusing space if it can. I tried this:
.......If you run the code repeatedly, you can see that sometimes the same address is re-used. When it becomes free, because the Sub has completed, the address becomes available for re-use. So it is still making a copy but it may sometimes put it in the same place.
Running repeatedly shows that the addresses can be re-used for different things for different runs.

Thanks for the extra input.
Having slept on it i see my first conclusion
_1a) Is this telling me..
_1a)(i) I can only make 1 copy ?
Was stupid , maybe..- i was missing the point again about the StrPtr(x) address – just because that is the same does not mean VBA is not making another copy – it is simply using the same address.

But this one seems very complicated. You are getting different results to me. But this may be explained by: “sometimes”. You used this word “sometimes” as well, in a slightly different context. The word seems to need to be extended to cover the same code working on a different run, different computer etc. etc....
_ ....But this could also be covered by your “...Running repeatedly shows that the addresses can be re-used for different things for different runs….“

Just a quick demo in that: your code from Post #12 and your results and mine:

[color=blue]Sub[/color] TestByValByRefStr_Post12() [color=darkgreen]'  RickXL  Post # 12[/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: Rick 479669860[/color] [color=red]; Alan 367477188[/color] [color=orange]; 118892172 [/color] [COLOR="#800080"]160373356[/COLOR]
40 [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address: Rick 535500540[/color] [color=red]; Alan 367478508[/color] [color=orange]; 118890516 [/color] [COLOR="#800080"]160373396[/COLOR]
50 [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address: Rick 476610300[/color] [color=red]; Alan 367478508[/color] [color=orange]; 118890516 [/color] [COLOR="#800080"]160373396[/COLOR]
60 [color=blue]Call[/color] mySubByRef(x)    [color=darkgreen]'Address: Rick 479669860[/color] [color=red]; Alan 367477188[/color] [color=orange]; 118892172 [/color] [COLOR="#800080"]160373356[/COLOR]
65 x = "111111"
70 [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address: Rick 625821292[/color] [color=red]; Alan 367477188[/color] [color=orange]; 118890124 [/color] [COLOR="#800080"]160373436[/COLOR]
75 [color=blue]Call[/color] mySubByVal(x)    [color=darkgreen]'Address: Rick 625752244[/color] [color=red]; Alan 367477188[/color] [color=orange]; 118890124 [/color] 1[COLOR="#800080"]60373436[/COLOR]
80 Debug.Print StrPtr(x) [color=darkgreen]'Address: Rick 479669860[/color] [color=red]; Alan 367478468[/color] [color=orange]; 118892172 [/color] [COLOR="#800080"]160373356[/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

Your results ; Mine XL2007 ; Mine XL2003 ; Mine XL 2010
( My different versions are on different computers. )


I get different results to you. The main difference being that the results are suggesting that my StrPtr(x) , that is to say the initial pointer address is in fact changing sometimes! This allows then the copy by a following ByVal call to use this address, which it sometimes does!
It looks like this one is virtually impossible to answer fully as clearly VBA is too inconsistent!
Note for example my first XL 2007 results.. the last result appeared to say that the StrPtr(x) address had changed. As I write I am looking at the results of this first run in my Immediate Window ( Ctrl G ). They are still there!! But repeating again my XL 2007 results then go on to tie up with my XL 2003 and XL 2010 results.
It would appear in order to be completely certain one must experiment using the specific version / computer one is using and specific code.
Passing on codes of this nature could clearly be very problematic!!

Even doing a Debug.Print appeared effect whether my StrPtr(x) , that is to say the initial pointer address is changed or not!!! Clearly there lies madness down the road of trying to figure out exactly what is going on in VBA here
Maybe these inconsistencies have been cleared up, or there is a change now since XL 2013 ( which you are using.....) Or maybe you have somehow a different computer / memory that is somehow allowing your StrPtr(x) , that is to say the initial pointer address, to remain constant.

The only consistent statements I seem to be able to say is:
The StrPtr(x) , that is to say the initial pointer address, assigned to a string variable itself can change. Clearly that appears to be happening by me
If that happens, the original address is “free” and often seems to be used by a following, or following, ByVal Call, or Calls
A whole multiple number of things can effect this,
_... even just testing for it that is to say doing the Debug.Print StrPtr(x) !!!!! Clearly this puts a question on the validity of using StrPtr(x) to get at the address – you may
Either:
_a).. change it as you try to determine what it is!!
( I see a parallel to the UsedRange phenomenon where the UsedRange Register is updated when for example you do a UsedRange.Rows
VBA .SpecialCells(xlCellTypeBlanks) – Only works on UsedRange? (Regardless of specified ra
)
If this is occurring, my experiments suggest it is occurring within the called ByRef Sub Procedure with a change in c ( that is to say x in c ) , mySubByRefcChange(x), but i expect the ther next explanation is the more likely
or
_b)..In the Sub routines that are called the actual change in StrPtr(x) occurs on leaving the Sub Procedure. Hence the Debug.Print within those Sub procedures will not catch those changes. A debug.Print immediately after will display the change

Further I would note that when reading up on getting Addresses of Variables in Visual Basic you often see disclaimers saying words to the effect “ take it as it is... no guarantees..!!...”




I note finally, that it is your
c=”22” ( in the Sub mySubByRef() )
Or
X=”111111” in the main program
That puts the spanner in the works for me and results in my StrPtr(x) possibly changing.
If i comment these out, then, as yet my StrPtr(x) remains constant. Maybe this makes some sense: For some reason my system needs to, or chooses to, change StrPtr(x) to accommodate the new string. It would appear not to be the case in your system or Excel Version.

( Note: c=”22” in the Sub mySubByVal() has no effected on StrPtr(x) – as expected, I think, as it is making no reference to x in the Call ByVal . However it can change the address given for the copy. )


The following is an attempt to summarise all the above Points:

Codes:

[color=darkgreen]'[/color]
[color=blue]Sub[/color] TestByValByRefStrWonky() '
10   [color=blue]Dim[/color] x [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'I am using this as a "pseudo" Global Variable. That is to say it has assigned a Pigeon Hole containing the Address where the String would be[/color]
11   [color=darkgreen]'   "get the Address of variable"..  maybe....  https://support.microsoft.com/en-us/kb/199824[/color]
12  Debug.Print " 12  Address main program-------StrPtr(x) " & StrPtr(x) [color=darkgreen]'I expect no Address yet[/color]
13  [color=blue]Let[/color] x = "qwer"
14  Debug.Print " 14  Address main program-------StrPtr(x) " & StrPtr(x) [color=darkgreen]' Bet it has an address now[/color]
15  [color=blue]Let[/color] x = ""
16  Debug.Print " 16  Address main program-------StrPtr(x) " & StrPtr(x) [color=darkgreen]' Might have another address now[/color]
17  [color=blue]Let[/color] x = vbNullString
18  Debug.Print " 18  Address main program-------StrPtr(x) " & StrPtr(x) [color=darkgreen]' Suspect it has no address again[/color]
20  [color=blue]Let[/color] x = "qwer"
30  Debug.Print " 30  Address main program-------StrPtr(x) " & StrPtr(x) [color=darkgreen]' Might have another address now, or the very first one as it is available[/color]
40  Debug.Print " 40  ";: [color=blue]Call[/color] mySubByVal(x)                           [color=darkgreen]'[/color]
50  Debug.Print " 50  ";: [color=blue]Call[/color] mySubByVal(x)
60  Debug.Print " 60  ";: [color=blue]Call[/color] mySubByValcChange(x)
70  Debug.Print " 70  ";: [color=blue]Call[/color] mySubByValcChange(x)
80  Debug.Print " 80  ";: [color=blue]Call[/color] mySubByVal(x)
90  Debug.Print " 90  Address main program-------StrPtr(x) " & StrPtr(x)
100 Debug.Print "100 Address main program------StrPtr(x) " & StrPtr(x)
110 Debug.Print "110 ";: [color=blue]Call[/color] mySubByRef(x)
120 Debug.Print "120 Address main program------StrPtr(x) " & StrPtr(x)
130 Debug.Print "130 ";: [color=blue]Call[/color] mySubByRefcChange(x)
140 Debug.Print "140 ";: [color=blue]Call[/color] mySubByRefcChange(x)
150 Debug.Print "150 Address main program------StrPtr(x) " & StrPtr(x)
160 Debug.Print "160 Address main program------StrPtr(x) " & StrPtr(x)
170 Debug.Print "170 ";: [color=blue]Call[/color] mySubByRefcChange(x)
180 Debug.Print "180 Address main program------StrPtr(x) " & StrPtr(x)
190 Debug.Print "190 Address main program------StrPtr(x) " & StrPtr(x)
200 Debug.Print "200 ";: [color=blue]Call[/color] mySubByRefcChange(x)
210 Debug.Print "210 ";: [color=blue]Call[/color] mySubByVal(x)
220 Debug.Print "220 Address main program------StrPtr(x) " & StrPtr(x)
230 Debug.Print "230 Address main program------StrPtr(x) " & StrPtr(x)
240 Debug.Print "240 ";: [color=blue]Call[/color] mySubByVal(x)
250 Debug.Print "250 ";: [color=blue]Call[/color] mySubByVal(x)
260 Debug.Print "260 ";: [color=blue]Call[/color] mySubByValcChange(x)
270 Debug.Print "270 ";: [color=blue]Call[/color] mySubByValcChange(x)
280 Debug.Print "280 ";: [color=blue]Call[/color] mySubByVal(x)
290
300 Debug.Print "300 ";: [color=blue]Call[/color] mySubByRef(x)
310 Debug.Print "310 Address main program------StrPtr(x) " & StrPtr(x)
320 Debug.Print "320  letting   x = 111111": [color=blue]Let[/color] x = "111111"
330 Debug.Print "330 Address main program------StrPtr(x) " & StrPtr(x)
340 Debug.Print "340 ";: [color=blue]Call[/color] mySubByRef(x)
350 Debug.Print "350 Address main program------StrPtr(x) " & StrPtr(x)
360 Debug.Print "360 ";: [color=blue]Call[/color] mySubByRefcChange(x)
370 Debug.Print "370 Address main program------StrPtr(x) " & StrPtr(x)
	
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
'
[color=blue]Sub[/color] mySubByRef(ByRef c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print "Address my[color=blue]Sub[/color]ByRef--------StrPtr(c) " & StrPtr(c)
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
Sub my[color=blue]Sub[/color]ByRefcChange(ByRef c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print "Address my[color=blue]Sub[/color]ByRefcChange StrPtr(c) " & StrPtr(c)
    c = "22"
[color=blue]End[/color] Sub
[color=darkgreen]'[/color]
[color=blue]Sub[/color] my[color=blue]Sub[/color]ByVal(ByVal c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print "Address mySubByVal--------StrPtr(c) " & StrPtr(c)
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
Sub mySubByValcChange(ByVal c [color=blue]As[/color] [color=blue]String[/color])
    Debug.Print "Address mySubByValcChange StrPtr(c) " & StrPtr(c)
    c = "22"
[color=blue]End[/color] Sub

12 Address main program-------StrPtr(x) 0 – from Dim x As String – No Address in Pigeon Hole
14 Address main program-------StrPtr(x) 367399700 – from Let x = "qwer" – First Available Address given
16 Address main program-------StrPtr(x) 367395380 – from Let x = "" - In my system a different Address given
18 Address main program-------StrPtr(x) 0 – from Let x = vbNullString – Back to no address
30 Address main program-------StrPtr(x) 367399700 – from Let x = "qwer" – back to an Address. ( The first is available so is used again)
40 Address mySubByVal--------StrPtr(c) 367395380 – from Call mySubByVal(x) – Chooses next available address for copy of x
50 Address mySubByVal--------StrPtr(c) 367398900 – from same Call mySubByVal(x) – My system chooses a new adresss, sometimes..
60 Address mySubByValcChange StrPtr(c) 367395380 – from Call mySubByValcChange(x) – data length has changes, my system has chosen to choose a new address..
70 Address mySubByValcChange StrPtr(c) 367395380 – from same Call mySubByValcChange(x) – my system has “settled down”, uses the same address this time for another copy.
80 Address mySubByVal--------StrPtr(c) 367395380 – from slightly different Call mySubByVal(x) – but my system stays calm and uses same address again for another copy.
90 Address main program-------StrPtr(x) 367399700 – from main program StrPtr(x) – Still using original address for “pseudo” global x
100 Address main program------StrPtr(x) 367399700 – from same main prog StrPtr(x) – no need to change x address, - has not changed
110 Address mySubByRef--------StrPtr(c) 367399700 – from Call mySubByRef(x) – I expect no change to x address caused by this as nothing is done to x which is taken in by Ref
120 Address main program------StrPtr(x) 367399700 - – from same main prog StrPtr(x) – just checking that no address change in x
130 Address mySubByRefcChange StrPtr(c) 367399700 – from Call mySubByRefcChange(x) – I actually expect x address has changed, but change occurred on leaving mySubByRefcChange(x) so the Debug.Print in it did not display it
140 Address mySubByRefcChange StrPtr(c) 367394820 - from same Call mySubByRefcChange(x) – i actually expect a change once again, but now the Debug.Print in from mySubByRefcChange(x) displays that from line 130.
150 Address main program------StrPtr(x) 367398900 – from main program StrPtr(x) – confirming now the change caused by Line 140
160 Address main program------StrPtr(x) 367398900 – from main program StrPtr(x) – just checking that Debug.Print does not change the address of x
170 Address mySubByRefcChange StrPtr(c) 367398900 - I expect a change again not shown by the Debug.Print in the mySubByRefcChange(x)
180 Address main program------StrPtr(x) 367399700 - from main program StrPtr(x) - change confirmed.
190 Address main program------StrPtr(x) 367399700 – no reason for change
200 Address mySubByRefcChange StrPtr(c) 367399700 - I expect a change again not shown by the Debug.Print in the mySubByRefcChange(x)

210 Address mySubByVal--------StrPtr(c) 367399700 – from Call mySubByVal(x) – At this poinjt my System has got a bit excited by all that has gone on with addresses and so by this call ByVal ( Copy Call ) it has looked for a new address. The reason it appears to have that of the last address of x is because actually the address of x has changed on leaving the last ByRef Call Sub. Threrefore the last address used for x is available now.
220 Address main program------StrPtr(x) 367394820 – from main program StrPtr(x) – confirming now the change caused by Line 200
230 Address main program------StrPtr(x) 367394820 - – no reason for change

240 Address mySubByVal--------StrPtr(c) 367395380 – in Lines 240 to .....
250 Address mySubByVal--------StrPtr(c) 367399700 - .....280, my system....
260 Address mySubByValcChange StrPtr(c) 367395380 - ... sometimes changes the....
270 Address mySubByValcChange StrPtr(c) 367395380 – address for the copy, but....
280 Address mySubByVal--------StrPtr(c) 367395380 – eventually settles down!!
300 Address mySubByRef--------StrPtr(c) 367394820 – from Call mySubByRef(x) which does not change c ( and so does not change x that is in c ) , I expect the address of x may not be changed...
310 Address main program------StrPtr(x) 367394820 – from main program StrPtr(x) - confirmed no change
320 letting x = 111111 – I expect this will change the address of x
330 Address main program------StrPtr(x) 367399700 - from main program StrPtr(x) - change confirmed

340 Address mySubByRef--------StrPtr(c) 367399700 - from Call mySubByRef(x) which does not change c ( and so does not change x that is in c ) , I expect the address of x may not be changed...

350 Address main program------StrPtr(x) 367399700 – from main program StrPtr(x) - confirmed no change
360 Address mySubByRefcChange StrPtr(c) 367399700 - from Call mySubByRefcChange(x) – I actually expect x address has changed, but change occurred on leaving mySubByRefcChange(x) so the Debug.Print in it did not display it

370 Address main program------StrPtr(x) 367478788 - from main program StrPtr(x) - change confirmed.
_ ...................................

_ ...I am not necessarily expecting anyone to wade through all the above, not immediately at least , but for future reference it may be useful...
_.... I may ask for some help in getting some results from my code from other users with other Excel versions and other computers, than i may think again and try to make a more concise, clear summary....in a day or two... when i have slept on it a few days!!!!



Alan
_...............................................

Last Quick EDIT: Actually the Debug.Print in mySubByRefcChange will give the right results, I just need to move it after the
c = "22"
which causes the Address in the Pigeon Hole to change

 

[RickXL]

Alan,

I have not looked at all the above but most of it can be explained.

First, the address is assigned in main code.
If you change the string then VBA will probably have to put it somewhere else so expect a new address.
When a Sub is finished the address used by the Sub will be freed so can be re-used.

It sometimes may not re-use the address straightaway. This may be a timing issue (i.e. it is freed up just a moment too late) or the VBA runtime program that runs the VBA code for us, may have done something else in the background that borrowed the address. (Please note, this is speculation on my part - I have no inside information on how it all works.)

 

[DocAElstein]

RickXL

......
It sometimes may not re-use the address straightaway. This may be a timing issue (i.e. it is freed up just a moment too late) or the VBA runtime program that runs the VBA code for us, may have done something else in the background that borrowed the address......)

Thanks, i had not thought of that... the speed thing especially, which could explain some of the inconsistencies..
Alan

 

[confusion123]

Consider this code.
The argument for PassByVal and PassByVal is a Variant and not an array, but they do pass an array. And the ByRef/ByVal acts as expected.
The compiler won't let me write ByVal anArray() As String, but VBA will pass an array ByVal.
(Or are we reaching the boundary of the term "work-around". )

Sub test1()
    Dim myArray() As String
    ReDim myArray(1 To 3)
    myArray(1) = " one": myArray(2) = "two": myArray(3) = "three"
    
    Call PassByRef(myArray)
    
    MsgBox UBound(myArray) & myArray(1) ' "2 first"
End Sub
Sub PassByRef(ByRef anArray As Variant)
    ReDim Preserve anArray(1 To 2)
    anArray(1) = " first"
End Sub

Sub test2()
    Dim myArray() As String
    ReDim myArray(1 To 3)
    myArray(1) = " one": myArray(2) = "two": myArray(3) = "three"
    
    Call PassByVal(myArray)
    
    MsgBox UBound(myArray) & myArray(1) ' "3 one"
End Sub
Sub PassByVal(ByVal anArray As Variant)
    ReDim anArray(1 To 2)
    anArray(1) = " first"
End Sub

Thanks to all for your help.

So if I understand you correctly, this statement is FALSE:

Arrays are always passed ByRef so using the keyword ByVal or ByRef is the same in that case.

ie arrays CAN be passed ByRef AND ByVal?