Quick question about my code that creates a 2D array

[spencer92291]

First of all, I am very new with coding and VBA is my first language. I have been teaching it to myself for 3 weeks now. I have a question in regards to the code below. In the part that says "Dim curShippingCharges(5, 9) as currency," does this mean that 6 columns and 10 rows are in the array, or that 6 arrays with 10 elements each are being created. If neither, what does the "5" and the "9" represent? Additionally, for "curShippingCharges(iCounter1, iCounter2) = ActiveCell.Offset(iCounter2, iCounter1).value", can you explain what the "iCounter1" and "iCounter2" mean exactly after "curShippingCharges", and also, what the "iCounter2" and the iCounter1" mean after "activecell.offset." I want to know intuitively the reasoning for all of this. At first I thought that the counters after "activecell.offset" corresponded to columns and then rows, but my friend just told me I could switch the code up to make it "rows then columns", which I don't get. If someone could just explain the two lines of code so I can understand it perfectly it would be greatly appreciated.

Sub TwoDimensionAray()
Dim curShippingCharges(5, 9) As Currency
Dim iCounter1 As Integer, iCounter2 As Integer
Worksheets("Practice").Activate
Range("G17").Activate
For iCounter1 = 0 To 5
For iCounter2 = 0 To 9
curShippingCharges(iCounter1, iCounter2) = ActiveCell.Offset(iCounter2, iCounter1).value
Next iCounter2
Next iCounter1
For iCounter1 = 0 To 5
For iCounter2 = 0 To 9
MsgBox (curShippingCharges(iCounter1, iCounter2))
Next iCounter2
Next iCounter1
End Sub

[Paul_Hossler]

Welcome to the Forum

Some comments that I hope answer your questions

'always a good idea since it means you MUST dim all valiables - check Help
Option Explicit

Sub TwoDimensionAray()
    '6 rows and 10 columns 0-6 and 0 - 10
    'you could use Dim curShippingCharges(1 to 5, 1 to 9) As Currency
    '   if you really wanted 1-5 and 1-9
    'or Option Base 1 at the top - check Help
    Dim curShippingCharges(5, 9) As Currency
    Dim iCounter1 As Integer, iCounter2 As Integer
    'no need normally to .Select or .Activate things
'    Worksheets("Practice").Activate
'    Range("G17").Activate
    
    For iCounter1 = 0 To 5
        For iCounter2 = 0 To 9
            'curShippingCharges is a 2 dimensional array so
            '   iCounter1 is the row number and
            '   iCounter2 is the column number
            'I assume you want the rows and columns transposed??? Offset(iCounter2, iCounter1) ??
            curShippingCharges(iCounter1, iCounter2) = Worksheets("Practice").Range("G17").Offset(iCounter2, iCounter1).Value
        Next iCounter2
    Next iCounter1

    'LBound() and UBound() -- check Help
    For iCounter1 = LBound(curShippingCharges, 1) To UBound(curShippingCharges, 1)
        For iCounter2 = LBound(curShippingCharges, 2) To UBound(curShippingCharges, 2)
            'I got tired of seeing this :-)
            'MsgBox (curShippingCharges(iCounter1, iCounter2))
        Next iCounter2
    Next iCounter1
End Sub

I put a breakpoint on the End and a Watch to see the inards of the data -- Check Help if you want to read up on using them

