Formules en VBA

[Quentin91]

Bonjour à tous !

Arrêtez moi si je me trompe :
Si au lieu d'avoir des formules dans une feuille excel, ces formules appartiennent à un code vba, je peux avoir les mêmes fonctions mais en gagnant quelques Mo sur la taille de mon fichier ? (les fonctions concernées sont de simples SI, RECHERCHEV, et des opérations entre colonnes)

Si la réponse est oui, alors je suis heureux, sinon, je n'ai plus qu'à pleurer devant mon Pc qui rame et rame ..... (les JO d'avirons, c'est pour bientôt avec lui ! lol ! )

Je ne maîtrise pas VBA, donc je vais poser quelques questions basiques (désolé si certaines réponses se trouvent dans des post que je n'aurais pas vu !) :
* SI et RECHERCHEV se nomment bien IF et VLOOKUP ?

* Comment je fais pour que la macro soit permanente (ne se déclenche pas sur ordre) ? Je pense que c'est une histoire de déclaration au début .... mais c'est tout ....

* Comment déclarer un format de cellule pour la colonne complète sans passer par programmation d'une sélection de la colonne à chaque ouverture ?

* Ce code doit cohabiter et continuer à fonctionner avec d'autres macros qui masquent des colonnes en fonction de mes besoins. Est-ce un souci ?


Merci d'avance à ceux qui prendront le temps de lire et éventuellement de me répondre


Quentin91

 

[@+Thierry]

Bonjour Quentin, le Forum

Huuum, ce n'est pas vraiment génial de faire un amalgame de questions de la sortes sur un même fil de discussion...

En fait je vais te répondre en grandes lignes .

*** Pour la question de gagner des Mo si tu fais calculer par VBA ou lieu de Formule Excel, non pas vraissemblablement, mais tout est relatif.

Imaginons un tableau avec 15000 ligne et sur l'extreme colonne du droite tu as mis une Formule VlookUp (RechercheV). Les 15000 formules VlookUp seront lourdes à manipuler à chaque fois dans ce classeurs, dans ce cas là deux solutions simples sans VBA :

1) on fait cette formule et on copie la zone pour la colle en "¨Paste Value" (coller valeur uniquement)

2) on rêgle Excel pour qu'il soit en mode de calcul Manuel. (Menu / Option / Onglet "Calculs") (F9 pour obtenir le calcul)

Sinon on peut voir pour générer une macro qui fasse la recherche sur une boucle sur les cellules, mais sur de grands tableaux ce sera toujours plus long qu'une formule.
De là à améliorer ceci il faut maîtriser en VBA un peu les Array dynamics (Tableau indexé séquentiellement) pour arriver à faire des performances de traitement raisonnable.)

Il reste aussi la possibilité de faire écrire provisoirement la formule par VBA puis de la figer en Paste Value...

*** Pour la question de SI = IF et RechercheV = VlookUp, oui

*** Pour déclencher une macro automatiquement tu fais une recherche dans ce Forum à "Macro Evènementielle", ou encore à "Private Sub Worksheet" qui peuvent se déclancher seules, à un changement, par exemple, sur une feuille. Attention ces macros ne se place pas dans un module standard mais en Private Module de chaque Feuille ou du Classeur.

*** Je ne comprends pas la question, formater une colonne complete ? En passant ou en NE passant pas par programmation ?

En passant par programmtion çà donnerait ceci :
Sub FormatColonne()
Columns("A:A").NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
End Sub
De plus à chaque ouverture ? si tu as sauvé ton travail, le Format sera là... ?

*** Cohabitation, non aucun souci à priori. Il faut toutefois faire attention à certaines règles, dont par exemple des déclarations de variables soignée, idem pour les noms des macros et des divers modules, celà t'aidera à t'y retrouver.

Bon Appétit
@+Thierry

 

[Dan]

Bonjour Quentin,

Ben tu as un truc bien plus simple pour déjà gagner en poids de ton fichier.

Evite de recopier chaque fois tes formules en les remplaçant par un nom que tu définis dans Menu / insertion / nom / définir.

Exemple :
Mettons que tu dois placer cette formule somme(A1:B1) dans la colonne C1.
1. Va dans menu / insertion / nom / définir
2. Dans la rubrique "Noms dans le classeur", tapes un nom - ex TOTO
3. Dans la rubrique "Fait référence à", encode ta formule "=SOMME(A1:B1)"
4. Confime en cliquant sur "AJOUTER" et ferme la fenetre
5. Dans ta feuille, en C1 tu encodes " =TOTO"

En procédant comme cela, tu peux réduire déjà ton fichier et ce sera nettement plus lisible car Excel aime mieux quand on travaille avec des noms plutôt que des formules dans les cellules.

