1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
| Option Explicit
Public Const indSheetMaster = 1 ' Source: Master
Public Const rowMasterPendenz = 1 ' le no de ligne
Public Const colMasterPendenz = 9 ' le numéro de la pendance (Property Key)
Public Const indSheetSlave = 2 ' Target: Slave
Public Const rowSlavePendenz = 1 ' le no de ligne
Public Const colSlavePendenz = 2 ' le numéro de la pendance
Function IsInCollection(ByVal collList As Collection, ByVal strKey As String) As Boolean
Dim lngRow As Integer, objItem As Object
On Error Resume Next
Set objItem = collList(strKey)
IsInCollection = Err.Number = 0
On Error GoTo 0
End Function
Function GetPendenz(ByVal collList As Collection, ByVal strKey As String) As Pendenz
On Error Resume Next
Set GetPendenz = collList(strKey)
If Err.Number <> 0 Then
Set GetPendenz = Nothing
End If
On Error GoTo 0
End Function
Sub MasterToSlave()
Dim objPendenz As Pendenz, pzObj As Pendenz, rngSource As Range, rngTarget As Range
Dim lngMasterRow As Integer, strKey As String, collPendenz As Collection
Set rngSource = Worksheets(indSheetMaster).Range("A1:A9")
Set rngTarget = Worksheets(indSheetSlave).Range("A1:A9")
Set objPendenz = New Pendenz
With objPendenz
.FremdNr = rngSource.Cells(rowMasterPendenz, colMasterPendenz)
.Row = rngSource.Row
End With
Set collPendenz = New Collection
collPendenz.Add objPendenz, key:=CStr(objPendenz.FremdNr)
' je parcours le Range XL des pendances dans le slave
strKey = CStr(rngTarget.Cells(rowSlavePendenz, colSlavePendenz))
If IsInCollection(collPendenz, strKey) Then ' Check if the key exists
' je lis le no de ligne dans la collection des objets
lngMasterRow = collPendenz(strKey).Row ' Second access
End If
Set pzObj = GetPendenz(collPendenz, strKey) ' Get the item at strKey
If Not pzObj Is Nothing Then ' Check if the item exists
lngMasterRow = pzObj.Row ' Direct access from the item
End If
collPendenz.Remove strKey ' Simulate the deletion of the Pendenz at strKey
Set objPendenz = Nothing
Set pzObj = GetPendenz(collPendenz, strKey)
If pzObj Is Nothing Then
Debug.Print "Cannot find the key " + strKey
End If
Set collPendenz = Nothing
End Sub |
Partager