Use variables to access 3 dimensional array elements in Excel VBA

Question

I want to read the value of array's element based on the value of variables:

Public Array1(3, 3) As String
Public Var1 As Integer
Public Var2 As Integer
...
Var1 = 1
Var2 = 1
Array1(1, 1) = "The_string"

I would expect MsgBox Array1(Var1, Var2) to display The_string but it doesn't. I get empty message. If I try MsgBox Array1(1, 1) everything works fine. When I try things like .Value I get errors.

What am I doing wrong? How can I access those array entries based on the value of my variables?

@SJR No idea why but after restarting whole Excel everything suddenly started to work. After changing some things it stopped to work again but then again i've restarted Excel to make it work again. Could it be i've found a bug in Excel 2013? — InnerBushman
– InnerBushman, Commented Nov 25, 2016 at 13:59
Who knows, though would be a very strange bug if it were one. — SJR
– SJR, Commented Nov 25, 2016 at 15:25
Maybe it is strange, bu aren't most of bugs strange? The problem is persistent. Things work after restart but break after one itteration of the code. It's almost impossible to debug MY code (and i'm not proficient in VBA) with this bug in Excel. I need to restart the application each time i try to use this array with variables. — InnerBushman
– InnerBushman, Commented Nov 28, 2016 at 7:17
It can't be very widespread because it's so fundamental we would all be affected. I think you need to post all your code for any of us to be able to check properly. — SJR
– SJR, Commented Nov 28, 2016 at 12:20

Doug Coats · Accepted Answer · 2016-11-25 13:58:14Z

1

Youre just accessing them incorrectly. Let me give you a fee examples of things that do work.

 rCell.Offset(0, 1).Value = A(ArCount, 1)

THe above states 1 space of the rCell = A(x,1). Notice how I dont say .Value?

         For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        j = CLng(((UBound(MyArray) - i) * Rnd) + i)
            If i <> j Then
                Temp = MyArray(i)
                MyArray(i) = MyArray(j)
                MyArray(j) = Temp
            End If
    Next i

This code is a basic random sorting of an array. Good example of array declaration and assignment of variables form that state.

A(count, 5) = arrResults(z, 5)

This one is pretty loaded and hard to describe w/o context. Just another example.

BlockEnd = GradCFArray(j + 1, k)

Another example.

I guess the issue youre having is accessing array contents. An array itself isnt a "Value" but it holds a value. Thats maybe why the .value isnt working for you?

answered Nov 25, 2016 at 13:58

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

InnerBushman Over a year ago

Thanks for your reply but the problem turned out to be with Excel program. After restarting it my code works as expected!

halfer · Accepted Answer · 2016-11-26 11:52:24Z

0

(Posted on behalf of the OP).

Turns out this code works. All I did was restart Excel! Possibly a bug.

answered Nov 26, 2016 at 11:52

community wiki

halfer

Collectives™ on Stack Overflow

Use variables to access 3 dimensional array elements in Excel VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related