VBA ByRef Argument Type Mismatch string into string

Question

Here's the code i try to run, I can't find anything wrong with it, why i can't pass String into function with String argument ? It keeps telling me ByRef Argument Type Mismatch.

Been trying other answers regarding this, i still can't find the solution. Hope can get a little help here.

Main Procedure :

Sub Macro()
Dim test As String
Dim arr() As String
Dim CompiledText As String
Dim temporary As String
Dim i As Long
Dim ab As String
Dim marker As Long
market = 0
test = Range("A1").Value
arr = Split(test, Chr(10))
test = ""
CompiledText = ""
For i = 0 To UBound(arr)
If (Left(arr(i), 1) <> "#") Then
    If (Trim(test) <> "") Then
        test = test & vbCrLf
    End If
    test = test & arr(i)
    If (InStr(arr(i), "ATTRS(") <> 0) Then
        If (CompiledText <> "") Then
            CompiledText = CompiledText & vbCrLf
        End If
        temporary = arr(i)
        CompiledText = CompiledText & GrabATTRS(CStr(temporary))
    ElseIf (InStr(arr(i), "ATTRN(") <> 0) Then
        If (CompiledText <> "") Then
            CompiledText = CompiledText & vbCrLf
        End If
        temporary = arr(i)
        CompiledText = CompiledText & GrabATTRN(CStr(temporary))
    ElseIf (InStr(arr(i), "DB(") <> 0) Then
        If (CompiledText <> "") Then
            CompiledText = CompiledText & vbCrLf
        End If
        temporary = arr(i)
        CompiledText = CompiledText & GrabDB(CStr(temporary))
    End If
Else
    If (marker <> i - 1) Then
        arr(marker) = arr(marker) & vbCrLf & CompiledText
        CompiledText = ""
    End If
    marker = i
End If
Next i
test = ""
For i = 0 To UBound(arr)
If (i > 0) Then
test = test & vbCrLf
End If
test = test & arr(i)
Next i

Range("B1").Value = test
End Sub

Function being called that not working properly :

Function GrabATTRS(ab As String) As String
Dim temp As String
Dim dimension As String
Dim attrib As String
temp = Split(Split(ab, "ATTRS(")(1), ")")(0)
dimension = onlyChars(Split(temp, ",")(0))
attib = onlyChar(Split(temp, ",")(UBound(Split(temp, ",")) - 1))
GrabATTRS = "#From dimension " & dimension & " pointing to " & attrib
End Function

Function GrabATTRN(ab As String) As String
Dim temp As String
Dim dimension As String
Dim attrib As String
temp = Split(Split(ab, "ATTRN(")(1), ")")(0)
dimension = onlyChars(Split(temp, ",")(0))
attib = onlyChar(Split(temp, ",")(UBound(Split(temp, ",")) - 1))
GrabATTRN = "#From dimension " & dimension & " pointing to " & attrib
End Function

Function GrabDB(ab As String) As String
Dim temp As String
Dim dimension As String
Dim attrib As String
temp = Split(Split(ab, "DB(")(1), ")")(0)
dimension = onlyChars(Split(temp, ",")(0))
GrabDB = "#From " & dimension & " Cube"
End Function

This one function can be skip checked since it's works well

Function onlyChars(S As String) As String
    Dim i As Integer
    retval = ""
    For i = 1 To Len(S)
        If Mid(S, i, 1) <> "'" Then
            retval = retval + Mid(S, i, 1)
        End If
    Next i
    onlyChars = retval
End Function


Option Explicit

Daniel Dušek Daniel Dušek · Accepted Answer · 2016-05-03T08:30:07

Split function retuns Varaint. Example e.g. from function GrabATTRS:

The result of Split can be put into a string variable and then pass ByRef to onlyChars.

call which causes ByRef error:

dimension = onlyChars(Split(temp, ",")(0))

with string result example:

Dim result As String
result = Split(temp, ",")(0)
dimension = onlyChars(result)

VBA ByRef Argument Type Mismatch string into string

1 Answers