Public Type TrouveVal
FeuiMax As String
NbMax As Long
Feuil As Variant
End Type
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 1) As String
FuncName = "MaxVal"
FuncDesc = "Retourne la valeur Max qui correspond à la feuille ou ce trouve cette valeur"
Category = 13 ' category 13 Personnalisées
ArgDesc(1) = "1 = Nom de la Feuille Ou 2 = Valeur Max de la Feuille"
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub
Function MaxVal(ByVal Nb As Byte) As Variant
Dim Res As TrouveVal
Res.Feuil = Array("un", "deux", "trois", "quatre", "cinq", "six", "sept", "huit", "neuf")
Dim Ws As Worksheet
Dim tableau() As Variant
' Hypothése Classeur de 10 Feuilles (Uniquement)
ReDim Preserve tableau(1 To (ThisWorkbook.Sheets.Count - 1), 1 To 2)
For Each Ws In ThisWorkbook.Worksheets
For i = 0 To 8
If LCase(Ws.Name) = LCase(Res.Feuil(i)) Then
tableau(Ws.Index, 1) = Ws.Name
tableau(Ws.Index, 2) = Ws.Cells(1, 5)
End If
Next i
Next Ws
' Stock
Res.FeuiMax = tableau(Application.Match(WorksheetFunction.Max(Application.Index(tableau, , 2)), Application.Index(tableau, , 2), 0), 1)
Res.NbMax = tableau(Application.Match(WorksheetFunction.Max(Application.Index(tableau, , 2)), Application.Index(tableau, , 2), 0), 2)
' Resultat
If Nb = 1 Then
MaxVal = Res.FeuiMax
Else
MaxVal = Res.NbMax
End If
End Function