■

Option Explicit

'データのフォーマットチェック用
Public Enum CheckFormatPattern
    Character
    Time
    Date
    Numbers
    NumHanToZen
    NumZenToHan
End Enum

'処理機能毎のパターン
Public Type ShoriPattern
    title As String                                     '列名称
    Required As Boolean                                 '必須
    Digits As Integer                                   '桁数
    checkFormat() As CheckFormatPattern                 'フォーマットチェック
    Grant As Boolean                                    '付与(コーテーションを付けるか)
    OutputFormat As String                              '出力時フォーマット
End Type

'処理機能毎のパターンデータ
Public Type ShoriDataInfo
    ColumnsCount As Integer
    pattern() As ShoriPattern
End Type


Public Enum aaaCellInfo
    Number                                              '連番
    PhoneNumber                                         '電話番号
    UnkoDate                                            '運行日付
End Enum

Sub Sample2()
    Dim buf  As String
    Dim colInfo As ShoriDataInfo
    Dim testData As Variant
    Dim colCount As Integer
    Dim fmtCount As Integer
    testData = Array("1", "09012345678", "11:33")
    
    colInfo = GetShoriDataInfo("aaa")
    
    
    'check
    For colCount = 0 To colInfo.ColumnsCount
        
        'データが空文字かチェック
        If testData(colCount) = "" Then
            If colInfo.pattern(colCount).Required = True Then
                '★エラーメッセージ
                GoTo Continue
            Else
                '必須出ない場合でも、以降の判定処理は行わない
                GoTo Continue
            End If
        End If
        
        '★不要なダブルコーテーション、シングルコーテーション、空白を削除
        
        '桁数チェック
        If LenB(StrConv(testData(colCount), vbFromUnicode)) > colInfo.pattern(colCount).Digits Then
            '★エラーメッセージ
            GoTo Continue
        End If
        
        'フォーマットチェック
        For fmtCount = 0 To UBound(colInfo.pattern(colCount).checkFormat)
            Call checkFormat(CStr(testData(colCount)), colInfo.pattern(colCount), fmtCount)
        Next

        
        
        
        
Continue:
        Next
    
    
    
    
    
    
    
    
    
    
'    Open ThisWorkbook.Path & "\Sample.csv" For Input As #1
'    Do Until EOF(1)
'            Line Input #1, buf
'            ''読み込んだデータをセルに代入する
'    Loop
'    Close #1
End Sub


Public Function GetShoriDataInfo(shoriName As String) As ShoriDataInfo
    Dim name As String
    Dim shoriInfo As ShoriDataInfo
    Dim colCount As Integer
    
    Select Case shoriName
        Case "aaa"
            

            ReDim Preserve shoriInfo.pattern(colCount) As ShoriPattern
            shoriInfo.pattern(colCount).title = "連番"
            shoriInfo.pattern(colCount).Required = True
            shoriInfo.pattern(colCount).Digits = 10
            ReDim Preserve shoriInfo.pattern(colCount).checkFormat(0) As CheckFormatPattern
            shoriInfo.pattern(colCount).checkFormat(0) = CheckFormatPattern.NumZenToHan
            ReDim Preserve shoriInfo.pattern(colCount).checkFormat(1) As CheckFormatPattern
            shoriInfo.pattern(colCount).checkFormat(1) = CheckFormatPattern.Numbers
            shoriInfo.pattern(colCount).Grant = False
            shoriInfo.pattern(colCount).OutputFormat = ""
            
            colCount = colCount + 1

            ReDim Preserve shoriInfo.pattern(colCount) As ShoriPattern
            shoriInfo.pattern(colCount).title = "電話番号"
            shoriInfo.pattern(colCount).Required = False
            shoriInfo.pattern(colCount).Digits = 11
            ReDim Preserve shoriInfo.pattern(colCount).checkFormat(0) As CheckFormatPattern
            shoriInfo.pattern(colCount).checkFormat(0) = CheckFormatPattern.Numbers
            shoriInfo.pattern(colCount).Grant = False
            shoriInfo.pattern(colCount).OutputFormat = ""
            
            colCount = colCount + 1
            
            ReDim Preserve shoriInfo.pattern(colCount) As ShoriPattern
            shoriInfo.pattern(colCount).title = "運行日付"
            shoriInfo.pattern(colCount).Required = False
            shoriInfo.pattern(colCount).Digits = 10
            ReDim Preserve shoriInfo.pattern(colCount).checkFormat(0) As CheckFormatPattern
            shoriInfo.pattern(colCount).checkFormat(0) = CheckFormatPattern.Date
            shoriInfo.pattern(colCount).Grant = False
            shoriInfo.pattern(colCount).OutputFormat = "hh:nn"
            
            shoriInfo.ColumnsCount = colCount
    End Select


    GetShoriDataInfo = shoriInfo
End Function


Private Sub checkFormat(targetData As String, pattern As ShoriPattern, fmtCount As Integer)
    Dim numCount As Integer
    Dim numData As String
    Select Case pattern.checkFormat(fmtCount)
        Case CheckFormatPattern.NumHanToZen
            numData = StrConv(targetData, vbWide)
            
            For numCount = 1 To Len(numData)
                If Not Mid(numData, numCount, 1) Like "[０-９]" Then
                    'エラーメッセージ
                End If
            Next numCount
        Case CheckFormatPattern.NumZenToHan
            numData = StrConv(targetData, vbNarrow)
            
            For numCount = 1 To Len(numData)
                If Not Mid(numData, numCount, 1) Like "[0-9]" Then
                    'エラーメッセージ
                End If
            Next numCount
        Case CheckFormatPattern.Numbers
            numData = StrConv(targetData, vbNarrow)
            
            For numCount = 1 To Len(numData)
                If Not Mid(numData, numCount, 1) Like "[0-9]" Then
                    'エラーメッセージ
                End If
            Next numCount
        Case CheckFormatPattern.Date
            If IsDate(Format(CStr(targetData), pattern.OutputFormat)) = False Then
                'エラーメッセージ
            End If
    End Select
End Sub