How to access populated array in another sub in VBA, w/o arguments?
1
I am so confused, and endless googling is so far defying me.
I have a sub that collected unique values in Column C into an array. Now that the array is created, I need to use it in a different sub so I can loop through the values.
I have tried passing it as an argument but then I can't figure out how to run the new sub that has arguments, i.e.:
Sub useArray(ByRef varArr() As String)
- how in the world do I run useArray? And useArray should be the main sub, anyways, so I am just confused about how I could then run the main sub and use this array variable that has already been defined/populated.
I tried using my sub that gets the unique values as a function, but it doesn't pass the values in the array back to the main sub. At the end of the function AND in the main sub I have:
MsgBox varArr(1)
In the function, it returns the first value. Back in the sub it returns an error.
An assistance would save my sanity!
arrays excel vba excel-vba
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
asked Nov 14 '18 at 22:42
LBJLBJ
63
add a comment |
1
I am so confused, and endless googling is so far defying me.
I have a sub that collected unique values in Column C into an array. Now that the array is created, I need to use it in a different sub so I can loop through the values.
I have tried passing it as an argument but then I can't figure out how to run the new sub that has arguments, i.e.:
Sub useArray(ByRef varArr() As String)
- how in the world do I run useArray? And useArray should be the main sub, anyways, so I am just confused about how I could then run the main sub and use this array variable that has already been defined/populated.
I tried using my sub that gets the unique values as a function, but it doesn't pass the values in the array back to the main sub. At the end of the function AND in the main sub I have:
MsgBox varArr(1)
In the function, it returns the first value. Back in the sub it returns an error.
An assistance would save my sanity!
arrays excel vba excel-vba
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
asked Nov 14 '18 at 22:42
LBJLBJ
63
You could turn your sub into a function
– K.Dᴀᴠɪs
Nov 14 '18 at 22:44
I've always referred to Pearson's descriptions for arrays: cpearson.com/excel/passingandreturningarrays.htm
– Rey Juna
Nov 14 '18 at 22:46
1
Can you edit the question to include more of your code? It isn't exactly clear what you're asking.
– Comintern
Nov 14 '18 at 23:01
add a comment |
1
1
1
I am so confused, and endless googling is so far defying me.
I have a sub that collected unique values in Column C into an array. Now that the array is created, I need to use it in a different sub so I can loop through the values.
I have tried passing it as an argument but then I can't figure out how to run the new sub that has arguments, i.e.:
Sub useArray(ByRef varArr() As String)
- how in the world do I run useArray? And useArray should be the main sub, anyways, so I am just confused about how I could then run the main sub and use this array variable that has already been defined/populated.
I tried using my sub that gets the unique values as a function, but it doesn't pass the values in the array back to the main sub. At the end of the function AND in the main sub I have:
MsgBox varArr(1)
In the function, it returns the first value. Back in the sub it returns an error.
An assistance would save my sanity!
arrays excel vba excel-vba
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
asked Nov 14 '18 at 22:42
LBJLBJ
63
I am so confused, and endless googling is so far defying me.
I have a sub that collected unique values in Column C into an array. Now that the array is created, I need to use it in a different sub so I can loop through the values.
I have tried passing it as an argument but then I can't figure out how to run the new sub that has arguments, i.e.:
Sub useArray(ByRef varArr() As String)
- how in the world do I run useArray? And useArray should be the main sub, anyways, so I am just confused about how I could then run the main sub and use this array variable that has already been defined/populated.
I tried using my sub that gets the unique values as a function, but it doesn't pass the values in the array back to the main sub. At the end of the function AND in the main sub I have:
MsgBox varArr(1)
In the function, it returns the first value. Back in the sub it returns an error.
An assistance would save my sanity!
arrays excel vba excel-vba
arrays excel vba excel-vba
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
asked Nov 14 '18 at 22:42
LBJLBJ
63
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
asked Nov 14 '18 at 22:42
LBJLBJ
63
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
edited Nov 14 '18 at 22:44
K.Dᴀᴠɪs
7,250112439
7,250112439
asked Nov 14 '18 at 22:42
LBJLBJ
63
asked Nov 14 '18 at 22:42
LBJLBJ
63
asked Nov 14 '18 at 22:42
LBJLBJ
63
63
You could turn your sub into a function
– K.Dᴀᴠɪs
Nov 14 '18 at 22:44
I've always referred to Pearson's descriptions for arrays: cpearson.com/excel/passingandreturningarrays.htm
– Rey Juna
Nov 14 '18 at 22:46
1
Can you edit the question to include more of your code? It isn't exactly clear what you're asking.
– Comintern
Nov 14 '18 at 23:01
add a comment |
You could turn your sub into a function
– K.Dᴀᴠɪs
Nov 14 '18 at 22:44
I've always referred to Pearson's descriptions for arrays: cpearson.com/excel/passingandreturningarrays.htm
– Rey Juna
Nov 14 '18 at 22:46
1
Can you edit the question to include more of your code? It isn't exactly clear what you're asking.
– Comintern
Nov 14 '18 at 23:01
You could turn your sub into a function
– K.Dᴀᴠɪs
Nov 14 '18 at 22:44
You could turn your sub into a function
– K.Dᴀᴠɪs
Nov 14 '18 at 22:44
I've always referred to Pearson's descriptions for arrays: cpearson.com/excel/passingandreturningarrays.htm
– Rey Juna
Nov 14 '18 at 22:46
I've always referred to Pearson's descriptions for arrays: cpearson.com/excel/passingandreturningarrays.htm
– Rey Juna
Nov 14 '18 at 22:46
1
1
Can you edit the question to include more of your code? It isn't exactly clear what you're asking.
– Comintern
Nov 14 '18 at 23:01
Can you edit the question to include more of your code? It isn't exactly clear what you're asking.
– Comintern
Nov 14 '18 at 23:01
add a comment |
2 Answers
2
active
oldest
votes
1
The preferred method is going to be turning your Sub into a Function:
Function useArray(ByRef varArr() As String) As string()
varArr(2) = "changed it"
useArray = varArr
End Function
Which you would call by:
Sub test()
Dim a(2) As String
a(0) = "a0"
a(1) = "a1"
a(2) = "a2"
MsgBox useArray(a)(2)
End Sub
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
Says 'Can't assign to array' onuseArray = varArr(2)in Excel 2010
– Arno van Boven
Nov 14 '18 at 23:33
That's because it'suseArray = varArr@ArnovanBoven -useArrayhasn't been dimensionalized until you assign the array to it
– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
1
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
|
show 1 more comment
0
Sub Main()
Call DoSomethingWithArray(PopulateArray)
End Sub
Function PopulateArray() As Variant
PopulateArray = Array("foo", "bar") ' return some array
End Function
Sub DoSomethingWithArray(ByVal someArray As Variant) ' process array
Dim i As Integer
For i = LBound(someArray) To UBound(someArray)
Debug.Print someArray(i)
Next i
End Sub
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
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
});
}
});
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%2f53309830%2fhow-to-access-populated-array-in-another-sub-in-vba-w-o-arguments%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
1
The preferred method is going to be turning your Sub into a Function:
Function useArray(ByRef varArr() As String) As string()
varArr(2) = "changed it"
useArray = varArr
End Function
Which you would call by:
Sub test()
Dim a(2) As String
a(0) = "a0"
a(1) = "a1"
a(2) = "a2"
MsgBox useArray(a)(2)
End Sub
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
Says 'Can't assign to array' onuseArray = varArr(2)in Excel 2010
– Arno van Boven
Nov 14 '18 at 23:33
That's because it'suseArray = varArr@ArnovanBoven -useArrayhasn't been dimensionalized until you assign the array to it
– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
1
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
|
show 1 more comment
1
The preferred method is going to be turning your Sub into a Function:
Function useArray(ByRef varArr() As String) As string()
varArr(2) = "changed it"
useArray = varArr
End Function
Which you would call by:
Sub test()
Dim a(2) As String
a(0) = "a0"
a(1) = "a1"
a(2) = "a2"
MsgBox useArray(a)(2)
End Sub
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
Says 'Can't assign to array' onuseArray = varArr(2)in Excel 2010
– Arno van Boven
Nov 14 '18 at 23:33
That's because it'suseArray = varArr@ArnovanBoven -useArrayhasn't been dimensionalized until you assign the array to it
– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
1
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
|
show 1 more comment
1
1
1
The preferred method is going to be turning your Sub into a Function:
Function useArray(ByRef varArr() As String) As string()
varArr(2) = "changed it"
useArray = varArr
End Function
Which you would call by:
Sub test()
Dim a(2) As String
a(0) = "a0"
a(1) = "a1"
a(2) = "a2"
MsgBox useArray(a)(2)
End Sub
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
The preferred method is going to be turning your Sub into a Function:
Function useArray(ByRef varArr() As String) As string()
varArr(2) = "changed it"
useArray = varArr
End Function
Which you would call by:
Sub test()
Dim a(2) As String
a(0) = "a0"
a(1) = "a1"
a(2) = "a2"
MsgBox useArray(a)(2)
End Sub
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
edited Nov 14 '18 at 23:35
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
answered Nov 14 '18 at 22:52
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
7,250112439
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
Says 'Can't assign to array' onuseArray = varArr(2)in Excel 2010
– Arno van Boven
Nov 14 '18 at 23:33
That's because it'suseArray = varArr@ArnovanBoven -useArrayhasn't been dimensionalized until you assign the array to it
– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
1
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
|
show 1 more comment
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
Says 'Can't assign to array' onuseArray = varArr(2)in Excel 2010
– Arno van Boven
Nov 14 '18 at 23:33
That's because it'suseArray = varArr@ArnovanBoven -useArrayhasn't been dimensionalized until you assign the array to it
– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
1
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
In Excel 2010 this won't compile.
– Arno van Boven
Nov 14 '18 at 23:24
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
@ArnovanBoven Strange that you're having issues... I'm not (No clue what the green screen is about in the last frame though - I think it's the color used to detect unchanged pixels to make the file smaller).
– K.Dᴀᴠɪs
Nov 14 '18 at 23:30
Says 'Can't assign to array' on
useArray = varArr(2) in Excel 2010– Arno van Boven
Nov 14 '18 at 23:33
Says 'Can't assign to array' on
useArray = varArr(2) in Excel 2010– Arno van Boven
Nov 14 '18 at 23:33
That's because it's
useArray = varArr @ArnovanBoven - useArray hasn't been dimensionalized until you assign the array to it– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
That's because it's
useArray = varArr @ArnovanBoven - useArray hasn't been dimensionalized until you assign the array to it– K.Dᴀᴠɪs
Nov 14 '18 at 23:34
1
1
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
Then you need to update the answer :)
– Arno van Boven
Nov 14 '18 at 23:34
|
show 1 more comment
0
Sub Main()
Call DoSomethingWithArray(PopulateArray)
End Sub
Function PopulateArray() As Variant
PopulateArray = Array("foo", "bar") ' return some array
End Function
Sub DoSomethingWithArray(ByVal someArray As Variant) ' process array
Dim i As Integer
For i = LBound(someArray) To UBound(someArray)
Debug.Print someArray(i)
Next i
End Sub
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
add a comment |
0
Sub Main()
Call DoSomethingWithArray(PopulateArray)
End Sub
Function PopulateArray() As Variant
PopulateArray = Array("foo", "bar") ' return some array
End Function
Sub DoSomethingWithArray(ByVal someArray As Variant) ' process array
Dim i As Integer
For i = LBound(someArray) To UBound(someArray)
Debug.Print someArray(i)
Next i
End Sub
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
add a comment |
0
0
0
Sub Main()
Call DoSomethingWithArray(PopulateArray)
End Sub
Function PopulateArray() As Variant
PopulateArray = Array("foo", "bar") ' return some array
End Function
Sub DoSomethingWithArray(ByVal someArray As Variant) ' process array
Dim i As Integer
For i = LBound(someArray) To UBound(someArray)
Debug.Print someArray(i)
Next i
End Sub
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
Sub Main()
Call DoSomethingWithArray(PopulateArray)
End Sub
Function PopulateArray() As Variant
PopulateArray = Array("foo", "bar") ' return some array
End Function
Sub DoSomethingWithArray(ByVal someArray As Variant) ' process array
Dim i As Integer
For i = LBound(someArray) To UBound(someArray)
Debug.Print someArray(i)
Next i
End Sub
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
answered Nov 14 '18 at 23:16
Arno van BovenArno van Boven
9529
9529
add a comment |
add a comment |
draft saved
draft discarded
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%2f53309830%2fhow-to-access-populated-array-in-another-sub-in-vba-w-o-arguments%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
You could turn your sub into a function
– K.Dᴀᴠɪs
Nov 14 '18 at 22:44
I've always referred to Pearson's descriptions for arrays: cpearson.com/excel/passingandreturningarrays.htm
– Rey Juna
Nov 14 '18 at 22:46
1
Can you edit the question to include more of your code? It isn't exactly clear what you're asking.
– Comintern
Nov 14 '18 at 23:01