How to put quotes only on strings (not on numbers) in a VBA comma-separated list?

Take a few milli-seconds to convert the array before the Join.

Dim myarray As Variant, mylist As String, i As Longmyarray = Application.Transpose(Application.Transpose(Range("a1:f1").Value2))For i = LBound(myarray) To UBound(myarray)    If Not IsNumeric(myarray(i)) Then _        myarray(i) = Chr(39) & Trim(myarray(i)) & Chr(39)Next imylist = "(" & Join(myarray, Chr(44)) & ")"Debug.Print mylist

Use a loop with a buffer to build the SQL. You won't notice a difference on the performance. You'll also have more control over the type and you'll be able to escape the quote in case a string contains one:

Sub Test()  Dim data()  data = [{ "a",1 ; "b",2 }]  Debug.Print ToSqlInsert("MyTable (Col1, Col2)", data)End SubPublic Function ToSqlInsert(target As String, data()) As String  Dim sb() As String, n As Long, r As Long, c As Long  ReDim sb(0 To UBound(data, 1) * UBound(data, 2) * 2)  sb(n) = "INSERT INTO " & target & " VALUES ("  n = n + 1  For r = 1 To UBound(data, 1)    For c = 1 To UBound(data, 2)      If c > 1 Then sb(n - 1) = ","      Select Case VBA.VarType(data(r, c))        Case vbString:  sb(n) = "'" & Replace$(data(r, c), "'", "''") & "'"        Case vbDate:    sb(n) = Int((data(r, c) - #1/1/1970#) * 86400) ' to epoche '        Case vbEmpty:   sb(n) = "NULL"        Case Else:      sb(n) = data(r, c)      End Select      n = n + 2    Next    sb(n - 1) = "),("  Next  sb(n - 1) = ");"  ToSqlInsert = Join$(sb, Empty)End Function