ReDim Preserve 2-dimensional Variant ArrayCreate ArrayList from arrayHow do I check if an array includes an object in JavaScript?How to append something to an array?PHP: Delete an element from an arrayHow to insert an item into an array at a specific index (JavaScript)?How do I empty an array in JavaScript?Loop through an array in JavaScriptHow to check if an object is an array?How do I remove a particular element from an array in JavaScript?For-each over an array in JavaScript?
Why did the Mercure fail?
Multiplicative persistence
Start making guitar arrangements
Creepy dinosaur pc game identification
Creature in Shazam mid-credits scene?
Yosemite Fire Rings - What to Expect?
Is there a name for this algorithm to calculate the concentration of a mixture of two solutions containing the same solute?
Which one is correct as adjective “protruding” or “protruded”?
Pre-mixing cryogenic fuels and using only one fuel tank
Closed-form expression for certain product
How to explain what's wrong with this application of the chain rule?
Non-trope happy ending?
why `nmap 192.168.1.97` returns less services than `nmap 127.0.0.1`?
Travelling outside the UK without a passport
What if a revenant (monster) gains fire resistance?
Store Credit Card Information in Password Manager?
Open a doc from terminal, but not by its name
Is it possible to put a rectangle as background in the author section?
Are the IPv6 address space and IPv4 address space completely disjoint?
Can I sign legal documents with a smiley face?
GraphicsGrid with a Label for each Column and Row
Drawing ramified coverings with tikz
"Spoil" vs "Ruin"
What is the evidence for the "tyranny of the majority problem" in a direct democracy context?
ReDim Preserve 2-dimensional Variant Array
Create ArrayList from arrayHow do I check if an array includes an object in JavaScript?How to append something to an array?PHP: Delete an element from an arrayHow to insert an item into an array at a specific index (JavaScript)?How do I empty an array in JavaScript?Loop through an array in JavaScriptHow to check if an object is an array?How do I remove a particular element from an array in JavaScript?For-each over an array in JavaScript?
This seems to be the millionth question on this topic, but searching didn't help me.
I'm trying to resize the last dimension of a two-dimensional array, but always get an "index out of bounds" error in the ReDim Preserve line.
Dim arrCurrentDataset As Variant
For i = 0 To UBound(fileNames) - 1
strPath = fileNames(i)
Set wkbSource = Workbooks.Open(Filename:=strPath, UpdateLinks:=xlUpdateLinksNever, ReadOnly:=True, Notify:=True)
Set wksSource = wkbSource.Sheets(1)
Dim lngRows As Long
lngRows = wksSource.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'Store dataset to array and afterwards increase second dimension by 2 -> create space to add Materialart and Beschaffungsart
arrCurrentDataset = wksSource.Range("A4:I" & lngRows).value
ReDim Preserve arrCurrentDataset(UBound(arrCurrentDataset, 1), UBound(arrCurrentDataset, 2) + 2)
'...
next i
Is there a problem with my declaration? Do I implicitly try to change the data type?
Thanks a lot for your help!
arrays excel vba
asked 2 days ago
cr44shcr44sh
206
add a comment |
This seems to be the millionth question on this topic, but searching didn't help me.
I'm trying to resize the last dimension of a two-dimensional array, but always get an "index out of bounds" error in the ReDim Preserve line.
Dim arrCurrentDataset As Variant
For i = 0 To UBound(fileNames) - 1
strPath = fileNames(i)
Set wkbSource = Workbooks.Open(Filename:=strPath, UpdateLinks:=xlUpdateLinksNever, ReadOnly:=True, Notify:=True)
Set wksSource = wkbSource.Sheets(1)
Dim lngRows As Long
lngRows = wksSource.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'Store dataset to array and afterwards increase second dimension by 2 -> create space to add Materialart and Beschaffungsart
arrCurrentDataset = wksSource.Range("A4:I" & lngRows).value
ReDim Preserve arrCurrentDataset(UBound(arrCurrentDataset, 1), UBound(arrCurrentDataset, 2) + 2)
'...
next i
Is there a problem with my declaration? Do I implicitly try to change the data type?
Thanks a lot for your help!
arrays excel vba
asked 2 days ago
cr44shcr44sh
206
When filling an array from range, lower bound becomes 1. When you ReDim you should then use ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2), otherwise you are trying to redim first dimension from "1 to n", to "0 to n"
– drgs
2 days ago
add a comment |
This seems to be the millionth question on this topic, but searching didn't help me.
I'm trying to resize the last dimension of a two-dimensional array, but always get an "index out of bounds" error in the ReDim Preserve line.
Dim arrCurrentDataset As Variant
For i = 0 To UBound(fileNames) - 1
strPath = fileNames(i)
Set wkbSource = Workbooks.Open(Filename:=strPath, UpdateLinks:=xlUpdateLinksNever, ReadOnly:=True, Notify:=True)
Set wksSource = wkbSource.Sheets(1)
Dim lngRows As Long
lngRows = wksSource.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'Store dataset to array and afterwards increase second dimension by 2 -> create space to add Materialart and Beschaffungsart
arrCurrentDataset = wksSource.Range("A4:I" & lngRows).value
ReDim Preserve arrCurrentDataset(UBound(arrCurrentDataset, 1), UBound(arrCurrentDataset, 2) + 2)
'...
next i
Is there a problem with my declaration? Do I implicitly try to change the data type?
Thanks a lot for your help!
arrays excel vba
asked 2 days ago
cr44shcr44sh
206
This seems to be the millionth question on this topic, but searching didn't help me.
I'm trying to resize the last dimension of a two-dimensional array, but always get an "index out of bounds" error in the ReDim Preserve line.
Dim arrCurrentDataset As Variant
For i = 0 To UBound(fileNames) - 1
strPath = fileNames(i)
Set wkbSource = Workbooks.Open(Filename:=strPath, UpdateLinks:=xlUpdateLinksNever, ReadOnly:=True, Notify:=True)
Set wksSource = wkbSource.Sheets(1)
Dim lngRows As Long
lngRows = wksSource.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'Store dataset to array and afterwards increase second dimension by 2 -> create space to add Materialart and Beschaffungsart
arrCurrentDataset = wksSource.Range("A4:I" & lngRows).value
ReDim Preserve arrCurrentDataset(UBound(arrCurrentDataset, 1), UBound(arrCurrentDataset, 2) + 2)
'...
next i
Is there a problem with my declaration? Do I implicitly try to change the data type?
Thanks a lot for your help!
arrays excel vba
arrays excel vba
asked 2 days ago
cr44shcr44sh
206
asked 2 days ago
cr44shcr44sh
206
asked 2 days ago
cr44shcr44sh
206
asked 2 days ago
cr44shcr44sh
206
asked 2 days ago
cr44shcr44sh
206
206
When filling an array from range, lower bound becomes 1. When you ReDim you should then use ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2), otherwise you are trying to redim first dimension from "1 to n", to "0 to n"
– drgs
2 days ago
add a comment |
When filling an array from range, lower bound becomes 1. When you ReDim you should then use ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2), otherwise you are trying to redim first dimension from "1 to n", to "0 to n"
– drgs
2 days ago
When filling an array from range, lower bound becomes 1. When you ReDim you should then use ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2), otherwise you are trying to redim first dimension from "1 to n", to "0 to n"
– drgs
2 days ago
When filling an array from range, lower bound becomes 1. When you ReDim you should then use ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2), otherwise you are trying to redim first dimension from "1 to n", to "0 to n"
– drgs
2 days ago
add a comment |
2 Answers
2
active
oldest
votes
Your array dimensions are 1 based, but the default is 0 (unless you have an Option Base 1 statement) so you have to specify that in the Redim:
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
answered 2 days ago
RoryRory
24.6k51725
2
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
add a comment |
When you declare a variable as Variant, it can be anything. It may be an array, or an 2-dimensional array, but it could also by an object or an string or something else.
So when you want it to be an array, you have to use a Redim-command. But when you hit your Redim the first time, you don't have an array yet, so Ubound will fail (it's only valid for arrays) - and as it is not an array, there is also nothing to preserve.
You can solve this by either initialize the array before your real work starts, or add a check if the variable contains an array
If isArray(arrCurrentDataset) Then
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
Else
' You have to make up your mind about the initial size of the array
ReDim arrCurrentDataset(1 to 10, 1 to 10)
End If
answered 2 days ago
FunThomasFunThomas
5,2611626
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%2f55281411%2fredim-preserve-2-dimensional-variant-array%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
Your array dimensions are 1 based, but the default is 0 (unless you have an Option Base 1 statement) so you have to specify that in the Redim:
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
answered 2 days ago
RoryRory
24.6k51725
2
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
add a comment |
Your array dimensions are 1 based, but the default is 0 (unless you have an Option Base 1 statement) so you have to specify that in the Redim:
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
answered 2 days ago
RoryRory
24.6k51725
2
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
add a comment |
Your array dimensions are 1 based, but the default is 0 (unless you have an Option Base 1 statement) so you have to specify that in the Redim:
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
answered 2 days ago
RoryRory
24.6k51725
Your array dimensions are 1 based, but the default is 0 (unless you have an Option Base 1 statement) so you have to specify that in the Redim:
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
answered 2 days ago
RoryRory
24.6k51725
answered 2 days ago
RoryRory
24.6k51725
answered 2 days ago
RoryRory
24.6k51725
answered 2 days ago
RoryRory
24.6k51725
24.6k51725
2
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
add a comment |
2
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
2
2
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
I like to use LBound to Ubound to avoid problems.
– user11217663
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
Excellent! Thank you Rory and drgs.
– cr44sh
2 days ago
add a comment |
When you declare a variable as Variant, it can be anything. It may be an array, or an 2-dimensional array, but it could also by an object or an string or something else.
So when you want it to be an array, you have to use a Redim-command. But when you hit your Redim the first time, you don't have an array yet, so Ubound will fail (it's only valid for arrays) - and as it is not an array, there is also nothing to preserve.
You can solve this by either initialize the array before your real work starts, or add a check if the variable contains an array
If isArray(arrCurrentDataset) Then
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
Else
' You have to make up your mind about the initial size of the array
ReDim arrCurrentDataset(1 to 10, 1 to 10)
End If
answered 2 days ago
FunThomasFunThomas
5,2611626
add a comment |
When you declare a variable as Variant, it can be anything. It may be an array, or an 2-dimensional array, but it could also by an object or an string or something else.
So when you want it to be an array, you have to use a Redim-command. But when you hit your Redim the first time, you don't have an array yet, so Ubound will fail (it's only valid for arrays) - and as it is not an array, there is also nothing to preserve.
You can solve this by either initialize the array before your real work starts, or add a check if the variable contains an array
If isArray(arrCurrentDataset) Then
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
Else
' You have to make up your mind about the initial size of the array
ReDim arrCurrentDataset(1 to 10, 1 to 10)
End If
answered 2 days ago
FunThomasFunThomas
5,2611626
add a comment |
When you declare a variable as Variant, it can be anything. It may be an array, or an 2-dimensional array, but it could also by an object or an string or something else.
So when you want it to be an array, you have to use a Redim-command. But when you hit your Redim the first time, you don't have an array yet, so Ubound will fail (it's only valid for arrays) - and as it is not an array, there is also nothing to preserve.
You can solve this by either initialize the array before your real work starts, or add a check if the variable contains an array
If isArray(arrCurrentDataset) Then
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
Else
' You have to make up your mind about the initial size of the array
ReDim arrCurrentDataset(1 to 10, 1 to 10)
End If
answered 2 days ago
FunThomasFunThomas
5,2611626
When you declare a variable as Variant, it can be anything. It may be an array, or an 2-dimensional array, but it could also by an object or an string or something else.
So when you want it to be an array, you have to use a Redim-command. But when you hit your Redim the first time, you don't have an array yet, so Ubound will fail (it's only valid for arrays) - and as it is not an array, there is also nothing to preserve.
You can solve this by either initialize the array before your real work starts, or add a check if the variable contains an array
If isArray(arrCurrentDataset) Then
ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
Else
' You have to make up your mind about the initial size of the array
ReDim arrCurrentDataset(1 to 10, 1 to 10)
End If
answered 2 days ago
FunThomasFunThomas
5,2611626
answered 2 days ago
FunThomasFunThomas
5,2611626
answered 2 days ago
FunThomasFunThomas
5,2611626
answered 2 days ago
FunThomasFunThomas
5,2611626
5,2611626
add a comment |
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.
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%2f55281411%2fredim-preserve-2-dimensional-variant-array%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
When filling an array from range, lower bound becomes 1. When you ReDim you should then use ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2), otherwise you are trying to redim first dimension from "1 to n", to "0 to n"
– drgs
2 days ago