VBA function to return array from string input

Question

I wrote a function to do some basic string parsing but for reasons currently unknown to me, my compiler keeps flagging my code for a type mismatch error. The function takes a string, replaces some characters with a space and then uses a split to turn the output into an array. The function is then set to the output array. When run, the function complete's its process but the compiler then throws the type mismatch error in the calling module. Code is below, thank you in advance for your assistance.

Sub Tester()

Dim TestArr()
Dim TestVar As String

Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False

TestVar = "Text-with/characters I need to\remove"

Debug.Print InputParser(TestVar)

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

'-----------------------------------------------------------------------------------------------------------------------------------

Function InputParser(InputStr As String)

Dim OutputArr() As String

If InStr(1, InputStr, "/", vbBinaryCompare) >= 1 Or _
    InStr(1, InputStr, "\", vbBinaryCompare) >= 1 Or _
    InStr(1, InputStr, "-", vbBinaryCompare) >= 1 Or _
    Left(InputStr, 1) = "'" Then.

        InputStr = Replace(InputStr, "/", " ", 1, , vbBinaryCompare)
        InputStr = Replace(InputStr, "\", " ", 1, , vbBinaryCompare)
        InputStr = Replace(InputStr, "-", " ", 1, , vbBinaryCompare)
        InputStr = Replace(InputStr, "'", vbNullString, 1, 1, vbBinaryCompare)
        OutputArr = Split(InputStr, " ", Compare:=vbBinaryCompare)
        Debug.Print Join(OutputArr, vbCrLf)
        Debug.Print TypeName(OutputArr)
End If

InputParser = OutputArr
Debug.Print TypeName(InputParser)

End Function

Debug.print expects a string or something which can be coerced to a string. You cannot debug.print an array. Try join() ing it first. — Tim Williams
– Tim Williams, Commented Apr 15, 2013 at 15:01
lol... Yes, yes it does... Thanks Tim, I don't know why that did not occur to me. Stackoverflow always has a way of making me feel dumb. — user1902208
– user1902208, Commented Apr 15, 2013 at 15:04

Nick · Accepted Answer · 2013-04-16 23:39:08Z

The problem is when you use Debug.Print to try to see the results of the function.

You can't debug.print an array!

Otherwise, the tweaked function provided by Jonners works fine.

If you need to see what's in the returned array, you will need to put a breakpoint after the array has been created, and then use the Locals window to examine it.

Also, Split annoyingly returns NULL if it doesn't have anything to work with. I created SmartSplit which I use instead of Split. SmartSplit returns the original string in an array if no actual splitting occurs. This means that you don't need to test for an array after using Split.

'------------------------------------------------------------------------------------------
'Procedure : SmartSplit
'Date : 2 March 2011
'Author : Nick Pullar
'Purpose : Enhances Split by outputting an array of one element if the strDelimiter is not found in strString
'Arguments : strString: The string that needs to be split
' strDelimiter: the string what holds the delimiter
'------------------------------------------------------------------------------------------
Function SmartSplit(ByVal strString As String, ByVal strDelimiter As String) As Variant
'Splits the string by strDelimiter using Split if at least one instance of strDelimiter is found in strString
'Otherwise just returns strString as an array as would be the result if Split had done its work
    Dim varResult As Variant

    If InStr(strString, strDelimiter) = 0 Then
        ReDim varResult(0)
        varResult(0) = strString
    Else
        varResult = Split(strString, strDelimiter)
    End If
    SmartSplit = varResult
End Function

I hope this helps.

Nick

Eight-Bit Guru · Accepted Answer · 2013-04-15 14:41:41Z

0

Firstly, you don't need the conditional test around the 'replace' - if the input string doesn't contain the target character, Replace won't do anything.

Secondly, your output array is only populated if you do actually replace something; in the situation where the string does not contain anything that needs replacing, your function returns a 'null' array.

Tweaked function:

Function InputParser(InputStr As String)
  Dim OutputArr() As String

  InputStr = Replace(InputStr, "/", " ", 1, , vbBinaryCompare)
  InputStr = Replace(InputStr, "\", " ", 1, , vbBinaryCompare)
  InputStr = Replace(InputStr, "-", " ", 1, , vbBinaryCompare)
  InputStr = Replace(InputStr, "'", vbNullString, 1, 1, vbBinaryCompare)

  OutputArr = Split(InputStr, " ", Compare:=vbBinaryCompare)

  Debug.Print Join(OutputArr, vbCrLf)
  Debug.Print TypeName(OutputArr)

  InputParser = OutputArr
  Debug.Print TypeName(InputParser)
End Function

answered Apr 15, 2013 at 14:41

Eight-Bit Guru

10k2 gold badges50 silver badges63 bronze badges

2 Comments

user1902208 Over a year ago

Thanks! I dropped your tweaked function in and although you pointed out some definite flaws in its logic, I'm still getting that type mismatch error... any ideas on that?

Eight-Bit Guru Over a year ago

Well, you do a Debug.Print on the Joined array in the Function, but on the native array in the caller - I'm no expert on VBA specifically, but maybe Debug.Print doesn't know what to do with an array...?

Collectives™ on Stack Overflow

VBA function to return array from string input

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related