ModComparaisonCubes.bas

Alt+F11 dans Excel, coller dans un module standard
Attribute VB_Name = "ModComparaisonCubes"
Option Explicit
'===============================================================================
' COMPARAISON DES CUBES PROD / RE7 - module standard unique
'-------------------------------------------------------------------------------
' Point d'entree  : LancerComparaisonCubes
' Utilitaire      : InitialiserClasseur (cree les feuilles Parametrage et
'                   Ecarts_connus si elles n'existent pas)
' Cible           : Excel Windows, VBA7 32/64 bits. Aucune reference a cocher :
'                   Scripting.Dictionary et Scripting.FileSystemObject sont
'                   utilises en liaison tardive.
' Principes       : lecture des sources en lecture seule, sans liens ni macros ;
'                   tout le traitement se fait en memoire sur des tableaux
'                   Variant 2D (.Value2) ; les classeurs produits ne contiennent
'                   que des valeurs.
'===============================================================================

'---- Noms de feuilles, fichiers et constantes metier --------------------------
Private Const FEUILLE_PARAM As String = "Parametrage"
Private Const FEUILLE_CONNUS As String = "Ecarts_connus"
Private Const FEUILLE_SUIVI As String = "Suivi"
Private Const DOSSIER_SORTIE As String = "Comparatif"
Private Const NOM_SUIVI As String = "Suivi des tests.xlsx"
Private Const NOM_SUIVI_PRECEDENT As String = "Suivi des tests - precedent.xlsx"
Private Const CLE_IM0 As String = "interestmargin|0m 0d"
Private Const CLE_AOP0 As String = "averageoutstandingprincipal|0m 0d"
' Mot de passe volontairement faux : un classeur protege leve une erreur
' au lieu d'afficher une invite bloquante ; il est ignore sinon.
Private Const MDP_FACTICE As String = "~aucun-mot-de-passe~"
Private Const FMT_XLSX As Long = 51          ' xlOpenXMLWorkbook
Private Const SECU_MACROS_OFF As Long = 3    ' msoAutomationSecurityForceDisable
Private Const MAX_LISTE As Long = 20         ' elements max listes dans un diagnostic

'---- Statuts globaux -----------------------------------------------------------
Private Const ST_CONFORME As String = "Conforme"
Private Const ST_OK As String = "OK"
Private Const ST_ECART0 As String = "Ecart 0m 0d"
Private Const ST_ECART As String = "Ecart"
Private Const ST_STRUCT As String = "Structure"
Private Const ST_MANQUANT As String = "Fichier manquant"
Private Const ST_ERREUR As String = "Erreur"
Private Const ST_VIDE As String = "Vide"
Private Const ST_NONTESTE As String = "Non teste"

'---- Etats d'un Cube lu --------------------------------------------------------
Private Const CUBE_OK As Long = 0
Private Const CUBE_VIDE As Long = 1
Private Const CUBE_STRUCT As Long = 2

'---- Types de valeurs ----------------------------------------------------------
Private Const TV_VIDE As Long = 0
Private Const TV_NOMBRE As Long = 1
Private Const TV_TEXTE As Long = 2
Private Const TV_ERREUR As Long = 3
Private Const TV_AUTRE As Long = 4

'---- Colonnes du Suivi : indice k => colonne Excel k + 1 (k = 1 => B) ----------
Private Const K_ID As Long = 1          ' B
Private Const K_DOSSIER As Long = 2     ' C
Private Const K_SOUSDOS As Long = 3     ' D
Private Const K_FICHIER As Long = 4     ' E
Private Const K_ATEST As Long = 5       ' F
Private Const K_PRIO As Long = 6        ' G
Private Const K_P_STATUT As Long = 7    ' H
Private Const K_P_CUBE As Long = 8      ' I
Private Const K_R_STATUT As Long = 11   ' L
Private Const K_R_CUBE As Long = 12     ' M
Private Const K_STATUT As Long = 15     ' P
Private Const K_IM0 As Long = 16        ' Q
Private Const K_AOP0 As Long = 17       ' R
Private Const K_NBECART As Long = 18    ' S
Private Const K_MAXABS As Long = 19     ' T
Private Const K_MAXREL As Long = 20     ' U
Private Const K_SOUSTOL As Long = 21    ' V
Private Const K_MONTANTS As Long = 22   ' W
Private Const K_COMMENT As Long = 29    ' AD
Private Const K_DATE As Long = 30       ' AE
Private Const K_CHEMIN As Long = 31     ' AF
Private Const K_EXT As Long = 32        ' AG
Private Const K_NDEP As Long = 33       ' AH
Private Const K_NMISS As Long = 34      ' AI
Private Const K_NNEW As Long = 35       ' AJ
Private Const K_NNUMP As Long = 36      ' AK
Private Const K_NNUMR As Long = 37      ' AL
Private Const K_NCOMP As Long = 38      ' AM
Private Const K_NMANQ As Long = 39      ' AN
Private Const K_DECAL As Long = 40      ' AO
Private Const K_NCONNUS As Long = 41    ' AP
Private Const K_REFS As Long = 42       ' AQ
Private Const K_DETAIL As Long = 43     ' AR
Private Const K_LIEN As Long = 44       ' AS
Private Const NB_COLS_SUIVI As Long = 44
Private Const LIGNE_GROUPES As Long = 7
Private Const LIGNE_ENTETE As Long = 8

'---- Types ---------------------------------------------------------------------
Private Type tEtatAppli
    ScreenUpdating As Boolean
    EnableEvents As Boolean
    DisplayAlerts As Boolean
    Calculation As Long
    AskToUpdateLinks As Boolean
    AutomationSecurity As Long
    StatusBar As Variant
End Type

' Cube normalise : en-tetes reconstitues + lignes metier, sans fusion,
' sans sous-total ni pied. Colonnes : NDim descriptions puis NMes mesures.
Private Type tCube
    Etat As Long
    Msg As String
    Feuille As String
    NLev As Long            ' nombre de niveaux d'en-tete
    NDim As Long            ' colonnes descriptives
    NMes As Long            ' colonnes de mesures
    NRows As Long           ' lignes metier
    NNum As Long            ' valeurs numeriques dans les mesures
    Hdr As Variant          ' (1..NLev, 1..NDim+NMes)
    Data As Variant         ' (1..NRows, 1..NDim+NMes)
    MesKey() As String      ' cle normalisee "groupe|periode"
    MesLib() As String      ' libelle affiche "Groupe / periode"
    MesGrp() As String      ' premier niveau normalise
    MesPer() As String      ' dernier niveau normalise
    SrcRow() As Long        ' ligne d'origine dans la feuille source
    Pied As String          ' signature du pied (Currency, CFError, titres)
End Type

Private Type tCmp
    Fait As Boolean
    LignesOK As Boolean
    MapPR() As Long         ' mesure PROD -> mesure RE7 (0 = aucune)
    MapRP() As Long         ' mesure RE7 -> mesure PROD (0 = aucune)
    DupP() As Boolean
    DupR() As Boolean
    Deplace() As Boolean    ' mesure RE7 deplacee
    NewIdx() As Long        ' mesures RE7 absentes de PROD
    NNew As Long
    HasDiff As Boolean      ' au moins une difference numerique non nulle
    Check As Variant
    DiffV As Variant
    DiffR As Variant
    ParGroupe As Object     ' groupe de mesure -> nb d'ecarts hors tolerance
End Type

Private Type tRes
    Statut As String
    Commentaire As String
    StatutP As String
    StatutR As String
    CubeP As Variant
    CubeR As Variant
    NNumP As Variant
    NNumR As Variant
    FeuilleP As String
    FeuilleR As String
    MsgP As String
    MsgR As String
    ComparaisonFaite As Boolean
    ColonnesAnalysees As Boolean
    NComp As Long
    NExact As Long
    NTol As Long
    NOut As Long
    NManq As Long
    NNonComp As Long
    NOutIM0 As Long
    NOutAOP0 As Long
    HasIM0 As Boolean
    HasAOP0 As Boolean
    MaxAbs As Double
    HasAbs As Boolean
    MaxRel As Double
    HasRel As Boolean
    NDep As Long
    NMiss As Long
    NNew As Long
    NDup As Long
    ListeDep As String
    ListeMiss As String
    ListeNew As String
    ListeDup As String
    PremierDecalage As Long
    Structure As Boolean
    MsgStruct As String
    Detail As String
    NConnus As Long
    RefsConnus As String
    DetailConnus As String
    CheminComp As String
    DateCtl As Date
End Type

'---- Variables de module (parametres et inventaire) ---------------------------
Private mFso As Object
Private mTolAbs As Double
Private mTolRel As Double
Private mRacineP As String
Private mRacineR As String
Private mSortie As String
Private mCheminSuivi As String
Private mDossiersExclus As Object
Private mFichiersExclus As Object
Private mErreursInventaire As String
Private mPrefixeEtape As String
' Referentiel des ecarts connus (motifs Like en minuscules)
Private mNbConnus As Long
Private mCnGrp() As String
Private mCnPer() As String
Private mCnFic() As String
Private mCnRef() As String
Private mCnCom() As String
' Zones fusionnees de la feuille en cours d'analyse
Private mNbFus As Long
Private mFusR1() As Long
Private mFusC1() As Long
Private mFusR2() As Long
Private mFusC2() As Long
Private mFusVues As Object

