ExcelVBA CSV読み込み時、金額等にカンマが含まれる場合の対策と部品

■呼び出し元

Sub test()
Dim originalString As String
Dim modifiedString As String
originalString = """A12345"",""山田太郎"",""5,000"",""s,a"""
modifiedString = ReplaceCommaWithColon(originalString)
MsgBox modifiedString
End Sub

■部品：区切り文字をカンマからコロンに変換（ただし各アイテムに含まれるカンマは変換しない）

Function ReplaceCommaWithColon(csvString As String) As String
'----------------------------------------------------------
'機能: 受け取った文字列のカンマをコロンに変換する｡
'      ただし､ダブルクォーテーションで囲まれているカンマは置き換えない
'引数: 対象の文字列
'戻り値: カンマをコロンに変換後の文字列
'---------------------------------------------------------
    Dim inQuotes As Boolean
    Dim resultString As String
    Dim char As String
    Dim nextChar As String
    
    inQuotes = False
    resultString = ""
    
    For i = 1 To Len(csvString)
        char = Mid(csvString, i, 1)
        If char = """" Then
            inQuotes = Not inQuotes
        End If
        
        If char = "," And Not inQuotes Then
            nextChar = Mid(csvString, i + 1, 1)
            If Not IsNumeric(nextChar) Or inQuotes Then
                resultString = resultString & ":"
            Else
                resultString = resultString & char
            End If
        Else
            resultString = resultString & char
        End If
    Next i
    
    ReplaceCommaWithColon = resultString
End Function

■コード組み込み例

'csvデータを行ごとに分割
csvLines = Split(ReplaceCommaWithColon(csvData), vbCrLf)

For Each csvLine In csvLines
    'CSVデータをコロンで分割
    csvFields = Split(csvLine, ":")
    'ここでcsvFieldsを処理する
Next csvLine