Microsoft 365 Conception d'un outils alliant faciliter d'utilisation et optimisation de la RAM/Processeur/Espace Disque

elgringo123456 · 6 Juillet 2023

Bonjour,

Je me permets de vous solliciter sur un problème de conception devant allier faciliter d'utilisation et allègement sur l'outil mis en place.
Au sein d'un classeur, il y a un outil en VBA (codé par macro cf. onglet Tools). Cet outil qui sur la base de sélection de données dans des cellules, permet de récupérer des données filtrées dans un autre onglet (Data) et de réaliser un calcul.
Le calcul est très rapide quant au filtre réalisé et calcul demandé.
Cet exercice de filtre sera répété à minima plus de 350 fois (via une boucle, par exemple sur la base d'une liste de 350 fonds).
A chaque fois que cet outil sera utilisé le résultat sera stocké dans une variable pour y être déposé plus tard dans Excel.
L'objectif est d'avoir une piste d'audit (pour plus tard si besoin en cas d'identification de valeur bizarre, d'accéder aux détails la liste, étant donné que le calcul coûte pas cher, je souhaiterai que cela soit relancé sur demande de l'utilisateur)
Je ne souhaite pas stocker cette liste (piste d'audit) dans un autre onglet car je vais démultiplier les données.
Je ne suis aussi pas très chaud en TCD car j'ai peur de surcharger mon classeur et j'ai besoin d'y ajouter des formules spécifiques à la fin.
Etant donné que le coût de calcul est très faible, une fois que les résultats sont déposés, je cherche à savoir comment adapté l'accès à cette piste d'audit en effectuant ce calcul à la demande.
Par exemple, je m'étais dit qu'en créant un lien hypertexte de la donnée calculée et accessible à l'outil c'est pas mal mais il manque :
1) Indiquer les filtres à réaliser
2) Générer le calcul à la volée
Malheureusement, le 1) et 2) à priori ne peuvent pas se faire uniquement via un lien hypertexte.
Auriez-vous des idées à me proposer ?
L'objectif est de :
A) Faciliter au mieux l'utilisation sur l'outil,
B) Minimiser le stockage (piste d'audit) des données ayant permis de calculer ces ratios (liste des données de base éviter de doubler l'onglet Data),
C) Régénérer ce calcul à la demande,
D) De minimiser l'utilisation de la RAM (éviter un tableau et TCD préférable d'utiliser le naming par colonne à priori).

Afin d'être le plus explicite possible, je vous propose un fichier en pièce jointe.
Onglet : Synthèse => Données calculées déposées,
Onglet : Tools => Calculateur (utilisation de façon ponctuelle par un utilisateur et utilisation dans une boucle),
Onglet : Data => Toutes les données permettant d'effectuer le calcul.
N.B. Dans la réalité, j'utilise d'autres onglets que Data en plus pour me permettre de réaliser ce calcul et j'ai plus de colonnes à intégrer dans l'onglet Tools.

Dynamique du process - Utilisation 1 :
1) Lancement d'une boucle permettant de récupérer les résultats de l'onglet Tools (pour chaque fonds),
2) Dépôt des résultats obtenus dans l'onglet Synthèse.

Dynamique du process - Utilisation 2 :
Une fois les résultats déposés, comment fait-on pour à partir de l'onglet Synthèse et par simple clic au niveau de la cellule par exemple "Synthese_Fds1" pouvoir accéder à l'onglet Tools et re génerer le calcul suivant la configuration de l'onglet Synthèse à la volée ?
Je pensais utiliser la gestion évènementielle d'Excel mais j'ai peur de surcharger complètement le workbook.

Dynamique du process - Utilisation 3 :
Utilisation de l'onglet Tools en mode standalone directement par l'utilisateur

Merci d'avance de vos suggestions
Bien cordialement
El Gringo 123456

xUpsilon · 7 Juillet 2023

Bonjour,

Déjà, je rencontre un souci lorsque je lance "Calcul", car la formule de SOMME en F... boucle sur elle même (comme on utilise une plage fixe $F$4:$F$35, si tu n'as que deux valeurs, tu vas vouloir sommer la cellule avec elle même à l'infini).
Personnellement, j'aurais utilisé la fonction DECALER, qui s'adapte dynamiquement à la taille de ta plage.

Ensuite, pour le fond de ta question sur l'utilisation d'un hyperlien pour "retourner en arrière et relancer le calcul", sache qu'il existe un évènement qui s'appelle Worksheet_FollowHyperlink, qui te permet de déclencher une exécution de macro lors de l'appel d'un hyperlien.
Dans Synthèse, tu as ton filtre en colonne B, donc au clic de l'hyperlien, on peut imaginer...

Zon · 7 Juillet 2023

