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