vba - 在VBA中设置数据结构

Question

我正在寻找set Excel VBA 中使用的数据结构。到目前为止我发现的是 Scripting.Dictionary ，它似乎是 map .

VBA中也有类似集合的东西吗？

基本上，我正在寻找一种数据结构，可以有效地查找是否已添加特定值。

Answer 1

看看.NET ArrayList ，它有Add、Contains、Sort等方法。您可以在VBS和VBA环境中实例化该对象:

Set ArrayList = CreateObject("System.Collections.ArrayList")

Scripting.Dictionary 也可能满足需求，它具有唯一的键，Exists 方法允许检查键是否已在字典中。

但是，对于这种情况，通过 ADODB 进行 SQL 请求可能会更有效。以下示例展示了如何通过对工作表的 SQL 查询检索唯一行:

Option Explicit

Sub GetDistinctRecords()

    Dim strConnection As String
    Dim strQuery As String
    Dim objConnection As Object
    Dim objRecordSet As Object

    Select Case LCase(Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".")))
        Case ".xls"
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='" & ThisWorkbook.FullName & "';Mode=Read;Extended Properties=""Excel 8.0;HDR=YES;"";"
        Case ".xlsm", ".xlsb"
            strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source='" & ThisWorkbook.FullName & "';Mode=Read;Extended Properties=""Excel 12.0 Macro;HDR=YES;"";"
    End Select

    strQuery = "SELECT DISTINCT * FROM [Sheet1$]"
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open strConnection
    Set objRecordSet = objConnection.Execute(strQuery)
    RecordSetToWorksheet Sheets(2), objRecordSet
    objConnection.Close

End Sub

Sub RecordSetToWorksheet(objSheet As Worksheet, objRecordSet As Object)

    Dim i As Long

    With objSheet
        .Cells.Delete
        For i = 1 To objRecordSet.Fields.Count
            .Cells(1, i).Value = objRecordSet.Fields(i - 1).Name
        Next
        .Cells(2, 1).CopyFromRecordset objRecordSet
        .Cells.Columns.AutoFit
    End With

End Sub

源数据应放置在Sheet1上，结果输出到Sheet2。该方法的唯一限制是 ADODB 连接到驱动器上的 Excel 工作簿，因此应在查询之前保存任何更改以获得实际结果。

如果您只想获取非不同行的集合，则查询应如下所示(仅作为示例，您必须将字段集放入查询中):

    strQuery = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country FROM [Sheet1$] GROUP BY CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country HAVING Count(*) > 1"