VBA join array elements to then pass as multiple parameters to function

Question

[Update: The question has been formed so that the functions can be copied directly into a VBA module and tested by running the test_callback_loop_function_works_with_multiple_parameters method]

I am using the Application.Run 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:

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

If no Parameters are supplied to the function then it simply runs the MethodCallback with the Dictionary's key value. If there are parameters then an additional step is triggered below:

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

The steps above allow a user to pass in parameters which contain {D:Value} which will then be replaced with the Dictionary's key value.

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:

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

End Function

The output should be:

1 - FooBar
2 - FooBar
3 - FooBar

But I'm getting an

Run-time error '449' - Argument not optional

error on the Application.Run MethodCallback, user_param_replace(Dictionary(Key), Params) line.

My hunch is that because I'm trying to join array elements together with a "," to then pass through as parameters (in the Join(Output, ",") line) to the method, it's causing the test to fail.

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?

@SJR Can you expand please. Not sure what you're referring to. — noobmaster69
– noobmaster69, Commented Nov 15, 2017 at 10:32
@SJR the Application.Run MethodCallback, user_param_replace(Dictionary(Key), Params) line. Will update the question. — noobmaster69
– noobmaster69, Commented Nov 15, 2017 at 10:33
In that line are you intending to pass user_param_replace(Dictionary(Key), Params) as an argument of MethodCallback? — SJR
– SJR, Commented Nov 15, 2017 at 11:07

Variatus · Accepted Answer · 2017-11-16 02:17:45Z

1

There is a problem with this line of code.

replace_dictionary_values = Replace$(Params, "{D:Value}", Item)

This line is called when IsMissing(Params) = True and, predictably, returns an error.

I also found that your test procedure can't work.

Function custom_debug_print_multiple_params(strPrint As String, strPrint2 As String) As String

    Debug.Print strPrint & strPrint2

End Function

All your variables are variants but the two parameters of the above function are of string type. The arguments should be declared ByVal if variants of string type are to be passed. I recommend to test each function individually and make sure that it works before using its return value as parameters for other functions.

I suspect that part of your problem may be caused by your rather indiscriminate use of variants. For example, the Replace function you invoke in the faulty line quoted above requires 3 strings as arguments. In your code, both Item and Params (if it would exist) are variants. There is a good chance that your plan could actually work, but when something doesn't quite work, as is the case here, all those corners that were cut will have to be checked, adding more time to the debugging effort than could be saved during coding.

In the first example below the calling procedure supplies the two strings required by the called procedure. Variants of string type are passed which are converted to strings by the ByVal argument.

Function Test_TestPrint() As Boolean

    Dim dictTest As New Scripting.Dictionary
    Dim MyArray(0 To 1) As Variant

    dictTest.Add 1, "1 - Foo"
    dictTest.Add 2, "2 - Foo"
    dictTest.Add 3, "3 - Foo"
    MyArray(0) = "{D:Value}"
    MyArray(1) = "Bar"

    TestPrint MyArray(0), MyArray(1)
'    user_func_dictionary_loop dictTest, "TestPrint", MyArray
    Test_TestPrint = True
End Function

Sub TestPrint(ByVal strPrint As String, ByVal strPrint2 As String)
    Debug.Print strPrint & strPrint2
End Sub

In the code below the array is passed to the executing procedure which expects such an array and prints out its elements.

Function Test_TestPrint2() As Boolean

    Dim dictTest As New Scripting.Dictionary
    Dim MyArray(0 To 1) As Variant

    dictTest.Add 1, "1 - Foo"
    dictTest.Add 2, "2 - Foo"
    dictTest.Add 3, "3 - Foo"
    MyArray(0) = "{D:Value}"
    MyArray(1) = "Bar"

Sub TestPrint2 MyArray
'    user_func_dictionary_loop dictTest, "TestPrint", MyArray
    Test_TestPrint2 = True
End Function

edited Nov 16, 2017 at 2:17

answered Nov 15, 2017 at 12:59

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

noobmaster69 Over a year ago

When I run the code through a fresh Excel workbook I do not get an error on the IsMissing(Params) line - I get the error as reported in my question (Ensuring Microsoft Scripting Runtime is enabled!). Using the watch window I see that the outputs of the user_param_replace method is 1 - Foo,Bar however it fails when that is passed as a parameter to the custom_debug_print_multiple_params method - even if the params are variants or ByVals.

Variatus Over a year ago

The IsMissing line MUST throw an error if Param is missing. If it doesn't, search for the reason. I have added two sets of procedures to my answer above. Both of them work. You can't pass an array to a function that is expecting two strings.

noobmaster69 Over a year ago

The IsMissing line is not throwing the error. I agree with your point about passing an array to a function expecting strings but I'm trying to use the Join(Output, ",") line to split the array into component strings and to then stitch it together with a comma. So the array becomes "Foo", "Bar" (two separate parameters) which I then try to pass, dynamically, to the custom_debug_print_multiple_params

noobmaster69 Over a year ago

Would the ParamsArray function be a better alternative than just passing in Optional Params?

Variatus Over a year ago

The receiving function would still have to decipher the paramarray. Therefore the paramarray should be preferable only if you have individual strings in the calling procedure. Since you have an array at that point you should teach the function to handle the array it receives. Incidentally, just because IsMissing doesn't throw an error doesn't mean it works as expected. It seems to me that you expect "{D:Value}" to be replaced with an item from the dictionary. Since there can't be any "{D:Value}" no replacement takes place. So, the function returns a null string as variant.

Collectives™ on Stack Overflow

VBA join array elements to then pass as multiple parameters to function

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related