C'est impossible parce qu'un désagremment d'Excel fait que lorsque l'on accède à une MFC par Macro, les adressages des cellules non figées évoluent selon la cellule active. Donc le seul moyen d'avoir un calcul juste est de selectionner la Cellule contenant la MFC.
Dans une Fonction qui doit être utilisable en tant que Formule dans un classeur, Toute action directe sur les cellules est Impossible. Donc l'usage du Select est à proscrire.
Le but étant qu'un utilisateur entre dans une cellule une Formule (ex: =CalcMFC....) et que le résultat arrive sans aucune autre manipulation.
Nous avons pour Selectionner une cellule l'obligation d'utiliser un SUB et pour ne pas faire de manipulation supplémentaire, nous allons utiliser une procédure d'interception d'évennement : OnCalculate. Cette procédure a tout de même un défaut : Les calculs sont réalisés par Excel avant son lancement. Donc prévoir tout simplement un lancement de calcul supplémentaire tout en déconnectant l'interception d'évennement pour ne pas boucler indéfiniment.
Elle rescence toute les Formules CalcMFC, qu'elle analyse et pour lesquelles elle réalise le calcul demandé. Un fois le résultat trouvé, elle le place dans un dictionaire public. Et lors du recalcul lancé par la procedure, la Fonction Personnalisée lit tout simplement ce dictionnaire, en extrait le résultat et l'envoie à la cellule
Le But du défis n'est pas de réaliser cela tout simplement, mais de le faire dans un classeur qui évolue selon le nombre de feuilles à explorer. Exemple : j'ai actuellement 3 feuilles de tarifs Fournisseurs mais dans le futur, je peux en ajouter 1, ou 3 ou 7...... Et le But est de ne rien modifier dans mes formules et que cela fonctionne toujours.
Alors le défis est de contrôler l'insertion, le Déplacement, La suppression ou encore la Modification du nom d'une feuille uniquement avec des formules
J'ai précisé Sans VBA, ce qui me laisse le loisir d'utiliser des Macros natives Excel : Les Macro Excel4. Ces macros Inutilisables dans des cellules classiques, peuvent néanmois être nommées.
La macro Excel4 LIRE.CLASSEUR me renvoie des renseignements sur le classeur actif. =LIRE.CLASSEUR(1) me renvoie une matrice avec les noms de feuille.
Il reste néanmoins la réactualisation de cette Formule Nommée qui ne se réactualise pas toute seule comme toutes les Macros Excel4. Une astuce simple est d'ajouter à la formule qui utilise cette Macro, une formule volatile (Aujourdhui(), Maintenant() ou encore Indirect()). Ces formules volatiles sont recalculées à chaque fois par excel.
Dans le classeur joint : en ligne 1, j'ai ajouté en fin de formule : & INDIRECT("IV1"), une concaténation d'une cellule vide
Le classeur fourni comprend 4 feuilles de recherches, mais la feuille Recherche est préparée pour 15 Feuilles
Le But du défis n'est pas de récupérer une feuille précédemment détruite bien que cela devienne possible, mais de créer une interruption supplémentaire. Il suffit pour cela d'ajouter une petite procédure à celle que je vais vous donner
La destruction d'une feuille est effective non pas à sa désactivation, mais à l'activation d'une autre feuille. Donc il devient facile en travaillant simultanément sur les interruptions Désactivate et Activate de créer une interruption sur la suppression d'une Feuille.
Public feuil As String, fc As Integer
Private Sub Workbook_SheetActivate(ByVal sh As Object)
If ThisWorkbook.Sheets.Count <> fc Then _
Workbook_SheetDeleted feuil
End Sub
Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
fc = ThisWorkbook.Sheets.Count
feuil = sh.Name
End Sub
Private Sub Workbook_SheetDeleted(ByVal sh As String)
MsgBox "Feuille : " & sh & " Détuite"
End Sub