Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

[bouclesdor]

Bonjour,

Je vais encore appel à vous les pros d'excel. J'ai récemment fait un fichier avec des formules Vlookup qui va chercher de l'info dans 26 feuilles différentes. Je remarque que mon fichier est lent. Ça prend 5 secondes entre le temps que je sais un information et que je puisse changer de ligne pour faire un autre suivi. Je me demandais si cette lenteur était dû aux nombreuses formules Vlookup?

Est-ce que si je remplace mes Vlookup par des fonctions indirect (qui iront quand même chercher mes infos dans mes feuilles) est-ce que le système sera moins lent?

Avant de me taper tout le fichier et refaire mes formules je fais appel à vous pour savoir si ça vaut la peine de tout modifier...

Merci à l'avance,

Bouclesdor

 

[JCGL]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Bonjour à tous,

Je serais tenter de te dire : fais l'essai...
Je pense que cela sera plus rapide. Reviens nous faire part de ton expérience.

A + à tous

Édition : Salut Gaël

 

[Gael]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Bonjour Bouclesdor, le forum et Salut JCGL,

Avant de tout modifier, essaye de te mettre en calcul sur ordre dans outils/options puis de lancer un calcul partiel sur les éléments modifiés avec une macro évènementielle de type:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.EntireRow.Calculate
Application.EnableEvents = True
End Sub

Dans cet exemple, le calcul ne se fera que sur la ligne qui contient la cellule modifiée et non pas sur l'ensemble du tableau comme dans un calcul automatique.

Il faut bien sûr adapter l'exemple à ton fichier et bien prendre en compte toutes les plages dont le contenu peut être affecté par la saisie. Le calcul partiel peut se faire de la même façon sur une cellule ou une plage de cellules.

@+

Gael

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Est-ce que par hazard on peut faire une fonction indirect qui va chercher l'info dans différente feuilles même si les feuilles ne sont pas identique ligne pour ligne...

(J'ai un tableau qui me sert à calculer les heures travaillés des employés et j'ai un onglet par paie. J'ai aussi un sommaire dans lequel j'ai tous mes employés avec le sommaire des vacances, maladies, etc... )

Je ne maitrise pas encore la fonction indirect mais j'arrive quand même à la faire fonctionner au bout d'un moment (essaie et erreur). Donc j'avais choisi la fonction Vlookup pour m'assurer que si mes feuilles n'était pas identiques (une ligne de plus) l'information retourner se basait sur l'info en entête de ligne...

Y a t'il une fonction alternative au Vlookup ou bien une techniqualité dans la fonction indirect que je ne connais pas mais qui pourrais se basé sur mon entête de ligne...?

Merci à l'avance,

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Merci Gael je crois qu'on écrivait en même temps 🙂 je vais tenté ton option et je vous reviens avec ça...

Merci!

 

[Gael]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Re,

Et si tu nous envoyais un exemple?

Gael

 

[JCGL]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Bonjour à tous,
Salut Gaël,

Merci de déposer un fichier, évidemment réduit en taille, avec des explications.

A + à tous

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Donc résultat après le test de Gael: au lieu de prendre 5 secondes ça en prend 3!! c'est un peu mieux... je vais quand même continuer de chercher...

PS voici le fichier j'ai gardé seulement 5 lignes d'employé et 5 onglets paies afin de réduire le plus possible mon fichier...

Donc exemple avec formule indirect: onglet "Vacances" voir cellule en jaune...
J'ai plein de formule vlookup dans mes onglets vacances, maladies, sans solde... je crois que c'est pour ça la lenteur du fichier...

Merci beaucoup!!

 

[Gael]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Bonjour Bouclesdor, bonjour à tous,

Dans ton exemple, tu as mis la macro dans "thisworkbook", il faut la mettre dans chaque feuille de calcul. je ne comprends pas pourquoi tu obtiens quand même des mises à jour avec un meilleur temps de réponse.
Une fois que tu as mis la macro dans chaque feuille, tu mets le calcul sur ordre dans outils-options et refais le test pour bien vérifier.

@+

Gael

PS: Erreur de ma part, tu peux bien sûr mettre la macro dans "Thisworkbook" mais dans ce cas, le code est légèrement modifié:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)Application.EnableEvents = False
Target.EntireRow.Calculate
Application.EnableEvents = True
End Sub

Gael

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Bonjour Gael,

Merci pour ta réponse j'ai fait des tests (en mettant dans chaque onglet le code ou bien dans this workbook) et ça ne fonctionne toujours pas... voir ci-joint j'ai fait des print screen de ce que j'ai fait... Je ne sais pas si c'est moi qui n'a pas fait une étape comme il faut mais bon je vais continuer à chercher....

Merci de ton aide Gael!

PS je ne suis pas la pro des VBA j'arrive a comprendre ce que le code fait mais pas plus...

Bouclesdor

 

[Gael]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Re,

Je ne connais pas XL2007, mais tu peux mettre dans "Thisworkbook", le code suivant:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

Le calcul se mettra en mode manuel à l'ouverture du document.

@+

Gael

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Merci Gael pour ta réponse encore une fois dès plus rapide!! je vais tester ton code dans mon fichier...

Pour votre info... je viens de me rendre compte que ce ne sont pas tous les onglets qui sont super lent. Mes sommaires sont super rapides et quelques feuilles de paies... je vais donc chercher dans mes feuilles ce qui pourrait causer ça... c'est peut-être pas mes Vlookup finalement mais quelque chose dans certaines feuilles (mise en forme automatique peut-être ou formule....) je vais regarder ça cet après-midi!

Je vous tiens au courant ...

Bouclesdor

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Rebonjour à vous tous,

Donc pour vous tenir informé c'est bel et bien un bug dans une feuille que j'ai recopier plusieurs fois car les premiers onglets (les paies que j'avais déjà controlé) allait très bien (sauf la feuille pour cette semaine pour laquelle j'ai eu temps de misère car elle était très lente et excel plentait toujours...) Mais toutes mes feuilles suivantes (qui étaient vides) étaient toutes lentes...

Par contre, je ne sais pas pourquoi... mystère...

Ce que j'ai fait pour résoudre le problème: j'ai pris une feuille qui allait rapidement et j'ai enlever toutes les infos dedans pour qu'elle soit de nouveau vierge et je l'ai recopier dans mon fichier.

Merci encore mille fois pour votre aide!

Bouclesdor

 

[bouclesdor]

Re : Lenteur formulaire avec Vlookup (indirect est-ce plus rapide)

Bon et bien finalement j'ai mis le doigt sur le bobo!!

Mon problème vient de la mise en forme conditionnelle qui se recopiait à chaque fois dans mes feuilles lorsque je copiais mes données donc je me ramassait avec des tonnes et des tonnes de mise en forme identique d'où la lenteur lors de la saisie...

Merci encore de votre aide!
Bonne journée
Bouclesdor