Type mismatch error passing a string to a function from an array (in VBA)

Question

I have a code like this

Sub Test()
    Dim ArrayStrings()
    Dim T As String, myResult As String

    ArrayStrings = Array("String1", "String2", "String3", "String4", "String5", "StringN")
    T = "myString"

    myResult = myFunction(T, ArrayStrings(1))
End Sub

Function myFunction(Tx1 As String, Tx2 As String) As String
    'myCode
End Function

This get an error "type mismatch" on "ArrayStrings(1)"

I tryed different ways but the only working seems to be:

    myResult = myFunction(T, CStr(ArrayStrings(1)))

But it seems strange convert a string to a string ... isn't it? Is there any other way?

Your casting an Array to a string, not a String to a string — SierraOscar
– SierraOscar, Commented Apr 9, 2015 at 9:38
You can assign the array(1) element to a string variable and pass that to the function, or declare Tx2 As Variant — tospig
– tospig, Commented Apr 9, 2015 at 9:44

R3uK · Accepted Answer · 2015-07-15 08:32:34Z

2

Try casting your array as a variant and then pass that into the function.

Option Explicit

Sub Test()
    Dim arr() As Variant
    Dim str As String

    arr = Array("1", "2", "3")
    str = "Hello, world!"

    MsgBox myFunction(str, arr)
End Sub

Function myFunction(str As String, arr As Variant) As String
    Dim var As Variant
    Dim strOutput As String

    For Each var In arr
        strOutput = strOutput & " " & var
    Next var

    myFunction = strOutput
End Function

edited Jul 15, 2015 at 8:32

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

answered Apr 9, 2015 at 9:45

Gareth

5,2636 gold badges47 silver badges73 bronze badges

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

1 Comment

genespos Over a year ago

why should I prefer to use Function myFunction(str As String, arr As Variant) As String>; why using Variant where I need a string?

R3uK · Accepted Answer · 2015-04-09 09:54:17Z

1

Indeed, converting a string to a string isn't glamourous...

Have you tried to use ByVal?

Function myFunction(Tx1 As String, ByVal Tx2 As String) As String

That should be enough

answered Apr 9, 2015 at 9:54

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

1 Comment

R3uK Over a year ago

Nope default is ByRef ;) I have to admit I use ByVal a lot (like for every arguments^^), not sure if it can be bad but it is widely useful!

Collectives™ on Stack Overflow

Type mismatch error passing a string to a function from an array (in VBA)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related