我實際上是在 Excel 中構建一個資料庫，其中作業表是表格，列是列，行是記錄，目前有點簡單。
我制作了一個函式，如果具有 Value1 和 Value2 的記錄已經在同一行上注冊，則回傳一個布林值，以防止重復。
這是我面臨的問題：我實際上正在為 3 個值匹配執行相同的函式
必須有一種方法可以根據陣列中的值的數量動態地制作它。但我只是堅持它。
有我的 2 個值匹配的初始代碼
Function checkDuplicate(ws As Worksheet, value1 As Variant, value2 As Variant) As Boolean
Dim rng As Range
Dim first As Variant
checkDuplicate= False
If (ws.Name <> "UI" And ws.Name <> "Lists") Then
With ws.Range("A:A")
Set rng = .Find(value1)
If Not rng Is Nothing Then
first = rng.Row
Do
If ws.Range("B" & rng.Row).Value = value2 Then
checkDuplicate= True
End If
Set rng = .FindNext(rng)
Loop While rng.Row <> first
End If
End With
End If
End Function
如果我的英語有點糟糕，或者有人已經幫助另一個人解決了同樣的問題，我深表歉意，因為我在搜索時沒有找到它。
任何幫助將不勝感激。
uj5u.com熱心網友回復：
如果您正在構建資料庫，請考慮使用 SQL
Option Explicit
Sub test()
MsgBox checkDuplicate(Sheet1, Array(1, "ABC", "2021-01-12"))
End Sub
Function checkDuplicate(ws As Worksheet, ar As Variant) As Boolean
Dim cn As Object, cmd As Object, rs As Object
Dim sql As String, arWhere() As String, i As Long
ReDim arWhere(UBound(ar))
For i = 0 To UBound(ar)
arWhere(i) = "[" & ws.Cells(1, i 1) & "] = ?" '
Next
sql = " SELECT COUNT(*) FROM [" & ws.Name & "$] " & _
" WHERE " & Join(arWhere, " AND ")
Debug.Print sql
'Connecting to the Data Source
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 XML;HDR=YES"";"
.Open
End With
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = cn
.CommandText = sql
For i = 0 To UBound(ar)
.Parameters.Append .CreateParameter(CStr(i), 12, 1) ' adVariant
Next
Set rs = .Execute(, ar)
End With
checkDuplicate = rs(0) > 0
cn.Close
End Function
或沒有 ADODB
Option Explicit
Function checkDuplicate(ws As Worksheet, valuesArray As Variant) As Boolean
Dim i As Long, n As Long, j As Long, z As Long
Dim ar
If ws.Name = "Interface" Or ws.Name = "Listes" Then Exit Function
z = LBound(valuesArray)
n = UBound(valuesArray) - z 1
With ws
ar = .UsedRange.Columns(1).Resize(, n)
For i = 1 To UBound(ar)
j = 1
Do
If ar(i, j) <> valuesArray(j z - 1) Then
Exit Do
End If
j = j 1
Loop While j <= n
If j > n Then
checkDuplicate = True
Exit Function
End If
Next
End With
End Function
uj5u.com熱心網友回復：
感謝您的回答
我已經在考慮用 SQL 構建一個資料庫，遺憾的是這并不真正符合我的需求，因為我存盤的資料幾乎沒有“邏輯鏈接”并且非常不同。
沒關系我想通了，但我覺得這段代碼不是很干凈，如果有人知道如何改進它，請隨時回答！
Function checkDuplicate(ws As Worksheet, valuesArray As Variant) As Boolean
Dim rng As Range
Dim first As Variant
Dim i As Long, j As Long
Dim elements As Long
checkDuplicate = False
elements = UBound(valuesArray) - LBound(valuesArray) 1
If (ws.Name <> "Interface" And ws.Name <> "Listes") Then
With ws.Range("A:A")
Set rng = .Find(valuesArray(0))
If Not rng Is Nothing Then
first = rng.Row
Do
i = 1
j = 1
Do
If ws.Cells(i 1, rng.Row).Value = valuesArray(i) Then
i = i 1
Else
j = j 1
End If
Loop Until i = elements Or j = elements
If i = elements Then
checkDuplicate = True
GoTo leave
End If
Set rng = .FindNext(rng)
Loop While rng.Row <> first
End If
End With
End If
leave:
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/365147.html
下一篇：如何使檔案夾路徑通用？