title: VBA join array elements to then pass as multiple parameters to function
body: <p><strong>[Update: The question has been formed so that the functions can be copied directly into a VBA module and tested by running the <code>test_callback_loop_function_works_with_multiple_parameters</code> method]</strong></p>
  
  <p>I am using the <code>Application.Run</code> function to dynamically call methods within my VBA. The idea is that this helper will save me looping through dictionaries throughout various functions/subs within my VBA. Instead I can just call the following helper which will do the looping for me:</p>
  
  <pre><code>Public Function user_func_dictionary_loop(Dictionary As Dictionary, _
                   MethodCallback As String, _
                   Optional Params As Variant) As Boolean
  
      Dim Key As Variant
  
      For Each Key In Dictionary
  
          If IsMissing(Params) Then
              Application.Run MethodCallback, Dictionary(Key)
          Else
              Application.Run MethodCallback, user_param_replace(Dictionary(Key), Params)
          End If
  
      Next Key
  
  End Function
  </code></pre>
  
  <p>If no Parameters are supplied to the function then it simply runs the <code>MethodCallback</code> with the Dictionary's key value. If there are parameters then an additional step is triggered below:</p>
  
  <pre><code>Private Function user_param_replace(Item As Variant, Optional Params As Variant) As Variant
  
      Dim i As Long
      Dim strTest As String
      Dim Output As Variant
  
      Output = replace_dictionary_values(Item, Params)
  
      If IsArray(Output) Then
          ReDim Preserve Output(0 To UBound(Output))
  
          user_param_replace = Join(Output, ",")
          Exit Function
  
      End If
  
      user_param_replace = Output
  
  End Function
  
  Private Function replace_dictionary_values(Item As Variant, Optional Params As Variant) As Variant
  
      Dim l As Long
      Dim varTemp() As Variant
      Dim Param_Item As Variant
  
      l = 0
      If IsMissing(Params) Or Not IsArray(Params) Then
          replace_dictionary_values = Replace$(Params, "{D:Value}", Item)
          Exit Function
      Else
  
          ReDim varTemp(0 To UBound(Params))
  
          For Each Param_Item In Params
              varTemp(l) = Replace$(Param_Item, "{D:Value}", Item)
              l = l + 1
          Next Param_Item
  
      End If
  
      replace_dictionary_values = varTemp
  
  End Function
  </code></pre>
  
  <p>The steps above allow a user to pass in parameters which contain <code>{D:Value}</code> which will then be replaced with the Dictionary's key value.</p>
  
  <p>I've made a small unit test below with the idea that it should test the functionality of my method. At present I'm getting an "Argument not optional" error:</p>
  
  <pre><code>Function test_callback_loop_function_works_with_multiple_parameters() As Boolean
  
      Dim dictTest As New Dictionary
  
      dictTest.Add 1, "1 - Foo"
      dictTest.Add 2, "2 - Foo"
      dictTest.Add 3, "3 - Foo"
  
      Dim MyArray(0 To 1) As Variant
  
      MyArray(0) = "{D:Value}"
      MyArray(1) = "Bar"
  
      user_func_dictionary_loop dictTest, "custom_debug_print_multiple_params", MyArray
  
      test_callback_loop_function_works_with_multiple_parameters = True
  
  End Function
  
  Function custom_debug_print_multiple_params(strPrint As String, strPrint2 As String) As String
  
      Debug.Print strPrint &amp; strPrint2
  
  End Function
  </code></pre>
  
  <p>The output should be:</p>
  
  <pre><code>1 - FooBar
  2 - FooBar
  3 - FooBar
  </code></pre>
  
  <p>But I'm getting an </p>
  
  <blockquote>
    <p>Run-time error '449' - Argument not optional</p>
  </blockquote>
  
  <p>error on the <code>Application.Run MethodCallback, user_param_replace(Dictionary(Key), Params)</code> line.</p>
  
  <p>My hunch is that because I'm trying to join array elements together with a "," to then pass through as parameters (in the <code>Join(Output, ",")</code> line) to the method, it's causing the test to fail.</p>
  
  <p>So my question is, within VBA, is it possible to join the elements of an array together so they can then be passed, dynamically, to another method/function?</p>