VBA : Supprimer les doublons




Nous voici donc sur le second article concernant le probléme des doublons dans les bases de données. La procédure suivante va nettoyer la base de tous les doublons qu'elle contient, ici nous considérons que les clés en surnombre sont placées dans  la première colonne.
Par sécurité il n'y a pas d'action dans la base de départ, le contenu de la base est stockée dans un tableau a(), puis après traitement, la nouvelle base contenue dans un tableau c() sera récupérée sur une autre feuille de calcul.
Nous utilisons bien sur l'objet Dictionary pour réaliser ce traitement, vous pouvez consulter l'article précédent si vous ne maîtrisez pas complétement cet objet.


Sub Doublons_BDD()

Dim a(), c()
Dim mondico As Object
Dim ligne, i, k As Long

  Application.ScreenUpdating = False


'va suspendre l'affichage à l'écran des travaux de la macro
'ce qui accélére son exécution le nombre de lignes dans la base pouvant être élevé

  Set mondico = CreateObject("Scripting.Dictionary")
 
  '********************************************************************
  'attention je suis sur la feuille ou se trouve ma base
  'sinon je declare un autre dico puis le dico est stockée dans le tableau
  'dim dico2 as object
  'Set dico2 = Sheets("BD")
  'a = dico2.Range("A1").CurrentRegion.Value
  '***************************************************************

 
  a = Range("A1").CurrentRegion.Value
  'La base est stockée dans le tableau a()


  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  ligne = 1
 
  For i = 1 To UBound(a)
    If Not mondico.Exists(a(i, 1)) Then
      mondico.Add a(i, 1), 1
      For k = 1 To UBound(a, 2): c(ligne, k) = a(i, k): Next k
      ligne = ligne + 1
    End If
  Next


  Sheets("BD-2").[A1].Resize(mondico.Count, UBound(a, 2)) = c

'il faut recopier le tableau c() sur la feuille de calcul "BD-2"
End Sub


Bonne lecture et bon courage pour vos adaptations...




VBA : Repérer les doublons



Nous allons consacrés deux articles à la problématique des doublons à l'intérieur
des bases de données. Les doublons pouvant se définir comme deux enregistrement rigoureusement identiques dans une base, les valeurs de tous les champs de l'enregistrement sont communs, ou alors seulement les données d'une colonne  (généralement la colonne qui doit servir de clé unique) sont communes (Numéro de client, E-mail...). Enfin par doublons il faut entendre que la valeur n'est pas forcément doublée, elle peut être triplée etc...



En général nous souhaitons pouvoir repérer les informations présentes en surnombre, cela fera l'objet de cet article. La suppression définitive des enregistrements redondants sera l'objet de l'article suivant.
Il existe plusieurs méthodes et techniques pour intervenir sur cette problématique, j'ai notament montré dans une vidéo du 12/04/2012 comment travailler grâce à la fonction =nb.si() et la commande Validation des données de l'onglet Données.

 En terme de programmation le bon réflexe est de penser Collection et Dictionary. Le premier objet étant réputé très lent en terme de résultat, nous allons ici présenter l'objet Dictionary.
 Cet objet associe des valeurs (nommées Items) à des clés (Keys). Il s'agit en fait d'un tableau à une dimension dont on accède aux éléments par une clé et non par un indice.

Soit un Dictionnaire MonDico :


 Keys   Items 
bleu2
rouge6
vert9
blanc4
noir7
 

Les métodhes et propriétés suivantes peuvent être associés au Dictionnaire :

Add clé,élémentAjoute une clé et la valeur associée
Exists(clé)Teste l'existence d'une clé
Tbl=ItemsDonne dans un tableau les éléments
Tbl=Keys Donne dans un tableau les clés
Remove (clé) Suprime la clé
Removeall Supprime tous les éléments
Count Donne le nombre d'éléments
Item(clé) =valeurModifie la valeur de la clé
Item(clé)Donne la valeur associée à la clé
CompareMode=vbTextCompare  Ignore la casse
 

Attention ! Vous ne pourrez pas utiliser l'objet Dictionary si vous n'avez pas chargé la bibliothéque "Scripting". Cocher la ligne "MicroSoft Scripting Runtime" dans la commande Outils / références... de votre éditeur VBA.

