Besoin aide VBA

[Spinzi]

Bonjour à tous,

étant toujours en quête d'optimisation et de rapidité, mais cependant nul en macro/VBA, je viens vers vous pour la construction d'un fichier que j'avais réussi avec des formules, mais qui s'avère légèrement long à l'éxécution (3-4 minutes).

Je pense procéder par phases (j'ai différentes bases mais surtout un gros fichier à optimiser).

J'ai déjà commencé par tout mettre en tableaux excel car j'ai lu plusieurs fois que les tableaux accéléraient la vitesse des programmes VBA.

Dans un premier temps je souhaite donc modifier le code VBA présent dans le fichier ci dessous qui permet d'alimenter les colonnes A, B et C avec des informations sans doublon.
Ces colonnes sans doublons serviront plus tard pour alimenter plusieurs listes de validation.

J'aimerai donc une modification du code pour prendre en compte le tableau qui est dynamique et qui est donc ajusté pile poil à la taille du tableau et donc des données : pas besoin de vérifier des milliers de lignes pour rien.

Si vous avez des questions, n'hésitez pas et merci d'avance pour les améliorations que vous pourriez apporter à ce fichier.

 

[Dranreb]

Re : Besoin aide VBA

Bonjour.
À propos, vous dites que le nom Base avait pour référence =#N/A ? C'est comme ça que vous l'avez trouvé, ou c'est la formule que j'y avais initialement mis qui rend #N/A contrairement à chez moi ?

=ColUti('Base en cours'!$D$2:$O$60000)

 

[Spinzi]

Re : Besoin aide VBA

Bonjour,

les 2 mon capitaine :
en voyant que le resultat était #NA, j'ai cherché d'où pouvait provenir l'erreur. Et soit il yavait un NA dans ma base, soit la référence était mauvaise.

J'ai regardé votre macro. Je n'ai pas compris grand chose mais je pense avoir saisi pourquoi elle était si complexe : elle devrait permettre, quelque soit le fichier, de pouvoir compiler des informations beaucoup plus facilement grâce au stockage des informations dans des "dictionnaires". Ainsi peut importe la forme, mise en page du fichier, la restitution et le stockage se feraient "automatiquement" ? Parce que je n'ai pas compris comment fonctionnait votre formule "ColUti" et à quoi correspondaitl e résultat.

Merci d'avance de votre retour !

 

[Dranreb]

Re : Besoin aide VBA

Très ennuyeux si ColUti ne fonctionne pas chez vous.
Il devrait permettre de définir un Range étendu d'un groupe de colonnes à partir d'une ligne donnée jusqu'à la dernière cellule trouvée renseignée de mieux qu'une chaine vide. Étant donné qu'il marche chez moi je ne peux que vous suggérer de mettre un point d'arrêt sur le Set ColUti et de dérouler en pas à pas.

 

[Spinzi]

Re : Besoin aide VBA

trés difficile de s'appropier un fichier non construit par nos soins. Encore plus quand on est nul en VBA. Donc j'ai essayé de trouver d'où cela pouvait venir mais rien n'a attiré mon attention ...

Pour plus de compréhension par rapport à ma formule, voila ce que j'attends de la formule VBA
Ce lien n'existe plus
sauf qu'au lieu d'avoir un critère, j'aimerai en avoir 3 (Nom de département, nom de centre de couts et responsable; les 3 étant choisi par liste déroulante en cascade)

 

[Dranreb]

Re : Besoin aide VBA

Bonjour
Mais ça vous l'avez déjà. Le module de classe ListesLiées ne constitue-t-il pas correctement les plages Lst ?
Il y a juste cette définition d'une plage Base à l'aide de ColUti qui semble poser problème, mais ça n'a rien à voir.
D'abord est ce que ça vient de ColUti ? Cette procédure, dans un nouveau module ordinaire, lancée quand la feuille Base en cours est active, qu'est-ce qu'elle donne ?

Sub test()
Dim R As Range
Set R = ColUti(ActiveSheet.[D2:O2])
If R Is Nothing Then MsgBox "Effectivement, ColUti n'arrive pas à isoler la plage", , "Test" _
                Else MsgBox "Plage isolée, addresse: " & R.Address(0, 0, xlA1, True), , "Test"
End Sub

P.S. Remarque: lorsque je saurai comment ce problème devra être résolu, je vous proposerai sans doute le classeur avec une nouvelle écriture du module de classe ListesLiées qui garantira que la liste établie pour chaque argument sera compatibles avec les autres arguments dont les valeurs existent dans la base même si on change subitement de cellules pour tous les arguments.

 

[Spinzi]

Re : Besoin aide VBA

Si je peux me permettre une légère remarque : je ne comprends rien à votre vocabulaire ! C'est purement macro et c'est un language que je ne comprends pas étant néophite. Alors j'ai bien compris quil fallait que je regarde et essaie de comprendre pourquoi

LL.Plage [Base]

bugg, et donc a priori dû à la liste définie "Base" dans le gestionnaire de nom qui renvoie #NA.

Mais je ne sais pas comment vous avez créé ces bases (manuellement ? avec du VBA) et n'arrive pas à retrouver le fameux ColUti.

Merci de votre compréhension, mais si j'arrive a (mieux) comprendre votre fichier, la solution devrait suivre.

Bien à vous

ps : je ne sais meme pas ajouter un module en VBA. Mais ça je le répète depuis le début de cette conversation. J'essaie de trouver un tuto pour faire votre manip

edit : j'ai créé un nouveau module dans le fichier que tu as fourni page 3, je ne sais pas comment voir si il fonctionne.

 

[Dranreb]

Re : Besoin aide VBA

Bonjour
Un des moyens d'exécuter une macro est d'utiliser la touche F5 lorsque le curseur texte est dedans. Assurez vous que la feuille active est la Base en cours. J'ai créé le nom "Base" avec mon gestionnaire personnel de noms dans le classeur avec pour référence =ColUti('Base en cours'!$D$2:$O$65536), mais j'envisage de le créer ou le rectifier avec du VBA lorsqu'on quitte la feuille. Le problème c'est que je ne sais pas tant que vous n'aurez pas fait le test si je peut y utiliser ColUti. Cette Function est écrite dans le module Utilit. Elle est très courte vu qu'elle utilise la PlgUti.

 

[Spinzi]

Re : Besoin aide VBA

Je viens d'insérer votre module (je suis allé sur ma base en cours, collé le vba puis F5), voilà le message qu'il rapporte :

Test
Plage isolée; addresse: '[LstLiéesSpinzi (1).xls]Base en cours'!D2:O9

Dites moi si il vous faut plus d'informations

 

[Dranreb]

Re : Besoin aide VBA

Bonne nouvelle. Ça marche. C'est apparemment juste son utilisation comme référence à une plage nommée qui pose problème contrairement à chez moi. Je définis donc cette plage en sortant de la feuille Base en cours (Worksheet_Deactivate dans module FBdD (Base en cours)).

 

[Spinzi]

Re : Besoin aide VBA

Plus de problème de mon coté si ce n'est un choix de cellule vide dans la validation alors que je n'en ai plus dans ma base (j'ai rempli les cases vides) => une fois que 2 des 3 listes de valdiations sont selectionnées, le choix vide apparait sur la derniere liste non encore selectionnée

ps : ca n'a pas d'importance si j'ai enregistré le fichier sous xlsm ?

 

[Dranreb]

Re : Besoin aide VBA

Je préfèrerais en .xls
Je crois que je vois ce que vous voulez dire. Mais je ne sais pas trop quoi y faire. C'est plus compliqué qu'il n'y parait car on ne peut plus agrandir une plage référencée (telle que celle en référence d'un nom) contenant moins de 2 lignes. Auriez vous une idée de la façon de procéder ? Ce n'est pas une erreur du module de classe, qui, lui, identifie correctement l'ensemble devant logiquement faire partie de la liste, mais qui s'interdit volontairement de laisser moins de 2 lignes dans cette plage de liste…

 

[Spinzi]

Re : Besoin aide VBA

Cela n'a rien d'impactant en soi pour l'instant car je ne sais pas si cela intéragit avec la liste de resultats qui devra découler de cette selection.
J'ai une autre question, toute bête : mon tableau base va plus loin que la colonne O. Pas besoin de changer le code ?

 

[Dranreb]

Re : Besoin aide VBA

Non, sauf dans la Private Sub Worksheet_Deactivate() de FBdD (Base en cours)
Changez le "O" de ColUti(Me.[D2:O2]).Name = "Base"

Information: Le module de classe peut jouer un rôle dans l'établissement de résultats devant découler de la sélection.
Il est en effet pourvu d'une méthode Lignes renvoyant une table des numéros de lignes dans la plage source compatibles avec les choix effectués dans les cellules Arguments en cours.
Mais vous ne parlez jamais d'en changer ? Je comprends alors de moins en moins pourquoi vous ne préférez pas des ComboBox dans un Userform. Le système existe maintenant depuis longtemps et a fait ses preuves.

 

[Spinzi]

Re : Besoin aide VBA

Je n'ai pas compris votre interrogation par rapport aux cellules Arguments en cours (je ne sais pas à quoi cela correspond).
Pour les combobox je ne vois pas l'avantage que cela pourrait procurer. Pour moi c'est un élément limitant car il ne permet pas l'acces à l'onglet quand l'userform est en cours de modification.
Mais je suis ouvert à toute proposition et si cela n'est pas long à concevoir un apercu des 2 solutions pourrait m'aider à choisir.

Toujours merci pour vos explications, si vous souhaitez des informations complémentaires, n'hésitez pas et vivement que je puisse tester la macro.

Non, sauf dans la Private Sub Worksheet_Deactivate() de FBdD (Base en cours)
Changez le "O" de ColUti(Me.[D2:O2]).Name = "Base"

Dans cette procédure, que cherche à faire ColUti ? Si c'est juste pour alimenter les listes de validations, les données servantes s'arrêtent à la colonne O, donc pas d'impact normalement. Cependant si cela peut servir à pêcher d'autre informations alors il faudra que je le change à chaque ajout de colonne ?

 

[Dranreb]

Re : Besoin aide VBA

Je n'ai pas compris votre interrogation par rapport aux cellules Arguments en cours

J'ai essentiellement conçu ce module pour un usage où les ComboBox ne sont pas applicables: lorsque dans certaines colonnes d'un tableau toutes les lignes portent une liste de validation dépendant de choix dans d'autres colonnes de cette même ligne et que l'ensemble des colonnes d'arguments dans cette ligne là forme les arguments courants.

il ne permet pas l'acces à l'onglet quand l'userform est en cours de modification

Si, il suffit qu'il soit affiché en non modal, c'est à dire soit en mettant sa propriété ShowModal à False, soit en ajoutant un paramètre 0 à l'appel à sa méthode Show.

il faudra que je le change à chaque ajout de colonne ?

À vrai dire, je ne vous l'ai pas encore dit, mais je suis perturbé depuis le début par le fait que les liste sont dans la même feuille, à gauche, que la base, au lieu d'être sur une feuille séparée. Mais si vous voulez néanmoins, pour la base, toujours prendre le reste des colonnes vous auriez intérêt à utiliser plutôt PlgUti:

PlgUti(Me.[D2]).Name = "Base"