'===============================================================================
' POINT D'ENTREE
'===============================================================================
Public Sub LancerComparaisonCubes()
    Dim etat As tEtatAppli, etatSauve As Boolean
    Dim msgFin As String, msgErr As String, styleMsg As VbMsgBoxStyle
    Dim dP As Object, dR As Object, dTous As Object, dPrec As Object, dBases As Object
    Dim cles() As String, nb As Long, i As Long, maxId As Long, nEcarts As Long
    Dim rel As String, prev As Variant, id As Variant, aTest As Variant, prio As Variant
    Dim sortie() As Variant, liens() As String, comptes(1 To 10) As Long
    Dim res As tRes, cle As Variant

    On Error GoTo Echec
    styleMsg = vbInformation
    SauverEtat etat: etatSauve = True
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    Application.AutomationSecurity = SECU_MACROS_OFF
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Inventaire..."

    Set mFso = CreateObject("Scripting.FileSystemObject")
    If Not LireParametres(msgErr) Then
        msgFin = "Parametrage invalide : " & msgErr: styleMsg = vbExclamation: GoTo Fin
    End If
    LireEcartsConnus

    ' Le suivi ne doit pas etre ouvert : il va etre regenere.
    If ClasseurOuvertParNom(NOM_SUIVI) Then
        msgFin = "Fermez le classeur """ & NOM_SUIVI & """ puis relancez.": styleMsg = vbExclamation: GoTo Fin
    End If
    ' Lecture du suivi precedent (Id, A_tester, Priorite). En cas d'echec on
    ' s'arrete pour ne jamais ecraser les saisies manuelles.
    If Not LireSuiviPrecedent(dPrec, maxId, msgErr) Then
        msgFin = msgErr & vbLf & "Le suivi existant n'a pas ete modifie.": styleMsg = vbExclamation: GoTo Fin
    End If

    ' Inventaire recursif des deux arborescences
    Set dP = Inventorier(mRacineP)
    Set dR = Inventorier(mRacineR)
    Set dTous = CreateObject("Scripting.Dictionary"): dTous.CompareMode = vbTextCompare
    For Each cle In dP.Keys
        If Not dTous.Exists(cle) Then dTous.Add cle, cle
    Next cle
    For Each cle In dR.Keys
        If Not dTous.Exists(cle) Then dTous.Add cle, cle
    Next cle
    ' Les lignes d'un suivi precedent dont le fichier a disparu sont conservees.
    For Each cle In dPrec.Keys
        If Not dTous.Exists(cle) Then dTous.Add cle, cle
    Next cle

    nb = dTous.Count
    If nb = 0 Then
        msgFin = "Aucun fichier Excel trouve dans les repertoires PROD et RE7." & mErreursInventaire
        styleMsg = vbExclamation: GoTo Fin
    End If
    cles = TrierCles(dTous)
    Set dBases = CompterBases(cles, nb)
    ReDim sortie(1 To nb, 1 To NB_COLS_SUIVI)
    ReDim liens(1 To nb)

    For i = 1 To nb
        rel = cles(i)
        id = Empty: aTest = Empty: prio = Empty
        If dPrec.Exists(rel) Then
            prev = dPrec(rel): id = prev(0): aTest = prev(1): prio = prev(2)
        End If
        If TypeValeur(id) <> TV_NOMBRE Then maxId = maxId + 1: id = maxId
        TraiterFichier i, nb, rel, dP.Exists(rel), dR.Exists(rel), _
            (UCase$(Trim$(ValeurTexte(aTest))) <> "N"), CheminComparatif(rel, dBases), res
        RemplirLigneSuivi sortie, i, rel, id, aTest, prio, res
        liens(i) = res.CheminComp
        CompterStatut comptes, res.Statut
        If res.Statut = ST_ECART Or res.Statut = ST_ECART0 Then nEcarts = nEcarts + 1
        If (i Mod 5) = 0 Then DoEvents
    Next i

    Application.StatusBar = "Mise a jour Suivi"
    EcrireSuivi sortie, nb, comptes, liens
    Application.StatusBar = "Termine - " & nb & " fichiers, " & nEcarts & " ecarts"

    msgFin = "Termine - " & nb & " fichiers, " & nEcarts & " en ecart." & vbLf & vbLf & _
        ST_CONFORME & " : " & comptes(2) & vbLf & ST_OK & " : " & comptes(3) & vbLf & _
        ST_ECART0 & " : " & comptes(4) & vbLf & ST_ECART & " : " & comptes(5) & vbLf & _
        ST_STRUCT & " : " & comptes(6) & vbLf & ST_VIDE & " : " & comptes(7) & vbLf & _
        ST_MANQUANT & " : " & comptes(8) & vbLf & ST_ERREUR & " : " & comptes(9) & vbLf & _
        ST_NONTESTE & " : " & comptes(10) & vbLf & vbLf & "Suivi : " & mCheminSuivi
    If Len(mErreursInventaire) > 0 Then
        msgFin = msgFin & vbLf & vbLf & "Dossiers non parcourus :" & mErreursInventaire
        styleMsg = vbExclamation
    End If

Fin:
    If etatSauve Then RestaurerEtat etat
    If Len(msgFin) > 0 Then MsgBox msgFin, styleMsg, "Comparaison des Cubes"
    Exit Sub
Echec:
    msgFin = "Traitement interrompu : " & Err.Description & " (erreur " & Err.Number & ")"
    styleMsg = vbCritical
    Resume Fin
End Sub

'===============================================================================
' UTILITAIRE : creation des feuilles de parametrage (n'ecrase rien)
'===============================================================================
Public Sub InitialiserClasseur()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(FEUILLE_PARAM)
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = FEUILLE_PARAM
        ws.Range("B1").Value2 = "Parametres de la comparaison des Cubes"
        ws.Range("B1").Font.Bold = True
        ws.Range("B3:B6").Value2 = Application.Transpose(Array("Repertoire de Prod", "Repertoire de Re7", _
            "Tolerance absolue", "Tolerance relative"))
        ws.Range("C5").Value2 = 0
        ws.Range("C6").Value2 = 0
        ws.Range("C6").NumberFormat = "0.00%"
        ws.Range("B3:B6").Font.Bold = True
        ws.Columns("B").ColumnWidth = 24
        ws.Columns("C").ColumnWidth = 90
        ws.Range("B8").Value2 = "Tolerance relative : saisir un pourcentage (1 % = 0,01). Vide = 0."
    End If
    Set ws = Nothing
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(FEUILLE_CONNUS)
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = FEUILLE_CONNUS
        ws.Range("B2:F2").Value2 = Array("Groupe", "Periode", "Motif fichier", "Reference", "Commentaire")
        ws.Range("B2:F2").Font.Bold = True
        ws.Range("B2:F2").Interior.Color = RGB(217, 217, 217)
        ws.Range("B3:F3").Value2 = Array("AverageOutstandingPrincipal", "0m 0d", "", "'ARC-7272", _
            "Exemple a adapter : AvOP at 0d changes")
        ws.Range("B5").Value2 = "Groupe / Periode : libelles d'en-tete du Cube (casse ignoree, jokers * et ? admis, vide = tous)."
        ws.Range("B6").Value2 = "Motif fichier : partie du chemin relatif (ex. *Bilan Constant*), vide = tous les fichiers."
        ws.Range("B7").Value2 = "Une ligne sans Reference est ignoree. Le statut du fichier n'est pas modifie : seul le rattachement est indique."
        ws.Columns("B:C").ColumnWidth = 30
        ws.Columns("D").ColumnWidth = 30
        ws.Columns("E").ColumnWidth = 18
        ws.Columns("F").ColumnWidth = 70
    End If
End Sub

'===============================================================================
' TRAITEMENT D'UNE PAIRE DE FICHIERS
'===============================================================================
Private Sub TraiterFichier(ByVal idx As Long, ByVal nb As Long, ByVal rel As String, _
        ByVal inP As Boolean, ByVal inR As Boolean, ByVal aTester As Boolean, _
        ByVal cheminComp As String, ByRef res As tRes)
    Dim vide As tRes, cP As tCube, cR As tCube, cmp As tCmp
    Dim okP As Boolean, okR As Boolean, msgP As String, msgR As String

    On Error GoTo Echec
    res = vide
    res.DateCtl = Now
    mPrefixeEtape = "Fichier " & idx & "/" & nb & " - "
    res.StatutP = IIf(inP, "Disponible", "Absent")
    res.StatutR = IIf(inR, "Disponible", "Absent")

    If Not inP And Not inR Then
        res.Statut = ST_MANQUANT
        res.Commentaire = "Absent en PROD et en RE7 (ligne conservee du suivi precedent)"
        Exit Sub
    End If
    If Not inP Or Not inR Then
        res.Statut = ST_MANQUANT
        res.Commentaire = IIf(inP, "Fichier RE7 absent", "Fichier PROD absent")
        Exit Sub
    End If
    If Not aTester Then
        res.Statut = ST_NONTESTE
        res.Commentaire = "Non teste (A_tester = N)"
        Exit Sub
    End If

    Etape "Lecture PROD"
    okP = LireCube(mRacineP & "\" & rel, cP, msgP)
    Etape "Lecture RE7"
    okR = LireCube(mRacineR & "\" & rel, cR, msgR)
    Etape "Normalisation"
    DecrireCube okP, cP, msgP, res.StatutP, res.CubeP, res.NNumP, res.FeuilleP, res.MsgP
    DecrireCube okR, cR, msgR, res.StatutR, res.CubeR, res.NNumR, res.FeuilleR, res.MsgR

    If Not okP Or Not okR Then
        res.Statut = ST_ERREUR
        If Not okP Then res.Commentaire = Ajout(res.Commentaire, "Erreur PROD : " & msgP)
        If Not okR Then res.Commentaire = Ajout(res.Commentaire, "Erreur RE7 : " & msgR)
        res.Detail = res.Commentaire
        Exit Sub
    End If

    If cP.Etat = CUBE_VIDE And cR.Etat = CUBE_VIDE Then
        res.Statut = ST_VIDE
        res.Commentaire = "Cube vide des deux cotes"
    ElseIf cP.Etat = CUBE_VIDE Or cR.Etat = CUBE_VIDE Then
        res.Structure = True
        res.Statut = ST_STRUCT
        AjouterStruct res, IIf(cP.Etat = CUBE_VIDE, "Cube PROD vide, RE7 renseigne", "Cube RE7 vide, PROD renseigne")
        res.Commentaire = res.MsgStruct
    ElseIf cP.Etat = CUBE_STRUCT Or cR.Etat = CUBE_STRUCT Then
        res.Structure = True
        res.Statut = ST_STRUCT
        If cP.Etat = CUBE_STRUCT Then AjouterStruct res, "Structure PROD non reconnue : " & cP.Msg
        If cR.Etat = CUBE_STRUCT Then AjouterStruct res, "Structure RE7 non reconnue : " & cR.Msg
        res.Commentaire = res.MsgStruct
    Else
        Comparer cP, cR, rel, cmp, res
        DeterminerStatut res
    End If

    Etape "Creation comparatif"
    EcrireComparatif cheminComp, rel, cP, cR, cmp, res
    Exit Sub
Echec:
    res.Statut = ST_ERREUR
    res.Commentaire = "Erreur de traitement : " & Err.Description
    res.Detail = res.Commentaire
End Sub

' Statut, nombre de lignes et diagnostics d'un cote (PROD ou RE7).
Private Sub DecrireCube(ByVal ok As Boolean, ByRef c As tCube, ByVal msg As String, ByRef statut As String, _
        ByRef nLignes As Variant, ByRef nNum As Variant, ByRef feuille As String, ByRef diag As String)
    If Not ok Then
        statut = "Erreur": diag = msg
    ElseIf c.Etat = CUBE_VIDE Then
        statut = "Vide": nLignes = 0: nNum = 0: feuille = c.Feuille: diag = c.Msg
    Else
        statut = "Disponible": feuille = c.Feuille: diag = c.Msg
        If c.Etat = CUBE_OK Then nLignes = c.NRows: nNum = c.NNum
    End If
End Sub

Private Sub DeterminerStatut(ByRef res As tRes)
    If res.Structure Then
        res.Statut = ST_STRUCT
    ElseIf res.NOut + res.NManq + res.NNonComp > 0 Then
        If res.NManq + res.NNonComp = 0 And res.NOut = res.NOutIM0 + res.NOutAOP0 Then
            res.Statut = ST_ECART0
        Else
            res.Statut = ST_ECART
        End If
    ElseIf res.NTol > 0 Then
        res.Statut = ST_OK
    ElseIf res.NComp > 0 Then
        res.Statut = ST_CONFORME
    Else
        res.Statut = ST_VIDE
    End If
    res.Commentaire = Ajout(res.MsgStruct, CommentaireComparaison(res))
    If res.Statut = ST_VIDE And res.ComparaisonFaite Then
        res.Commentaire = Ajout(res.Commentaire, "aucune valeur numerique comparable")
    End If
End Sub

Private Function CommentaireComparaison(ByRef res As tRes) As String
    Dim t As String
    If res.NDep > 0 Then t = Ajout(t, res.NDep & " colonne(s) deplacee(s)")
    If Not res.ComparaisonFaite Then
        If res.ColonnesAnalysees Then t = Ajout(t, "comparaison numerique suspendue")
        CommentaireComparaison = t
        Exit Function
    End If
    If res.NOut > 0 Then
        If res.NOut = res.NOutIM0 + res.NOutAOP0 Then
            If res.NOutIM0 > 0 Then t = Ajout(t, res.NOutIM0 & " ecart(s) sur InterestMargin / 0m 0d")
            If res.NOutAOP0 > 0 Then t = Ajout(t, res.NOutAOP0 & " ecart(s) sur AverageOutstandingPrincipal / 0m 0d")
        Else
            t = Ajout(t, res.NOut & " ecart(s) hors tolerance ; max absolu " & Format$(res.MaxAbs, "#,##0.00"))
            If res.NOutIM0 + res.NOutAOP0 > 0 Then t = Ajout(t, "dont " & (res.NOutIM0 + res.NOutAOP0) & " sur 0m 0d")
        End If
    End If
    If res.NTol > 0 Then t = Ajout(t, res.NTol & " ecart(s) sous tolerance (arrondis presumes)")
    If res.NManq > 0 Then t = Ajout(t, res.NManq & " valeur(s) presente(s) d'un seul cote")
    If res.NNonComp > 0 Then t = Ajout(t, res.NNonComp & " valeur(s) non comparable(s)")
    If res.NConnus > 0 Then t = Ajout(t, res.NConnus & " ecart(s) rattache(s) a des cas connus : " & res.RefsConnus)
    If res.NOut + res.NTol + res.NManq + res.NNonComp = 0 And res.NComp > 0 Then t = Ajout(t, "aucune difference")
    CommentaireComparaison = t
End Function

'===============================================================================
' LECTURE ET NORMALISATION D'UN CUBE
'===============================================================================
Private Function LireCube(ByVal chemin As String, ByRef cube As tCube, ByRef msg As String) As Boolean
    Dim wb As Workbook, ouvert As Boolean, nomFic As String
    On Error GoTo Echec
    nomFic = mFso.GetFileName(chemin)
    ' Excel n'ouvre pas deux classeurs de meme nom ; surtout ne pas recuperer
    ' (puis fermer) un classeur ouvert par l'utilisateur.
    If ClasseurOuvertParNom(nomFic) Then
        msg = "un classeur nomme """ & nomFic & """ est deja ouvert dans Excel"
        Exit Function
    End If
    Set wb = Workbooks.Open(Filename:=chemin, UpdateLinks:=0, ReadOnly:=True, _
        IgnoreReadOnlyRecommended:=True, Notify:=False, AddToMru:=False, Password:=MDP_FACTICE)
    ouvert = True
    AnalyserClasseur wb, cube
    LireCube = True
Nettoyage:
    If ouvert Then
        On Error Resume Next
        wb.Close SaveChanges:=False
    End If
    Exit Function
Echec:
    msg = "ouverture ou lecture impossible : " & Err.Description
    LireCube = False
    Resume Nettoyage
End Function

' Une seule feuille non vide attendue. Plusieurs feuilles non vides => ambigu.
Private Sub AnalyserClasseur(ByVal wb As Workbook, ByRef cube As tCube)
    Dim ws As Worksheet, cible As Worksheet, nb As Long, lr As Long, lc As Long, noms As String
    For Each ws In wb.Worksheets
        If DerniereCellule(ws, lr, lc) Then
            nb = nb + 1
            If cible Is Nothing Then Set cible = ws
            noms = Ajout(noms, ws.Name)
        End If
    Next ws
    If nb = 0 Then
        cube.Etat = CUBE_VIDE: cube.Msg = "classeur sans contenu"
        If wb.Worksheets.Count > 0 Then cube.Feuille = wb.Worksheets(1).Name
    ElseIf nb > 1 Then
        cube.Etat = CUBE_STRUCT: cube.Msg = nb & " feuilles non vides (" & noms & ") : feuille du Cube ambigue"
    Else
        cube.Feuille = cible.Name
        AnalyserFeuille cible, cube
    End If
End Sub

' Regles deterministes de reconnaissance :
'  1. premiere ligne de donnees = premiere ligne contenant un nombre ;
'  2. pied = premiere ligne debutant par Currency/CFError ; aucune valeur
'     numerique n'est admise dessous ; les lignes sans nombre situees apres la
'     derniere ligne chiffree (titres de dimensions) font partie du pied ;
'  3. sous-totaux = lignes contenant le texte "Somme"/"Sommes" ;
'  4. premiere colonne de mesure = colonne suivant le dernier texte des lignes
'     metier ; une colonne descriptive purement numerique rend la limite ambigue ;
'  5. en-tetes = lignes contigues au-dessus des donnees ayant un libelle dans
'     les colonnes de mesures (fusions reconstituees) ;
'  6. cle de mesure = niveaux d'en-tete non vides joints par "|".
Private Sub AnalyserFeuille(ByVal ws As Worksheet, ByRef cube As tCube)
    Dim V As Variant, L As Variant, Dm As Variant, D As Variant, H As Variant, tmp As Variant
    Dim lr As Long, lc As Long, r As Long, c As Long, k As Long, f As Long, i As Long, j As Long
    Dim rr As Long, cc As Long, r0 As Long, rPied As Long, rFin As Long, hHaut As Long
    Dim derTexte As Long, pdc As Long, nDim As Long, nMes As Long, nLev As Long, nLig As Long
    Dim sousTotal() As Boolean, ligneVide() As Boolean, colMes() As Long, lignes() As Long
    Dim aTexte As Boolean, aNombre As Boolean, aDonnee As Boolean
    Dim cle As String, lib As String, t As String, grp As String, per As String

    If Not DerniereCellule(ws, lr, lc) Then
        cube.Etat = CUBE_VIDE: cube.Msg = "feuille vide": Exit Sub
    End If
    If lr = 1 And lc = 1 Then
        tmp = ws.Cells(1, 1).Value2
        ReDim V(1 To 1, 1 To 1)
        V(1, 1) = tmp
    Else
        V = ws.Range(ws.Cells(1, 1), ws.Cells(lr, lc)).Value2
    End If

    ' 1) Premiere ligne de donnees
    For r = 1 To lr
        For c = 1 To lc
            If TypeValeur(V(r, c)) = TV_NOMBRE Then r0 = r: Exit For
        Next c
        If r0 > 0 Then Exit For
    Next r
    If r0 = 0 Then
        cube.Etat = CUBE_VIDE: cube.Msg = "en-tetes ou libelles sans valeur numerique": Exit Sub
    End If

    ' 2) Pied
    rPied = lr + 1
    For r = r0 To lr
        t = PremierTexte(V, r, lc)
        If t = "currency" Or t = "cferror" Then rPied = r: Exit For
    Next r
    For r = rPied To lr
        If LigneANombre(V, r, lc) Then
            MarquerStructure cube, "valeurs numeriques sous le pied Currency/CFError (ligne " & r & ")": Exit Sub
        End If
    Next r
    For r = rPied - 1 To r0 Step -1
        If LigneANombre(V, r, lc) Then rFin = r: Exit For
    Next r
    If rFin = 0 Then
        MarquerStructure cube, "aucune ligne de donnees avant le pied": Exit Sub
    End If
    cube.Pied = SignatureLignes(V, rFin + 1, lr, lc)

    ' 3) Sous-totaux et lignes vides du corps
    ReDim sousTotal(r0 To rFin)
    ReDim ligneVide(r0 To rFin)
    For r = r0 To rFin
        ligneVide(r) = True
        For c = 1 To lc
            k = TypeValeur(V(r, c))
            If k <> TV_VIDE Then
                ligneVide(r) = False
                If k = TV_TEXTE Then
                    t = NormTexte(CStr(V(r, c)))
                    If t = "somme" Or t = "sommes" Then sousTotal(r) = True: Exit For
                End If
            End If
        Next c
    Next r

    ' 4) Limite descriptions / mesures
    For r = r0 To rFin
        If Not sousTotal(r) And Not ligneVide(r) Then
            For c = lc To derTexte + 1 Step -1
                If EstTexte(V(r, c)) Then derTexte = c: Exit For
            Next c
        End If
    Next r
    pdc = derTexte + 1
    If pdc > lc Then
        MarquerStructure cube, "aucune colonne de mesure a droite des descriptions": Exit Sub
    End If
    nDim = pdc - 1
    For c = 1 To nDim
        aTexte = False: aNombre = False
        For r = r0 To rFin
            If Not sousTotal(r) And Not ligneVide(r) Then
                k = TypeValeur(V(r, c))
                If k = TV_NOMBRE Then
                    aNombre = True
                ElseIf k = TV_TEXTE Or k = TV_AUTRE Then
                    aTexte = True
                End If
            End If
        Next r
        If aNombre And Not aTexte Then
            MarquerStructure cube, "colonne " & LettreCol(c) & " numerique dans la zone des descriptions : " & _
                "limite descriptions/mesures ambigue": Exit Sub
        End If
    Next c

    ' 5) Fusions reelles (corps et en-tetes)
    CollecterFusions ws, rFin, lc

    ' 6) En-tetes reconstitues
    If r0 = 1 Then
        MarquerStructure cube, "aucune ligne d'en-tete au-dessus des donnees": Exit Sub
    End If
    ReDim L(1 To r0 - 1, 1 To lc)
    For r = 1 To r0 - 1
        For c = 1 To lc
            L(r, c) = V(r, c)
        Next c
    Next r
    For f = 1 To mNbFus
        If mFusR1(f) < r0 Then
            tmp = V(mFusR1(f), mFusC1(f))
            For rr = mFusR1(f) To MinL(mFusR2(f), r0 - 1)
                For cc = mFusC1(f) To MinL(mFusC2(f), lc)
                    L(rr, cc) = tmp
                Next cc
            Next rr
        End If
    Next f
    For r = r0 - 1 To 1 Step -1
        aDonnee = False
        For c = pdc To lc
            If TypeValeur(L(r, c)) <> TV_VIDE Then aDonnee = True: Exit For
        Next c
        If Not aDonnee Then Exit For
        hHaut = r
    Next r
    If hHaut = 0 Then
        MarquerStructure cube, "en-tete des mesures introuvable au-dessus de la ligne " & r0: Exit Sub
    End If
    nLev = r0 - hHaut

    ' 7) Colonnes de mesures et cles metier
    ReDim colMes(1 To lc - nDim)
    ReDim cube.MesKey(1 To lc - nDim)
    ReDim cube.MesLib(1 To lc - nDim)
    ReDim cube.MesGrp(1 To lc - nDim)
    ReDim cube.MesPer(1 To lc - nDim)
    For c = pdc To lc
        cle = "": lib = "": grp = "": per = ""
        For r = hHaut To r0 - 1
            t = Trim$(ValeurTexte(L(r, c)))
            If Len(t) > 0 Then
                If Len(cle) > 0 Then cle = cle & "|": lib = lib & " / "
                cle = cle & NormTexte(t)
                lib = lib & t
                If Len(grp) = 0 Then grp = NormTexte(t)
                per = NormTexte(t)
            End If
        Next r
        aDonnee = False
        For r = r0 To rFin
            If Not sousTotal(r) And Not ligneVide(r) Then
                If TypeValeur(V(r, c)) <> TV_VIDE Then aDonnee = True: Exit For
            End If
        Next r
        If Len(cle) = 0 Then
            If aDonnee Then
                MarquerStructure cube, "colonne " & LettreCol(c) & " : valeurs sans en-tete de mesure": Exit Sub
            End If
        Else
            nMes = nMes + 1
            colMes(nMes) = c
            cube.MesKey(nMes) = cle
            cube.MesLib(nMes) = lib
            cube.MesGrp(nMes) = grp
            cube.MesPer(nMes) = per
        End If
    Next c
    If nMes = 0 Then
        MarquerStructure cube, "aucune mesure identifiable": Exit Sub
    End If

    ' 8) Lignes metier (ordre d'origine conserve)
    ReDim lignes(1 To rFin - r0 + 1)
    For r = r0 To rFin
        If Not sousTotal(r) And Not ligneVide(r) Then nLig = nLig + 1: lignes(nLig) = r
    Next r
    cube.NDim = nDim: cube.NMes = nMes: cube.NLev = nLev: cube.NRows = nLig
    If nLig = 0 Then
        cube.Etat = CUBE_VIDE: cube.Msg = "en-tetes sans ligne de donnee metier": Exit Sub
    End If

    ' 9) Descriptions : propagation de la valeur source dans sa zone fusionnee
    '    uniquement, jamais dans les mesures.
    If nDim > 0 Then
        ReDim Dm(r0 To rFin, 1 To nDim)
        For r = r0 To rFin
            For c = 1 To nDim
                Dm(r, c) = V(r, c)
            Next c
        Next r
        For f = 1 To mNbFus
            If mFusR1(f) >= r0 And mFusR1(f) <= rFin And mFusC1(f) <= nDim Then
                tmp = V(mFusR1(f), mFusC1(f))
                For rr = mFusR1(f) To MinL(mFusR2(f), rFin)
                    For cc = mFusC1(f) To MinL(mFusC2(f), nDim)
                        Dm(rr, cc) = tmp
                    Next cc
                Next rr
            End If
        Next f
    End If

    ' 10) Tableaux normalises
    ReDim D(1 To nLig, 1 To nDim + nMes)
    ReDim cube.SrcRow(1 To nLig)
    For i = 1 To nLig
        r = lignes(i)
        cube.SrcRow(i) = r
        For c = 1 To nDim
            D(i, c) = Dm(r, c)
        Next c
        For j = 1 To nMes
            D(i, nDim + j) = V(r, colMes(j))
            If TypeValeur(D(i, nDim + j)) = TV_NOMBRE Then cube.NNum = cube.NNum + 1
        Next j
    Next i
    ReDim H(1 To nLev, 1 To nDim + nMes)
    For k = 1 To nLev
        r = hHaut + k - 1
        For c = 1 To nDim
            H(k, c) = L(r, c)
        Next c
        For j = 1 To nMes
            H(k, nDim + j) = L(r, colMes(j))
        Next j
    Next k
    cube.Data = D
    cube.Hdr = H
    cube.Etat = CUBE_OK
End Sub

Private Sub MarquerStructure(ByRef cube As tCube, ByVal msg As String)
    cube.Etat = CUBE_STRUCT
    cube.Msg = msg
End Sub

' Bornes reelles : Find ignore les cellules seulement mises en forme.
Private Function DerniereCellule(ByVal ws As Worksheet, ByRef lr As Long, ByRef lc As Long) As Boolean
    Dim cel As Range
    Set cel = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If cel Is Nothing Then Exit Function
    lr = cel.Row
    Set cel = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    lc = cel.Column
    DerniereCellule = True
End Function

'---- Fusions : recherche par dichotomie colonne par colonne --------------------
' Range.MergeCells renvoie False (aucune fusion), True (toutes fusionnees) ou
' Null (mixte) : on ne descend que dans les portions mixtes.
Private Sub CollecterFusions(ByVal ws As Worksheet, ByVal lr As Long, ByVal lc As Long)
    Dim c As Long, m As Variant
    mNbFus = 0
    ReDim mFusR1(1 To 16): ReDim mFusC1(1 To 16): ReDim mFusR2(1 To 16): ReDim mFusC2(1 To 16)
    Set mFusVues = CreateObject("Scripting.Dictionary")
    m = ws.Range(ws.Cells(1, 1), ws.Cells(lr, lc)).MergeCells
    If Not IsNull(m) Then
        If Not m Then Exit Sub
    End If
    For c = 1 To lc
        ChercherFusions ws, c, 1, lr
    Next c
End Sub

Private Sub ChercherFusions(ByVal ws As Worksheet, ByVal c As Long, ByVal r1 As Long, ByVal r2 As Long)
    Dim m As Variant, r As Long, zone As Range, milieu As Long
    m = ws.Range(ws.Cells(r1, c), ws.Cells(r2, c)).MergeCells
    If IsNull(m) Then
        milieu = (r1 + r2) \ 2
        ChercherFusions ws, c, r1, milieu
        ChercherFusions ws, c, milieu + 1, r2
    ElseIf m Then
        r = r1
        Do While r <= r2
            Set zone = ws.Cells(r, c).MergeArea
            AjouterFusion zone
            r = zone.Row + zone.Rows.Count
        Loop
    End If
End Sub

Private Sub AjouterFusion(ByVal zone As Range)
    Dim cle As String
    cle = zone.Address
    If mFusVues.Exists(cle) Then Exit Sub
    mFusVues.Add cle, True
    mNbFus = mNbFus + 1
    If mNbFus > UBound(mFusR1) Then
        ReDim Preserve mFusR1(1 To 2 * mNbFus)
        ReDim Preserve mFusC1(1 To 2 * mNbFus)
        ReDim Preserve mFusR2(1 To 2 * mNbFus)
        ReDim Preserve mFusC2(1 To 2 * mNbFus)
    End If
    mFusR1(mNbFus) = zone.Row
    mFusC1(mNbFus) = zone.Column
    mFusR2(mNbFus) = zone.Row + zone.Rows.Count - 1
    mFusC2(mNbFus) = zone.Column + zone.Columns.Count - 1
End Sub

'===============================================================================
' COMPARAISON
'===============================================================================
Private Sub Comparer(ByRef cP As tCube, ByRef cR As tCube, ByVal rel As String, ByRef cmp As tCmp, ByRef res As tRes)
    Dim dP As Object, dR As Object
    Dim i As Long, j As Long, k As Long, r As Long, col As Long, colR As Long
    Dim nP As Long, nR As Long, nL As Long, nCols As Long, nMin As Long, premier As Long
    Dim tChk() As Variant, tDv() As Variant, tDr() As Variant
    Dim a As Variant, b As Variant, kA As Long, kB As Long
    Dim d As Double, ad As Double, rl As Double, relOK As Boolean
    Dim regle As Long, nbRegle() As Long, estIM0 As Boolean, estAOP0 As Boolean
    Dim relMin As String, marque As String, grpLib As String, t As String

    cmp.Fait = True
    nP = cP.NMes: nR = cR.NMes
    ReDim cmp.MapPR(1 To nP): ReDim cmp.DupP(1 To nP)
    ReDim cmp.MapRP(1 To nR): ReDim cmp.DupR(1 To nR)
    ReDim cmp.Deplace(1 To nR): ReDim cmp.NewIdx(1 To nR)
    Set cmp.ParGroupe = CreateObject("Scripting.Dictionary")

    ' --- Alignement des colonnes par cle metier ---
    Etape "Alignement colonnes"
    Set dP = IndexerCles(cP.MesKey, nP, cmp.DupP)
    Set dR = IndexerCles(cR.MesKey, nR, cmp.DupR)
    For j = 1 To nR
        If dP.Exists(cR.MesKey(j)) Then
            i = dP(cR.MesKey(j))
            If Not cmp.DupR(j) And Not cmp.DupP(i) Then cmp.MapRP(j) = i: cmp.MapPR(i) = j
        Else
            cmp.NNew = cmp.NNew + 1
            cmp.NewIdx(cmp.NNew) = j
            AjouterListe res.ListeNew, cR.MesLib(j), cmp.NNew
        End If
        If cmp.DupR(j) Then res.NDup = res.NDup + 1: AjouterListe res.ListeDup, "RE7 " & cR.MesLib(j), res.NDup
    Next j
    For i = 1 To nP
        If cmp.DupP(i) Then res.NDup = res.NDup + 1: AjouterListe res.ListeDup, "PROD " & cP.MesLib(i), res.NDup
        If Not dR.Exists(cP.MesKey(i)) Then res.NMiss = res.NMiss + 1: AjouterListe res.ListeMiss, cP.MesLib(i), res.NMiss
    Next i
    res.NNew = cmp.NNew
    If dP.Exists(CLE_IM0) Then res.HasIM0 = (cmp.MapPR(dP(CLE_IM0)) > 0)
    If dP.Exists(CLE_AOP0) Then res.HasAOP0 = (cmp.MapPR(dP(CLE_AOP0)) > 0)
    MarquerDeplacements cmp, cR, res
    res.ColonnesAnalysees = True
    If res.NMiss > 0 Then AjouterStruct res, res.NMiss & " colonne(s) absente(s) en RE7 (" & res.ListeMiss & ")"
    If res.NNew > 0 Then AjouterStruct res, res.NNew & " colonne(s) nouvelle(s) en RE7 (" & res.ListeNew & ")"
    If res.NDup > 0 Then AjouterStruct res, "cle(s) de mesure en doublon, non comparees (" & res.ListeDup & ")"

    ' --- Verification positionnelle des lignes ---
    Etape "Verification lignes"
    cmp.LignesOK = True
    If cP.NDim <> cR.NDim Then
        cmp.LignesOK = False
        AjouterStruct res, "nombre de colonnes descriptives different (PROD " & cP.NDim & " / RE7 " & cR.NDim & ")"
    Else
        nMin = MinL(cP.NRows, cR.NRows)
        For r = 1 To nMin
            For k = 1 To cP.NDim
                If Not ValeursIdentiques(cP.Data(r, k), cR.Data(r, k)) Then premier = r: Exit For
            Next k
            If premier > 0 Then Exit For
        Next r
        If premier = 0 And cP.NRows <> cR.NRows Then premier = nMin + 1
        If premier > 0 Then
            cmp.LignesOK = False
            res.PremierDecalage = premier
            t = "Ecart de structure a la ligne logique " & premier & " (lignes metier PROD " & cP.NRows & _
                " / RE7 " & cR.NRows
            If premier <= cP.NRows Then t = t & " ; ligne source PROD " & cP.SrcRow(premier)
            If premier <= cR.NRows Then t = t & " ; ligne source RE7 " & cR.SrcRow(premier)
            AjouterStruct res, t & ")"
        End If
    End If
    If StrComp(cP.Pied, cR.Pied, vbBinaryCompare) <> 0 Then AjouterStruct res, "pied du Cube different"
    If Not cmp.LignesOK Then Exit Sub

    ' --- Comparaison numerique en memoire ---
    Etape "Comparaison"
    res.ComparaisonFaite = True
    nL = cP.NLev
    nCols = cP.NDim + nP + cmp.NNew
    ReDim tChk(1 To nL + cP.NRows, 1 To nCols)
    ReDim tDv(1 To nL + cP.NRows, 1 To nCols)
    ReDim tDr(1 To nL + cP.NRows, 1 To nCols)
    For r = 1 To nL
        For col = 1 To cP.NDim + nP
            tChk(r, col) = cP.Hdr(r, col): tDv(r, col) = tChk(r, col): tDr(r, col) = tChk(r, col)
        Next col
    Next r
    For k = 1 To cmp.NNew
        col = cP.NDim + nP + k
        tChk(nL, col) = cR.MesLib(cmp.NewIdx(k)) & " [RE7 seul]": tDv(nL, col) = tChk(nL, col): tDr(nL, col) = tChk(nL, col)
    Next k
    For r = 1 To cP.NRows
        For col = 1 To cP.NDim
            tChk(nL + r, col) = cP.Data(r, col): tDv(nL + r, col) = tChk(nL + r, col): tDr(nL + r, col) = tChk(nL + r, col)
        Next col
    Next r

    ReDim nbRegle(0 To mNbConnus)
    relMin = LCase$(Replace(rel, "/", "\"))
    For i = 1 To nP
        col = cP.NDim + i
        j = cmp.MapPR(i)
        If j > 0 Then
            colR = cR.NDim + j
            regle = RegleConnue(cP.MesGrp(i), cP.MesPer(i), relMin)
            estIM0 = (cP.MesKey(i) = CLE_IM0)
            estAOP0 = (cP.MesKey(i) = CLE_AOP0)
            grpLib = Split(cP.MesLib(i), " / ")(0)
            For r = 1 To cP.NRows
                a = cP.Data(r, col): b = cR.Data(r, colR)
                kA = TypeValeur(a): kB = TypeValeur(b)
                If kA = TV_NOMBRE And kB = TV_NOMBRE Then
                    res.NComp = res.NComp + 1
                    d = CDbl(b) - CDbl(a)
                    ad = Abs(d)
                    If Not res.HasAbs Or ad > res.MaxAbs Then res.MaxAbs = ad: res.HasAbs = True
                    relOK = False: rl = 0
                    If CDbl(a) <> 0 Then
                        rl = ad / Abs(CDbl(a)): relOK = True
                    ElseIf d = 0 Then
                        rl = 0: relOK = True            ' PROD = RE7 = 0 => 0 %
                    End If
                    If relOK Then
                        If Not res.HasRel Or rl > res.MaxRel Then res.MaxRel = rl: res.HasRel = True
                    End If
                    If d = 0 Then
                        tChk(nL + r, col) = "OK"
                        res.NExact = res.NExact + 1
                    Else
                        cmp.HasDiff = True
                        tDv(nL + r, col) = d
                        If relOK Then tDr(nL + r, col) = rl Else tDr(nL + r, col) = "N/A"
                        If ad <= mTolAbs Or (relOK And rl <= mTolRel) Then
                            tChk(nL + r, col) = "Tolere"
                            res.NTol = res.NTol + 1
                        Else
                            tChk(nL + r, col) = "Ecart"
                            res.NOut = res.NOut + 1
                            If estIM0 Then res.NOutIM0 = res.NOutIM0 + 1
                            If estAOP0 Then res.NOutAOP0 = res.NOutAOP0 + 1
                            cmp.ParGroupe(grpLib) = cmp.ParGroupe(grpLib) + 1
                            nbRegle(regle) = nbRegle(regle) + 1
                        End If
                    End If
                ElseIf kA = TV_VIDE And kB = TV_VIDE Then
                    ' rien a comparer
                ElseIf kA = TV_VIDE Or kB = TV_VIDE Then
                    tChk(nL + r, col) = "Manquant"          ' vide <> 0
                    res.NManq = res.NManq + 1
                ElseIf kA = kB And kA <> TV_ERREUR And ValeursIdentiques(a, b) Then
                    ' meme texte des deux cotes : pas une valeur numerique, rien a signaler
                Else
                    tChk(nL + r, col) = "Non comparable"    ' erreur Excel ou types incoherents
                    res.NNonComp = res.NNonComp + 1
                End If
            Next r
        Else
            ' Colonne PROD sans correspondance (absente ou cle en doublon)
            If cmp.DupP(i) Or dR.Exists(cP.MesKey(i)) Then marque = "Non comparable" Else marque = "Manquant"
            For r = 1 To cP.NRows
                If TypeValeur(cP.Data(r, col)) <> TV_VIDE Then tChk(nL + r, col) = marque
            Next r
        End If
    Next i
    For k = 1 To cmp.NNew
        j = cmp.NewIdx(k)
        col = cP.NDim + nP + k
        If cmp.DupR(j) Then marque = "Non comparable" Else marque = "Manquant"
        For r = 1 To cR.NRows
            If TypeValeur(cR.Data(r, cR.NDim + j)) <> TV_VIDE Then tChk(nL + r, col) = marque
        Next r
    Next k

    For k = 1 To mNbConnus
        If nbRegle(k) > 0 Then
            res.NConnus = res.NConnus + nbRegle(k)
            res.RefsConnus = Ajout(res.RefsConnus, mCnRef(k) & " (" & nbRegle(k) & ")")
            res.DetailConnus = Ajout(res.DetailConnus, mCnRef(k) & " : " & mCnCom(k) & " (" & nbRegle(k) & ")")
        End If
    Next k
    cmp.Check = tChk
    If cmp.HasDiff Then
        cmp.DiffV = tDv
        cmp.DiffR = tDr
    End If
End Sub

' Dictionnaire cle -> premier indice ; marque toutes les occurrences des doublons.
Private Function IndexerCles(ByRef cles() As String, ByVal n As Long, ByRef dup() As Boolean) As Object
    Dim d As Object, i As Long
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbBinaryCompare
    For i = 1 To n
        If d.Exists(cles(i)) Then
            dup(i) = True
            dup(d(cles(i))) = True
        Else
            d.Add cles(i), i
        End If
    Next i
    Set IndexerCles = d
End Function

' Colonnes deplacees = colonnes RE7 hors de la plus longue sous-suite croissante
' des positions PROD (nombre minimal de deplacements expliquant l'ordre RE7).
Private Sub MarquerDeplacements(ByRef cmp As tCmp, ByRef cR As tCube, ByRef res As tRes)
    Dim seq() As Long, pos() As Long, fin() As Long, prec() As Long, dansLis() As Boolean
    Dim m As Long, j As Long, lg As Long, lo As Long, hi As Long, mi As Long, k As Long
    If cR.NMes = 0 Then Exit Sub
    ReDim seq(1 To cR.NMes): ReDim pos(1 To cR.NMes)
    For j = 1 To cR.NMes
        If cmp.MapRP(j) > 0 Then m = m + 1: seq(m) = cmp.MapRP(j): pos(m) = j
    Next j
    If m = 0 Then Exit Sub
    ReDim fin(1 To m): ReDim prec(1 To m): ReDim dansLis(1 To m)
    For j = 1 To m
        lo = 1: hi = lg
        Do While lo <= hi
            mi = (lo + hi) \ 2
            If seq(fin(mi)) < seq(j) Then lo = mi + 1 Else hi = mi - 1
        Loop
        If lo > 1 Then prec(j) = fin(lo - 1)
        fin(lo) = j
        If lo > lg Then lg = lo
    Next j
    k = fin(lg)
    Do While k > 0
        dansLis(k) = True
        k = prec(k)
    Loop
    For j = 1 To m
        If Not dansLis(j) Then
            cmp.Deplace(pos(j)) = True
            res.NDep = res.NDep + 1
            AjouterListe res.ListeDep, cR.MesLib(pos(j)), res.NDep
        End If
    Next j
End Sub

Private Function RegleConnue(ByVal grp As String, ByVal per As String, ByVal relMin As String) As Long
    Dim k As Long
    For k = 1 To mNbConnus
        If grp Like mCnGrp(k) Then
            If per Like mCnPer(k) Then
                If relMin Like mCnFic(k) Then RegleConnue = k: Exit Function
            End If
        End If
    Next k
End Function

Private Sub AjouterStruct(ByRef res As tRes, ByVal msg As String)
    res.Structure = True
    res.MsgStruct = Ajout(res.MsgStruct, msg)
End Sub

'===============================================================================
' CLASSEUR COMPARATIF
'===============================================================================
Private Sub EcrireComparatif(ByVal chemin As String, ByVal rel As String, ByRef cP As tCube, ByRef cR As tCube, _
        ByRef cmp As tCmp, ByRef res As tRes)
    Dim wb As Workbook, ws As Worksheet, ouvert As Boolean, msgErr As String
    On Error GoTo Echec
    CreerDossiers mFso.GetParentFolderName(chemin)
    If ClasseurOuvertParNom(mFso.GetFileName(chemin)) Then
        Err.Raise vbObjectError + 600, , "un classeur de meme nom est ouvert dans Excel"
    End If
    Set wb = Workbooks.Add(xlWBATWorksheet)
    ouvert = True
    Set ws = wb.Worksheets(1)
    ws.Name = "Synthese"
    EcrireSynthese ws, rel, cP, cR, cmp, res
    EcrireCube AjouterFeuille(wb, "PROD"), cP
    EcrireCube AjouterFeuille(wb, "RE7"), cR
    If cmp.Fait And res.NDep > 0 Then EcrireRe7Aligne AjouterFeuille(wb, "RE7_aligne"), cP, cR, cmp
    Set ws = AjouterFeuille(wb, "CHECK")
    If res.ComparaisonFaite Then
        EcrireGrille ws, cmp.Check, cP.NLev, cP.NDim, "", True
        If cmp.HasDiff Then
            EcrireGrille AjouterFeuille(wb, "Ecart_Valeur"), cmp.DiffV, cP.NLev, cP.NDim, "General", False
            EcrireGrille AjouterFeuille(wb, "Ecart_Relatif"), cmp.DiffR, cP.NLev, cP.NDim, "0.00%", False
        End If
    Else
        ws.Range("A1").Value2 = "'Comparaison cellule a cellule non effectuee : " & _
            IIf(Len(res.Commentaire) > 0, res.Commentaire, res.Statut)
    End If
    wb.SaveAs Filename:=chemin, FileFormat:=FMT_XLSX
    res.CheminComp = chemin
Nettoyage:
    If ouvert Then
        On Error Resume Next
        wb.Close SaveChanges:=False
    End If
    Exit Sub
Echec:
    msgErr = Err.Description
    res.Detail = Ajout(res.Detail, "Comparatif non enregistre : " & msgErr)
    res.Commentaire = Ajout(res.Commentaire, "comparatif non enregistre")
    Resume Nettoyage
End Sub

Private Function AjouterFeuille(ByVal wb As Workbook, ByVal nom As String) As Worksheet
    Dim ws As Worksheet
    Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    ws.Name = nom
    Set AjouterFeuille = ws
End Function

Private Sub EcrireCube(ByVal ws As Worksheet, ByRef c As tCube)
    Dim arr() As Variant, r As Long, col As Long
    If c.Etat <> CUBE_OK Then
        ws.Range("A1").Value2 = "'" & IIf(c.Etat = CUBE_VIDE, "Cube vide : ", "Structure non reconnue : ") & c.Msg
        Exit Sub
    End If
    ReDim arr(1 To c.NLev + c.NRows, 1 To c.NDim + c.NMes)
    For r = 1 To c.NLev
        For col = 1 To c.NDim + c.NMes
            arr(r, col) = c.Hdr(r, col)
        Next col
    Next r
    For r = 1 To c.NRows
        For col = 1 To c.NDim + c.NMes
            arr(c.NLev + r, col) = c.Data(r, col)
        Next col
    Next r
    EcrireGrille ws, arr, c.NLev, c.NDim, "", False
End Sub

' RE7 reordonne selon PROD (colonnes absentes laissees vides), puis colonnes
' propres a RE7. L'onglet RE7 d'origine n'est pas modifie.
Private Sub EcrireRe7Aligne(ByVal ws As Worksheet, ByRef cP As tCube, ByRef cR As tCube, ByRef cmp As tCmp)
    Dim arr() As Variant, nL As Long, nCols As Long, r As Long, col As Long, i As Long, j As Long, k As Long
    nL = cR.NLev
    nCols = cR.NDim + cP.NMes + cmp.NNew
    ReDim arr(1 To nL + cR.NRows, 1 To nCols)
    For col = 1 To cR.NDim
        For r = 1 To nL
            arr(r, col) = cR.Hdr(r, col)
        Next r
        For r = 1 To cR.NRows
            arr(nL + r, col) = cR.Data(r, col)
        Next r
    Next col
    For i = 1 To cP.NMes
        col = cR.NDim + i
        j = cmp.MapPR(i)
        If j > 0 Then
            CopierColonne arr, col, cR, cR.NDim + j
        Else
            arr(nL, col) = cP.MesLib(i) & " [absente RE7]"
        End If
    Next i
    For k = 1 To cmp.NNew
        CopierColonne arr, cR.NDim + cP.NMes + k, cR, cR.NDim + cmp.NewIdx(k)
    Next k
    EcrireGrille ws, arr, nL, cR.NDim, "", False
End Sub

Private Sub CopierColonne(ByRef arr() As Variant, ByVal colDest As Long, ByRef c As tCube, ByVal colSrc As Long)
    Dim r As Long
    For r = 1 To c.NLev
        arr(r, colDest) = c.Hdr(r, colSrc)
    Next r
    For r = 1 To c.NRows
        arr(c.NLev + r, colDest) = c.Data(r, colSrc)
    Next r
End Sub

' Ecrit un tableau en un bloc, puis une mise en forme legere par plages.
Private Sub EcrireGrille(ByVal ws As Worksheet, ByVal arr As Variant, ByVal nLev As Long, ByVal nDim As Long, _
        ByVal fmtMesures As String, ByVal estCheck As Boolean)
    Dim nR As Long, nC As Long, zone As Range
    nR = UBound(arr, 1): nC = UBound(arr, 2)
    ProtegerTextes arr
    ws.Cells(1, 1).Resize(nR, nC).Value2 = arr
    If nLev > 0 Then
        With ws.Cells(1, 1).Resize(nLev, nC)
            .Font.Bold = True
            .Interior.Color = RGB(217, 225, 242)
        End With
    End If
    If nDim > 0 Then ws.Cells(1, 1).Resize(1, nDim).EntireColumn.ColumnWidth = 16
    If nC > nDim Then ws.Cells(1, nDim + 1).Resize(1, nC - nDim).EntireColumn.ColumnWidth = 13
    If nR > nLev And nC > nDim Then
        Set zone = ws.Cells(nLev + 1, nDim + 1).Resize(nR - nLev, nC - nDim)
        If Len(fmtMesures) > 0 Then zone.NumberFormat = fmtMesures
        If estCheck Then
            ' 4 regles au total (pas de mise en forme cellule par cellule)
            zone.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Ecart""").Interior.Color = RGB(244, 176, 132)
            zone.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Tolere""").Interior.Color = RGB(255, 242, 204)
            zone.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Manquant""").Interior.Color = RGB(255, 199, 206)
            zone.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Non comparable""").Interior.Color = RGB(255, 199, 206)
        End If
    End If
End Sub

Private Sub EcrireSynthese(ByVal ws As Worksheet, ByVal rel As String, ByRef cP As tCube, ByRef cR As tCube, _
        ByRef cmp As tCmp, ByRef res As tRes)
    Dim t() As Variant, n As Long, g As Variant, nG As Long
    If Not cmp.ParGroupe Is Nothing Then nG = cmp.ParGroupe.Count
    ReDim t(1 To 70 + nG, 1 To 2)
    Ligne2 t, n, "Fichier", rel
    Ligne2 t, n, "Source PROD", mRacineP & "\" & rel
    Ligne2 t, n, "Source RE7", mRacineR & "\" & rel
    Ligne2 t, n, "Date du controle", Format$(res.DateCtl, "dd/mm/yyyy hh:nn:ss")
    Ligne2 t, n, "Statut", res.Statut
    Ligne2 t, n, "Commentaire", res.Commentaire
    Ligne2 t, n, "Tolerance absolue", mTolAbs
    Ligne2 t, n, "Tolerance relative", Format$(mTolRel, "0.########%")
    Ligne2 t, n, "", ""
    Ligne2 t, n, "PROD - statut / feuille", res.StatutP & " / " & res.FeuilleP
    Ligne2 t, n, "PROD - diagnostic de lecture", res.MsgP
    Ligne2 t, n, "PROD - lignes metier", res.CubeP
    Ligne2 t, n, "PROD - colonnes descriptives / mesures / niveaux d'en-tete", cP.NDim & " / " & cP.NMes & " / " & cP.NLev
    Ligne2 t, n, "PROD - valeurs numeriques", res.NNumP
    Ligne2 t, n, "RE7 - statut / feuille", res.StatutR & " / " & res.FeuilleR
    Ligne2 t, n, "RE7 - diagnostic de lecture", res.MsgR
    Ligne2 t, n, "RE7 - lignes metier", res.CubeR
    Ligne2 t, n, "RE7 - colonnes descriptives / mesures / niveaux d'en-tete", cR.NDim & " / " & cR.NMes & " / " & cR.NLev
    Ligne2 t, n, "RE7 - valeurs numeriques", res.NNumR
    Ligne2 t, n, "", ""
    Ligne2 t, n, "Anomalies de structure", IIf(res.Structure, res.MsgStruct, "aucune")
    Ligne2 t, n, "Premiere ligne logique decalee", IIf(res.PremierDecalage > 0, res.PremierDecalage, "")
    Ligne2 t, n, "Colonnes deplacees", res.NDep
    Ligne2 t, n, "Colonnes deplacees (liste)", res.ListeDep
    Ligne2 t, n, "Colonnes absentes en RE7", res.NMiss
    Ligne2 t, n, "Colonnes absentes en RE7 (liste)", res.ListeMiss
    Ligne2 t, n, "Colonnes nouvelles en RE7", res.NNew
    Ligne2 t, n, "Colonnes nouvelles en RE7 (liste)", res.ListeNew
    Ligne2 t, n, "Cles de mesure en doublon", res.ListeDup
    Ligne2 t, n, "", ""
    If res.ComparaisonFaite Then
        Ligne2 t, n, "Paires numeriques comparees", res.NComp
        Ligne2 t, n, "Valeurs identiques", res.NExact
        Ligne2 t, n, "Differences sous tolerance (arrondis presumes, non prouves)", res.NTol
        Ligne2 t, n, "Differences hors tolerance", res.NOut
        Ligne2 t, n, "Valeurs presentes d'un seul cote (Manquant)", res.NManq
        Ligne2 t, n, "Valeurs non comparables (erreur Excel ou types differents)", res.NNonComp
        Ligne2 t, n, "Max ecart absolu", IIf(res.HasAbs, res.MaxAbs, "N/A")
        Ligne2 t, n, "Max ecart relatif", IIf(res.HasRel, Format$(res.MaxRel, "0.0000%"), "N/A")
        Ligne2 t, n, "Ecarts InterestMargin / 0m 0d", IIf(res.HasIM0, res.NOutIM0, "N/A (mesure non rapprochee)")
        Ligne2 t, n, "Ecarts AverageOutstandingPrincipal / 0m 0d", IIf(res.HasAOP0, res.NOutAOP0, "N/A (mesure non rapprochee)")
        Ligne2 t, n, "Ecarts rattaches a des cas connus", res.NConnus
        Ligne2 t, n, "Cas connus", res.DetailConnus
        If nG > 0 Then
            Ligne2 t, n, "", ""
            Ligne2 t, n, "Ecarts hors tolerance par groupe de mesure", ""
            For Each g In cmp.ParGroupe.Keys
                Ligne2 t, n, "   " & g, cmp.ParGroupe(g)
            Next g
        End If
    Else
        Ligne2 t, n, "Comparaison numerique", "non effectuee"
    End If
    If Len(res.Detail) > 0 Then Ligne2 t, n, "Detail technique", res.Detail
    ProtegerTextes t
    ws.Range("A1").Resize(n, 2).Value2 = t
    ws.Columns("A").ColumnWidth = 58
    ws.Columns("B").ColumnWidth = 110
    ws.Range("A1").Resize(n, 1).Font.Bold = True
    ws.Range("B1").Resize(n, 1).HorizontalAlignment = xlLeft
    ws.Range("B5").Interior.Color = CouleurStatut(res.Statut)
End Sub

Private Sub Ligne2(ByRef t() As Variant, ByRef n As Long, ByVal lib As String, ByVal valeur As Variant)
    n = n + 1
    t(n, 1) = lib
    t(n, 2) = valeur
End Sub

'===============================================================================
' SUIVI DES TESTS
'===============================================================================
Private Sub RemplirLigneSuivi(ByRef s() As Variant, ByVal i As Long, ByVal rel As String, ByVal id As Variant, _
        ByVal aTest As Variant, ByVal prio As Variant, ByRef res As tRes)
    Dim parts() As String, nomFic As String, p As Long, k As Long

    parts = Split(rel, "\")
    nomFic = parts(UBound(parts))
    s(i, K_ID) = id
    If UBound(parts) = 0 Then
        s(i, K_DOSSIER) = "(racine)"
    Else
        s(i, K_DOSSIER) = parts(0)
        For k = 1 To UBound(parts) - 1
            s(i, K_SOUSDOS) = s(i, K_SOUSDOS) & IIf(k > 1, "\", "") & parts(k)
        Next k
    End If
    p = InStrRev(nomFic, ".")
    If p > 0 Then
        s(i, K_FICHIER) = Left$(nomFic, p - 1)
        s(i, K_EXT) = Mid$(nomFic, p + 1)
    Else
        s(i, K_FICHIER) = nomFic
    End If
    s(i, K_ATEST) = aTest
    s(i, K_PRIO) = prio
    s(i, K_CHEMIN) = rel

    ' PROD / RE7 : DataSet et Erreur DataSet laisses vides (reserves V2)
    s(i, K_P_STATUT) = res.StatutP
    s(i, K_P_CUBE) = res.CubeP
    s(i, K_R_STATUT) = res.StatutR
    s(i, K_R_CUBE) = res.CubeR

    s(i, K_STATUT) = res.Statut
    If res.ComparaisonFaite Then
        s(i, K_IM0) = IIf(res.HasIM0, res.NOutIM0, "N/A")
        s(i, K_AOP0) = IIf(res.HasAOP0, res.NOutAOP0, "N/A")
        s(i, K_NBECART) = res.NOut + res.NManq + res.NNonComp
        s(i, K_MAXABS) = IIf(res.HasAbs, res.MaxAbs, "N/A")
        s(i, K_MAXREL) = IIf(res.HasRel, res.MaxRel, "N/A")
        s(i, K_SOUSTOL) = res.NTol
        s(i, K_MONTANTS) = res.NOut
        s(i, K_NCOMP) = res.NComp
        s(i, K_NMANQ) = res.NManq + res.NNonComp
        s(i, K_NCONNUS) = res.NConnus
        s(i, K_REFS) = res.RefsConnus
    ElseIf res.Statut = ST_STRUCT Then
        For k = K_IM0 To K_MONTANTS
            s(i, k) = "N/A"
        Next k
    End If
    s(i, K_COMMENT) = res.Commentaire
    If res.Statut <> ST_MANQUANT Or res.StatutP <> "Absent" Or res.StatutR <> "Absent" Then s(i, K_DATE) = CDbl(res.DateCtl)
    If res.ColonnesAnalysees Then
        s(i, K_NDEP) = res.NDep
        s(i, K_NMISS) = res.NMiss
        s(i, K_NNEW) = res.NNew
    End If
    s(i, K_NNUMP) = res.NNumP
    s(i, K_NNUMR) = res.NNumR
    If res.PremierDecalage > 0 Then s(i, K_DECAL) = res.PremierDecalage
    s(i, K_DETAIL) = res.Detail
End Sub

Private Sub EcrireSuivi(ByRef s() As Variant, ByVal nb As Long, ByRef comptes() As Long, ByRef liens() As String)
    Dim wb As Workbook, ws As Worksheet, ouvert As Boolean, msgErr As String
    Dim i As Long, k As Long, derL As Long, v As Variant, sv As Variant, entetes As Variant, libs As Variant, cel As Range

    On Error GoTo Echec
    Set wb = Workbooks.Add(xlWBATWorksheet)
    ouvert = True
    Set ws = wb.Worksheets(1)
    ws.Name = FEUILLE_SUIVI
    ws.Cells.Font.Size = 10
    derL = LIGNE_ENTETE + nb

    ' --- Titre et statistiques generales ---
    ws.Range("B1").Value2 = "Suivi des tests - Comparaison des Cubes PROD / RE7"
    ws.Range("B1").Font.Bold = True
    ws.Range("B1").Font.Size = 14
    ws.Range("B2").Value2 = "'Dernier lancement : " & Format$(Now, "dd/mm/yyyy hh:nn") & "   |   PROD : " & mRacineP & _
        "   |   RE7 : " & mRacineR & "   |   Tolerance absolue : " & mTolAbs & "   |   Tolerance relative : " & _
        Format$(mTolRel, "0.########%")
    ws.Range("B2").Font.Color = RGB(89, 89, 89)
    libs = Array("Fichiers", ST_CONFORME, ST_OK, ST_ECART0, ST_ECART, ST_STRUCT, ST_VIDE, ST_MANQUANT, ST_ERREUR, ST_NONTESTE)
    comptes(1) = nb
    For k = 0 To 9
        Set cel = ws.Cells(3, 2 + k)
        cel.Value2 = libs(k)
        cel.Font.Bold = True
        cel.HorizontalAlignment = xlCenter
        If k = 0 Then cel.Interior.Color = RGB(217, 217, 217) Else cel.Interior.Color = CouleurStatut(CStr(libs(k)))
        If libs(k) = ST_ERREUR Then cel.Font.Color = vbWhite
        ws.Cells(4, 2 + k).Value2 = comptes(k + 1)
        ws.Cells(4, 2 + k).HorizontalAlignment = xlCenter
    Next k
    ws.Range("B3:K4").Borders.LineStyle = xlContinuous
    ws.Range("B3:K4").Borders.Color = RGB(191, 191, 191)
    ws.Range("B5").Value2 = "Legende : DataSet non traite dans cette version (colonnes J, K, N, O et X a AC reservees V2, laissees vides). " & _
        "S = cellules en ecart (hors tolerance + presentes d'un seul cote + non comparables). W = ecarts de montants hors tolerance, " & _
        "inclut Q et R : ne pas additionner. V = differences non nulles sous tolerance (arrondis presumes, non prouves). " & _
        "N/A = non calculable ; cellule vide = non applicable."
    ws.Range("B5").Font.Italic = True
    ws.Range("B5").Font.Size = 9
    ws.Range("B5").Font.Color = RGB(118, 118, 118)

    ' --- En-tetes groupes ---
    BlocEntete ws, 1, 6, "IDENTIFICATION / PILOTAGE", RGB(248, 203, 173), RGB(0, 0, 0)
    BlocEntete ws, 7, 10, "PROD", RGB(31, 78, 121), RGB(255, 255, 255)
    BlocEntete ws, 11, 14, "RE7", RGB(112, 48, 160), RGB(255, 255, 255)
    BlocEntete ws, 15, 30, "TYPES D'ECARTS / CONTROLE", RGB(55, 86, 35), RGB(255, 255, 255)
    BlocEntete ws, 31, NB_COLS_SUIVI, "DIAGNOSTICS TECHNIQUES", RGB(128, 128, 128), RGB(255, 255, 255)
    entetes = Array("Id", "Dossier", "Sous-dossier", "Fichier", "A_tester", "Priorite", _
        "Statut", "Cube", "DataSet", "Erreur DataSet", _
        "Statut", "Cube", "DataSet", "Erreur DataSet", _
        "Statut", "Cube - Ecarts InterestMargin / 0m 0d", "Cube - Ecarts AverageOutstandingPrincipal / 0m 0d", _
        "Cube - Nb de cellules en ecart", "Cube - Max ecart absolu", "Cube - Max ecart relatif", _
        "Cube - Ecarts sous tolerance / arrondis presumes", "Cube - Ecarts montants", _
        "DataSet - Ecarts AverageOutstandingPrincipal / 0m 0d", "DataSet - Ecarts d'arrondis", _
        "DataSet - Ecarts de maturite initiale / amortissement", "DataSet - Ecarts maturite initiale / amortissement (variante)", _
        "Erreur DataSet - Ecarts maturite initiale / amortissement", "Erreur DataSet - Ecarts ContractIndex", _
        "Commentaire", "Date controle", _
        "Chemin relatif", "Extension", "Nb colonnes deplacees", "Nb colonnes absentes RE7", "Nb colonnes nouvelles RE7", _
        "Nb valeurs num. PROD", "Nb valeurs num. RE7", "Nb paires comparees", "Nb manquants / non comparables", _
        "1re ligne logique decalee", "Nb ecarts cas connus", "Ref. cas connus", "Detail / erreur", "Comparatif")
    For k = 0 To NB_COLS_SUIVI - 1
        ws.Cells(LIGNE_ENTETE, 2 + k).Value2 = entetes(k)
    Next k
    With ws.Range(ws.Cells(LIGNE_ENTETE, 2), ws.Cells(LIGNE_ENTETE, 1 + NB_COLS_SUIVI))
        .Font.Bold = True
        .WrapText = True
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlCenter
        .RowHeight = 54
    End With
    ColorerEntete ws, 1, 6, RGB(252, 228, 214)
    ColorerEntete ws, 7, 10, RGB(221, 235, 247)
    ColorerEntete ws, 11, 14, RGB(228, 223, 236)
    ColorerEntete ws, 15, 30, RGB(226, 239, 218)
    ColorerEntete ws, 31, NB_COLS_SUIVI, RGB(237, 237, 237)

    ' --- Donnees (valeurs uniquement) ---
    sv = s
    ProtegerTextes sv
    ws.Range(ws.Cells(LIGNE_ENTETE + 1, 2), ws.Cells(derL, 1 + NB_COLS_SUIVI)).Value2 = sv

    ' Colonnes reservees V2 : fond gris clair, cellules vides
    For Each v In Array(9, 10, 13, 14, 23, 24, 25, 26, 27, 28)
        ws.Range(ws.Cells(LIGNE_ENTETE + 1, 1 + v), ws.Cells(derL, 1 + v)).Interior.Color = RGB(242, 242, 242)
        ws.Cells(LIGNE_ENTETE, 1 + v).Font.Italic = True
        ws.Cells(LIGNE_ENTETE, 1 + v).Font.Color = RGB(118, 118, 118)
    Next v
    ws.Range(ws.Cells(LIGNE_ENTETE + 1, 1 + K_MAXABS), ws.Cells(derL, 1 + K_MAXABS)).NumberFormat = "#,##0.00"
    ws.Range(ws.Cells(LIGNE_ENTETE + 1, 1 + K_MAXREL), ws.Cells(derL, 1 + K_MAXREL)).NumberFormat = "0.00%"
    ws.Range(ws.Cells(LIGNE_ENTETE + 1, 1 + K_DATE), ws.Cells(derL, 1 + K_DATE)).NumberFormat = "dd/mm/yyyy hh:mm"
    ws.Range(ws.Cells(LIGNE_ENTETE + 1, 1 + K_ID), ws.Cells(derL, 1 + K_ID)).HorizontalAlignment = xlCenter
    ws.Range(ws.Cells(LIGNE_ENTETE + 1, 1 + K_ATEST), ws.Cells(derL, 1 + K_PRIO)).HorizontalAlignment = xlCenter

    ' Statuts et compteurs d'ecarts (quelques centaines de cellules au plus)
    For i = 1 To nb
        Set cel = ws.Cells(LIGNE_ENTETE + i, 1 + K_STATUT)
        cel.Interior.Color = CouleurStatut(CStr(s(i, K_STATUT)))
        cel.Font.Bold = True
        If s(i, K_STATUT) = ST_ERREUR Then cel.Font.Color = vbWhite
        ColorerStatutCote ws.Cells(LIGNE_ENTETE + i, 1 + K_P_STATUT)
        ColorerStatutCote ws.Cells(LIGNE_ENTETE + i, 1 + K_R_STATUT)
        For k = K_IM0 To K_MONTANTS
            If k <> K_MAXABS And k <> K_MAXREL And k <> K_SOUSTOL Then
                If TypeValeur(s(i, k)) = TV_NOMBRE Then
                    If s(i, k) > 0 Then ws.Cells(LIGNE_ENTETE + i, 1 + k).Interior.Color = RGB(244, 176, 132)
                End If
            End If
        Next k
        If Len(liens(i)) > 0 Then
            ws.Hyperlinks.Add Anchor:=ws.Cells(LIGNE_ENTETE + i, 1 + K_LIEN), Address:=liens(i), TextToDisplay:="Ouvrir"
        End If
    Next i

    ' Bordures, largeurs, filtre, volets figes
    With ws.Range(ws.Cells(LIGNE_GROUPES, 2), ws.Cells(derL, 1 + NB_COLS_SUIVI)).Borders
        .LineStyle = xlContinuous
        .Color = RGB(191, 191, 191)
    End With
    LargeursSuivi ws
    ws.Range(ws.Cells(LIGNE_ENTETE, 2), ws.Cells(derL, 1 + NB_COLS_SUIVI)).AutoFilter
    With wb.Windows(1)
        .ScrollRow = 1
        .ScrollColumn = 1
        .SplitRow = LIGNE_ENTETE
        .SplitColumn = 5
        .FreezePanes = True
    End With

    If mFso.FileExists(mCheminSuivi) Then
        mFso.CopyFile mCheminSuivi, ThisWorkbook.Path & "\" & NOM_SUIVI_PRECEDENT, True
    End If
    wb.SaveAs Filename:=mCheminSuivi, FileFormat:=FMT_XLSX
Nettoyage:
    If ouvert Then
        On Error Resume Next
        wb.Close SaveChanges:=False
        On Error GoTo 0
    End If
    If Len(msgErr) > 0 Then Err.Raise vbObjectError + 700, , "Suivi non enregistre : " & msgErr
    Exit Sub
Echec:
    msgErr = Err.Description
    Resume Nettoyage
End Sub

Private Sub BlocEntete(ByVal ws As Worksheet, ByVal k1 As Long, ByVal k2 As Long, ByVal titre As String, _
        ByVal fond As Long, ByVal police As Long)
    With ws.Range(ws.Cells(LIGNE_GROUPES, 1 + k1), ws.Cells(LIGNE_GROUPES, 1 + k2))
        .Merge
        .Value2 = titre
        .Interior.Color = fond
        .Font.Color = police
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
End Sub

Private Sub ColorerEntete(ByVal ws As Worksheet, ByVal k1 As Long, ByVal k2 As Long, ByVal fond As Long)
    ws.Range(ws.Cells(LIGNE_ENTETE, 1 + k1), ws.Cells(LIGNE_ENTETE, 1 + k2)).Interior.Color = fond
End Sub

Private Sub ColorerStatutCote(ByVal cel As Range)
    Select Case CStr(cel.Value2)
        Case "Absent": cel.Interior.Color = RGB(255, 242, 204)
        Case "Erreur": cel.Interior.Color = RGB(255, 199, 206)
        Case "Vide": cel.Interior.Color = RGB(217, 217, 217)
    End Select
End Sub

Private Sub LargeursSuivi(ByVal ws As Worksheet)
    Dim largeurs As Variant, k As Long
    largeurs = Array(6, 24, 18, 34, 8, 8, _
        11, 8, 9, 9, 11, 8, 9, 9, _
        13, 13, 15, 12, 14, 11, 14, 12, 12, 11, 13, 13, 13, 13, 60, 15, _
        45, 8, 10, 10, 10, 10, 10, 10, 11, 10, 10, 22, 40, 11)
    ws.Columns("A").ColumnWidth = 2
    For k = 0 To NB_COLS_SUIVI - 1
        ws.Columns(2 + k).ColumnWidth = largeurs(k)
    Next k
End Sub

Private Function CouleurStatut(ByVal st As String) As Long
    Select Case st
        Case ST_CONFORME: CouleurStatut = RGB(198, 239, 206)
        Case ST_OK: CouleurStatut = RGB(226, 239, 218)
        Case ST_ECART0: CouleurStatut = RGB(255, 230, 153)
        Case ST_ECART: CouleurStatut = RGB(244, 176, 132)
        Case ST_STRUCT: CouleurStatut = RGB(255, 199, 206)
        Case ST_VIDE: CouleurStatut = RGB(217, 217, 217)
        Case ST_MANQUANT: CouleurStatut = RGB(255, 242, 204)
        Case ST_ERREUR: CouleurStatut = RGB(192, 0, 0)
        Case Else: CouleurStatut = RGB(242, 242, 242)
    End Select
End Function

Private Sub CompterStatut(ByRef comptes() As Long, ByVal st As String)
    Select Case st
        Case ST_CONFORME: comptes(2) = comptes(2) + 1
        Case ST_OK: comptes(3) = comptes(3) + 1
        Case ST_ECART0: comptes(4) = comptes(4) + 1
        Case ST_ECART: comptes(5) = comptes(5) + 1
        Case ST_STRUCT: comptes(6) = comptes(6) + 1
        Case ST_VIDE: comptes(7) = comptes(7) + 1
        Case ST_MANQUANT: comptes(8) = comptes(8) + 1
        Case ST_ERREUR: comptes(9) = comptes(9) + 1
        Case ST_NONTESTE: comptes(10) = comptes(10) + 1
    End Select
End Sub

' Relit Id / A_tester / Priorite du suivi existant, indexes par chemin relatif.
Private Function LireSuiviPrecedent(ByRef d As Object, ByRef maxId As Long, ByRef msg As String) As Boolean
    Dim wb As Workbook, ws As Worksheet, ouvert As Boolean, r As Long, hdr As Long, der As Long
    Dim arr As Variant, i As Long, chemin As String

    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare
    On Error GoTo Echec
    If Not mFso.FileExists(mCheminSuivi) Then LireSuiviPrecedent = True: Exit Function
    Set wb = Workbooks.Open(Filename:=mCheminSuivi, UpdateLinks:=0, ReadOnly:=True, _
        AddToMru:=False, Notify:=False, Password:=MDP_FACTICE)
    ouvert = True
    Set ws = wb.Worksheets(FEUILLE_SUIVI)
    For r = 1 To 50
        If NormTexte(ValeurTexte(ws.Cells(r, 1 + K_ID).Value2)) = "id" And _
           NormTexte(ValeurTexte(ws.Cells(r, 1 + K_CHEMIN).Value2)) = "chemin relatif" Then hdr = r: Exit For
    Next r
    If hdr = 0 Then Err.Raise vbObjectError + 701, , "en-tete du tableau introuvable (colonnes Id en B et Chemin relatif en AF)"
    der = ws.Cells(ws.Rows.Count, 1 + K_CHEMIN).End(xlUp).Row
    If der > hdr Then
        arr = ws.Range(ws.Cells(hdr + 1, 2), ws.Cells(der, 1 + NB_COLS_SUIVI)).Value2
        For i = 1 To UBound(arr, 1)
            chemin = Trim$(ValeurTexte(arr(i, K_CHEMIN)))
            If Len(chemin) > 0 Then
                If Not d.Exists(chemin) Then d.Add chemin, Array(arr(i, K_ID), arr(i, K_ATEST), arr(i, K_PRIO))
                If TypeValeur(arr(i, K_ID)) = TV_NOMBRE Then
                    If arr(i, K_ID) > maxId Then maxId = CLng(arr(i, K_ID))
                End If
            End If
        Next i
    End If
    LireSuiviPrecedent = True
Nettoyage:
    If ouvert Then
        On Error Resume Next
        wb.Close SaveChanges:=False
    End If
    Exit Function
Echec:
    msg = "Lecture du suivi existant """ & mCheminSuivi & """ impossible : " & Err.Description
    LireSuiviPrecedent = False
    Resume Nettoyage
End Function

'===============================================================================
' PARAMETRES ET REFERENTIEL DES ECARTS CONNUS
'===============================================================================
Private Function LireParametres(ByRef msg As String) As Boolean
    Dim ws As Worksheet, der As Long, r As Long
    Dim lP As Long, lR As Long, lTA As Long, lTR As Long

    If Len(ThisWorkbook.Path) = 0 Then
        msg = "enregistrez d'abord le classeur .xlsm (le dossier de sortie est cree a cote).": Exit Function
    End If
    If InStr(1, ThisWorkbook.Path, "://") > 0 Then
        msg = "le classeur doit etre enregistre dans un dossier local ou reseau (pas une adresse web OneDrive/SharePoint).": Exit Function
    End If
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(FEUILLE_PARAM)
    On Error GoTo 0
    If ws Is Nothing Then
        msg = "feuille """ & FEUILLE_PARAM & """ introuvable (macro InitialiserClasseur disponible).": Exit Function
    End If

    der = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    For r = 1 To der
        Select Case NormLibelle(ValeurTexte(ws.Cells(r, 2).Value2))
            Case "repertoire de prod": If lP > 0 Then msg = "libelle  Repertoire de Prod  en double.": Exit Function
                lP = r
            Case "repertoire de re7": If lR > 0 Then msg = "libelle  Repertoire de Re7  en double.": Exit Function
                lR = r
            Case "tolerance absolue": If lTA > 0 Then msg = "libelle  Tolerance absolue  en double.": Exit Function
                lTA = r
            Case "tolerance relative": If lTR > 0 Then msg = "libelle  Tolerance relative  en double.": Exit Function
                lTR = r
        End Select
    Next r
    If lP = 0 Then msg = "libelle  Repertoire de Prod  introuvable en colonne B.": Exit Function
    If lR = 0 Then msg = "libelle  Repertoire de Re7  introuvable en colonne B.": Exit Function
    If Not LireRepertoire(ws.Cells(lP, 3).Value2, "Prod", mRacineP, msg) Then Exit Function
    If Not LireRepertoire(ws.Cells(lR, 3).Value2, "Re7", mRacineR, msg) Then Exit Function
    If StrComp(mRacineP, mRacineR, vbTextCompare) = 0 Then
        msg = "les repertoires PROD et RE7 sont identiques.": Exit Function
    End If
    If Not LireTolerance(ws, lTA, "absolue", mTolAbs, msg) Then Exit Function
    If Not LireTolerance(ws, lTR, "relative", mTolRel, msg) Then Exit Function

    mSortie = ThisWorkbook.Path & "\" & DOSSIER_SORTIE
    mCheminSuivi = ThisWorkbook.Path & "\" & NOM_SUIVI
    ' Exclusions d'un parcours potentiellement imbrique
    Set mDossiersExclus = CreateObject("Scripting.Dictionary")
    mDossiersExclus.CompareMode = vbTextCompare
    mDossiersExclus(mSortie) = True
    mDossiersExclus(mRacineP) = True
    mDossiersExclus(mRacineR) = True
    Set mFichiersExclus = CreateObject("Scripting.Dictionary")
    mFichiersExclus.CompareMode = vbTextCompare
    mFichiersExclus(ThisWorkbook.FullName) = True
    mFichiersExclus(mCheminSuivi) = True
    mFichiersExclus(ThisWorkbook.Path & "\" & NOM_SUIVI_PRECEDENT) = True
    mErreursInventaire = ""
    LireParametres = True
End Function

Private Function LireRepertoire(ByVal v As Variant, ByVal nom As String, ByRef chemin As String, ByRef msg As String) As Boolean
    Dim p As String
    p = Trim$(ValeurTexte(v))
    Do While Len(p) > 3 And (Right$(p, 1) = "\" Or Right$(p, 1) = "/")
        p = Left$(p, Len(p) - 1)
    Loop
    If Len(p) = 0 Then msg = "repertoire de " & nom & " non renseigne (colonne C).": Exit Function
    If Not mFso.FolderExists(p) Then msg = "repertoire de " & nom & " introuvable : " & p: Exit Function
    chemin = mFso.GetFolder(p).Path
    If Right$(chemin, 1) = "\" Then chemin = Left$(chemin, Len(chemin) - 1)
    LireRepertoire = True
End Function

Private Function LireTolerance(ByVal ws As Worksheet, ByVal ligne As Long, ByVal nom As String, _
        ByRef valeur As Double, ByRef msg As String) As Boolean
    Dim v As Variant
    valeur = 0
    If ligne = 0 Then LireTolerance = True: Exit Function       ' libelle absent => 0
    v = ws.Cells(ligne, 3).Value2
    Select Case TypeValeur(v)
        Case TV_VIDE
            valeur = 0
        Case TV_NOMBRE
            If CDbl(v) < 0 Then msg = "tolerance " & nom & " negative.": Exit Function
            valeur = CDbl(v)
        Case Else
            msg = "tolerance " & nom & " non numerique (cellule C" & ligne & ").": Exit Function
    End Select
    LireTolerance = True
End Function

' Feuille facultative "Ecarts_connus" : en-tete "Groupe" en colonne B, puis
' Periode (C), Motif fichier (D), Reference (E), Commentaire (F).
Private Sub LireEcartsConnus()
    Dim ws As Worksheet, r As Long, hdr As Long, der As Long, ref As String, fic As String
    mNbConnus = 0
    ReDim mCnGrp(0 To 0): ReDim mCnPer(0 To 0): ReDim mCnFic(0 To 0): ReDim mCnRef(0 To 0): ReDim mCnCom(0 To 0)
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(FEUILLE_CONNUS)
    On Error GoTo 0
    If ws Is Nothing Then Exit Sub
    For r = 1 To 30
        If NormLibelle(ValeurTexte(ws.Cells(r, 2).Value2)) = "groupe" Then hdr = r: Exit For
    Next r
    If hdr = 0 Then Exit Sub
    der = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row
    For r = hdr + 1 To der
        ref = Trim$(ValeurTexte(ws.Cells(r, 5).Value2))
        If Len(ref) > 0 Then
            mNbConnus = mNbConnus + 1
            ReDim Preserve mCnGrp(0 To mNbConnus): ReDim Preserve mCnPer(0 To mNbConnus)
            ReDim Preserve mCnFic(0 To mNbConnus): ReDim Preserve mCnRef(0 To mNbConnus)
            ReDim Preserve mCnCom(0 To mNbConnus)
            mCnGrp(mNbConnus) = MotifOuTout(NormTexte(ValeurTexte(ws.Cells(r, 2).Value2)))
            mCnPer(mNbConnus) = MotifOuTout(NormTexte(ValeurTexte(ws.Cells(r, 3).Value2)))
            fic = LCase$(Trim$(Replace(ValeurTexte(ws.Cells(r, 4).Value2), "/", "\")))
            If Len(fic) = 0 Then
                fic = "*"
            ElseIf InStr(fic, "*") = 0 And InStr(fic, "?") = 0 Then
                fic = "*" & fic & "*"
            End If
            mCnFic(mNbConnus) = fic
            mCnRef(mNbConnus) = ref
            mCnCom(mNbConnus) = Trim$(ValeurTexte(ws.Cells(r, 6).Value2))
        End If
    Next r
End Sub

Private Function MotifOuTout(ByVal s As String) As String
    If Len(s) = 0 Then MotifOuTout = "*" Else MotifOuTout = s
End Function

'===============================================================================
' INVENTAIRE DES FICHIERS
'===============================================================================
Private Function Inventorier(ByVal racine As String) As Object
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare
    ParcourirDossier mFso.GetFolder(racine), racine & "\", d
    Set Inventorier = d
End Function

Private Sub ParcourirDossier(ByVal dossier As Object, ByVal base As String, ByVal d As Object)
    Dim f As Object, sd As Object, nom As String, ext As String, rel As String, chemin As String
    On Error GoTo Echec
    chemin = dossier.Path
    For Each f In dossier.Files
        nom = f.Name
        If Left$(nom, 2) <> "~$" Then
            ext = LCase$(mFso.GetExtensionName(nom))
            If ext = "xlsx" Or ext = "xlsm" Or ext = "xlsb" Or ext = "xls" Then
                If Not mFichiersExclus.Exists(f.Path) Then
                    rel = Mid$(f.Path, Len(base) + 1)
                    If Not d.Exists(rel) Then d.Add rel, rel
                End If
            End If
        End If
    Next f
    For Each sd In dossier.SubFolders
        If Not mDossiersExclus.Exists(sd.Path) Then ParcourirDossier sd, base, d
    Next sd
    Exit Sub
Echec:
    mErreursInventaire = mErreursInventaire & vbLf & chemin & " : " & Err.Description
End Sub

' Nombre de fichiers par (sous-dossier, nom sans extension) : sert a eviter
' les collisions de noms des comparatifs (a.xlsx et a.xlsm).
Private Function CompterBases(ByRef cles() As String, ByVal nb As Long) As Object
    Dim d As Object, i As Long, cle As String
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare
    For i = 1 To nb
        cle = CleBase(cles(i))
        d(cle) = d(cle) + 1
    Next i
    Set CompterBases = d
End Function

Private Function CleBase(ByVal rel As String) As String
    Dim p As Long
    p = InStrRev(rel, ".")
    If p > InStrRev(rel, "\") Then CleBase = Left$(rel, p - 1) Else CleBase = rel
End Function

Private Function CheminComparatif(ByVal rel As String, ByVal dBases As Object) As String
    Dim pS As Long, pP As Long, dossier As String, nomFic As String, base As String, ext As String
    pS = InStrRev(rel, "\")
    If pS > 0 Then dossier = Left$(rel, pS - 1)
    nomFic = Mid$(rel, pS + 1)
    pP = InStrRev(nomFic, ".")
    If pP > 0 Then
        base = Left$(nomFic, pP - 1): ext = Mid$(nomFic, pP + 1)
    Else
        base = nomFic
    End If
    If dBases(CleBase(rel)) > 1 Then base = base & " (" & ext & ")"
    CheminComparatif = mSortie & IIf(Len(dossier) > 0, "\" & dossier, "") & "\Comparatif - " & base & ".xlsx"
End Function

Private Sub CreerDossiers(ByVal chemin As String)
    If Len(chemin) = 0 Then Exit Sub
    If mFso.FolderExists(chemin) Then Exit Sub
    CreerDossiers mFso.GetParentFolderName(chemin)
    mFso.CreateFolder chemin
End Sub

Private Function TrierCles(ByVal d As Object) As String()
    Dim t() As String, k As Variant, i As Long
    ReDim t(1 To d.Count)
    For Each k In d.Keys
        i = i + 1
        t(i) = CStr(d(k))
    Next k
    If d.Count > 1 Then TriRapide t, 1, d.Count
    TrierCles = t
End Function

Private Sub TriRapide(ByRef t() As String, ByVal bas As Long, ByVal haut As Long)
    Dim i As Long, j As Long, pivot As String, tmp As String
    i = bas: j = haut
    pivot = t((bas + haut) \ 2)
    Do While i <= j
        Do While StrComp(t(i), pivot, vbTextCompare) < 0
            i = i + 1
        Loop
        Do While StrComp(t(j), pivot, vbTextCompare) > 0
            j = j - 1
        Loop
        If i <= j Then
            tmp = t(i): t(i) = t(j): t(j) = tmp
            i = i + 1: j = j - 1
        End If
    Loop
    If bas < j Then TriRapide t, bas, j
    If i < haut Then TriRapide t, i, haut
End Sub

'===============================================================================
' OUTILS GENERAUX
'===============================================================================
Private Function TypeValeur(ByVal v As Variant) As Long
    Select Case VarType(v)
        Case vbEmpty, vbNull
            TypeValeur = TV_VIDE
        Case vbString
            If Len(v) = 0 Then TypeValeur = TV_VIDE Else TypeValeur = TV_TEXTE
        Case vbDouble, vbSingle, vbCurrency, vbDecimal, vbLong, vbInteger, vbByte, vbDate
            TypeValeur = TV_NOMBRE
        Case vbError
            TypeValeur = TV_ERREUR
        Case Else
            TypeValeur = TV_AUTRE
    End Select
End Function

Private Function EstTexte(ByVal v As Variant) As Boolean
    Dim k As Long
    k = TypeValeur(v)
    EstTexte = (k = TV_TEXTE Or k = TV_AUTRE)
End Function

' Egalite stricte : meme type et meme valeur (texte sensible a la casse).
Private Function ValeursIdentiques(ByVal a As Variant, ByVal b As Variant) As Boolean
    Dim kA As Long
    kA = TypeValeur(a)
    If kA <> TypeValeur(b) Then Exit Function
    Select Case kA
        Case TV_VIDE: ValeursIdentiques = True
        Case TV_NOMBRE: ValeursIdentiques = (CDbl(a) = CDbl(b))
        Case TV_TEXTE: ValeursIdentiques = (StrComp(CStr(a), CStr(b), vbBinaryCompare) = 0)
        Case Else: ValeursIdentiques = (StrComp(CStr(a), CStr(b), vbBinaryCompare) = 0)
    End Select
End Function

Private Function ValeurTexte(ByVal v As Variant) As String
    Select Case TypeValeur(v)
        Case TV_VIDE: ValeurTexte = ""
        Case TV_ERREUR: ValeurTexte = CStr(v)
        Case Else
            If IsArray(v) Or IsObject(v) Then ValeurTexte = "" Else ValeurTexte = CStr(v)
    End Select
End Function

Private Function NormTexte(ByVal s As String) As String
    s = Replace(s, ChrW(160), " ")
    s = Replace(s, vbTab, " ")
    s = Replace(s, vbCr, " ")
    s = Replace(s, vbLf, " ")
    s = Trim$(s)
    Do While InStr(s, "  ") > 0
        s = Replace(s, "  ", " ")
    Loop
    NormTexte = LCase$(s)
End Function

Private Function NormLibelle(ByVal s As String) As String
    s = SansAccents(NormTexte(s))
    Do While Right$(s, 1) = ":"
        s = Trim$(Left$(s, Len(s) - 1))
    Loop
    NormLibelle = s
End Function

Private Function SansAccents(ByVal s As String) As String
    ' Source = lettres accentuees construites par ChrW (aucun litteral accentue ici),
    ' Cible  = equivalents ASCII, dans le meme ordre.
    Dim src As String, dst As String, i As Long
    src = ChrW(224) & ChrW(226) & ChrW(228) & ChrW(233) & ChrW(232) & ChrW(234) & ChrW(235) & _
          ChrW(238) & ChrW(239) & ChrW(244) & ChrW(246) & ChrW(249) & ChrW(251) & ChrW(252) & ChrW(231) & _
          ChrW(192) & ChrW(194) & ChrW(201) & ChrW(200) & ChrW(212) & ChrW(199)
    dst = "aaaeeeeiioouuuc" & "AAEEOC"
    For i = 1 To Len(src)
        s = Replace(s, Mid$(src, i, 1), Mid$(dst, i, 1))
    Next i
    SansAccents = s
End Function

Private Function PremierTexte(ByRef V As Variant, ByVal r As Long, ByVal lc As Long) As String
    Dim c As Long, k As Long
    For c = 1 To lc
        k = TypeValeur(V(r, c))
        If k <> TV_VIDE Then
            If k = TV_TEXTE Then PremierTexte = NormTexte(CStr(V(r, c)))
            Exit Function
        End If
    Next c
End Function

Private Function LigneANombre(ByRef V As Variant, ByVal r As Long, ByVal lc As Long) As Boolean
    Dim c As Long
    For c = 1 To lc
        If TypeValeur(V(r, c)) = TV_NOMBRE Then LigneANombre = True: Exit Function
    Next c
End Function

Private Function SignatureLignes(ByRef V As Variant, ByVal r1 As Long, ByVal r2 As Long, ByVal lc As Long) As String
    Dim r As Long, c As Long, s As String
    For r = r1 To r2
        For c = 1 To lc
            If TypeValeur(V(r, c)) <> TV_VIDE Then s = s & ValeurTexte(V(r, c)) & "|"
        Next c
    Next r
    SignatureLignes = s
End Function

' Prefixe apostrophe : Excel stocke le texte tel quel (pas de conversion de
' "9402130" en nombre, de "1/2" en date, ni de "=..." en formule).
Private Sub ProtegerTextes(ByRef arr As Variant)
    Dim i As Long, j As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            If VarType(arr(i, j)) = vbString Then
                If Len(arr(i, j)) > 0 Then arr(i, j) = "'" & arr(i, j)
            End If
        Next j
    Next i
End Sub

Private Function Ajout(ByVal t As String, ByVal s As String) As String
    If Len(s) = 0 Then
        Ajout = t
    ElseIf Len(t) = 0 Then
        Ajout = s
    Else
        Ajout = t & " ; " & s
    End If
End Function

Private Sub AjouterListe(ByRef liste As String, ByVal element As String, ByVal n As Long)
    If n <= MAX_LISTE Then
        liste = Ajout(liste, element)
    ElseIf n = MAX_LISTE + 1 Then
        liste = liste & " ; ..."
    End If
End Sub

Private Function MinL(ByVal a As Long, ByVal b As Long) As Long
    If a < b Then MinL = a Else MinL = b
End Function

Private Function LettreCol(ByVal n As Long) As String
    Dim s As String, m As Long
    Do While n > 0
        m = (n - 1) Mod 26
        s = Chr$(65 + m) & s
        n = (n - m - 1) \ 26
    Loop
    LettreCol = s
End Function

Private Function ClasseurOuvertParNom(ByVal nom As String) As Boolean
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        If StrComp(wb.Name, nom, vbTextCompare) = 0 Then ClasseurOuvertParNom = True: Exit Function
    Next wb
End Function

Private Sub Etape(ByVal libelle As String)
    Application.StatusBar = mPrefixeEtape & libelle
End Sub

Private Sub SauverEtat(ByRef st As tEtatAppli)
    st.ScreenUpdating = Application.ScreenUpdating
    st.EnableEvents = Application.EnableEvents
    st.DisplayAlerts = Application.DisplayAlerts
    st.Calculation = Application.Calculation
    st.AskToUpdateLinks = Application.AskToUpdateLinks
    st.AutomationSecurity = Application.AutomationSecurity
    st.StatusBar = Application.StatusBar
End Sub

Private Sub RestaurerEtat(ByRef st As tEtatAppli)
    On Error Resume Next
    Application.Calculation = st.Calculation
    Application.AutomationSecurity = st.AutomationSecurity
    Application.AskToUpdateLinks = st.AskToUpdateLinks
    Application.DisplayAlerts = st.DisplayAlerts
    Application.EnableEvents = st.EnableEvents
    If VarType(st.StatusBar) = vbBoolean Then
        Application.StatusBar = False
    Else
        Application.StatusBar = st.StatusBar
    End If
    Application.ScreenUpdating = st.ScreenUpdating
End Sub