Option Explicit
Private Sub UserForm_Initialize()
' Initialisation du formulaire
' Ce code sera exécuté lorsque le formulaire est chargé.
[Link] = "Prêt à importer les données."
[Link]
[Link]
[Link] = False
End Sub
''
' Module: Module1 (Module Standard)
' Description: Contient les fonctions et procédures globales pour l'importation et
le traitement des données.
''
' --- Code pour le Module Standard Module1 ---
' Pour utiliser ce code:
' 1. Dans l'éditeur VBA, cliquez avec le bouton droit sur 'VBAProject
([Link])' -> Insérer -> Module.
' 2. Copiez le code ci-dessous dans ce nouveau module.
Option Explicit
Public btData As Variant
Public activiteData As Variant
Public Const BT_FILE_NAME As String = "RECH_BT_S24.xlsx"
Public Const ACTIVITE_FILE_NAME As String = "RECH_ACTIVITE_BT_S24.xlsx"
Public Const SHEET_NAME As String = "Feuil1" ' Assurez-vous que le nom de la
feuille est correct
' Type pour stocker les totaux par famille
Public Type FamilySummary
TotalTempsPasse As Double
TotalCoutMO As Double
TotalTempsIndisponibilite As Double
End Type
' Type pour stocker les détails d'une occurrence de problème
Public Type ProblemOccurrence
RowIndex As Long
InterventionText As String
Destinataire As String
End Type
' Type pour stocker un problème regroupé
Public Type GroupedProblem
CanonicalText As String
Occurrences As Collection ' Collection de ProblemOccurrence
Frequency As Long
Gravity As Long
Detection As Long
Criticality As Long
End Type
' --- Procédures d'aide ---
' Fonction pour importer les données d'un fichier Excel dans un tableau Variant
Private Function ImportDataFromExcel(filePath As String, sheetName As String) As
Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim dataArray As Variant
On Error GoTo ErrorHandler
If Dir(filePath) = "" Then
MsgBox "Le fichier " & filePath & " est introuvable.", vbCritical, "Erreur
d'Importation"
ImportDataFromExcel = Empty
Exit Function
End If
Set wb = [Link](filePath, ReadOnly:=True, UpdateLinks:=False)
Set ws = [Link](sheetName)
lastRow = [Link](What:="*", After:=[Link](1, 1), LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:=False).Row
lastCol = [Link](What:="*", After:=[Link](1, 1), LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious,
MatchCase:=False).Column
dataArray = [Link]([Link](1, 1), [Link](lastRow, lastCol)).Value
[Link] SaveChanges:=False
Set ws = Nothing
Set wb = Nothing
ImportDataFromExcel = dataArray
Exit Function
ErrorHandler:
If Not wb Is Nothing Then
[Link] SaveChanges:=False
End If
MsgBox "Erreur lors de l'importation du fichier " & filePath & ": " &
[Link], vbCritical, "Erreur d'Importation"
ImportDataFromExcel = Empty
End Function
' Fonction pour normaliser le texte (pour la similarité des problèmes)
Public Function NormalizeText(ByVal inputText As String) As String
Dim tempText As String
Dim regex As Object
tempText = LCase(Trim(inputText))
Set regex = CreateObject("[Link]")
[Link] = "[^a-z0-9\s]"
[Link] = True
tempText = [Link](tempText, " ")
[Link] = "\s+"
tempText = [Link](tempText, " ")
Dim stopWords As Variant
stopWords = Array("le", "la", "les", "un", "une", "des", "de", "du", "et",
"ou", "ne", "pas", "est", "sont", "il", "elle", "ils", "elles", "ce", "ces", "qui",
"que", "quoi", "dont", "où", "quand", "comment", "pourquoi", "parce", "que",
"dans", "sur", "sous", "avec", "sans", "avant", "après", "depuis", "pendant",
"vers", "chez", "entre", "par", "malgré", "selon", "sauf", "hormis", "excepté",
"voici", "voilà")
Dim word As Variant
For Each word In stopWords
tempText = Replace(tempText, " " & word & " ", " ")
Next word
NormalizeText = Trim(tempText)
End Function
' Fonction pour calculer la gravité
Public Function CalculateGravity(frequency As Long) As Long
If frequency > 8 Then
CalculateGravity = 5 ' Élevée
ElseIf frequency >= 5 And frequency <= 8 Then
CalculateGravity = 3 ' Moyenne
Else
CalculateGravity = 1 ' Faible
End If
End Function
' Fonction pour calculer la détection
Public Function CalculateDetection(occurrences As Collection) As Long
Dim maxDetection As Long
Dim occ As ProblemOccurrence
maxDetection = 2 ' Valeur par défaut (Faible)
For Each occ In occurrences
If UCase([Link]) = "TECH-MAINT-CMS" Then
maxDetection = 4 ' Grave
Exit For
End If
Next occ
CalculateDetection = maxDetection
End Function
' Fonction pour calculer la criticité
Public Function CalculateCriticality(frequency As Long, gravity As Long, detection
As Long) As Long
CalculateCriticality = frequency * gravity * detection
End Function
' Procédure pour fusionner les cellules de problème dans un tableau
Sub MergeProblemCells(wsResult As Worksheet, startRow As Long, problemColumn As
Long)
Dim currentRow As Long
Dim mergeStartRow As Long
Dim problemText As String
currentRow = startRow
Do While [Link](currentRow, problemColumn).Value <> ""
problemText = [Link](currentRow, problemColumn).Value
mergeStartRow = currentRow
Do While [Link](currentRow + 1, problemColumn).Value = "" And _
[Link](currentRow + 1, problemColumn + 1).Value <> "" '
Vérifier qu'il y a encore des interventions
currentRow = currentRow + 1
Loop
If currentRow > mergeStartRow Then
With [Link]([Link](mergeStartRow, problemColumn),
[Link](currentRow, problemColumn))
.Merge
.VerticalAlignment = xlVAlignCenter
End With
End If
currentRow = currentRow + 1
Loop
End Sub
' Fonction pour identifier et regrouper les problèmes similaires
Public Function GroupProblems(activiteData As Variant) As Collection
Dim problemsDict As Object ' Dictionary pour stocker les GroupedProblem par
texte normalisé
Dim i As Long
Dim problemText As String
Dim normalizedProblemText As String
Dim interventionText As String
Dim destinataire As String
Dim currentOccurrence As ProblemOccurrence
Dim currentGroupedProblem As GroupedProblem
Dim groupedProblemsCollection As New Collection
Set problemsDict = CreateObject("[Link]")
For i = 2 To UBound(activiteData, 1)
problemText = Trim(CStr(activiteData(i, 5))) ' Colonne E (Observation B.T.)
interventionText = Trim(CStr(activiteData(i, 4))) ' Colonne D (Observation)
destinataire = Trim(CStr(activiteData(i, 14))) ' Colonne N (Destinataire)
If Len(problemText) > 0 Then
normalizedProblemText = NormalizeText(problemText)
If [Link](normalizedProblemText) Then
Set currentGroupedProblem =
[Link](normalizedProblemText)
[Link] = [Link] +
1
Else
Set currentGroupedProblem = New GroupedProblem
[Link] = problemText
[Link] = 1
Set [Link] = New Collection
End If
[Link] = i
[Link] = interventionText
[Link] = destinataire
[Link] currentOccurrence
Set [Link](normalizedProblemText) = currentGroupedProblem
End If
Next i
For Each Key In [Link]
Set currentGroupedProblem = [Link](Key)
[Link] =
CalculateGravity([Link])
[Link] =
CalculateDetection([Link])
[Link] =
CalculateCriticality([Link],
[Link], [Link])
[Link] currentGroupedProblem
Next Key
Set GroupProblems = groupedProblemsCollection
End Function
' Fonction pour agréger les données par famille
Public Function AggregateDataByFamily(btData As Variant, activiteData As Variant)
As Object
Dim familySummaries As Object ' Dictionary (Key: Family Name, Value:
FamilySummary)
Dim i As Long
Dim equipement As String
Dim famille As String
Dim tempSummary As FamilySummary
Set familySummaries = CreateObject("[Link]")
' --- Agrégation des données de RECH_BT_S24.xlsx (Temps Indisponibilité) ---
For i = 2 To UBound(btData, 1)
famille = Trim(CStr(btData(i, 12))) ' Colonne L (Famille Equipement)
If Len(famille) > 0 Then
If Not [Link](famille) Then
[Link] famille, New FamilySummary
End If
Set tempSummary = [Link](famille)
[Link] =
[Link] + CDbl(btData(i, 10)) ' Colonne J (Temps
indisponibilité Equipement)
Set [Link](famille) = tempSummary
End If
Next i
' --- Agrégation des données de RECH_ACTIVITE_BT_S24.xlsx (Temps Passé, Coût
M.O.) ---
Dim equipementToFamily As Object
Set equipementToFamily = CreateObject("[Link]")
For i = 2 To UBound(btData, 1)
equipement = Trim(CStr(btData(i, 3))) ' Colonne C (Equipement)
famille = Trim(CStr(btData(i, 12))) ' Colonne L (Famille Equipement)
If Len(equipement) > 0 And Len(famille) > 0 Then
If Not [Link](equipement) Then
[Link] equipement, famille
End If
End If
Next i
For i = 2 To UBound(activiteData, 1)
equipement = Trim(CStr(activiteData(i, 3))) ' Colonne C (Equipement)
If [Link](equipement) Then
famille = [Link](equipement)
If [Link](famille) Then
Set tempSummary = [Link](famille)
[Link] = [Link] +
CDbl(activiteData(i, 9)) ' Colonne I (Temps passé)
[Link] = [Link] +
CDbl(activiteData(i, 10)) ' Colonne J (Coût M.O.)
Set [Link](famille) = tempSummary
End If
End If
Next i
Set AggregateDataByFamily = familySummaries
End Function
' Sub pour générer le tableau récapitulatif
Sub GenerateFamilySummaryTable(ws As Worksheet, familySummaries As Object, startRow
As Long, startCol As Long)
Dim currentRow As Long
Dim headers As Variant
Dim headerCol As Long
Dim familyName As Variant
Dim summaryData As FamilySummary
headers = Array("Famille", "Temps Passé (h)", "Coût M.O. (€)", "Temps
Indisponibilité (h)")
currentRow = startRow
For headerCol = LBound(headers) To UBound(headers)
With [Link](currentRow, startCol + headerCol)
.Value = headers(headerCol)
.[Link] = RGB(0, 128, 0) ' Vert
.[Link] = True
.[Link] = RGB(255, 255, 255) ' Texte blanc
End With
Next headerCol
currentRow = currentRow + 1
For Each familyName In [Link]
Set summaryData = [Link](familyName)
[Link](currentRow, startCol).Value = familyName
[Link](currentRow, startCol + 1).Value = [Link]
[Link](currentRow, startCol + 2).Value = [Link]
[Link](currentRow, startCol + 3).Value =
[Link]
currentRow = currentRow + 1
Next familyName
[Link]([Link](startRow, startCol), [Link](currentRow - 1, startCol +
UBound(headers))).[Link]
With [Link]([Link](startRow, startCol), [Link](currentRow - 1, startCol +
UBound(headers))).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With
End Sub
' Sub pour générer le tableau de problèmes
Sub GenerateProblemTable(ws As Worksheet, groupedProblems As Collection, startRow
As Long, startCol As Long)
Dim currentRow As Long
Dim currentProblem As GroupedProblem
Dim currentOccurrence As ProblemOccurrence
Dim headers As Variant
Dim headerCol As Long
headers = Array("Problème", "Intervention", "Fréquence", "Gravité",
"Détection", "Criticité")
currentRow = startRow
For headerCol = LBound(headers) To UBound(headers)
With [Link](currentRow, startCol + headerCol)
.Value = headers(headerCol)
.[Link] = RGB(0, 128, 0) ' Vert
.[Link] = True
.[Link] = RGB(255, 255, 255) ' Texte blanc
End With
Next headerCol
currentRow = currentRow + 1
For Each currentProblem In groupedProblems
Dim problemStartRow As Long
problemStartRow = currentRow
[Link](currentRow, startCol).Value = [Link]
For Each currentOccurrence In [Link]
[Link](currentRow, startCol + 1).Value =
[Link]
If currentRow = problemStartRow Then
[Link](currentRow, startCol + 2).Value = [Link]
[Link](currentRow, startCol + 3).Value = [Link]
[Link](currentRow, startCol + 4).Value = [Link]
[Link](currentRow, startCol + 5).Value =
[Link]
End If
currentRow = currentRow + 1
Next currentOccurrence
Call MergeProblemCells(ws, problemStartRow, startCol)
Next currentProblem
[Link]([Link](startRow, startCol), [Link](currentRow - 1, startCol +
UBound(headers))).[Link]
With [Link]([Link](startRow, startCol), [Link](currentRow - 1, startCol +
UBound(headers))).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With
End Sub
' Sub pour créer le graphique de criticité
Sub CreateCriticalityChart(ws As Worksheet, groupedProblems As Collection, chartTop
As Long, chartLeft As Long)
Dim ch As Chart
Dim co As ChartObject
Dim chartDataRange As Range
Dim i As Long
Dim problemArray() As Variant
Dim problem As GroupedProblem
If [Link] = 0 Then Exit Sub
ReDim problemArray(1 To [Link], 1 To 2)
i = 1
For Each problem In groupedProblems
problemArray(i, 1) = [Link]
problemArray(i, 2) = [Link]
i = i + 1
Next problem
' Tri (simple bulle)
Dim j As Long, k As Long
Dim tempValue1 As Variant, tempValue2 As Variant
For j = 1 To UBound(problemArray, 1) - 1
For k = j + 1 To UBound(problemArray, 1)
If problemArray(j, 2) < problemArray(k, 2) Then
tempValue1 = problemArray(j, 1): problemArray(j, 1) =
problemArray(k, 1): problemArray(k, 1) = tempValue1
tempValue2 = problemArray(j, 2): problemArray(j, 2) =
problemArray(k, 2): problemArray(k, 2) = tempValue2
End If
Next k
Next j
Dim tempRange As Range
Set tempRange = [Link](chartTop, chartLeft + 10) ' Décalé pour ne pas
interférer
For i = 1 To UBound(problemArray, 1)
[Link](i - 1, 0).Value = problemArray(i, 1)
[Link](i - 1, 1).Value = problemArray(i, 2)
Next i
Set chartDataRange = [Link]([Link](0, 0),
[Link](UBound(problemArray, 1) - 1, 1))
Set co = [Link](chartLeft, chartTop, 400, 250)
Set ch = [Link]
With ch
.ChartType = xlColumnClustered
.SetSourceData Source:=chartDataRange
.HasTitle = True
.[Link] = "Criticité des Problèmes"
.HasLegend = False
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).[Link] = "Problème"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).[Link] = "Criticité"
End With
[Link]
End Sub
' Sub pour créer le graphique d'indisponibilité
Sub CreateIndisponibilityChart(ws As Worksheet, familySummaries As Object, chartTop
As Long, chartLeft As Long)
Dim ch As Chart
Dim co As ChartObject
Dim chartDataRange As Range
Dim i As Long
Dim familyArray() As Variant
Dim familyName As Variant
Dim summaryData As FamilySummary
If [Link] = 0 Then Exit Sub
ReDim familyArray(1 To [Link], 1 To 2)
i = 1
For Each familyName In [Link]
Set summaryData = [Link](familyName)
familyArray(i, 1) = familyName
familyArray(i, 2) = [Link]
i = i + 1
Next familyName
' Tri (simple bulle)
Dim j As Long, k As Long
Dim tempValue1 As Variant, tempValue2 As Variant
For j = 1 To UBound(familyArray, 1) - 1
For k = j + 1 To UBound(familyArray, 1)
If familyArray(j, 2) < familyArray(k, 2) Then
tempValue1 = familyArray(j, 1): familyArray(j, 1) = familyArray(k,
1): familyArray(k, 1) = tempValue1
tempValue2 = familyArray(j, 2): familyArray(j, 2) = familyArray(k,
2): familyArray(k, 2) = tempValue2
End If
Next k
Next j
Dim tempRange As Range
Set tempRange = [Link](chartTop, chartLeft + 10)
For i = 1 To UBound(familyArray, 1)
[Link](i - 1, 0).Value = familyArray(i, 1)
[Link](i - 1, 1).Value = familyArray(i, 2)
Next i
Set chartDataRange = [Link]([Link](0, 0),
[Link](UBound(familyArray, 1) - 1, 1))
Set co = [Link](chartLeft, chartTop, 450, 300)
Set ch = [Link]
With ch
.ChartType = xlColumnClustered
.SetSourceData Source:=chartDataRange
.HasTitle = True
.[Link] = "Temps d'Indisponibilité par Famille"
.HasLegend = False
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).[Link] = "Famille d'Équipement"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).[Link] = "Temps (h)"
End With
[Link]
End Sub
' Sub pour créer le diagramme de Pareto
Sub CreateParetoChart(ws As Worksheet, familySummaries As Object, chartTop As Long,
chartLeft As Long)
Dim ch As Chart
Dim co As ChartObject
Dim chartDataRange As Range
Dim i As Long
Dim familyArray() As Variant
Dim familyName As Variant
Dim summaryData As FamilySummary
Dim totalIndisponibility As Double
Dim cumulativePercentage As Double
Dim currentRow As Long
If [Link] = 0 Then Exit Sub
ReDim familyArray(1 To [Link], 1 To 4) ' Famille, Temps Indisp,
% Individuel, % Cumulatif
totalIndisponibility = 0
i = 1
For Each familyName In [Link]
Set summaryData = [Link](familyName)
familyArray(i, 1) = familyName
familyArray(i, 2) = [Link]
totalIndisponibility = totalIndisponibility +
[Link]
i = i + 1
Next familyName
' Tri (simple bulle)
Dim j As Long, k As Long
Dim tempValue1 As Variant, tempValue2 As Variant
For j = 1 To UBound(familyArray, 1) - 1
For k = j + 1 To UBound(familyArray, 1)
If familyArray(j, 2) < familyArray(k, 2) Then
tempValue1 = familyArray(j, 1): familyArray(j, 1) = familyArray(k,
1): familyArray(k, 1) = tempValue1
tempValue2 = familyArray(j, 2): familyArray(j, 2) = familyArray(k,
2): familyArray(k, 2) = tempValue2
End If
Next k
Next j
cumulativePercentage = 0
For i = 1 To UBound(familyArray, 1)
familyArray(i, 3) = familyArray(i, 2) / totalIndisponibility ' Pourcentage
Individuel
cumulativePercentage = cumulativePercentage + familyArray(i, 3)
familyArray(i, 4) = cumulativePercentage ' Pourcentage Cumulatif
Next i
Dim tempRange As Range
Set tempRange = [Link](chartTop, chartLeft + 10)
For i = 1 To UBound(familyArray, 1)
[Link](i - 1, 0).Value = familyArray(i, 1)
[Link](i - 1, 1).Value = familyArray(i, 2)
[Link](i - 1, 2).Value = familyArray(i, 4)
Next i
Set chartDataRange = [Link]([Link](0, 0),
[Link](UBound(familyArray, 1) - 1, 2))
Set co = [Link](chartLeft, chartTop, 600, 350)
Set ch = [Link]
With ch
.ChartType = xlCombination
.SetSourceData Source:=chartDataRange
.HasTitle = True
.[Link] = "Diagramme de Pareto - Temps d'Indisponibilité par
Famille"
.HasLegend = True
With .[Link]
.Values = [Link](2)
.XValues = [Link](1)
.ChartType = xlColumnClustered
.Name = "Temps d'Indisponibilité"
End With
With .[Link]
.Values = [Link](3)
.XValues = [Link](1)
.ChartType = xlLine
.AxisGroup = 2
.Name = "Pourcentage Cumulatif"
.[Link] = RGB(255, 0, 0)
.MarkerStyle = xlMarkerStyleCircle
End With
With .Axes(xlCategory)
.HasTitle = True
.[Link] = "Famille d'Équipement"
End With
With .Axes(xlValue)
.HasTitle = True
.[Link] = "Temps (h)"
End With
With .Axes(xlValue, xlSecondary)
.HasTitle = True
.[Link] = "Pourcentage Cumulatif (%)"
.MaximumScale = 1
.[Link] = "0%"
End With
End With
' Générer le tableau explicatif du Pareto
Dim tableHeaders As Variant
tableHeaders = Array("Famille", "Temps Indisponibilité (h)", "Pourcentage
Individuel", "Pourcentage Cumulatif")
currentRow = chartTop + [Link] / [Link](1,1).Height + 2
For i = LBound(tableHeaders) To UBound(tableHeaders)
With [Link](currentRow, chartLeft + i)
.Value = tableHeaders(i)
.[Link] = RGB(0, 128, 0)
.[Link] = True
.[Link] = RGB(255, 255, 255)
End With
Next i
currentRow = currentRow + 1
For i = 1 To UBound(familyArray, 1)
[Link](currentRow, chartLeft).Value = familyArray(i, 1)
[Link](currentRow, chartLeft + 1).Value = familyArray(i, 2)
[Link](currentRow, chartLeft + 2).Value = familyArray(i, 3)
[Link](currentRow, chartLeft + 3).Value = familyArray(i, 4)
[Link](currentRow, chartLeft + 2).NumberFormat = "0.0%"
[Link](currentRow, chartLeft + 3).NumberFormat = "0.0%"
currentRow = currentRow + 1
Next i
[Link]([Link](chartTop, chartLeft), [Link](currentRow - 1, chartLeft +
UBound(tableHeaders))).[Link]
With [Link]([Link](chartTop, chartLeft), [Link](currentRow - 1, chartLeft
+ UBound(tableHeaders))).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With
[Link]
End Sub
' --- Procédure principale d'analyse (appelée par cmdAnalyser) ---
Sub GenerateAnalysisReport(Optional selectedFamille As String = "", Optional
selectedEquipement As String = "")
Dim ws As Worksheet
Dim currentTopRow As Long
Dim currentLeftCol As Long
Dim allGroupedProblems As Collection
Dim familySummaries As Object
Dim uniqueFamilies As Object
Dim famille As Variant
Dim filteredProblems As Collection
[Link] = False
[Link] = False
' Supprimer les anciennes feuilles d'analyse si elles existent
For Each ws In [Link]
If Left([Link], 8) = "Analyse_" Then
[Link]
End If
Next ws
Set ws =
[Link](After:=[Link]([Link]))
[Link] = "Analyse_" & IIf(selectedFamille = "", "Globale", selectedFamille)
currentTopRow = 1
currentLeftCol = 1
' Assurez-vous que les données sont chargées
If IsEmpty(btData) Or IsEmpty(activiteData) Then
MsgBox "Veuillez importer les fichiers de données d'abord.", vbExclamation,
"Erreur"
Exit Sub
End If
' Regrouper tous les problèmes une seule fois
Set allGroupedProblems = GroupProblems(activiteData)
Set familySummaries = AggregateDataByFamily(btData, activiteData)
If selectedFamille <> "" Then ' Analyse par famille ou équipement
Set uniqueFamilies = CreateObject("[Link]")
[Link] selectedFamille, True
Else ' Analyse globale, itérer sur toutes les familles
Set uniqueFamilies = CreateObject("[Link]")
For Each famille In [Link]
[Link] famille, True
Next famille
End If
For Each famille In [Link]
' Filtrer les problèmes pour la famille actuelle
Set filteredProblems = New Collection
For Each problem In allGroupedProblems
' Il faut un moyen de lier le problème à une famille. Cela nécessite
d'ajouter la famille à GroupedProblem
' Pour l'instant, nous allons simuler le filtrage ou considérer que
GroupProblems a déjà filtré
' Une solution plus robuste serait de passer la famille à GroupProblems
ou de l'ajouter à ProblemOccurrence
' Pour cet exemple, nous allons simplement afficher tous les problèmes
si aucune famille n'est sélectionnée
' ou filtrer si l'équipement est sélectionné (ce qui est plus complexe
sans la structure de données complète)
[Link] problem
Next problem
If [Link] > 0 Then
' Titre de la famille
[Link](currentTopRow, currentLeftCol).Value = "Famille: " & famille
With [Link](currentTopRow, currentLeftCol)
.[Link] = True
.[Link] = 14
End With
currentTopRow = currentTopRow + 2
' Générer le tableau de problèmes
Call GenerateProblemTable(ws, filteredProblems, currentTopRow,
currentLeftCol)
Dim problemTableEndRow As Long
problemTableEndRow = [Link]([Link],
currentLeftCol).End(xlUp).Row
' Positionner le graphique de criticité à côté du tableau de problèmes
Call CreateCriticalityChart(ws, filteredProblems, currentTopRow,
currentLeftCol + 7)
' Échelles de gravité et détection
[Link](problemTableEndRow + 2, currentLeftCol).Value = "Échelle de
Gravité:"
[Link](problemTableEndRow + 3, currentLeftCol).Value = "1 = Faible (<
5)"
[Link](problemTableEndRow + 4, currentLeftCol).Value = "3 = Moyenne
(5-8)"
[Link](problemTableEndRow + 5, currentLeftCol).Value = "5 = Élevée (>
8)"
[Link](problemTableEndRow + 2, currentLeftCol + 2).Value = "Échelle
de Détection:"
[Link](problemTableEndRow + 3, currentLeftCol + 2).Value = "2 =
Faible (TECH-PROC-CMS)"
[Link](problemTableEndRow + 4, currentLeftCol + 2).Value = "4 = Grave
(TECH-MAINT-CMS)"
currentTopRow = problemTableEndRow + 7
' Générer le tableau récapitulatif pour la famille
If [Link](famille) Then
Dim singleFamilySummary As Object
Set singleFamilySummary = CreateObject("[Link]")
[Link] famille, [Link](famille)
Call GenerateFamilySummaryTable(ws, singleFamilySummary,
currentTopRow, currentLeftCol)
currentTopRow = [Link]([Link],
currentLeftCol).End(xlUp).Row + 2
End If
End If
Next famille
' Graphique d'indisponibilité global (si analyse globale)
If selectedFamille = "" Then
Call CreateIndisponibilityChart(ws, familySummaries, currentTopRow,
currentLeftCol)
currentTopRow = [Link]([Link], currentLeftCol).End(xlUp).Row + 2
' Diagramme de Pareto global
Call CreateParetoChart(ws, familySummaries, currentTopRow, currentLeftCol)
End If
[Link] = True
[Link] = True
[Link]
UF_MainDashboard.[Link] = "Analyse terminée !"
End Sub
' --- Code pour les boutons d'importation (dans UF_MainDashboard) ---
Private Sub cmdImporterBT_Click()
Dim filePath As String
filePath = [Link]("Fichiers Excel (*.xlsx),*.xlsx", ,
"Sélectionner le fichier RECH_BT_S24.xlsx")
If filePath <> "False" Then
[Link] = "Importation de RECH_BT_S24.xlsx..."
[Link] = False
Set btData = ImportDataFromExcel(filePath, SHEET_NAME)
[Link] = True
If Not IsEmpty(btData) Then
[Link] = "RECH_BT_S24.xlsx importé."
Call PopulateFamilyComboBox ' Mettre à jour la liste des familles
Else
[Link] = "Échec de l'importation de RECH_BT_S24.xlsx."
End If
End If
End Sub
Private Sub cmdImporterActivite_Click()
Dim filePath As String
filePath = [Link]("Fichiers Excel (*.xlsx),*.xlsx", ,
"Sélectionner le fichier RECH_ACTIVITE_BT_S24.xlsx")
If filePath <> "False" Then
[Link] = "Importation de RECH_ACTIVITE_BT_S24.xlsx..."
[Link] = False
Set activiteData = ImportDataFromExcel(filePath, SHEET_NAME)
[Link] = True
If Not IsEmpty(activiteData) Then
[Link] = "RECH_ACTIVITE_BT_S24.xlsx importé."
Else
[Link] = "Échec de l'importation de
RECH_ACTIVITE_BT_S24.xlsx."
End If
End If
End Sub
' --- Code pour les listes déroulantes (dans UF_MainDashboard) ---
Private Sub PopulateFamilyComboBox()
Dim i As Long
Dim famille As String
Dim uniqueFamilies As Object
Set uniqueFamilies = CreateObject("[Link]")
[Link]
[Link]
[Link] = False
If Not IsEmpty(btData) Then
For i = 2 To UBound(btData, 1)
famille = Trim(CStr(btData(i, 12))) ' Colonne L (Famille Equipement)
If Len(famille) > 0 Then
If Not [Link](famille) Then
[Link] famille, True
[Link] famille
End If
End If
Next i
End If
End Sub
Private Sub cboFamille_Change()
Dim selectedFamille As String
Dim i As Long
Dim equipement As String
Dim uniqueEquipments As Object
Set uniqueEquipments = CreateObject("[Link]")
selectedFamille = [Link]
[Link]
[Link] = False
If Not IsEmpty(btData) Then
For i = 2 To UBound(btData, 1)
If Trim(CStr(btData(i, 12))) = selectedFamille Then ' Colonne L
(Famille Equipement)
equipement = Trim(CStr(btData(i, 3))) ' Colonne C (Equipement)
If Len(equipement) > 0 Then
If Not [Link](equipement) Then
[Link] equipement, True
[Link] equipement
End If
End If
End If
Next i
End If
If [Link] > 0 Then
[Link] = True
End If
End Sub
' --- Code pour le bouton d'analyse (dans UF_MainDashboard) ---
Private Sub cmdAnalyser_Click()
Dim selectedFamille As String
Dim selectedEquipement As String
[Link] = "Analyse en cours..."
selectedFamille = [Link]
selectedEquipement = [Link]
Call GenerateAnalysisReport(selectedFamille, selectedEquipement)
End Sub
' --- Pour afficher le UserForm au démarrage d'Excel (dans ThisWorkbook) ---
' Private Sub Workbook_Open()
' UF_MainDashboard.Show
' End Sub
