Use For each Statement to go through two-dimensional Array - per row / not per column
First of all, I don´t want to use nested For-loops, because I read that these would become slower with bigger amount of data (n can be up to 10k). So, now, I have an array arrData(n,2)
, while n
is variable, and I want to go through this array per row, using the for each statement.
So here is my code. For simplification I inserted an (2,2)-array:
Sub test()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(0, 1) = 0
arrData(0, 2) = 0
arrData(1, 0) = 1
arrData(1, 1) = 1
arrData(1, 2) = 1
arrData(2, 0) = 2
arrData(2, 1) = 2
arrData(2, 2) = 2
For Each Element In arrData
MsgBox Element
Next Element
End Sub
I get 012012012, but I want to get 000111222.
arrays vba for-loop multidimensional-array each
add a comment |
First of all, I don´t want to use nested For-loops, because I read that these would become slower with bigger amount of data (n can be up to 10k). So, now, I have an array arrData(n,2)
, while n
is variable, and I want to go through this array per row, using the for each statement.
So here is my code. For simplification I inserted an (2,2)-array:
Sub test()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(0, 1) = 0
arrData(0, 2) = 0
arrData(1, 0) = 1
arrData(1, 1) = 1
arrData(1, 2) = 1
arrData(2, 0) = 2
arrData(2, 1) = 2
arrData(2, 2) = 2
For Each Element In arrData
MsgBox Element
Next Element
End Sub
I get 012012012, but I want to get 000111222.
arrays vba for-loop multidimensional-array each
2
Using a nested loop is no problem.
– Tim Williams
Nov 11 at 21:52
1
Use For loops to iterate arrays
– Mathieu Guindon
Nov 11 at 22:00
add a comment |
First of all, I don´t want to use nested For-loops, because I read that these would become slower with bigger amount of data (n can be up to 10k). So, now, I have an array arrData(n,2)
, while n
is variable, and I want to go through this array per row, using the for each statement.
So here is my code. For simplification I inserted an (2,2)-array:
Sub test()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(0, 1) = 0
arrData(0, 2) = 0
arrData(1, 0) = 1
arrData(1, 1) = 1
arrData(1, 2) = 1
arrData(2, 0) = 2
arrData(2, 1) = 2
arrData(2, 2) = 2
For Each Element In arrData
MsgBox Element
Next Element
End Sub
I get 012012012, but I want to get 000111222.
arrays vba for-loop multidimensional-array each
First of all, I don´t want to use nested For-loops, because I read that these would become slower with bigger amount of data (n can be up to 10k). So, now, I have an array arrData(n,2)
, while n
is variable, and I want to go through this array per row, using the for each statement.
So here is my code. For simplification I inserted an (2,2)-array:
Sub test()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(0, 1) = 0
arrData(0, 2) = 0
arrData(1, 0) = 1
arrData(1, 1) = 1
arrData(1, 2) = 1
arrData(2, 0) = 2
arrData(2, 1) = 2
arrData(2, 2) = 2
For Each Element In arrData
MsgBox Element
Next Element
End Sub
I get 012012012, but I want to get 000111222.
arrays vba for-loop multidimensional-array each
arrays vba for-loop multidimensional-array each
asked Nov 11 at 21:02
T. P.
145
145
2
Using a nested loop is no problem.
– Tim Williams
Nov 11 at 21:52
1
Use For loops to iterate arrays
– Mathieu Guindon
Nov 11 at 22:00
add a comment |
2
Using a nested loop is no problem.
– Tim Williams
Nov 11 at 21:52
1
Use For loops to iterate arrays
– Mathieu Guindon
Nov 11 at 22:00
2
2
Using a nested loop is no problem.
– Tim Williams
Nov 11 at 21:52
Using a nested loop is no problem.
– Tim Williams
Nov 11 at 21:52
1
1
Use For loops to iterate arrays
– Mathieu Guindon
Nov 11 at 22:00
Use For loops to iterate arrays
– Mathieu Guindon
Nov 11 at 22:00
add a comment |
2 Answers
2
active
oldest
votes
First, let's address this misconception:
I read that these would become slower with bigger amount of data (n can be up to 10k)
This is simply not true. Multi-dimensional arrays are only "slower" than single dimensional arrays because the memory address of the indexer has to be calculated (more on this later). What you are most likely referring to is the computational complexity of a nested loop - the number of iterations increases as the product of bounds of each loop. You have a fixed number of elements, so this will be the same regardless of how they are accessed. If you want to perform operation o
on every member of a two dimensional array, you will perform that computation b1 * b2 times. Period.
Now, to explain the result that your sample code gives, lets look at how VBA lays out an array in memory. I'll simplify this a bit by only looking at the data area (there is also a SAFEARRAY structure that holds meta-information about the array, but that's not really germane). An array with a single dimension is laid out as a contiguous area of memory, and VBA maintains a pointer to the first element. For example, a one dimensional array of Long would look like this (Dim foo(4) As Long
):
The SAFEARRAY structure holds a pointer to 'Element 0', and when you access this in your code, it multiplies the indexer by the length of the element type in bytes, and then returns the value at that memory address. So if the first element were at memory address 0x0000 and you accessed foo(2)
, it would multiply 2 by 4 (the length of a Long
, add that to 0x0000, and give you the 4 bytes beginning at 0x0008.
Basically, A + (L * E1)
, where A
is the base address, L
is the element length, and E1
is the element you're requesting.
The second dimension adds N
copies of this layout in memory where N
is the number of elements in the second dimension. So, the array in your example code is laid out like this (Dim foo(2, 2) As Long
) :
VBA indexes this the same as the one dimensional array, except for the second dimension, it adds the product of the indexer for the second dimension and the total length of a complete first dimension to the address calculation for the element in dimension one.
Basically, A + (L * E1) + (L * B1 * E2)
, where B1
is the element count of the first dimension and E2
is the index for the second. So, if you were accessing foo(1, 1)
from a base address of 0x0000, it would be 0 + (4 * 1) + (4 * 3 * 1)
, or
0x0010.
Quick aside - this is why you can't Redim Preserve
anything other than the top dimension of an array - that's the only case where it's a simple memory allocation and copy.
So, pivoting to your example, your values are stored like this this in memory:
When you use For Each
, VBA's array iterator simply gives you back each element in memory order, so you get 012012012. For your specific example, you can get them back in 000111222 order by transposing it - what you are calling the "row", is actually the first dimension in your example:
Sub Example()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(1, 0) = 0
arrData(2, 0) = 0
arrData(0, 1) = 1
arrData(1, 1) = 1
arrData(2, 1) = 1
arrData(0, 2) = 2
arrData(1, 2) = 2
arrData(2, 2) = 2
For Each Element In arrData
Debug.Print Element
Next Element
End Sub
This lays the array out like so in memory:
That said, there's more overhead to the For Each
loop than there is for a simple For
loop because VBA has to use an array enumerator and push _NewEnum
calls onto the stack. While you might see a tiny performance increase in the indexing because it is only adding an offset to the memory address instead of performing the longer calculation each time, this is more than outweighed by repeatedly pushing and popping the call stack. So, long story short, just nest the loops:
Dim outer As Long
Dim inner As Long
For outer = LBound(arrData, 1) To UBound(arrData, 1)
For inner = LBound(arrData, 2) To UBound(arrData, 2)
Debug.Print arrData(outer, inner)
Next
Next
In your case, you would "transpose" the array by swapping the inner and outer loops.
NOTE: I am not using "row" in the context of Excel (although it would be the first rank), and by "transpose", I do not mean using Excel's Transpose
function - that would have worse performance than either alternative.
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
add a comment |
I am a little confused. I don't think using a nested structure to loop rows and columns will prove that significant a performance hit. If you have lots of data you have lots of data.
You need to loop rows and columns in order to access all items in an efficient manner i.e. nested loop structure. I don't know of a more efficient manner to access each item from a 2D array.
Where you can improve your code is to use a For Loop which is faster than a For Each when dealing with arrays.
1
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253206%2fuse-for-each-statement-to-go-through-two-dimensional-array-per-row-not-per-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
First, let's address this misconception:
I read that these would become slower with bigger amount of data (n can be up to 10k)
This is simply not true. Multi-dimensional arrays are only "slower" than single dimensional arrays because the memory address of the indexer has to be calculated (more on this later). What you are most likely referring to is the computational complexity of a nested loop - the number of iterations increases as the product of bounds of each loop. You have a fixed number of elements, so this will be the same regardless of how they are accessed. If you want to perform operation o
on every member of a two dimensional array, you will perform that computation b1 * b2 times. Period.
Now, to explain the result that your sample code gives, lets look at how VBA lays out an array in memory. I'll simplify this a bit by only looking at the data area (there is also a SAFEARRAY structure that holds meta-information about the array, but that's not really germane). An array with a single dimension is laid out as a contiguous area of memory, and VBA maintains a pointer to the first element. For example, a one dimensional array of Long would look like this (Dim foo(4) As Long
):
The SAFEARRAY structure holds a pointer to 'Element 0', and when you access this in your code, it multiplies the indexer by the length of the element type in bytes, and then returns the value at that memory address. So if the first element were at memory address 0x0000 and you accessed foo(2)
, it would multiply 2 by 4 (the length of a Long
, add that to 0x0000, and give you the 4 bytes beginning at 0x0008.
Basically, A + (L * E1)
, where A
is the base address, L
is the element length, and E1
is the element you're requesting.
The second dimension adds N
copies of this layout in memory where N
is the number of elements in the second dimension. So, the array in your example code is laid out like this (Dim foo(2, 2) As Long
) :
VBA indexes this the same as the one dimensional array, except for the second dimension, it adds the product of the indexer for the second dimension and the total length of a complete first dimension to the address calculation for the element in dimension one.
Basically, A + (L * E1) + (L * B1 * E2)
, where B1
is the element count of the first dimension and E2
is the index for the second. So, if you were accessing foo(1, 1)
from a base address of 0x0000, it would be 0 + (4 * 1) + (4 * 3 * 1)
, or
0x0010.
Quick aside - this is why you can't Redim Preserve
anything other than the top dimension of an array - that's the only case where it's a simple memory allocation and copy.
So, pivoting to your example, your values are stored like this this in memory:
When you use For Each
, VBA's array iterator simply gives you back each element in memory order, so you get 012012012. For your specific example, you can get them back in 000111222 order by transposing it - what you are calling the "row", is actually the first dimension in your example:
Sub Example()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(1, 0) = 0
arrData(2, 0) = 0
arrData(0, 1) = 1
arrData(1, 1) = 1
arrData(2, 1) = 1
arrData(0, 2) = 2
arrData(1, 2) = 2
arrData(2, 2) = 2
For Each Element In arrData
Debug.Print Element
Next Element
End Sub
This lays the array out like so in memory:
That said, there's more overhead to the For Each
loop than there is for a simple For
loop because VBA has to use an array enumerator and push _NewEnum
calls onto the stack. While you might see a tiny performance increase in the indexing because it is only adding an offset to the memory address instead of performing the longer calculation each time, this is more than outweighed by repeatedly pushing and popping the call stack. So, long story short, just nest the loops:
Dim outer As Long
Dim inner As Long
For outer = LBound(arrData, 1) To UBound(arrData, 1)
For inner = LBound(arrData, 2) To UBound(arrData, 2)
Debug.Print arrData(outer, inner)
Next
Next
In your case, you would "transpose" the array by swapping the inner and outer loops.
NOTE: I am not using "row" in the context of Excel (although it would be the first rank), and by "transpose", I do not mean using Excel's Transpose
function - that would have worse performance than either alternative.
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
add a comment |
First, let's address this misconception:
I read that these would become slower with bigger amount of data (n can be up to 10k)
This is simply not true. Multi-dimensional arrays are only "slower" than single dimensional arrays because the memory address of the indexer has to be calculated (more on this later). What you are most likely referring to is the computational complexity of a nested loop - the number of iterations increases as the product of bounds of each loop. You have a fixed number of elements, so this will be the same regardless of how they are accessed. If you want to perform operation o
on every member of a two dimensional array, you will perform that computation b1 * b2 times. Period.
Now, to explain the result that your sample code gives, lets look at how VBA lays out an array in memory. I'll simplify this a bit by only looking at the data area (there is also a SAFEARRAY structure that holds meta-information about the array, but that's not really germane). An array with a single dimension is laid out as a contiguous area of memory, and VBA maintains a pointer to the first element. For example, a one dimensional array of Long would look like this (Dim foo(4) As Long
):
The SAFEARRAY structure holds a pointer to 'Element 0', and when you access this in your code, it multiplies the indexer by the length of the element type in bytes, and then returns the value at that memory address. So if the first element were at memory address 0x0000 and you accessed foo(2)
, it would multiply 2 by 4 (the length of a Long
, add that to 0x0000, and give you the 4 bytes beginning at 0x0008.
Basically, A + (L * E1)
, where A
is the base address, L
is the element length, and E1
is the element you're requesting.
The second dimension adds N
copies of this layout in memory where N
is the number of elements in the second dimension. So, the array in your example code is laid out like this (Dim foo(2, 2) As Long
) :
VBA indexes this the same as the one dimensional array, except for the second dimension, it adds the product of the indexer for the second dimension and the total length of a complete first dimension to the address calculation for the element in dimension one.
Basically, A + (L * E1) + (L * B1 * E2)
, where B1
is the element count of the first dimension and E2
is the index for the second. So, if you were accessing foo(1, 1)
from a base address of 0x0000, it would be 0 + (4 * 1) + (4 * 3 * 1)
, or
0x0010.
Quick aside - this is why you can't Redim Preserve
anything other than the top dimension of an array - that's the only case where it's a simple memory allocation and copy.
So, pivoting to your example, your values are stored like this this in memory:
When you use For Each
, VBA's array iterator simply gives you back each element in memory order, so you get 012012012. For your specific example, you can get them back in 000111222 order by transposing it - what you are calling the "row", is actually the first dimension in your example:
Sub Example()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(1, 0) = 0
arrData(2, 0) = 0
arrData(0, 1) = 1
arrData(1, 1) = 1
arrData(2, 1) = 1
arrData(0, 2) = 2
arrData(1, 2) = 2
arrData(2, 2) = 2
For Each Element In arrData
Debug.Print Element
Next Element
End Sub
This lays the array out like so in memory:
That said, there's more overhead to the For Each
loop than there is for a simple For
loop because VBA has to use an array enumerator and push _NewEnum
calls onto the stack. While you might see a tiny performance increase in the indexing because it is only adding an offset to the memory address instead of performing the longer calculation each time, this is more than outweighed by repeatedly pushing and popping the call stack. So, long story short, just nest the loops:
Dim outer As Long
Dim inner As Long
For outer = LBound(arrData, 1) To UBound(arrData, 1)
For inner = LBound(arrData, 2) To UBound(arrData, 2)
Debug.Print arrData(outer, inner)
Next
Next
In your case, you would "transpose" the array by swapping the inner and outer loops.
NOTE: I am not using "row" in the context of Excel (although it would be the first rank), and by "transpose", I do not mean using Excel's Transpose
function - that would have worse performance than either alternative.
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
add a comment |
First, let's address this misconception:
I read that these would become slower with bigger amount of data (n can be up to 10k)
This is simply not true. Multi-dimensional arrays are only "slower" than single dimensional arrays because the memory address of the indexer has to be calculated (more on this later). What you are most likely referring to is the computational complexity of a nested loop - the number of iterations increases as the product of bounds of each loop. You have a fixed number of elements, so this will be the same regardless of how they are accessed. If you want to perform operation o
on every member of a two dimensional array, you will perform that computation b1 * b2 times. Period.
Now, to explain the result that your sample code gives, lets look at how VBA lays out an array in memory. I'll simplify this a bit by only looking at the data area (there is also a SAFEARRAY structure that holds meta-information about the array, but that's not really germane). An array with a single dimension is laid out as a contiguous area of memory, and VBA maintains a pointer to the first element. For example, a one dimensional array of Long would look like this (Dim foo(4) As Long
):
The SAFEARRAY structure holds a pointer to 'Element 0', and when you access this in your code, it multiplies the indexer by the length of the element type in bytes, and then returns the value at that memory address. So if the first element were at memory address 0x0000 and you accessed foo(2)
, it would multiply 2 by 4 (the length of a Long
, add that to 0x0000, and give you the 4 bytes beginning at 0x0008.
Basically, A + (L * E1)
, where A
is the base address, L
is the element length, and E1
is the element you're requesting.
The second dimension adds N
copies of this layout in memory where N
is the number of elements in the second dimension. So, the array in your example code is laid out like this (Dim foo(2, 2) As Long
) :
VBA indexes this the same as the one dimensional array, except for the second dimension, it adds the product of the indexer for the second dimension and the total length of a complete first dimension to the address calculation for the element in dimension one.
Basically, A + (L * E1) + (L * B1 * E2)
, where B1
is the element count of the first dimension and E2
is the index for the second. So, if you were accessing foo(1, 1)
from a base address of 0x0000, it would be 0 + (4 * 1) + (4 * 3 * 1)
, or
0x0010.
Quick aside - this is why you can't Redim Preserve
anything other than the top dimension of an array - that's the only case where it's a simple memory allocation and copy.
So, pivoting to your example, your values are stored like this this in memory:
When you use For Each
, VBA's array iterator simply gives you back each element in memory order, so you get 012012012. For your specific example, you can get them back in 000111222 order by transposing it - what you are calling the "row", is actually the first dimension in your example:
Sub Example()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(1, 0) = 0
arrData(2, 0) = 0
arrData(0, 1) = 1
arrData(1, 1) = 1
arrData(2, 1) = 1
arrData(0, 2) = 2
arrData(1, 2) = 2
arrData(2, 2) = 2
For Each Element In arrData
Debug.Print Element
Next Element
End Sub
This lays the array out like so in memory:
That said, there's more overhead to the For Each
loop than there is for a simple For
loop because VBA has to use an array enumerator and push _NewEnum
calls onto the stack. While you might see a tiny performance increase in the indexing because it is only adding an offset to the memory address instead of performing the longer calculation each time, this is more than outweighed by repeatedly pushing and popping the call stack. So, long story short, just nest the loops:
Dim outer As Long
Dim inner As Long
For outer = LBound(arrData, 1) To UBound(arrData, 1)
For inner = LBound(arrData, 2) To UBound(arrData, 2)
Debug.Print arrData(outer, inner)
Next
Next
In your case, you would "transpose" the array by swapping the inner and outer loops.
NOTE: I am not using "row" in the context of Excel (although it would be the first rank), and by "transpose", I do not mean using Excel's Transpose
function - that would have worse performance than either alternative.
First, let's address this misconception:
I read that these would become slower with bigger amount of data (n can be up to 10k)
This is simply not true. Multi-dimensional arrays are only "slower" than single dimensional arrays because the memory address of the indexer has to be calculated (more on this later). What you are most likely referring to is the computational complexity of a nested loop - the number of iterations increases as the product of bounds of each loop. You have a fixed number of elements, so this will be the same regardless of how they are accessed. If you want to perform operation o
on every member of a two dimensional array, you will perform that computation b1 * b2 times. Period.
Now, to explain the result that your sample code gives, lets look at how VBA lays out an array in memory. I'll simplify this a bit by only looking at the data area (there is also a SAFEARRAY structure that holds meta-information about the array, but that's not really germane). An array with a single dimension is laid out as a contiguous area of memory, and VBA maintains a pointer to the first element. For example, a one dimensional array of Long would look like this (Dim foo(4) As Long
):
The SAFEARRAY structure holds a pointer to 'Element 0', and when you access this in your code, it multiplies the indexer by the length of the element type in bytes, and then returns the value at that memory address. So if the first element were at memory address 0x0000 and you accessed foo(2)
, it would multiply 2 by 4 (the length of a Long
, add that to 0x0000, and give you the 4 bytes beginning at 0x0008.
Basically, A + (L * E1)
, where A
is the base address, L
is the element length, and E1
is the element you're requesting.
The second dimension adds N
copies of this layout in memory where N
is the number of elements in the second dimension. So, the array in your example code is laid out like this (Dim foo(2, 2) As Long
) :
VBA indexes this the same as the one dimensional array, except for the second dimension, it adds the product of the indexer for the second dimension and the total length of a complete first dimension to the address calculation for the element in dimension one.
Basically, A + (L * E1) + (L * B1 * E2)
, where B1
is the element count of the first dimension and E2
is the index for the second. So, if you were accessing foo(1, 1)
from a base address of 0x0000, it would be 0 + (4 * 1) + (4 * 3 * 1)
, or
0x0010.
Quick aside - this is why you can't Redim Preserve
anything other than the top dimension of an array - that's the only case where it's a simple memory allocation and copy.
So, pivoting to your example, your values are stored like this this in memory:
When you use For Each
, VBA's array iterator simply gives you back each element in memory order, so you get 012012012. For your specific example, you can get them back in 000111222 order by transposing it - what you are calling the "row", is actually the first dimension in your example:
Sub Example()
Dim arrData(2, 2) As Variant
arrData(0, 0) = 0
arrData(1, 0) = 0
arrData(2, 0) = 0
arrData(0, 1) = 1
arrData(1, 1) = 1
arrData(2, 1) = 1
arrData(0, 2) = 2
arrData(1, 2) = 2
arrData(2, 2) = 2
For Each Element In arrData
Debug.Print Element
Next Element
End Sub
This lays the array out like so in memory:
That said, there's more overhead to the For Each
loop than there is for a simple For
loop because VBA has to use an array enumerator and push _NewEnum
calls onto the stack. While you might see a tiny performance increase in the indexing because it is only adding an offset to the memory address instead of performing the longer calculation each time, this is more than outweighed by repeatedly pushing and popping the call stack. So, long story short, just nest the loops:
Dim outer As Long
Dim inner As Long
For outer = LBound(arrData, 1) To UBound(arrData, 1)
For inner = LBound(arrData, 2) To UBound(arrData, 2)
Debug.Print arrData(outer, inner)
Next
Next
In your case, you would "transpose" the array by swapping the inner and outer loops.
NOTE: I am not using "row" in the context of Excel (although it would be the first rank), and by "transpose", I do not mean using Excel's Transpose
function - that would have worse performance than either alternative.
answered Nov 11 at 23:59
Comintern
18.4k42354
18.4k42354
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
add a comment |
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Really interesting. Thank you. +
– QHarr
Nov 12 at 3:49
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
Thanks a lot for the extensive explanation
– T. P.
Nov 12 at 16:48
add a comment |
I am a little confused. I don't think using a nested structure to loop rows and columns will prove that significant a performance hit. If you have lots of data you have lots of data.
You need to loop rows and columns in order to access all items in an efficient manner i.e. nested loop structure. I don't know of a more efficient manner to access each item from a 2D array.
Where you can improve your code is to use a For Loop which is faster than a For Each when dealing with arrays.
1
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
add a comment |
I am a little confused. I don't think using a nested structure to loop rows and columns will prove that significant a performance hit. If you have lots of data you have lots of data.
You need to loop rows and columns in order to access all items in an efficient manner i.e. nested loop structure. I don't know of a more efficient manner to access each item from a 2D array.
Where you can improve your code is to use a For Loop which is faster than a For Each when dealing with arrays.
1
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
add a comment |
I am a little confused. I don't think using a nested structure to loop rows and columns will prove that significant a performance hit. If you have lots of data you have lots of data.
You need to loop rows and columns in order to access all items in an efficient manner i.e. nested loop structure. I don't know of a more efficient manner to access each item from a 2D array.
Where you can improve your code is to use a For Loop which is faster than a For Each when dealing with arrays.
I am a little confused. I don't think using a nested structure to loop rows and columns will prove that significant a performance hit. If you have lots of data you have lots of data.
You need to loop rows and columns in order to access all items in an efficient manner i.e. nested loop structure. I don't know of a more efficient manner to access each item from a 2D array.
Where you can improve your code is to use a For Loop which is faster than a For Each when dealing with arrays.
answered Nov 11 at 21:14
QHarr
29.8k81841
29.8k81841
1
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
add a comment |
1
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
1
1
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
Thanks a lot for the explanation
– T. P.
Nov 12 at 16:49
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253206%2fuse-for-each-statement-to-go-through-two-dimensional-array-per-row-not-per-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Using a nested loop is no problem.
– Tim Williams
Nov 11 at 21:52
1
Use For loops to iterate arrays
– Mathieu Guindon
Nov 11 at 22:00