VBA - 2D Array to CSV

Here is another version using the FileSystemObject and Strings.Join()

Public Sub SaveAsCSV(ByRef data() As Variant, ByVal sFilename As String, Optional ByVal sDelimiter As String = ",", Optional ByVal quote As String = """")
    Dim fso As New FileSystemObject
    Dim fs As TextStream
    Set fs = fso.OpenTextFile(sFilename, ForWriting, True)
    
    Dim n_rows As Long, n_cols As Long
    n_rows = UBound(data, 1)
    n_cols = UBound(data, 2)
    
    Dim i As Long, j As Long
    Dim temp() As String
    ReDim temp(1 To n_cols)
    For i = 1 To n_rows
        For j = 1 To n_cols
            If IsNumeric(data(i, j)) Then
                temp(j) = CStr(data(i, j))
            Else
                temp(j) = quote & data(i, j) & quote
            End If
        Next j
        
        fs.WriteLine Strings.Join(temp, sDelimiter)
    Next i
    fs.Close

End Sub

A TextStream is a more modern approach to file manipulations.

The single line of code that writes the data is fs.WriteLine Strings.Join(temp, sDelimiter) and the remaining code is just data manipulation to add " to non-numeric values. The Join() command takes an array of strings and combines the elements into a single string with a delimiter. ["a","b","c"] => "a,b,c" so you don't have to manually decide when to write the delimiter and when not.

A sample driving code for the above is (defined under a worksheet)

Public Sub T()

    Dim x() As Variant
    x = Range("C4").Resize(22, 4).Value2
    Call SaveAsCSV(x, "test_data.csv")

End Sub

with results

_{Remember to add a reference to Microsoft Scripting Runtime in order to have access to the FileSytemObject class}

I went via another approach, I hope this helps someone in the future, which i got from here

Public Sub SaveAsCSV(arr_SelectedCMHist() As Variant, sFilename As String, Optional sDelimiter As String = ",", Optional quote As String = """")

Dim n As Long 'counter
Dim M As Long 'counter
Dim sCSV As String 'csv string to print

opf = FreeFile

  Open sFilename For Output As #opf
  For n = 0 To UBound(arr_SelectedCMHist(), 1)
    sCSV = ""
    For M = 0 To UBound(arr_SelectedCMHist(), 2)
      sCSV = sCSV & quote & Format(arr_SelectedCMHist(n, M)) & quote & sDelimiter
    Next M
    sCSV = Left(sCSV, Len(sCSV) - 1) 'remove last Delimiter
    Print #opf, sCSV
  Next n
  Close #opf

End Sub