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
– braX, Commented Sep 7, 2019 at 8: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
– Sunfile, Commented Sep 7, 2019 at 8:51

Storax · Accepted Answer · 2019-09-07 09:18:54Z

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 7, 2019 at 9:18

Storax

12.7k3 gold badges21 silver badges36 bronze badges

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

2 Comments

Sunfile Over a year ago

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.

Storax Over a year ago

ByRef is the default and therefore in your code an argument must have the same data type as defined in the header, see MSDN.

John · Accepted Answer · 2019-09-08 18:42:00Z

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 8, 2019 at 18:42

John

1,0049 silver badges16 bronze badges

1 Comment

Sunfile Over a year ago

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?

Collectives™ on Stack Overflow

How to use array values as function parameter

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related