La procédure ci dessous va vous permettre de vous familiariser avec les manipulations de base en matière de dictionnaire. Pour ceux qui veulent étudier cette question de manière plus approfondie, lisez l'excellent article de Jacques Boisgontier.


Option Explicit
Option Base 1
Sub ListeDictionnaire()

Dim mondico As Object, cle
Set mondico = CreateObject("Scripting.Dictionary")
'déclaration classique de l'objet dictionnaire notez toutefois le ,cle
'qui servira de variable (type range) pour la colonne clé

Dim TabC()
'tableau non typé et non dimensionné pour recevoir les clés du dictionnaire
Dim TabI()
'tableau non typé et non dimensionné pour recevoir les items du dictionnaire

'*** Les trois méthodes permettant l'association des items aux clés ***
  mondico.item("bleu") = 2
  mondico.item("rouge") = 6
  mondico("vert") = 9
  If Not mondico.Exists("violet") Then mondico.Add "blanc", 4
  mondico.item("noir") = 7
 
 '*** vérifions l'item pour une clé ***
  cle = "noir"
  MsgBox cle & " : " & mondico.item(cle), vbOKOnly + vbInformation
 
  '*** vérifions tous les items de  toutes les clés ***
  For Each cle In mondico.Keys
     MsgBox cle & " : " & mondico.item(cle), _
     vbOKOnly + vbInformation, vbOKOnly + vbInformation, _
     " Clés + Item : "
  Next cle
 
  '*** pour passer des éléments clés et items dans des tableaux ***
  TabC = mondico.Keys
  TabI = mondico.Items
  MsgBox TabC(3) & " : " & TabI(3), _
  vbOKOnly + vbInformation, vbOKOnly + vbInformation, _
  " Clés + Item : "
   
 '*** faire des Statistiques sur les items ***
MsgBox "Total : " & Application.Sum(mondico.Items) _
 & " - Moyenne : " & Application.Average(mondico.Items), _
 vbOKOnly + vbInformation, "Statistiques : "
End Sub

Venons en maintenant au probléme de repérage des doublons, analysons ensemble la procédure suivante qui va repérer par un code couleur touts les doublons de ma base sur la colonne que j'ai choisi comme étant la clé de mon dictionnaire.

Sub Doublons_repere()

Dim mondico As Object, cle
Set mondico = CreateObject("Scripting.Dictionary")
 
Columns("A:A").Select
Selection.Interior.ColorIndex = xlNone
'nous travaillons donc sur la colonne A
'pour chaque clé de A2 jusqu'à la fin de la colonne

  For Each cle In Range("a2", Range("a2").End(xlDown))
  'on va affecter à chaque clé du dictionnaire un item = à 1
     mondico.item(cle.Value) = mondico.item(cle.Value) + 1
     'si la clé est présente 2 fois ou plus on aura un item > à 1
  Next cle
 
  For Each cle In Range("a2", Range("a2").End(xlDown))
   'reste alors le coloriage du fond des cellules pour les item
      'présents 2 fois ou plus

    If mondico.item(cle.Value) > 1 Then cle.Interior.ColorIndex = 6
  Next cle
End Sub

Bonne lecture et bon courage pour vos adaptations...


 

VBA : La fonction OnTime




Comment déclencher l’exécution d’une macro commande ou d’une procédure VBA en fonction du temps, c’est-à-dire comment créer un minuteur pouvant déclencher l’exécution d’une action à une date précise ou après un intervalle de temps déterminé. Nous allons étudier ici la méthode OnTime de l’objet application qui permet d’arrivée à ce résultat. Cette méthode es décrite à l’aide de 4 paramètres que nous allons décrire ici. La syntaxe en est :


Application.OnTime EarliestTime, Procedure, [LatestTime], [Schedule]

 

EarliestTime

 

(Argument obligatoire) est la valeur temps qui indique le moment de démarrage d’une procédure. Cette programmation horaire peut s’écrire et se concevoir e deux manières :

 

Lancer une procédure à une heure précise : 

 

Dans ce premier exemple (Sub attendre_exemple1) l’exécution de la macro aura lieu à 10 h et 53’. Nous utilisons la fonction TimeValue( ) qui va retourner une variable de type date contenant l’heure. Ensuite la méthode OnTime exécutera la procédure affiche_1 (noter que le nom de la procédure est utilisé sous la forme d’une chaîne de texte). La boite de dialogue affiche alors l’heure courante.

