vbNewline vs Chr(10) as linebreak delimiter in Win

Question

I have an Excel sub that uses the Split() function to split CSV data from a cell into an array. However, depending on the version of Excel/OS I'm using, the character used as the line break delimiter changes:

Excel 2011 / Mac OSX:

 fullArray = Split(CSV, vbNewLine) 'successfully returns array

 fullArray = Split(CSV, Chr(10)) 'fails and returns only a single cell

Excel 2007 / Windows 7:

  fullArray = Split(CSV, Chr(10)) 'successfully returns array

  fullArray = Split(CSV, vbNewLine) 'fails and returns only a single cell

Anyone else noticed this/has an explanation why this is going on?

Answer 1

If you need to support multiple OS (or different versions on the same OS) you can look in to conditional compilation statements.

You can refer to this list of built-in compiler constants:

http://www.utteraccess.com/wiki/index.php/Conditional_Compilation#Built_In_Compiler_Constants

Define your delimiter variable as a string and assign it the result of a function.

Dim dlmt as String

dlmt = newLine()

fullArray = Split(CSV, dlmt)

The function then uses the conditional compilation constant to check the OS:

Function newLine() As String

#If Win32 Or Win64 Then
    ret = Chr(10)
#ElseIf Mac Then
    ret = vbNewLine
#End If

newLine = ret

End Function

Frankly now that I do this I remember it's not strictly necessary to use conditional compile here unless you have methods/properties that won't compile in some versions. You could use the more simple property of Application.OperatingSystem:

Function newLine() As String

Select Case Application.OperatingSystem
    Case Like "Windows*" 
        ret = Chr(10)
    Case Else
        ret = vbNewLine
End Select

End Function

Answer 2

As John mentioned in the comments, the two operating systems have different NewLine character.

And hence before you split it, check for which character is present and then split it. For example

newL = InStr(1, CSV, vbNewLine)
vbChrTen = InStr(1, CSV, Chr(10))

If newL > 0 And vbChrTen > 0 Then
    MsgBox "The string contains both. How would you like to handle it?"
    '
    '~~> Rest of the code
    '
ElseIf newL > 0 Then
    fullArray = Split(CSV, vbNewLine)
ElseIf vbChrTen > 0 Then
    fullArray = Split(CSV, Chr(10))
Else
    MsgBox "The string doesn't contain either of the de-limiters. How would you like to handle it?"
    '
    '~~> Rest of the code
    '
End If