Convert range to an array
I know a lot of threads regarding this topic already exist, but I still can't find a solution that works in this scenario. The following two subs keep giving me a "subscript out of range" error.
Sub test1()
Dim Arr() As Variant
Arr= Range("A1:A10")
MsgBox Arr(0)
End Sub
Sub test1()
Dim Arr As Variant
Arr= Range("A1:A10").Value
MsgBox Arr(0)
End Sub
excel vba
asked Nov 22 '18 at 16:03
Garry WGarry W
767
add a comment |
I know a lot of threads regarding this topic already exist, but I still can't find a solution that works in this scenario. The following two subs keep giving me a "subscript out of range" error.
Sub test1()
Dim Arr() As Variant
Arr= Range("A1:A10")
MsgBox Arr(0)
End Sub
Sub test1()
Dim Arr As Variant
Arr= Range("A1:A10").Value
MsgBox Arr(0)
End Sub
excel vba
asked Nov 22 '18 at 16:03
Garry WGarry W
767
add a comment |
I know a lot of threads regarding this topic already exist, but I still can't find a solution that works in this scenario. The following two subs keep giving me a "subscript out of range" error.
Sub test1()
Dim Arr() As Variant
Arr= Range("A1:A10")
MsgBox Arr(0)
End Sub
Sub test1()
Dim Arr As Variant
Arr= Range("A1:A10").Value
MsgBox Arr(0)
End Sub
excel vba
asked Nov 22 '18 at 16:03
Garry WGarry W
767
I know a lot of threads regarding this topic already exist, but I still can't find a solution that works in this scenario. The following two subs keep giving me a "subscript out of range" error.
Sub test1()
Dim Arr() As Variant
Arr= Range("A1:A10")
MsgBox Arr(0)
End Sub
Sub test1()
Dim Arr As Variant
Arr= Range("A1:A10").Value
MsgBox Arr(0)
End Sub
excel vba
excel vba
asked Nov 22 '18 at 16:03
Garry WGarry W
767
asked Nov 22 '18 at 16:03
Garry WGarry W
767
edited Nov 22 '18 at 16:39
Garry W
asked Nov 22 '18 at 16:03
Garry WGarry W
767
asked Nov 22 '18 at 16:03
Garry WGarry W
767
asked Nov 22 '18 at 16:03
Garry WGarry W
767
767
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
EDITED for clarity, in light of the comments below.
Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.
In your particular case, this would work:
Sub test1()
Dim Arr As Variant
Dim row As Long
Dim col As Long
row = 1
col = 1
Arr = Range("A1:A10").Value
MsgBox Arr(row, col)
End Sub
In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:
Sub test2()
Dim rng As Range
Dim Arr As Variant
Dim row As Integer
Dim col As Integer
row = 1
col = 1
Set rng = Range("A1:A1") '<== 1 cell only!
Arr = rng.Value
'Ensure we're dealing with an array even in this case.
If Not IsArray(Arr) Then
ReDim Arr(1 To 1, 1 To 1) As Variant
Arr(1, 1) = rng.Value
End If
MsgBox Arr(row, col)
End Sub
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
1
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but ifArris expanded toRange("A1:B10")thenArr(4,2)would return info from B4.
– Darren Bartrup-Cook
Nov 22 '18 at 16:10
3
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
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%2f53434664%2fconvert-range-to-an-array%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
EDITED for clarity, in light of the comments below.
Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.
In your particular case, this would work:
Sub test1()
Dim Arr As Variant
Dim row As Long
Dim col As Long
row = 1
col = 1
Arr = Range("A1:A10").Value
MsgBox Arr(row, col)
End Sub
In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:
Sub test2()
Dim rng As Range
Dim Arr As Variant
Dim row As Integer
Dim col As Integer
row = 1
col = 1
Set rng = Range("A1:A1") '<== 1 cell only!
Arr = rng.Value
'Ensure we're dealing with an array even in this case.
If Not IsArray(Arr) Then
ReDim Arr(1 To 1, 1 To 1) As Variant
Arr(1, 1) = rng.Value
End If
MsgBox Arr(row, col)
End Sub
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
1
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but ifArris expanded toRange("A1:B10")thenArr(4,2)would return info from B4.
– Darren Bartrup-Cook
Nov 22 '18 at 16:10
3
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
add a comment |
EDITED for clarity, in light of the comments below.
Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.
In your particular case, this would work:
Sub test1()
Dim Arr As Variant
Dim row As Long
Dim col As Long
row = 1
col = 1
Arr = Range("A1:A10").Value
MsgBox Arr(row, col)
End Sub
In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:
Sub test2()
Dim rng As Range
Dim Arr As Variant
Dim row As Integer
Dim col As Integer
row = 1
col = 1
Set rng = Range("A1:A1") '<== 1 cell only!
Arr = rng.Value
'Ensure we're dealing with an array even in this case.
If Not IsArray(Arr) Then
ReDim Arr(1 To 1, 1 To 1) As Variant
Arr(1, 1) = rng.Value
End If
MsgBox Arr(row, col)
End Sub
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
1
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but ifArris expanded toRange("A1:B10")thenArr(4,2)would return info from B4.
– Darren Bartrup-Cook
Nov 22 '18 at 16:10
3
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
add a comment |
EDITED for clarity, in light of the comments below.
Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.
In your particular case, this would work:
Sub test1()
Dim Arr As Variant
Dim row As Long
Dim col As Long
row = 1
col = 1
Arr = Range("A1:A10").Value
MsgBox Arr(row, col)
End Sub
In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:
Sub test2()
Dim rng As Range
Dim Arr As Variant
Dim row As Integer
Dim col As Integer
row = 1
col = 1
Set rng = Range("A1:A1") '<== 1 cell only!
Arr = rng.Value
'Ensure we're dealing with an array even in this case.
If Not IsArray(Arr) Then
ReDim Arr(1 To 1, 1 To 1) As Variant
Arr(1, 1) = rng.Value
End If
MsgBox Arr(row, col)
End Sub
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
EDITED for clarity, in light of the comments below.
Assigning a range's Value to a Variant variable will either result in the variable containing a one-based 2D array of Variants indexed by row and column (in this order), or containing the actual value of the range if it's a 1-cell range.
In your particular case, this would work:
Sub test1()
Dim Arr As Variant
Dim row As Long
Dim col As Long
row = 1
col = 1
Arr = Range("A1:A10").Value
MsgBox Arr(row, col)
End Sub
In a more general approach, if your downstream code expects to deal with an array, but you range has the possibility to cover a single cell, you can force an array even in such a situation, along those lines:
Sub test2()
Dim rng As Range
Dim Arr As Variant
Dim row As Integer
Dim col As Integer
row = 1
col = 1
Set rng = Range("A1:A1") '<== 1 cell only!
Arr = rng.Value
'Ensure we're dealing with an array even in this case.
If Not IsArray(Arr) Then
ReDim Arr(1 To 1, 1 To 1) As Variant
Arr(1, 1) = rng.Value
End If
MsgBox Arr(row, col)
End Sub
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
edited Nov 22 '18 at 16:30
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
answered Nov 22 '18 at 16:07
ExcelosaurusExcelosaurus
2,1321715
2,1321715
1
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but ifArris expanded toRange("A1:B10")thenArr(4,2)would return info from B4.
– Darren Bartrup-Cook
Nov 22 '18 at 16:10
3
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
add a comment |
1
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but ifArris expanded toRange("A1:B10")thenArr(4,2)would return info from B4.
– Darren Bartrup-Cook
Nov 22 '18 at 16:10
3
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
1
1
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but if
Arr is expanded to Range("A1:B10") then Arr(4,2) would return info from B4.– Darren Bartrup-Cook
Nov 22 '18 at 16:10
Worth adding that the first dimension is the row and the second is the column. On a one column range the second dimension must be 1, but if
Arr is expanded to Range("A1:B10") then Arr(4,2) would return info from B4.– Darren Bartrup-Cook
Nov 22 '18 at 16:10
3
3
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
Fair enough; will edit. Another point is that a 1 cell range's value won't be an array at all.
– Excelosaurus
Nov 22 '18 at 16:13
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%2f53434664%2fconvert-range-to-an-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
