Rechercher les liaisons externes liées à une validation

Bonjour à tous,

La présence de liens externes est un problème assez courant et assez très très sioux.

Il faut en général:
- faire une recherche dans l'ensemble du classeur pour un "[" ou un ".xl" (CTRL+F, et choisir de chercher sur tout le classeur)
- vérifier les noms : onglet Formules, Gestionnaire de noms
- vérifier les mises en forme conditionnelle : pour un prochain article.
- vérifier les validations

Voici donc une petite macro qui va permettre de parcourir l'ensemble des validations de données d'un classeur pour identifier celles qui pourraient contenir des liaisons externes.

Une fois la macro exécutée, une nouvelle feuille est ajoutée au fichier.

Vous noterez que j'utilise des variables tableaux pour stocker le résultat. Ça n'est pas nécessaire, mais cela accélère grandement l'exécution du code, donc c'est une bonne pratique au final, surtout si vous avez des dizaines de milliers de lignes.

Option Explicit

Sub TrouveLiensExternesValidation()
    'Definition des variables
    Dim rgeCell As Range
    Dim sDvForm As String
    Dim counter As Integer
    Dim wksResult As Worksheet
    Dim wks As Worksheet
    Dim arrNomFeuille As Variant
    Dim arrAdresseCellule As Variant
    Dim arrFormule As Variant
    ReDim arrNomFeuille(1 To 1) As Variant
    ReDim arrAdresseCellule(1 To 1) As Variant
    ReDim arrFormule(1 To 1) As Variant
    Dim arrResult As Variant
    
    Dim lRow As Long
    Dim lRowResultat As Long
    'masquer l'actualisation
    Application.ScreenUpdating = False
    'compteur
    lRow = 1
    'on boucle sur toutes les feuilles
    For Each wks In ActiveWorkbook.Worksheets
        wks.Visible = xlSheetVisible
        'on boucle sur toutes les cellules de la feuille
        For Each rgeCell In wks.UsedRange.Cells
            'On reprend la formule
            On Error Resume Next
            sDvForm = ""
            sDvForm = rgeCell.Validation.Formula1
            On Error GoTo 0
            'il y a plusieurs options. On peut tester la presence d'un "[" ou bien du ".xl"
            If InStr(1, sDvForm, ".xl") > 0 Then 'on trouve un ".xl"
                lRow = lRow + 1
                'on peut faire un Preserve, car une seule dimension dans le tableau
                ReDim Preserve arrNomFeuille(1 To lRow) As Variant
                ReDim Preserve arrAdresseCellule(1 To lRow) As Variant
                ReDim Preserve arrFormule(1 To lRow) As Variant
                'on stocke les donnees
                arrNomFeuille(lRow) = wks.Name
                arrAdresseCellule(lRow) = rgeCell.Address
                arrFormule(lRow) = "'" & sDvForm
            End If
        Next rgeCell
    Next wks
    'on rapatrie les resultats dans la feuille
    'On revient en A1
    If lRow <> 0 Then
        'creer la feuille de resultat
        Set wksResult = ActiveWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets(1))
        'wksResult.Name = "external links"
        ReDim arrResult(1 To lRow, 1 To 3) As Variant
        arrResult(1, 1) = "Nom de la feuille"
        arrResult(1, 2) = "Adresse de la cellule"
        arrResult(1, 3) = "Formule"
        For lRowResultat = 2 To UBound(arrNomFeuille, 1)
            arrResult(lRowResultat, 1) = arrNomFeuille(lRowResultat)
            arrResult(lRowResultat, 2) = arrAdresseCellule(lRowResultat)
            arrResult(lRowResultat, 3) = arrFormule(lRowResultat)
        Next lRowResultat
        wksResult.Range("A1:C" & UBound(arrResult, 1)).Value = arrResult
    End If
    Application.ScreenUpdating = True
End Sub

Source d'inspiration : http://dailydoseofexcel.com/archives/2009/05/04/finding-external-links-in-data-validation/#comment-1045713

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Automatisation de l’hyper-productivité

Et on continue sur la lancée de cette astuce , en permettant l'automatisation de la création des corrections automatiques.