Bonjour,

Moi je comprends pas vraiment le but final.

Le filtre CLassique Excel peut suffire peut être , pourquoi saisr des valeurs en B3 et B6 alors qu'en selectionnant dans un filtre tu auras le même résultat ??

Tu veux fliquer l'utilisateur ??

A+++

xUpsilon · 7 Juillet 2023

Bonjour,

Déjà, je rencontre un souci lorsque je lance "Calcul", car la formule de SOMME en F... boucle sur elle même (comme on utilise une plage fixe $F$4:$F$35, si tu n'as que deux valeurs, tu vas vouloir sommer la cellule avec elle même à l'infini).
Personnellement, j'aurais utilisé la fonction DECALER, qui s'adapte dynamiquement à la taille de ta plage.

Ensuite, pour le fond de ta question sur l'utilisation d'un hyperlien pour "retourner en arrière et relancer le calcul", sache qu'il existe un évènement qui s'appelle Worksheet_FollowHyperlink, qui te permet de déclencher une exécution de macro lors de l'appel d'un hyperlien.
Dans Synthèse, tu as ton filtre en colonne B, donc au clic de l'hyperlien, on peut imaginer récupérer la valeur de filtrage via l'adresse de l'hyperlien, puis réutiliser ça pour relancer la macro dans Tools, comme tu l'aurais fait en lançant depuis Tools.

Bonne journée,

elgringo123456 · 7 Juillet 2023

Bonjour xUpsilon
Vraiment big big thank U pour la fonctionnalité Worksheet_FollowHyperlink !!!!
Sacré bonne idée. Je ne connaissais pas du tout !!!!
Désolé pour le template que j'ai crée au regard de l'erreur de la SOMME car j'ai enormément simplifier le calcul et j'ai peu testé le mini outil
You've made my day !
Have a good day
El Gringo123456

elgringo123456 · 7 Juillet 2023

Zon à dit:
Bonjour,

Moi je comprends pas vraiment le but final.

Le filtre CLassique Excel peut suffire peut être , pourquoi saisr des valeurs en B3 et B6 alors qu'en selectionnant dans un filtre tu auras le même résultat ??

Tu veux fliquer l'utilisateur ??

A+++

