VBA Collection - Passing a variable as a key

Multi tool use
I'm trying to write code that returns the name of the current month in French. I have created a collection, and it does return my month, but I cannot connect it to the second function that returns the number of the current month.
The first and second MsgBox return DECEMBRE and "12" as expected, but the last one returns the runtime error '5': Invalid procedure call or argument.
Sub test()
Dim Month1 As String
Dim dict As New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
Month1 = """" & Month(Now) & """"
MsgBox (dict.Item("12")) '1st check - OK
MsgBox (Month1) '2nd check - OK
MsgBox (dict.Item(Month1)) 'final result - FAIL
End Sub
Where is the mistake?
excel vba excel-vba
add a comment |
I'm trying to write code that returns the name of the current month in French. I have created a collection, and it does return my month, but I cannot connect it to the second function that returns the number of the current month.
The first and second MsgBox return DECEMBRE and "12" as expected, but the last one returns the runtime error '5': Invalid procedure call or argument.
Sub test()
Dim Month1 As String
Dim dict As New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
Month1 = """" & Month(Now) & """"
MsgBox (dict.Item("12")) '1st check - OK
MsgBox (Month1) '2nd check - OK
MsgBox (dict.Item(Month1)) 'final result - FAIL
End Sub
Where is the mistake?
excel vba excel-vba
2
Why are you going to extra lengths to store the quotes insideMonth1
? Quotes are for string literals, the key is12
(a string, not number 12), not"12"
with quotes.
– GSerg
Dec 27 '18 at 16:36
1
Watch out for extraneous parentheses:(dict.Item(Month1))
is getting evaluated as an expression, then the result is passed by value as the first argument toMsgBox
- you would run into compile-time issues if you tried to pass a second argument to the function, because(expression, expression)
isn't an expression that can be evaluated and whose result can be passed by value as the first argument toMsgBox
. In other words, doMsgBox dict.Item(Month1)
instead ofMsgBox (dict.Item(Month1))
, and in general avoid extraneous parentheses in procedure calls.
– Mathieu Guindon
Dec 27 '18 at 16:46
GSerg thank you, you have opened my eyes :) I have ignored the type of the variable. As someone mentioned, it's always good to have Locals window open, I was not aware that it's possible, it's a great tool and saves me a lot of concerns. Thank you Mathieu, I have corrected the code. You are right, it's a trap :)
– Mick
Dec 28 '18 at 9:33
add a comment |
I'm trying to write code that returns the name of the current month in French. I have created a collection, and it does return my month, but I cannot connect it to the second function that returns the number of the current month.
The first and second MsgBox return DECEMBRE and "12" as expected, but the last one returns the runtime error '5': Invalid procedure call or argument.
Sub test()
Dim Month1 As String
Dim dict As New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
Month1 = """" & Month(Now) & """"
MsgBox (dict.Item("12")) '1st check - OK
MsgBox (Month1) '2nd check - OK
MsgBox (dict.Item(Month1)) 'final result - FAIL
End Sub
Where is the mistake?
excel vba excel-vba
I'm trying to write code that returns the name of the current month in French. I have created a collection, and it does return my month, but I cannot connect it to the second function that returns the number of the current month.
The first and second MsgBox return DECEMBRE and "12" as expected, but the last one returns the runtime error '5': Invalid procedure call or argument.
Sub test()
Dim Month1 As String
Dim dict As New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
Month1 = """" & Month(Now) & """"
MsgBox (dict.Item("12")) '1st check - OK
MsgBox (Month1) '2nd check - OK
MsgBox (dict.Item(Month1)) 'final result - FAIL
End Sub
Where is the mistake?
excel vba excel-vba
excel vba excel-vba
edited Dec 27 '18 at 17:24