Voici un petit fichier qui va vous permettre juste ca :
Téléchargez le fichier ici.

Ouvrez le fichier, activez les macros, et cliquez sur le bouton, puis tapez ii[ESPACE] ou iii[ESPACE] ou encore iie[ESPACE] dans une cellule pour immédiatement voir le résultat. Vous pouvez ensuite double-cliquer sur les arguments de la formules pour les remplacer par la plage appropriée.
Merci à Dominique Handelsman pour l'idée de la dernière formule.

Si vous avez d'autres formules longues à proposer, n'hésitez pas et je les ajouterai au fichier.
Une petite recherche internet des meilleurs méga-formules serait d'ailleurs une bonne idée pour un prochain article !

A bientôt.

Gaetan Mourmant

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Une astuce très productive : ii pour =INDEX(ColRésultat;EQUIV(ValCherchée;ColRecherche;0))

Ca faisait un moment que je n'avais pas vu une astuce qui fasse gagner autant de temps, donc je suis ravi de la partager !
N'hésitez-pas à vous inscrire à la newsletter gratuite (plus bas) pour ne manquer aucune astuce sur Excel.

Vous connaissez déjà les nombreuses discussions qui existent sur le sujet du Recherchev vs. Index/match, sinon, je vous invite à revoir ces deux résumés de la situation :
http://www.xlerateur.com/divers/2016/12/31/index-equiv-vs-recherchev-5477/
http://www.xlerateur.com/divers/2017/01/02/6-bonnes-raisons-dabandonner-recherchev-meme-si-vous-vous-appelez-flash-5514/

Donc, un des arguments pour l'utilisation de Recherchev est que c'est plus rapide à taper. Voici donc une astuce qui va vous permettre de résoudre cet inconvénient.

Dans Fichier - Options - Vérification - Options de correction automatique - Correction automatique,
et vous ajoutez une nouvelle option :
ii pour
=INDEX(ColRésultat;EQUIV(ValCherchée;ColRecherche;0)
Ce qui donne :

ou encore pour un tableau à double entrée:
iii pour
=INDEX(TabRésultat;EQUIV(ValCherchée;ColRecherche;0);EQUIV(ValCherchée;LigRecherche;0))
Ce qui donne :

Voici l'endroit où il faut ajouter la correction automatique:

Il ne vous reste plus qu'à taper ii ou iii, puis espace, puis à double cliquer sur les arguments pour compléter la formule.

C'est tout !

Source : https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14049132-make-a-simple-safer-version-of-vlookup-and-index-m?page=4&per_page=20

A bientôt.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Créer un « Tout » dans une formule sommeprod

Suite à la question d'un client, voici comment créer une SOMMEPROD qui permettent de prendre en compte un critère "(Tout)", un peu à la manière d'un tableau croisé dynamique.

Pour cela, on va utiliser la formule SI et ensuite créer une série de {VRAI;VRAI;VRAI}, ce qui va donc nous donner ceci :

=SOMMEPROD((Tableau1[Montant])*(SI(D1="(Tout)";Tableau1[Ville];D1)=Tableau1[Ville]))

Donc, si D1 est égal à "Tout"; alors on tester [Ville]=[Ville] et donc : {VRAI;VRAI;VRAI;VRAI}

Voici un petit fichier qui illustre cette question.

A bientôt.
Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Chercher une référence circulaire

Bonjour à tous,

Voici comment traquer une référence circulaire ?

Une référence circulaire, c'est lorsque vous faites référence à la même cellule (directement ou indirectement), et de fait le calcul devient circulaire, donc insoluble d'une certaine manière.
Par exemple ici :

Tout d'abord, et c'est contre-intuitif, l'erreur ne se trouve pas forcément ici :

Mais vous aurez plus de chance de la trouver là :

Vous pouvez ensuite analyser les cellules trouvées pour comprendre le problème.

Parfois, si la situation est assez complexe, SUR UNE COPIE DE SAUVEGARDE, supprimer les formules des références circulaires jusqu'à ce que celle-ci devienne grisé. Ca vous permet de remonter à la source.

Espérant que cela vous aide !

A bientôt.

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !