how to redim preserve jagged array (arrays in array) in VBA?
0
I have an array of arrays (not a 2D array) in EXCEL VBA and I need to add elements to the inner arrays.
e.g.:
Option Explicit
Sub ArrayofArrays()
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
End Sub
I could now access the inner element by:
debug.print OuterArray(0)(0)
which prints me "Foo"
But how can I extend the array inside OuterArray?
The last line only adds an empty element to the OuterArray:
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
But what I want is this:
¦___OuterArray(0)
¦_____________OuterArray(0)(0): "Foo"
¦_____________OuterArray(0)(1): "Bar"
Thanks!
arrays excel vba jagged-arrays
asked Jan 18 at 12:23
cekarcekar
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
I have an array of arrays (not a 2D array) in EXCEL VBA and I need to add elements to the inner arrays.
e.g.:
Option Explicit
Sub ArrayofArrays()
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
End Sub
I could now access the inner element by:
debug.print OuterArray(0)(0)
which prints me "Foo"
But how can I extend the array inside OuterArray?
The last line only adds an empty element to the OuterArray:
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
But what I want is this:
¦___OuterArray(0)
¦_____________OuterArray(0)(0): "Foo"
¦_____________OuterArray(0)(1): "Bar"
Thanks!
arrays excel vba jagged-arrays
asked Jan 18 at 12:23
cekarcekar
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Why aren't you re-dimmingInnerArray?
– SJR
Jan 18 at 12:27
You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense?
– skulden
Jan 18 at 12:38
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray
– cekar
Jan 18 at 13:21
add a comment |
0
0
0
I have an array of arrays (not a 2D array) in EXCEL VBA and I need to add elements to the inner arrays.
e.g.:
Option Explicit
Sub ArrayofArrays()
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
End Sub
I could now access the inner element by:
debug.print OuterArray(0)(0)
which prints me "Foo"
But how can I extend the array inside OuterArray?
The last line only adds an empty element to the OuterArray:
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
But what I want is this:
¦___OuterArray(0)
¦_____________OuterArray(0)(0): "Foo"
¦_____________OuterArray(0)(1): "Bar"
Thanks!
arrays excel vba jagged-arrays
asked Jan 18 at 12:23
cekarcekar
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have an array of arrays (not a 2D array) in EXCEL VBA and I need to add elements to the inner arrays.
e.g.:
Option Explicit
Sub ArrayofArrays()
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
End Sub
I could now access the inner element by:
debug.print OuterArray(0)(0)
which prints me "Foo"
But how can I extend the array inside OuterArray?
The last line only adds an empty element to the OuterArray:
ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
But what I want is this:
¦___OuterArray(0)
¦_____________OuterArray(0)(0): "Foo"
¦_____________OuterArray(0)(1): "Bar"
Thanks!
arrays excel vba jagged-arrays
arrays excel vba jagged-arrays
asked Jan 18 at 12:23
cekarcekar
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 12:23
cekarcekar
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 12:23
cekarcekar
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 12:23
cekarcekar
285
asked Jan 18 at 12:23
cekarcekar
285
285
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
cekar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Why aren't you re-dimmingInnerArray?
– SJR
Jan 18 at 12:27
You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense?
– skulden
Jan 18 at 12:38
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray
– cekar
Jan 18 at 13:21
add a comment |
1
Why aren't you re-dimmingInnerArray?
– SJR
Jan 18 at 12:27
You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense?
– skulden
Jan 18 at 12:38
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray
– cekar
Jan 18 at 13:21
1
1
Why aren't you re-dimming
InnerArray?– SJR
Jan 18 at 12:27
Why aren't you re-dimming
InnerArray?– SJR
Jan 18 at 12:27
You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense?
– skulden
Jan 18 at 12:38
You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense?
– skulden
Jan 18 at 12:38
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray
– cekar
Jan 18 at 13:21
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray
– cekar
Jan 18 at 13:21
add a comment |
1 Answer
1
active
oldest
votes
0
VBA won't allow you to directly ReDim the inner array however, you can achieve it quite easily by using an intermediary helper array (in this example named tmp)
Option Explicit
Sub ArrayofArrays()
Dim tmp As Variant
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
tmp = OuterArray(0)
ReDim Preserve tmp(LBound(tmp) To UBound(tmp) + 1)
OuterArray(0) = tmp
Erase tmp
OuterArray(0)(1) = "Bar"
Debug.Print OuterArray(0)(1)
End Sub
answered Jan 18 at 12:37
TomTom
5,79211741
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
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
});
}
});
cekar is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2f54253970%2fhow-to-redim-preserve-jagged-array-arrays-in-array-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
VBA won't allow you to directly ReDim the inner array however, you can achieve it quite easily by using an intermediary helper array (in this example named tmp)
Option Explicit
Sub ArrayofArrays()
Dim tmp As Variant
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
tmp = OuterArray(0)
ReDim Preserve tmp(LBound(tmp) To UBound(tmp) + 1)
OuterArray(0) = tmp
Erase tmp
OuterArray(0)(1) = "Bar"
Debug.Print OuterArray(0)(1)
End Sub
answered Jan 18 at 12:37
TomTom
5,79211741
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
add a comment |
0
VBA won't allow you to directly ReDim the inner array however, you can achieve it quite easily by using an intermediary helper array (in this example named tmp)
Option Explicit
Sub ArrayofArrays()
Dim tmp As Variant
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
tmp = OuterArray(0)
ReDim Preserve tmp(LBound(tmp) To UBound(tmp) + 1)
OuterArray(0) = tmp
Erase tmp
OuterArray(0)(1) = "Bar"
Debug.Print OuterArray(0)(1)
End Sub
answered Jan 18 at 12:37
TomTom
5,79211741
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
add a comment |
0
0
0
VBA won't allow you to directly ReDim the inner array however, you can achieve it quite easily by using an intermediary helper array (in this example named tmp)
Option Explicit
Sub ArrayofArrays()
Dim tmp As Variant
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
tmp = OuterArray(0)
ReDim Preserve tmp(LBound(tmp) To UBound(tmp) + 1)
OuterArray(0) = tmp
Erase tmp
OuterArray(0)(1) = "Bar"
Debug.Print OuterArray(0)(1)
End Sub
answered Jan 18 at 12:37
TomTom
5,79211741
VBA won't allow you to directly ReDim the inner array however, you can achieve it quite easily by using an intermediary helper array (in this example named tmp)
Option Explicit
Sub ArrayofArrays()
Dim tmp As Variant
Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)
Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)
InnerArray(0) = "Foo"
OuterArray(0) = InnerArray
tmp = OuterArray(0)
ReDim Preserve tmp(LBound(tmp) To UBound(tmp) + 1)
OuterArray(0) = tmp
Erase tmp
OuterArray(0)(1) = "Bar"
Debug.Print OuterArray(0)(1)
End Sub
answered Jan 18 at 12:37
TomTom
5,79211741
edited Jan 18 at 12:43
answered Jan 18 at 12:37
TomTom
5,79211741
answered Jan 18 at 12:37
TomTom
5,79211741
answered Jan 18 at 12:37
TomTom
5,79211741
5,79211741
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
add a comment |
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
Thanks for your answer. It worked perfectly!
– cekar
Jan 18 at 13:19
add a comment |
cekar is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
cekar is a new contributor. Be nice, and check out our Code of Conduct.
cekar is a new contributor. Be nice, and check out our Code of Conduct.
cekar is a new contributor. Be nice, and check out our Code of Conduct.
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.
draft saved
draft discarded
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%2f54253970%2fhow-to-redim-preserve-jagged-array-arrays-in-array-in-vba%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
1
Why aren't you re-dimming
InnerArray?– SJR
Jan 18 at 12:27
You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense?
– skulden
Jan 18 at 12:38
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray
– cekar
Jan 18 at 13:21