Share via

Can't change value of array element. VBA Bug?

Anonymous
2011-10-31T21:41:56+00:00

Coding MS access VBA

I have a dictionary object  that is

key, value

string, 2D array (variant)

the 2D array is obtained by assigning an excel 2D range to a vba variant (variant = range.value)

I pass the dictionary to a sub

sub ModifyDict(ByRef DictToChange as Scripting.Dictionary)

Dim sk As Variant

Dim i As Integer, j As Integer

Dim mRows As Integer, mCols As Integer

For Each sk In DictToChange .Keys

        mRows = UBound(DictToChange (sk), 1)

        mCols = UBound(DictToChange (sk), 2)

        For i = 1 To mRows

            For j = 1 To mCols

                DictToChange (sk)(i, j) = SOME_CONSTANT

            Next j

        Next i

Next sk

End Sub

This function executes without error but fails to change any element of the  2D array

Any insights?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-11-03T06:11:54+00:00

Hi,

Based on my understanding, it can be considered a known issue in vba, but it does not give an error, since you can compile it success.

Best regards

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-11-02T10:35:50+00:00

    Thanks.

    I implemented a workaround after realizing vba won't do it. What's surprising is that vba does not give an error.

    Can this be considered a bug in vba then?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-11-02T08:29:37+00:00

    Hi,

    It seems that you cannot assign another value to the element of 2D array which stored in dictionary. You need to define a temp 2D array and assign changed value to this temp 2D array element. Please check the following code snippet.

    Sub ChangeVal()

    Set DictToChange = CreateObject("Scripting.dictionary")

    Dim Data1(1 To 2, 1 To 2)

    Dim Data2(1 To 2, 1 To 2)

    Dim Data3(1 To 2, 1 To 2)

    Data1(1, 1) = 1

    Data1(1, 2) = 2

    Data1(2, 1) = 3

    Data1(2, 2) = 4

    Data2(1, 1) = 1

    Data2(1, 2) = 2

    Data2(2, 1) = 3

    Data2(2, 2) = 4

    DictToChange.Add "a", Data1

    DictToChange.Add "b", Data2

    Dim sk As Variant

    Dim i As Integer, j As Integer

    Dim mRows As Integer, mCols As Integer

    For Each sk In DictToChange.Keys

            mRows = UBound(DictToChange(sk), 1)

            mCols = UBound(DictToChange(sk), 2)

            For i = 1 To mRows

                For j = 1 To mCols

                    Data3(i, j) = 0

                Next j

            Next i

                    DictToChange.Remove (sk)

                    DictToChange.Item(sk) = Data3

    Next sk

    End Sub

    Hope this helps

    Best regards

    Was this answer helpful?

    0 comments