TylerH
15.4k105067
15.4k105067
asked Dec 27 '18 at 16:34
Mick
73
73
2
Why are you going to extra lengths to store the quotes insideMonth1
? Quotes are for string literals, the key is12
(a string, not number 12), not"12"
with quotes.
– GSerg
Dec 27 '18 at 16:36
1
Watch out for extraneous parentheses:(dict.Item(Month1))
is getting evaluated as an expression, then the result is passed by value as the first argument toMsgBox
- you would run into compile-time issues if you tried to pass a second argument to the function, because(expression, expression)
isn't an expression that can be evaluated and whose result can be passed by value as the first argument toMsgBox
. In other words, doMsgBox dict.Item(Month1)
instead ofMsgBox (dict.Item(Month1))
, and in general avoid extraneous parentheses in procedure calls.
– Mathieu Guindon
Dec 27 '18 at 16:46
GSerg thank you, you have opened my eyes :) I have ignored the type of the variable. As someone mentioned, it's always good to have Locals window open, I was not aware that it's possible, it's a great tool and saves me a lot of concerns. Thank you Mathieu, I have corrected the code. You are right, it's a trap :)
– Mick
Dec 28 '18 at 9:33
add a comment |
2
Why are you going to extra lengths to store the quotes insideMonth1
? Quotes are for string literals, the key is12
(a string, not number 12), not"12"
with quotes.
– GSerg
Dec 27 '18 at 16:36
1
Watch out for extraneous parentheses:(dict.Item(Month1))
is getting evaluated as an expression, then the result is passed by value as the first argument toMsgBox
- you would run into compile-time issues if you tried to pass a second argument to the function, because(expression, expression)
isn't an expression that can be evaluated and whose result can be passed by value as the first argument toMsgBox
. In other words, doMsgBox dict.Item(Month1)
instead ofMsgBox (dict.Item(Month1))
, and in general avoid extraneous parentheses in procedure calls.
– Mathieu Guindon
Dec 27 '18 at 16:46
GSerg thank you, you have opened my eyes :) I have ignored the type of the variable. As someone mentioned, it's always good to have Locals window open, I was not aware that it's possible, it's a great tool and saves me a lot of concerns. Thank you Mathieu, I have corrected the code. You are right, it's a trap :)
– Mick
Dec 28 '18 at 9:33
2
2
Why are you going to extra lengths to store the quotes inside
Month1
? Quotes are for string literals, the key is 12
(a string, not number 12), not "12"
with quotes.– GSerg
Dec 27 '18 at 16:36
Why are you going to extra lengths to store the quotes inside
Month1
? Quotes are for string literals, the key is 12
(a string, not number 12), not "12"
with quotes.– GSerg
Dec 27 '18 at 16:36
1
1
Watch out for extraneous parentheses:
(dict.Item(Month1))
is getting evaluated as an expression, then the result is passed by value as the first argument to MsgBox
- you would run into compile-time issues if you tried to pass a second argument to the function, because (expression, expression)
isn't an expression that can be evaluated and whose result can be passed by value as the first argument to MsgBox
. In other words, do MsgBox dict.Item(Month1)
instead of MsgBox (dict.Item(Month1))
, and in general avoid extraneous parentheses in procedure calls.– Mathieu Guindon
Dec 27 '18 at 16:46
Watch out for extraneous parentheses:
(dict.Item(Month1))
is getting evaluated as an expression, then the result is passed by value as the first argument to MsgBox
- you would run into compile-time issues if you tried to pass a second argument to the function, because (expression, expression)
isn't an expression that can be evaluated and whose result can be passed by value as the first argument to MsgBox
. In other words, do MsgBox dict.Item(Month1)
instead of MsgBox (dict.Item(Month1))
, and in general avoid extraneous parentheses in procedure calls.– Mathieu Guindon
Dec 27 '18 at 16:46
GSerg thank you, you have opened my eyes :) I have ignored the type of the variable. As someone mentioned, it's always good to have Locals window open, I was not aware that it's possible, it's a great tool and saves me a lot of concerns. Thank you Mathieu, I have corrected the code. You are right, it's a trap :)
– Mick
Dec 28 '18 at 9:33
GSerg thank you, you have opened my eyes :) I have ignored the type of the variable. As someone mentioned, it's always good to have Locals window open, I was not aware that it's possible, it's a great tool and saves me a lot of concerns. Thank you Mathieu, I have corrected the code. You are right, it's a trap :)
– Mick
Dec 28 '18 at 9:33
add a comment |
4 Answers
4
active
oldest
votes
"""" & Month(Now) & """"
is wrapping the value in double quotes where "1"
is just converting the number 1 into a string. A VBA collection doesn't even care if a number in numeric or if it is a String. Both Month1 = Month(Now)
and MsgBox dict.Item(Month(Now))
work.
That being said I would just use an array.
Function getMois(MonthIndex As Long) As String
getMois = Array("JANVIER", "FÉVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DÉCEMBRE")(MonthIndex - 1)
End Function
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
add a comment |
Use just:
Month1 = "" & Month(Now)
add a comment |
Function GetMonth(iMonth%)
GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
End Function
add a comment |
I rewrote your code a bit to separate concerns and simply used Excel's implicit conversion to get the Month(Now) as a string. VBA will simply coerce the value into a string, but you could also use type conversion to convert numbers to strings using CSTR().
Overall, some of the other solutions are compact, and you should use them, but it does point out that you might need to become more aware of separation of concerns (essentially, good code habits), type conversions, and implicit/explicit conversion in VBA. Also, you might find it easier to use Debug.Print when coding/debugging, as well as using the Immediate and Locals windows to understand your code.
Code
Private dict As Collection
Sub SetArray()
Set dict = New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
End Sub
The tests:
Sub Test()
SetArray
Dim Month1 As String
Debug.Print dict.Item("12") '1st check - OK
Month1 = Month(Now)
Debug.Print Month1 '2nd check - OK
Debug.Print dict.Item(Month1) 'final result - SUCCEED
Month1 = CStr(Month(Now))
Debug.Print dict.Item(Month1) 'final result - SUCCEED
End Sub
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
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%2f53948101%2fvba-collection-passing-a-variable-as-a-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
"""" & Month(Now) & """"
is wrapping the value in double quotes where "1"
is just converting the number 1 into a string. A VBA collection doesn't even care if a number in numeric or if it is a String. Both Month1 = Month(Now)
and MsgBox dict.Item(Month(Now))
work.
That being said I would just use an array.
Function getMois(MonthIndex As Long) As String
getMois = Array("JANVIER", "FÉVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DÉCEMBRE")(MonthIndex - 1)
End Function
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
add a comment |
"""" & Month(Now) & """"
is wrapping the value in double quotes where "1"
is just converting the number 1 into a string. A VBA collection doesn't even care if a number in numeric or if it is a String. Both Month1 = Month(Now)
and MsgBox dict.Item(Month(Now))
work.
That being said I would just use an array.
Function getMois(MonthIndex As Long) As String
getMois = Array("JANVIER", "FÉVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DÉCEMBRE")(MonthIndex - 1)
End Function
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
add a comment |
"""" & Month(Now) & """"
is wrapping the value in double quotes where "1"
is just converting the number 1 into a string. A VBA collection doesn't even care if a number in numeric or if it is a String. Both Month1 = Month(Now)
and MsgBox dict.Item(Month(Now))
work.
That being said I would just use an array.
Function getMois(MonthIndex As Long) As String
getMois = Array("JANVIER", "FÉVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DÉCEMBRE")(MonthIndex - 1)
End Function
"""" & Month(Now) & """"
is wrapping the value in double quotes where "1"
is just converting the number 1 into a string. A VBA collection doesn't even care if a number in numeric or if it is a String. Both Month1 = Month(Now)
and MsgBox dict.Item(Month(Now))
work.
That being said I would just use an array.
Function getMois(MonthIndex As Long) As String
getMois = Array("JANVIER", "FÉVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DÉCEMBRE")(MonthIndex - 1)
End Function
answered Dec 27 '18 at 17:51


