VBA Collection - Passing a variable as a key

Multi tool use
Multi tool use












0














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?










share|improve this question




















  • 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








  • 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










  • 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
















0














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?










share|improve this question




















  • 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








  • 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










  • 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














0












0








0







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?










share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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




    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














  • 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








  • 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










  • 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












4 Answers
4






active

oldest

votes


















1














"""" & 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





share|improve this answer





















  • 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





















2














Use just:



Month1 = "" & Month(Now)





share|improve this answer





























    0














    Function GetMonth(iMonth%)
    GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
    End Function





    share|improve this answer





























      0














      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





      share|improve this answer










      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













      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


















      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









      1














      """" & 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





      share|improve this answer





















      • 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


















      1














      """" & 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





      share|improve this answer





















      • 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
















      1












      1








      1






      """" & 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





      share|improve this answer












      """" & 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






      share|improve this answer












      share|improve this answer



      share|improve this answer










      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




















      • 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















      2














      Use just:



      Month1 = "" & Month(Now)





      share|improve this answer


























        2














        Use just:



        Month1 = "" & Month(Now)





        share|improve this answer
























          2












          2








          2






          Use just:



          Month1 = "" & Month(Now)





          share|improve this answer












          Use just:



          Month1 = "" & Month(Now)






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Dec 27 '18 at 16:36









          Rory

          23.9k51723




          23.9k51723























              0














              Function GetMonth(iMonth%)
              GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
              End Function





              share|improve this answer


























                0














                Function GetMonth(iMonth%)
                GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
                End Function





                share|improve this answer
























                  0












                  0








                  0






                  Function GetMonth(iMonth%)
                  GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
                  End Function





                  share|improve this answer












                  Function GetMonth(iMonth%)
                  GetMonth = Choose(iMonth, "JANVIER", ..., "DÉCEMBRE")
                  End Function






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Dec 27 '18 at 18:01









                  JohnyL

                  3,4361822




                  3,4361822























                      0














                      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





                      share|improve this answer










                      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


















                      0














                      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





                      share|improve this answer










                      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
















                      0












                      0








                      0






                      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





                      share|improve this answer










                      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






                      share|improve this answer










                      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.









                      share|improve this answer



                      share|improve this answer








                      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




















                      • 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




















                      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.





                      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.




                      draft saved


                      draft discarded














                      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





















































                      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
                      hWCRM0RErhgj1E,hMA55BN0g ttOOAKV0pJFq,do0JrHtTc3SRPGarXW

                      Popular posts from this blog

                      Monofisismo

                      Angular Downloading a file using contenturl with Basic Authentication

                      Olmecas