BTW, the [#] icon will add some [ CODE ] and [ / CODE ] tags that you can paste your code between to make it format nice

[spencer92291]

" For iCounter1 = 0 To 5
For iCounter2 = 0 To 9
'curShippingCharges is a 2 dimensional array so
' iCounter1 is the row number and
' iCounter2 is the column number
'I assume you want the rows and columns transposed??? Offset(iCounter2, iCounter1) ??
curShippingCharges(iCounter1, iCounter2) = Worksheets("Practice").Range("G17").Offset(iCounter2, iCounter1).Value
Next iCounter2
Next iCounter1 "

What I don't understand is how the "iCounter1" is the row number and "iCounter2" is the column number in "curshippingCharges(iCounter1, iCounter2)" because my command is working perfectly and iCounter1 is the column number and iCounter2 is actually the row number. And what to do you mean by transposed? I'm just trying to get the values in 10 rows of cells by 6 columns of cells into an array. I just really want to know why they are in the order they are in. Why is it "(iCounter1, iCounter2)" after curShippingCharges but "(iCounter2, iCounter1)" after offset?

[mancubus]

you can populate an array from range. in this case its lower bound is 1. also you dont need to dimension it.

like this:

Sub TwoDimensionAray()

    Dim curShippingCharges
    Dim c01 As String
    
    curShippingCharges = Worksheets("Practice").Range("G17:P22").Value
    
    For j = 1 To UBound(curShippingCharges)
        c01 = c01 & Join(Application.Index(curShippingCharges, j, 0), vbTab) & vbCr
        'this line is adopted from the code in http://www.vbaexpress.com/forum/showthread.php?50429-Macro-VBA-code-help
    Next
    
    MsgBox c01
    'display the range values in msgbox

End Sub

[spencer92291]

This is all good, but I'm paying for a subscription to Lynda.com and I'm watching videos that taught me the way I currently did. I really just want to understand the question I asked, I don't want another way to do arrays, I just want to know intuitively the meaning behind the one line of code I've been asking about. Thank you for showing me this way, but I really really really just want to understand this one line of code before it drives me crazy.

[mancubus]

curShippingCharges = Worksheets("Practice").Range("G17:P22").Value
assigns all values in G17:P22 to a variant variable (curShippingCharges). now you have 6 X 10 "matrix".

UBound(curShippingCharges)
returns the upper bound of the first dimension (you may call it "row" in this case), which is 6.

UBound(curShippingCharges, 2)
returns the upper bound of the second dimension (you may call it "column" in this case), which is 10.

Application.Index(curShippingCharges, j, 0)
returns a 1D array of the jth "row" from 2D array.

Application.Index(curShippingCharges, 0, j)
returns a 1D array of the jth "column" from 2D array.

Join(Application.Index(curShippingCharges, j, 0), vbTab)
joins all elements in 1D array by a tab character.

c01 = c01 & Join(Application.Index(sn, j), vbTab) & vbCr
at first c01 is null. to concatenate all rows of 2D array one after another we use c01 = c01 & .....
"& vbCr" adds a carriage return at the end of each "row" to display the text in multiple lines.



ps: blue lines are not from the code and are additional info.

[spencer92291]

Lol I hope I'm not coming off as rude, but once again, I'm asking for MY line of code to be explained lol. I just want an answer to my question because it's driving me crazy

[Paul_Hossler]

It looks like your macro is executing 100% the way it's coded. IF it does what you want, then it's fine

However ....

curShippingCharges is a 2D array with 6 rows (0 - 5) and 10 columns (0 - 9)

Your 2 loops go down the rows(0,1,2,3,4,5) and within each row goes across the columns (0,1,2,3,4,5,6,7,8,9) in that row

iCounter1 is the rows, and iCounter2 is the columns

Your .Offset(iCounter2, iCounter1).Value seems to be reversed from what you might intend. .Offset (#rows from the base cell, #columns from the base cell)

Sub TwoDimensionAray()
    Dim curShippingCharges(5, 9) As String
    Dim iCounter1 As Integer, iCounter2 As Integer
    
    For iCounter1 = 0 To 5
        For iCounter2 = 0 To 9
            'case 1
            curShippingCharges(iCounter1, iCounter2) = Worksheets("Practice").Range("G17").Offset(iCounter2, iCounter1).Value
            'case 2
            'curShippingCharges(iCounter1, iCounter2) = Worksheets("Practice").Range("G17").Offset(iCounter1, iCounter2).Value
        Next iCounter2
    Next iCounter1
    MsgBox "Top-Left = " & curShippingCharges(0, 0)
    MsgBox "Bottom-Right = " & curShippingCharges(5, 9)

End Sub

Capture.JPG

It depends if you want the blue+orange cells or the blue+green cells in the 6 rows by 10 col array.

[Paul_Hossler]

If you want, here's the xlsm I was using

[spencer92291]

"Your .Offset(iCounter2, iCounter1).Value seems to be reversed from what you might intend. .Offset (#rows from the base cell, #columns from the base cell)". In response to the previous quote, when I try this, it doesn't capture all the values. I even tried the code you commented out "offset(icaputre1, icapture2).value". So is it typically always supposed to be columns and then rows, but this code for some reason just happens to work and do rows then columns?

[Paul_Hossler]

It depends if you want the blue+orange cells or the blue+green cells in the 6 rows by 10 col array.

Either will work, but it depends on the data you want from the worksheet

Your original will get the blue+green or 10 rows and 6 columns

The alternative will get blue+orange or 6 rows and 10 columns

[Aflatoon]

Offset is always rows then columns. The transposing occurs because you are using the row variable from the array as the column variable for the offset, and vice versa:

curShippingCharges(iCounter1, iCounter2) = ActiveCell.Offset(iCounter2, iCounter1).value

Therefore, as Paul said, you are populating a 6 row and 10 column array from a 10 row and 6 column range.