excel - vbNewline vs Chr(10) as linebreak delimiter in Windows vs. Mac OSX

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