如何在VBA逗号分隔列表中仅为字符串（非数字）添加引号？

Great question! When dealing with 50k+ rows, cutting down on unnecessary type conversions in PostgreSQL and avoiding explicit column loops in VBA is critical for keeping performance snappy. Here’s a clean, loop-free solution that formats your array values correctly—adding quotes only to non-numeric entries:

The Solution Code #

' Assume myRange is your target single-row Excel range
Dim formattedValues As Variant
Dim myList As String

' Use Excel's array evaluation to format all values in one go
formattedValues = myRange.Parent.Evaluate( _
    "IF(ISNUMBER(" & myRange.Address & ")," & _
    myRange.Address & "," & _
    """'""&" & myRange.Address & "&""'""")

' Convert the 2D range array to a 1D array compatible with Join
formattedValues = Application.Transpose(Application.Transpose(formattedValues))

' Build the final VALUES clause for your INSERT statement
myList = "(" & Join(formattedValues, ",") & ")"

How This Works #

• Single-step formatting: Evaluate runs an array formula across the entire range at once:
  • Numeric values stay unquoted (so PostgreSQL reads them directly as numeric types, no conversion overhead)
  • Non-numeric values get wrapped in single quotes (e.g., 'a', 'd')
• Array conversion: The double Transpose turns the 2D array returned by Evaluate into a 1D array that the Join function can process
• Final output: For your example array ("a","b",32,"d",17.2), this produces ('a','b',32,'d',17.2) exactly as needed

Edge Case Adjustments #

• Empty cells: If you need to insert NULL instead of empty strings (''), update the IF formula to check for blanks first:

  formattedValues = myRange.Parent.Evaluate( _
      "IF(ISBLANK(" & myRange.Address & "),""NULL""," & _
      "IF(ISNUMBER(" & myRange.Address & ")," & myRange.Address & ",""'"&"&" & myRange.Address & "&""'"")")
  

• Date values: Excel stores dates as numbers, so add a check to format them into PostgreSQL’s 'YYYY-MM-DD' format:

  formattedValues = myRange.Parent.Evaluate( _
      "IF(ISDATE(" & myRange.Address & "),""'""&TEXT(" & myRange.Address & ",""YYYY-MM-DD"")&""'""," & _
      "IF(ISNUMBER(" & myRange.Address & ")," & myRange.Address & ",""'"&"&" & myRange.Address & "&""'"")")
  

This approach keeps your code concise, avoids slow loop logic, and eliminates unnecessary type conversion work for PostgreSQL—ideal for large datasets.

内容的提问来源于stack exchange，提问作者R. Bourgeon