excel vba how to change array content by referencing array name

Question

I am trying to make change to the array named arraySrc in the following manner:

Dim arraySrc(0 To 1) As Integer

arraySrc(0) = 1
arraySrc(1) = 2
Dim arrayTmp

arrayTmp = arraySrc
arrayTmp(0) = 0
arrayTmp(1) = 1

Actually, I want to use one name as a handle to make change to multiple arrays individually by case, for example, I have a function to return the array name, I want to then set the returned array name to arrayTmp, then make change to arrayTmp directly using the format arrayTmp(0)=0 eg, hoping to make change to the original array

However, by using variant doesn't work. Can anybody please let me know how to implement this?

Dim arrayTmp is variant type bye default as you have not declared explicitly the data type. What is not working? What you are doing is changing the values in arrayTmp. — bonCodigo
– bonCodigo, Commented Feb 4, 2013 at 20:35

bonCodigo · Accepted Answer · 2013-02-04 21:01:52Z

If you want to change values in arraySrc you need to refer to the indices of that array.

e.g. which you have already done.

arraySrc(0) = 1
arraySrc(1) = 2

Just because you copy arraySrc to arrayTmp, the latter is not going to keep the reference to arraySrc.

However this is possible if you had passed a reference of arraySrc via a funtion's parameter.

e.g.

Option Explicit

Sub myArrays()
Dim arraySrc(0 To 1) As Integer
    arraySrc(0) = 1
    arraySrc(1) = 2
    '-- the referencing
    arrayReference arraySrc
End Sub

Function arrayReference(ByRef varr() As Integer) As Variant
    If Not IsVarArrayEmpty(varr) Then
        varr(0) = 0
        varr(1) = 1
    End If
    arrayReference = varr
End Function

'--check for empty array - additional
Function IsVarArrayEmpty(anArray As Variant) As Boolean
    Dim i As Integer

    On Error Resume Next
        i = UBound(anArray, 1)
    If Err.Number = 0 Then
        IsVarArrayEmpty = False
    Else
        IsVarArrayEmpty = True
    End If
End Function

enter image description here

Doug Glancy · Accepted Answer · 2013-02-04 21:03:46Z

2

Do you mean something like this? The ByRef argument means that the source array, passed as a parameter will also be changed:

Sub test()
Dim arraySrc(0 To 1) As Integer
arraySrc(0) = 1
arraySrc(1) = 2
PassByRef arraySrc
Debug.Print arraySrc(0)
Debug.Print arraySrc(1)
End Sub

Sub PassByRef(ByRef arrayTmp() As Integer)
arrayTmp(0) = 0
arrayTmp(1) = 1
End Sub

edited Feb 4, 2013 at 21:03

answered Feb 4, 2013 at 20:40

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

4 Comments

Max Over a year ago

Actually, I want to use one name as a handle to make change to multiple arrays individually by case, for example, I need a function to return the array name, then set the return value to arrayTmp, then make change to arrayTmp, hoping to make change to the original array

Doug Glancy Over a year ago

I'm still not totally clear from your comment what the inputs and outputs would be. Maybe you should edit your original question to better reflect what you are trying to do. I believe you can use the concept @boncodigo and I have outlined to do it, but can't be sure.

Max Over a year ago

It is like I need a pointer to the array. I only want to manipulate array content through the pointer, no matter which array the pointer points to. Is this clear?

bonCodigo Over a year ago

@Max C Pointers in VBA? You can pass around a reference to the integer array to functions via the ByRef keyword,which is exactly on the above answers. The AddressOf operator will give you the integer address of a variable, but VBA is unable to do anything with it; it's only useful for passing to a DLL.

Collectives™ on Stack Overflow

excel vba how to change array content by referencing array name

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related