vba - Excel VBA Application.Match错误处理和消息传递

Question

我正在尝试将Excel的数据sheet存储到array中。
数据如下所示:


我使用的代码:

Sub StoreData()

Dim Data() As String

'Count number of Line in Sheet1
Sheet1_size = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

'Array to store data
ReDim Data(1 To Sheet1_size - 1, 1 To 6) As String

'storing data into array
For i = 1 To Sheet1_size - 1

    With Worksheets("Sheet1")

         Data(i, 1) = .Cells(i + 1, Application.Match("Name", .Rows(1), 0))
         Data(i, 2) = .Cells(i + 1, Application.Match("Sex", .Rows(1), 0))
         Data(i, 3) = .Cells(i + 1, Application.Match("Age", .Rows(1), 0))
         Data(i, 4) = .Cells(i + 1, Application.Match("Nationality", .Rows(1), 0))
         Data(i, 5) = .Cells(i + 1, Application.Match("License", .Rows(1), 0))
         Data(i, 6) = .Cells(i + 1, Application.Match("Hand", .Rows(1), 0))

    End With
Next i

End Sub

上面的 sheet1看起来一切正常。

但是，每次在 sheet1中列的顺序和数量可能会有所不同。例如，可能是: Name Age Nationality或 Name License Hand Sex Age Nationality或 Nationality Age等。此表由人们填写，因此他们可能会忘记包括一些变量。

如果缺少任何一列，我将在下面得到一个 error:


我要显示的是 message/message box，其中缺少列名，而不是此错误。如果有几列遗漏，我想向所有遗漏的名称发送消息。

禁用错误不是解决方案，因为屏幕上没有传递消息。有什么办法吗？

Answer 1

这是我建议的解决方案:

Option Explicit
Option Compare Text

Public Sub StoreData()

Dim ws As Worksheet
Dim Data As Variant
Dim LastRow As Long, LastColumn As Long
Dim nColumn As Long, RequirementCount As Long, CheckCount As Long
Dim RequirementList() As String, ErrorMessage As String

'Determine the range
Set ws = ThisWorkbook.Worksheets("Sheet1")
LastRow = ws.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = ws.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
'Range to array
Data = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastColumn)).Value2

'Set requirements
RequirementList = Split("Name|Nationality|Age|License|Hand|Sex", "|")

'Compare all available columns against the requirements
For nColumn = 1 To UBound(Data, 2)
    For RequirementCount = LBound(RequirementList) To UBound(RequirementList)
        If Data(1, nColumn) = RequirementList(RequirementCount) Then
            RequirementList(RequirementCount) = vbNullString
            CheckCount = CheckCount + 1
        End If
    Next RequirementCount
Next nColumn

'If less then the required 6 columns were found then pass a message box to the user telling him/her about it
If CheckCount <> 6 Then
    ErrorMessage = "The following columns are missing:" & Chr(10)
    For RequirementCount = LBound(RequirementList) To UBound(RequirementList)
        ErrorMessage = ErrorMessage & IIf(RequirementList(RequirementCount) = vbNullString, "", "   -" & RequirementList(RequirementCount) & Chr(10))
    Next RequirementCount
    MsgBox ErrorMessage
Else
    MsgBox "All columns are accounted for and ready for import."
End If

End Sub

查看代码中的注释以获取更多信息。另外，请注意 Option Compare Text在代码顶部的重要性，以确保 Age = age = aGe等。