How to use array values as function parameter

Question

I want to pass the values of an array to a function that accepts these parameters. When I do it based on the code provided, I get an error telling me: "ByRef argument type mismatch" I understand that my array is declared as variant, but the values I pass to it, are from the appropriate data type for the function to accept based on VarType(arrValues(1 - 3)) so what should be changed to the code?

This is a simplyfied version of the code I will be using lateron, where the function will actual return something usefull and will receive many more parameters.

Sub CallFunctionWithArray()
    Dim arrValues(1 To 3) As Variant

    arrValues(1) = "One"
    arrValues(2) = 19 - 11 - 2019
    arrValues(3) = 25

    Call ReturnValuesOfArray(arrValues(1), arrValues(2), arrValues(3))

End Sub

Function ReturnValuesOfArray(ValueOne As String, ValueTwo As Date, ValueThree As Integer)
    Debug.Print ValueOne
    Debug.Print ValueTwo
    Debug.Print ValueThree
End Function

Would expect the code to run and print the values of the passed array to de immediate window in this case.

`19 - 11 - 2019` is not a date - it's 19 minus 11 minus 2019 - an integer... try instead `CVDate("19-11-2019")` — braX, Sep 07 '19 at 08:31
@braX, used your recommendation, still returns the same error: "ByRef argument type mismatch" Also changed arrValues(2) to string and the function parameter to string, doesn't make any difference. When the error occurs, the first parameter in the call return... is highlighted. — Sunfile, Sep 07 '19 at 08:51

score 2 · Answer 1 · answered Sep 07 '19 at 09:18

2

I am not sure what you want to achieve but for the code you posted you need to add the ByVal Keyword in the function header like that

Function ReturnValuesOfArray(ByVal ValueOne As String, ByVal ValueTwo As Date, ByVal ValueThree As Integer)
    Debug.Print ValueOne
    Debug.Print ValueTwo
    Debug.Print ValueThree
End Function

answered Sep 07 '19 at 09:18

Storax

11,158
3
16
33

thanks this solved my problem. I had tried once to set ByVal in the header, but only for the first argument. I wasn't aware you need to set ByVal/ByRef for each of the arguments. – Sunfile Sep 07 '19 at 09:23
1

`ByRef` is the default and therefore in your code an argument must have the same data type as defined in the header, see [MSDN](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/byref-argument-type-mismatch). – Storax Sep 07 '19 at 09:29

score 1 · Accepted Answer · answered Sep 08 '19 at 18:42

1

You construct your array and you pass it as a Variant ..
Take a look here :

Private Sub Test()
Dim s(2) As String
s(0) = "X"
s(1) = "Y"
s(2) = "Z"
displayArr s

End Sub
Private Sub displayArr(ArrayInput As Variant)
For i = LBound(ArrayInput) To UBound(ArrayInput)
Debug.Print ArrayInput(i)
Next
End Sub

answered Sep 08 '19 at 18:42

John

974
8
16

this solution is rather usefull. However, In my function declaration, I mention the data types of each parameter. Based on your code, you will use variant because you accept the array as parameter, so there won't be a check on the data type of each parameter right? So this is less controlling the data types going in, or is there another way to handle that part? – Sunfile Sep 09 '19 at 18:26

How to use array values as function parameter

2 Answers2