TinMan
2,156212
2,156212
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
add a comment |
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
Thank you TinMan! Yes you are completely right, I have tried to give the dict.Item argument that looks exactly the same, but has a wrong type. I will learn more about the arrays as well, it's obviously much more simple than my solution.
– Mick
Dec 28 '18 at 9:40
add a comment |
Use just:
Month1 = "" & Month(Now)
add a comment |
Use just:
Month1 = "" & Month(Now)
add a comment |
Use just:
Month1 = "" & Month(Now)
Use just:
Month1 = "" & Month(Now)
answered Dec 27 '18 at 16:36


Rory
23.9k51723
23.9k51723
add a comment |
add a comment |
Function GetMonth(iMonth%)
GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
End Function
add a comment |
Function GetMonth(iMonth%)
GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
End Function
add a comment |
Function GetMonth(iMonth%)
GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
End Function
Function GetMonth(iMonth%)
GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
End Function
answered Dec 27 '18 at 18:01


JohnyL
3,4361822
3,4361822
add a comment |
add a comment |
I rewrote your code a bit to separate concerns and simply used Excel's implicit conversion to get the Month(Now) as a string. VBA will simply coerce the value into a string, but you could also use type conversion to convert numbers to strings using CSTR().
Overall, some of the other solutions are compact, and you should use them, but it does point out that you might need to become more aware of separation of concerns (essentially, good code habits), type conversions, and implicit/explicit conversion in VBA. Also, you might find it easier to use Debug.Print when coding/debugging, as well as using the Immediate and Locals windows to understand your code.
Code
Private dict As Collection
Sub SetArray()
Set dict = New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
End Sub
The tests:
Sub Test()
SetArray
Dim Month1 As String
Debug.Print dict.Item("12") '1st check - OK
Month1 = Month(Now)
Debug.Print Month1 '2nd check - OK
Debug.Print dict.Item(Month1) 'final result - SUCCEED
Month1 = CStr(Month(Now))
Debug.Print dict.Item(Month1) 'final result - SUCCEED
End Sub
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
add a comment |
I rewrote your code a bit to separate concerns and simply used Excel's implicit conversion to get the Month(Now) as a string. VBA will simply coerce the value into a string, but you could also use type conversion to convert numbers to strings using CSTR().
Overall, some of the other solutions are compact, and you should use them, but it does point out that you might need to become more aware of separation of concerns (essentially, good code habits), type conversions, and implicit/explicit conversion in VBA. Also, you might find it easier to use Debug.Print when coding/debugging, as well as using the Immediate and Locals windows to understand your code.
Code
Private dict As Collection
Sub SetArray()
Set dict = New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
End Sub
The tests:
Sub Test()
SetArray
Dim Month1 As String
Debug.Print dict.Item("12") '1st check - OK
Month1 = Month(Now)
Debug.Print Month1 '2nd check - OK
Debug.Print dict.Item(Month1) 'final result - SUCCEED
Month1 = CStr(Month(Now))
Debug.Print dict.Item(Month1) 'final result - SUCCEED
End Sub
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
add a comment |
I rewrote your code a bit to separate concerns and simply used Excel's implicit conversion to get the Month(Now) as a string. VBA will simply coerce the value into a string, but you could also use type conversion to convert numbers to strings using CSTR().
Overall, some of the other solutions are compact, and you should use them, but it does point out that you might need to become more aware of separation of concerns (essentially, good code habits), type conversions, and implicit/explicit conversion in VBA. Also, you might find it easier to use Debug.Print when coding/debugging, as well as using the Immediate and Locals windows to understand your code.
Code
Private dict As Collection
Sub SetArray()
Set dict = New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
End Sub
The tests:
Sub Test()
SetArray
Dim Month1 As String
Debug.Print dict.Item("12") '1st check - OK
Month1 = Month(Now)
Debug.Print Month1 '2nd check - OK
Debug.Print dict.Item(Month1) 'final result - SUCCEED
Month1 = CStr(Month(Now))
Debug.Print dict.Item(Month1) 'final result - SUCCEED
End Sub
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I rewrote your code a bit to separate concerns and simply used Excel's implicit conversion to get the Month(Now) as a string. VBA will simply coerce the value into a string, but you could also use type conversion to convert numbers to strings using CSTR().
Overall, some of the other solutions are compact, and you should use them, but it does point out that you might need to become more aware of separation of concerns (essentially, good code habits), type conversions, and implicit/explicit conversion in VBA. Also, you might find it easier to use Debug.Print when coding/debugging, as well as using the Immediate and Locals windows to understand your code.
Code
Private dict As Collection
Sub SetArray()
Set dict = New Collection
dict.Add "JANVIER", "1"
dict.Add "FÉVRIER", "2"
dict.Add "MARS", "3"
dict.Add "AVRIL", "4"
dict.Add "MAI", "5"
dict.Add "JUIN", "6"
dict.Add "JUILLET", "7"
dict.Add "AOUT", "8"
dict.Add "SEPTEMBRE", "9"
dict.Add "OCTOBRE", "10"
dict.Add "NOVEMBRE", "11"
dict.Add "DÉCEMBRE", "12"
End Sub
The tests:
Sub Test()
SetArray
Dim Month1 As String
Debug.Print dict.Item("12") '1st check - OK
Month1 = Month(Now)
Debug.Print Month1 '2nd check - OK
Debug.Print dict.Item(Month1) 'final result - SUCCEED
Month1 = CStr(Month(Now))
Debug.Print dict.Item(Month1) 'final result - SUCCEED
End Sub
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Dec 27 '18 at 19:56
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered Dec 27 '18 at 19:11
James Igoe
13
13
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
James Igoe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
add a comment |
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
Thank you James, I have learned more about debugging options and found the Locals window which from now will assist me everywhere, it's just what I was looking for :) It helped me to find the solution on my own. Works!
– Mick
Dec 28 '18 at 9:44
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%2f53948101%2fvba-collection-passing-a-variable-as-a-key%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
Hmd8XSA 3n
2
Why are you going to extra lengths to store the quotes inside
Month1
? Quotes are for string literals, the key is12
(a string, not number 12), not"12"
with quotes.– GSerg
Dec 27 '18 at 16:36
1
Watch out for extraneous parentheses:
(dict.Item(Month1))
is getting evaluated as an expression, then the result is passed by value as the first argument toMsgBox
- you would run into compile-time issues if you tried to pass a second argument to the function, because(expression, expression)
isn't an expression that can be evaluated and whose result can be passed by value as the first argument toMsgBox
. In other words, doMsgBox dict.Item(Month1)
instead ofMsgBox (dict.Item(Month1))
, and in general avoid extraneous parentheses in procedure calls.– Mathieu Guindon
Dec 27 '18 at 16:46
GSerg thank you, you have opened my eyes :) I have ignored the type of the variable. As someone mentioned, it's always good to have Locals window open, I was not aware that it's possible, it's a great tool and saves me a lot of concerns. Thank you Mathieu, I have corrected the code. You are right, it's a trap :)
– Mick
Dec 28 '18 at 9:33