Public heure As Date ‘ou Variant
Sub attendre_exemple1() ‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = TimeValue("10:53:00")
Application.OnTime heure, "affiche_1", , True
End Sub
Sub affiche_1()
MsgBox "ll est : " & heure, vbOKOnly + vbInformation, "Horloge : "
End Sub

 

Lancer une procédure après un délai imposé :

 

Dans ce deuxième exemple il faudra attendre 10 secondes pour voir la macro s’exécuter. Nous utilisons la fonction Now qui va retourner une variable de type date contenant l’heure et la date système de l’ordinateur à laquelle nous allons ajouter le délai. OnTime exécutera la procédure affiche_2. La boite de dialogue affiche ici la date système complète.

Public heure As Date
Sub attendre_exemple_2()‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = Now + TimeValue("00:00:10")
Application.OnTime heure, "affiche_2", , True
End Sub
Sub affiche_2()
MsgBox heure, vbOKOnly + vbInformation, "Horloge : "
End Sub

 

Procedure

 

(Argument obligatoire) est la valeur de type chaîne de texte qui contient le nom de la procédure à exécuter.

LatestTime


(Argument facultatif) est la valeur temps qui indique le délai maximal d’attente d’Excel en cas d’indisponibilité de ce dernier (exécution d’une autre procédure en cours). Si le logiciel n'est pas disponible au bout de ce délai, la procédure ne s'exécutera pas. Si ce paramètre est omis, le logiciel peut attendre indéfiniment avant l’exécution de OnTime, il semble donc que cette seconde option soit recommandable.
Si vous devez indiquer une valeur LatestTime, vous pouvez la calculer à partir de EarliestTime.


LatestTime = EarliestTime + 10 'pour attendre 10 secondes la disponibilité d’Excel.

Schedule

 

 (Argument facultatif) est la valeur de type booléen qui indique si la procédure doit être exécutée ou non. La valeur par défaut est True. Le problème est que pour stopper une procédure OnTime il faut renvoyer à nouveau cette dernière en paramétrant la valeur de Schedule à False. Cette opération générant une erreur il faudra utiliser le processus habituel en matière de gestion d’erreur « On Error Resume Next » qui détournera tous messages d’erreurs liés aux instructions suivantes.

Dans ce troisième et dernier exemple, nous allons afficher à quatre reprises (pendant une minute) l’heure courante dans la cellule A1 (mise en format hh:mm:ss) avec un intervalle de 15 secondes entre chaque nouvel affichage, puis nous interromprons la procédure en passant l’argument Schedule à False, c’est seulement de cette manière que l’arrêt de la méthode OnTime s’effectue convenablement.

Public heure As Date
Public compteur As Byte
Sub attendre_exemple_3()()‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = Now + TimeValue("00:00:15")
Application.OnTime heure, "affiche_3", , True
End Sub
Sub affiche_3()
Dim EcrireH As String
Range("a1").ClearContents
EcrireH = heure
Range("a1").Value = EcrireH
compteur = compteur + 1
If compteur = 4 Then
MsgBox "TERMINE", vbOKOnly + vbInformation, "Horloge : "
Range("a1").ClearContents
compteur = 0
On Error Resume Next
Application.OnTime heure, "affiche_3", , False
Else
attendre_exemple_3
End If
End Sub

Bon courage pour vos tests et vos adaptations...


Excel 2013 : Le mode SDI





Dans la nouvelle version du logiciel Excel 2013 le mode SDI (single document interface) remplace le mode MDI (multiple document interface), d'accord mais de quoi s'agit il concrètement ? En fait à chaque ouverture d'un nouveau classeur celui ci apparaitra dans une nouvelle fenêtre du logiciel.

Ainsi vous n'avez plus plusieurs fenêtres de documents à l'intérieur d'un seule instance du logiciel, donc il devient possible par exemple d'afficher deux classeurs sur deux écrans distinctes. Cela facilite la consultation des classeurs et l'ensemble des travaux en multi-fenêtrage comme le  passage d'informations d'un classeur à l'autre par exemple. 
Cette petite vidéo vous en fait la démonstration.




Excel 2013 : Le mode SDI par O_Picot_chez_AV


Bonne consultation...



top