Bonjour Zon,
Désolé pour mon mismatch au niveau de la description et au niveau du mini fichier Excel.
Je souhaite effectuer des calculs mais vraiment plus compliqué qu'une somme (je me suis permis de simplifier mon calcul et me concentrer sur mon soucis de conception).
Objectifs :
1) Onglet Tools est utilisé par qui veut (tout utilisateur) en effectuant des filtres et après il lance le calcul et obtient le résultat
2) Onglet Synthèse,
2.1.) il y a un code VBA qui va utiliser le bouton "Calcul" mais en spécifiant une batterie de filtres (pas que 2 filtres dans l'exemple) de manière itérative
2.2) Chaque résultat sera déposé dans Synthèse
N.B. Etant que le calcul ne coûte vraiment pas cher (je ne stocke pas la piste d'audit), je le lance au besoin par l'utilisateur
Lors du dépôt dans Synthèse, je créer un lien hypertexte me permettant d'accéder au bon endroit dans l'onglet Tools et de lancer le bouton "Calcul" avec les filtres concernés par ce calcul

Je ne sais pas si j'ai été claire car effectivement j'ai été très ou trop prolixe !
Je pense qu'xUpsilon m'a donné une belle piste de réflexion (et pas d'audit sans jeu de mots !)
Depuis le temps que je suis cette communauté, je suis très content car il y a toujours quelqu'un pour donner un coup de main en mode super conseil !
Même si j'ai un peu évolué, je reste toujours très impressionné par le niveau des gens et qualité des réponse au sein de cette communauté
Donc vraiment : MERCI BEAUCOUP !

Zon · 7 Juillet 2023

Re,

A priori ton hyperlien doit renvoyer vers NomFds tout le temps, si c'est pas le cas il faut le dire...

Mon idée est de créer l'hyperlien avec comme affichage le nom du fonds et la date( pour commencer) à la fin de Triglobal. avec séparateur _.
Quand on clique sur l'hyperlien (evenement décrit par @elgringo123456 ) on récupère le nom du fond ainsi que la date , on relance Triglobalfiltrerequete avec ce 2 derniers en parametres.

Pour cela:

Rajouter cette procédure dans ton code (aprés le end function)

Code:

Sub CreerHyperlienCell(F As Worksheet, Cel$, Optional Adres$, _
                        Optional SubAdres$, Optional TexteAffiche$)
 
   With F
     .Hyperlinks.Add .Range(Cel).End(xlUp), Adres, SubAdres, , TexteAffiche
   End With
End Sub

et celle ci

Code:

Sub SuivreHyperlien(ByVal Hyperlien As Hyperlink)
   Dim T
 
   T = Split(CStr(Hyperlien.TextToDisplay), "_") ' on récupere la date et le nom fu fonds séparés
   TRIGlobalFiltreRequete CDate(T(0)), CStr(T(1))
 
End Sub

Moi je transformerai ta fonction TRIGlobalFiltreRequete en procédure

VB:

Sub TRIGlobalFiltreRequete(ByVal DateRef As Date, ByVal Fdsin$)

il faut bien sûr supprimer l'affectation du résultat de la fonction

Code:

TRIGlobalFiltreRequete = Cells(4 + Li2Montant.Count, 6).Value

qu'on peut remplacer par

Code:

CreerHyperlienCell Sheets("Synthèse"), "C65536", , "NomFds", DateRef & "_" & Fdsin

Tu crées une autre procédure (pas ex Tri) que tu affectes à ton bouton Calcul

Code:

Sub Tri()
  TRIGlobalFiltreRequete Range("DateRef").Value, Range("NomFds").Value
End Sub

Enfin dans le module de la feuille Synthese tu dois avoir ceci

Code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  SuivreHyperlien Target
End Sub

NB: Pour les plages nommées , pas besoin de spécifier le nom de feuille, ni de classeur tant qu'on est dans le bon classeur.
Il manque des tests , améliorer peut être les performance de triglobal, la gestion des doublons pour les "filtres" créés....

A+++

elgringo123456 · 7 Juillet 2023

Zon à dit:
Re,

A priori ton hyperlien doit renvoyer vers NomFds tout le temps, si c'est pas le cas il faut le dire...

Mon idée est de créer l'hyperlien avec comme affichage le nom du fonds et la date( pour commencer) à la fin de Triglobal. avec séparateur _.
Quand on clique sur l'hyperlien (evenement décrit par @elgringo123456 ) on récupère le nom du fond ainsi que la date , on relance Triglobalfiltrerequete avec ce 2 derniers en parametres.

Pour cela:

Rajouter cette procédure dans ton code (aprés le end function)

Code:

Sub CreerHyperlienCell(F As Worksheet, Cel$, Optional Adres$, _ Optional SubAdres$, Optional TexteAffiche$) With F .Hyperlinks.Add .Range(Cel).End(xlUp), Adres, SubAdres, , TexteAffiche End With End Sub

et celle ci

Code:

Sub SuivreHyperlien(ByVal Hyperlien As Hyperlink) Dim T T = Split(CStr(Hyperlien.TextToDisplay), "_") ' on récupere la date et le nom fu fonds séparés TRIGlobalFiltreRequete CDate(T(0)), CStr(T(1)) End Sub

Moi je transformerai ta fonction TRIGlobalFiltreRequete en procédure

VB:

Sub TRIGlobalFiltreRequete(ByVal DateRef As Date, ByVal Fdsin$)

il faut bien sûr supprimer l'affectation du résultat de la fonction

Code:

TRIGlobalFiltreRequete = Cells(4 + Li2Montant.Count, 6).Value

qu'on peut remplacer par

Code:

CreerHyperlienCell Sheets("Synthèse"), "C65536", , "NomFds", DateRef & "_" & Fdsin

Tu crées une autre procédure (pas ex Tri) que tu affectes à ton bouton Calcul

Code:

Sub Tri() TRIGlobalFiltreRequete Range("DateRef").Value, Range("NomFds").Value End Sub

Enfin dans le module de la feuille Synthese tu dois avoir ceci

Code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) SuivreHyperlien Target End Sub

NB: Pour les plages nommées , pas besoin de spécifier le nom de feuille, ni de classeur tant qu'on est dans le bon classeur.
Il manque des tests , améliorer peut être les performance de triglobal, la gestion des doublons pour les "filtres" créés....

A+++

Merci beaucoup Zon pour toutes ces explications que je vais me faire un plaisir de décortiquer !
Je n'ai qu'un mot à dire => Vous êtes super forts !!
J'adore car le seul truc que j'ai c'est que sur Excel je ne sais rien et je vois ou sont mes maitres à penser
Encore MERCI !!!!

Microsoft 365 Conception d'un outils alliant faciliter d'utilisation et optimisation de la RAM/Processeur/Espace Disque

elgringo123456

XLDnaute Occasionnel

Pièces jointes

xUpsilon

Zon

XLDnaute Impliqué

xUpsilon

XLDnaute Accro

elgringo123456

XLDnaute Occasionnel

elgringo123456

XLDnaute Occasionnel

Zon

XLDnaute Impliqué

elgringo123456

XLDnaute Occasionnel

Discussions similaires