CODEWITHSUNDEEP
vba
learningnewthings
learningnewthings

Reputation: 9

Excel VBA: Failed to pass a string array to a Function

VBA Beginner here.

I am trying to pass an array of strings from a subroutine to a function which will then modify each string in the array. However I get the "Type:array or user-defined type expected" error message.

I have tried redefining different data types for the array so it is aligned with the data type entered in the function but to no avail.

Hope you can help! THank you so much!

Below is the dummy code:

Sub text()
    Dim haha() As Variant

    haha = Array("Tom", "Mary", "Adam")
    testing (haha())
    MsgBox Join(haha, " ")

End Sub

Function testing(ByRef check() As String) As String()
    Dim track As Long

    For track = LBound(check) To UBound(check)
        check(track) = check(track) & " OMG"
    Next
End Function

Upvotes: 1

Views: 1771

Answers (3)

Tom
Tom

Reputation: 747

A couple of suggestions to improve your code:

Dim haha() As String

You define the type of the entry in the array, not the array itself. Use the way mentioned by mielk to fill the array.

Function Testing(byref check as variant) As String

This will avoid problems with undefined variables. Not clear why you feel that the function should return a string though. Maybe even convert to a Sub instead.

Upvotes: 0

mielk
mielk

Reputation: 3940

You have a few errors in your code:

  • There are two ways of invoking methods:

    1) with Call keyword - in this case you must give all the parameters in brackets:

    Call testing(haha)

    2) without Call keyword - in this case you just give your parameters after the name of function:

    testing haha

    In your code you combined both of them and this is syntax error.

  • If you pass an array as a parameter to function you don't need to put brackets like that: testing (haha()).

    The proper syntax is:

    testing(haha)

  • Function testing requires as a parameter an array of String type, you cannot pass object of other type instead since it causes compile error Type mismatch. Currently you are trying to pass variable haha which is of Variant type.

  • You can change the type of haha variable to array of strings (to avoid the error described above):

    Dim haha() As String

    However, in this case you cannot assign the value of function Array to it, since the result of this function is of Variant type. You would have to replace this code:

    haha = Array("Tom", "Mary", "Adam")

    with this:

    ReDim haha(1 To 3)
haha(1) = "Tom"
haha(2) = "Mary"
haha(3) = "Adam"

Upvotes: 1

99moorem
99moorem

Reputation: 1983

In orignial code, a string is not the same variant (I believe they both would need to be variant? someone can verify), you dont need the brackets after testing, only need brackets if you are setting to another value e.g.

haha2 = testing(haha())

Below code should be ok

Sub text()
Dim haha()
haha = Array("Tom", "Mary", "Adam")
testing haha()
MsgBox Join(haha, " ")

End Sub

Function testing(ByRef check()) As String
Dim track As Long
For track = LBound(check) To UBound(check)
    check(track) = check(track) & " OMG"
Next
End Function

Upvotes: 1

Related Questions