Passing array or class, ByVal or ByRef


Active Member
Jul 27, 2014
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.
Last edited:


Banned user
May 24, 2014
Hi Pgc...
You can also use a local array in your routine. ....
I almost missed that. Another alternative Thanks.
You might be on dodgy grounds though... sounds a bit like “..... removal of the entire issue …. can only be performed by altering the called routine.....” LOL ;) ... But i like it..

I guess it is a Sort of an Incognito way of doing it. I guess I would call that a "ByORefviginalIncognitByVal" way of doing it, for want of anything more obscure, obscene or un - appropriate. !? ... It looks as though it is doing a ByORefiginal but “hidden” in the Function is the copied from the ORefiginal Array taken in which then has any changes done on it rather than having changes done on the ORefiginal Array , which is more usual.

I did a demo Main Code ( test7() ) and a required demo Called Procedure ( Function )
I was a bit concerned that on leaving the Function there may still be some attempt by VBA to do some sort of updating to the ORefiginal Array. Presumably this does no harm as it effectively updates it to the state it already is. It is probably splitting hairs but i guess this could have speed / memory implications in a real situation. And possibly some pointing to Pigeon Hole and addresses there-in changes which are unnecessary and maybe occasionally undesired.
So I added the
vbNullChangeArrayChange anArray()
statement - applied to the Array brought in ByORefiginal, anArray() ( or the original myArray() – as it is ByRef ) . This statement prevents writing to anArray() and freezes the pointers used for its elements locations. This would result in raising an Error in the attempt to write on leaving the Function. So i have some Error handling to deal with that.
Finally after the return in the Main Function I use the opposite Statement
vbNullChange anArray()
to reinstate writing to the original Array ( the dafault situation for VBA Arrays ),
Further I “unplug” the Error handler appropriately after this.
Then as with all the test codes the lines are added to Message out the state of the Original Array and returned Array

( Also given in the Uploaded File in last post )

[COLOR=blue]Sub[/COLOR] test7() 'Pass an Array to a Funktion ByRef . Apply vbNullChangeArrayChange
[COLOR=darkgreen]'    "ByORefviginalIncognitByVal"[/COLOR]
[COLOR=blue]Dim[/COLOR] myArray() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/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]String[/COLOR]
[COLOR=blue]Let[/COLOR] myArray2() = FunkPassArrayByRefvbNullChangeArrayChange(myArray())
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] 0 [COLOR=darkgreen]'On FueRror GoTo 0 '  As the exception was not raised this line is sufficient to "unplug" the FueRror Handler[/COLOR]
vbArrayChange 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]' "3 one" ' Not Changed - It was sent  ByRef but there a local Copy was made and any workings done on that local Array. Any possible changes leaking or slipping in by the return of the function are prohibited by the vbNullChangeArrayChange Statement[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
[COLOR=blue]Function[/COLOR] FunkPassArrayByRefvbNullChangeArrayChange(ByRef anArray() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR] ''
[COLOR=blue]Dim[/COLOR] PgcLocalArray() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] [COLOR=darkgreen]' pgc   You can also use a local array in your routine. This simulates passing the array ByVal.[/COLOR]
[COLOR=blue]Let[/COLOR] PgcLocalArray() = anArray()
[COLOR=blue]ReDim[/COLOR] [COLOR=blue]Preserve[/COLOR] PgcLocalArray(1 [COLOR=blue]To[/COLOR] 2)
PgcLocalArray(1) = " first"
[COLOR=blue]Let[/COLOR] FunkPassArray[COLOR=blue]ByRef[/COLOR]vbNullChangeArrayChange = PgcLocalArray()
vbNullChangeArrayChange anArray() [COLOR=darkgreen]' Statement to prevents writing to anArray() and freezing of the pointers used for its elements locations.[/COLOR]
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]Resume[/COLOR] [COLOR=blue]Next[/COLOR] [COLOR=darkgreen]'On FueRror Resume Next' Error handler to handle Error Predicted in next ( exiting Function ) Line.....[/COLOR]
[COLOR=blue]End[/COLOR] Function [COLOR=darkgreen]' ....Will have raised exceptional VBA FueRror Situation, as updates to anArray() could not be made, but code continues due to On FueRror Resume Next, which instructed this continuation and prevented the Exception being raised[/COLOR]
[COLOR=blue]Sub[/COLOR] vbArrayChange([COLOR=blue]ByRef[/COLOR] AORefviginal() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR])
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
[COLOR=blue]Sub[/COLOR] vbNullChangeArrayChange(ByRef AORefviginal() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR])
[COLOR=blue]End[/COLOR] Sub

Last edited:
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Latest member

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