VBA::合并阵列

Question

我正在创建一个搜索函数，该函数允许用户在数据库中同时搜索多达3个不同的属性（prop1,2和3），并且我通过将搜索到的prop的结果放入数组，在VBA中创建了此子。但是，现在我最多有3个阵列，我需要合并这些阵列，以便在结果中只显示阵列中重复的数据。关于如何1）只查看用户正在搜索的属性的数组，以及2）只将重复的数据放入最终数组，以便在结果范围内显示，有什么建议吗？非常感谢您的帮助！谢谢

Answer 1

假设您的条目直接来自数据库，因此对于一个属性是唯一的，我可以想出以下步骤来获得一个简单的解决方案：

1. 将数组合并在一起（prop1、pro2和prop3temp）
2. 计数每个元素的出现次数（在本例中，代码为tempCount）

3. 根据出现的情况，创建最终数组（此处称为结果）

   Dim prop1() As Variant
   Dim prop2() As Variant
   Dim prop3() As Variant
   Dim temp() As Variant
   Dim tempCount() As Integer
   Dim result() As Variant
   ReDim temp(UBound(prop1) + UBound(prop2) + UBound(prop3) + 1)
   'merge arrays
   Dim i As Integer
   On Error Resume Next
       For i = 0 To UBound(temp)
       temp(i * 3) = prop1(i)
       temp(i * 3 + 1) = prop2(i)
       temp(i * 3 + 2) = prop3(i)
   Next i
   'count occurences
   ReDim tempCount(UBound(temp) + 1)
   Dim j As Integer
   For i = 0 To UBound(temp)
   tempCount(i) = 1
   For j = 0 To i - 1
   'comparison of elements
       If temp(i) = temp(j) Then
       tempCount(i) = tempCount(i) + 1
       End If
   Next j
   Next i
   ReDim result(UBound(temp) + 1)
   'if an element occurs 3 times, add it to result
   Dim count As Integer
   count = 0
   For i = 0 To UBound(tempCount)
       If tempCount(i) = 3 Then
           result(count) = temp(i)
           count = count + 1
       End If
   Next i
   

为了检查一些样本，我将其添加到代码中。它只需将数组temp、result和tempCount打印到列A、B和C。

'some sample arrays
prop1 = Array("a", "b", "c", "d", "e")
prop2 = Array("b", "c", "f")
prop3 = Array("b", "c", "d", "g")
'some sample Output
'temp
Cells(1, 1).Value = "temp:"
For i = 0 To UBound(temp)
    Cells(i + 2, 1).Value = temp(i)
Next i
'result
Cells(1, 2).Value = "result:"
For i = 0 To UBound(result)
    Cells(i + 2, 2).Value = result(i)
Next i
'count:
Cells(1, 3).Value = "count:"
For i = 0 To UBound(tempCount)
    Cells(i + 2, 3).Value = tempCount(i)
Next i

注意：tempCount只保存元素被观察点的累计出现次数。