Maintenant, des choses à éviter - dans l'ordre:
1. Une image en arrière
2. Les couleurs de fond dans les cellules
3. Les données en couleurs
4. Le quadrillage en couleur

Vois déjà cela et fais savoir si cela réduit ton fichier

@+

Dan

 

[Quentin91]

Thierry, Dan

Merci pour vos réponses, je vais tester ces premières pistes dans l'AM.

Mon tableau ne fait pour l'instant que 1700 lignes pour 32 colonnes, mais je le renseigne tous les jours en ajoutant une trentaine de lignes.

Dan, le problème avec les définitions de noms, c'est qu'il faut redéfinir un autre nom à chaque ajout dans la liste (à ma connaissance, lorsqu'on insère une ligne, la plage sélectionnée reste la même (pas de prise en compte automatique des lignes insérées)), donc faut redéfinir les formules. Si tu as un truc, je suis à ton écoute !
Sinon, pas de mise en forme particulière (juste une colonne grisée et la ligne des titres en gras)


Désolé pour ce post avec plusieurs sujets, je me suis laissé importer !

 

[@+Thierry]

Bonjour Dan, re Quentin, le Forum

Pour les plages nommées, je suis certain que celà facilite grandement la saisie de formules çà oui ! (quand c'est applicable, ce qui ne semble pas le cas pour Quentin).

Mais par contre Dan es-tu sûre de ta source pour ce qui est de l'optimisation des formules et leur taille ? En fait, je ne suis pas du tout spécialiste en formules (çà vous le savez !! lol), mais en me plaçant du coté VBA je ne vois pas de différence de rapidité d'éxécution entre un code se référant à un object Range("A1:A500") ou Range("LaPlageA1A500"). Mais c'est possible que celà fasse mieux passer la pillule coté Excel.

Sinon pour Quentin et si l'option de Dan s'avère conforme, on peut aussi faire écrire le nom de la plage nommée par VBA (voir cette démo Lien supprimé où j'utilise ce procédé de plage nommée dynamiquement en feuille database.

Bon Courage et bon aprèm
@+Thierry

 

[Dan]

Bonsoir Quentin, Thierry,

Quentin : tu ne dois pas redéfinir le nom à chaque ligne. Si tu ne place pas des $ devant la référence de la cellule, l'adaptation se fait automatiquement. Vois mon exempe dans le fichier attaché.

Bien sûr il te faut recopier la formule dans la nouvelle cellule mais elle reste avec la même (ici TOTO)

Thierry : Salut heureux de te revoir ici. Les emplois de Noms sont vivement conseillés avec Excel. Mais bien entendu avec VBA c'est autre chose. Là on est dans un autre domaine plus complexe et comme tu le sais VBA a ses lettres de noblesse.
Avec ton exemple Quentin devrait déjà pouvoir faire un choix je pense.

Maintenant, il faut savoir s'il y a vraiment besoin d'utiliser VBA pour le tableau. Et là je pense que l'on gagnerait à avoir un petit fichier exmple de notre ami Quentin...

Bonne soirée

@+

Dan

 

[Dan]

Re,

oups le fichier ...

Dan

 

[Quentin91]

Bonsoir Thierry, Dan, le forum

Je vous fais un petit exemple demain au taf

Dan : en fait, j'ai fait l'amalgame entre la définition d'un nom pour une plage de cellule et pour une formule, tel que tu me l'expliquais. Je tente dès demain avec celles qui me préocuppent !

En fait, ma préoccupation est de rendre utilisable ce fichier par d'autres sans limite de taille par rapport aux formules à tirer en fonction du nombre de lignes. Ce qui prend de la place, c'est les formules tirées sur 3000 lignes et qui renvoie une valeur 0 ou "". Et je pense que le dimensionnement idéal du tableau serait de 8000 lignes (là, je suis à 3000 !!!)

Mes idées ne sont plus trop claires et en plus, j'ai pas le fichier chez moi pour donner des exemples précis.

Bonne nuit !

@ bientôt

 

[Quentin91]

Bonjour Thierrry, Dan !

Voilà un petit exemple !
En plus, des macros qui masquent certaines colonnes pour différentes opérations de saisie.

Je vais commencer de nommer mes formules pour voir la différence

Merci de votre aide

 

[Dan]

Bonsoir Quentin,

Alors j'ai repris ton fichier et y ai apporté quelques modifications pour plus de convivialité à l'encodage (enfin il me semble).

Pour éviter que les formules ne soient modifiables, il te suffit de faire ceci :

1. Sélectionne les cellules devant contenir les formules
2. Va dans menu / format / cellule puis onglet protection
3. Coche les cases "Verrouillée et masquée"
4. Va dans menu / outils / protection feuille puis place un mot de passe (un mot en blanc fonctionne aussi).

J'ai effectué une protection sur les cellules concernées par la colonne CNUF.

Vois si cela te convient.

@+

Dan