XL 2019 Suppression espace de début et fin - Macro très rapide

Bastien43 · 28 Juillet 2021

Bonjour,

J'ai créé cette macro pour supprimer les espaces de début et de fin de chaque cellule (de toute une colonne : 12000 lignes)

Comment accélérer la macro, ce n'est pas rapide... ?? Existe-t-il un code plus rapide ?

VB:

Sub SupEspace()

Dim plage, cellule

Set plage = Range("D2:D12000")

For Each cellule In plage

    cellule.Value = Trim(cellule.Value)

Next cellule

End Sub

Efgé · 28 Juillet 2021

Bonjour Bastien43

Par tableau (array)

VB:

Sub SupEspace2()

Dim plage As Range
Dim T As Variant
Dim i&, j&

Set plage = Range("D2:D12000")
T = plage
For i = LBound(T, 1) To UBound(T, 1)
    For j = LBound(T, 2) To UBound(T, 2)
        T(i, j) = Trim(T(i, j))
    Next j
Next i
plage.FormulaLocal = T
End Sub

Cordialement

patricktoulon · 31 Juillet 2021

re
Bonjour @job75
ta formulation : c'est bien un evaluate abrégé on est d'accords
donc c'est bien une formule
ta formulation c'est comme si tu avais écris .value=evaluate("TRIM(p)")
tu évalue donc bien une formule avec une référence circulaire
et pour preuve en non abrégé c'est la même chose un poil plus rapide encore 0.19

VB:

Sub testx()
    [A1:A100000] = "    k,goertfre    ezorfjezojfez    gfrejgr   "
End Sub

Sub SupEspace()
With [A1:A100000]
    .Name = "P" 'plage nommée
    tim = Timer
    .Value = Evaluate("TRIM(p)") 'SUPPRESPACE
MsgBox Format(Timer - tim, "#0.00")
End With
End Sub

et la preuve c'est que ça fonctionne maintenant sur 2013
je crois que tu oublie que "[blablabla]" c'est une évaluation avant tout( en ecriture abrégée) , pas seulement un range
exemple

Code:

Sub test()
MsgBox [5+10+15]
MsgBox Evaluate("5+10+15")
End Sub

c'est d'ailleurs l'object d'une longue discussion que j'ai eu il y a quelques années avec Pierre fauconier sur DVP
quand a l'utilité et la rentabilité d'utiliser le mode abrégé pour designer une plage
car en effet il force vba a évaluer l'object (perte de temps et mémoire selon lui )

d'ailleurs une autre preuve que tu te méprends
c'est que trim de vba trim un string et non une plage
donc si c’était pas interprété comme un evaluation de la fonction "TRIM " d'excel ,tu aurais un bon chbouncht de message d'erreur
essaie donc msgbox trim([A1:A100000])(1) tu verra tu aura un bong!!!!

job75 · 31 Juillet 2021

Bah la fonction Evaluate se contente d'évaluer et c'est tout.

Elle ne peut pas savoir où son résultat va être entré et encore moins créer une référence circulaire.

mapomme · 31 Juillet 2021

Bonjour à tous ,

Uniquement pour le fun!

En reprenant le fichier de @Efgé du post #35, en modifiant un peu la macro de @job75 (colonne auxiliaire fixe et copies par valeur), sur ma bécane (moins rapide que celle de @job75 ), j'améliore un peu la durée d'exécution. Je n'ai pas touché aux deux autres macros.

patricktoulon · 31 Juillet 2021

re
ben si c'est la ou le calcul itératif entre en jeu justement
plage .value= evaluation(formule matricielle de la même plage)
si on pouvait ralentir le fonctionnement on se rendrait contre que les valeurs changent les unes après les autres on est donc bien dans une référence circulaire de part le ".value=la même valeur- les espaces"
en gros ta matricielle fait
cellule1=cellule1 - les espace
cellule2=cellule2-les espaces
etc..etc...
sauf que c'est une evaluation et donc la cellule perd sa valeur d'origine autrement dit plantage
en vba dans une boucle ,il n'y aurait pas de problème on serait sur un array que l'on repose au même endroit
mais la en matricielle c'est comme si tu metais en A1 ,A2,A3,etc.. le résultat de TRIM(la cellule concerné ) sauf que la valeur original est perdue sans le calcul itératif

en tout cas j'invente rien ça fonctionne maintenant donc ......
je sertais curieux de savoir si sur 2016 ou 365 vous avez cette option et si oui la décocher pour voir

patricktoulon · 31 Juillet 2021

maintenant je vais essayer de voir comment on peut activer cet option par VBA si c'est possible
comme ca je pourrais modifier ma fonction dans l'archive et la simplifier que ce soit pour proper,lower,upper,TRIM
au pire je ferais une version 2016 et + et celle ci je la laisse comme ça

job75 · 31 Juillet 2021

patricktoulon à dit:
essaie donc msgbox trim([A1:A100000])(1) tu verra tu aura un bong!!!!

Les crochets doivent encadrer le Trim, ceci va très bien chez moi :

VB:

X = [Trim(A1:A100000)]
MsgBox X(1, 1)
MsgBox X(2, 1)

patricktoulon · 31 Juillet 2021

re
bon je vais essayer d’être plus explicite
on a donc en
A1 " toto la fritte "
A2 "bidule machin "

ta formule dans une evaluation matricielle abrégée
[TRIM(A1:A2)]

les résultats Attendus
"toto la fritte"
"bidule machin"

que crois tu qu'il y ai dans les items de ta matrice par evaluate en matricielle ??
1 le résultat de chaque cellule
2 la formule pour chaque cellule
3 la formule matricielle qui sera indexé automatiquement sur l'extention a auteur de nombre de ligne de la plage lors de la pause sur range

'********************************************************
quand on evalue en matricielle(plusieurs cellules) LE RESULTAT c'est la matrice PAS LES VALEURS
'********************************************************
dans les items tu a la formule si je peux m'exprimer ainsi "{=TRIM(A1:A2)}"
comme si tu le faisais dans une colonne à coté
j'ai bien dis "si je peux m'exprimer ainsi

alors oui bien sur que si tu fait un msgbox sur un des items tu aura le résultat ma fois c'est le but

et quand tu pose ta matrice dans une colonne à coté par exemple bien évidemment que tu a les valeurs dans les cells et non les formules de la même manière qu'avec le msgbox

mais quand tu la pause sur la même colonne l’évaluation de chaque item est fait sur du vide puisque la valeur est perdue

Attention !!!! j'insiste sur ce point!!!
je parle bien d'évaluation matricielle et non d'une évaluation unitaire ou d'expression

Efgé · 31 Juillet 2021

patricktoulon à dit:
maintenant je vais essayer de voir comment on peut activer cet option par VBA[...]

Avant de manipuler l'environnement, on peux tester une modif de la macro de Job75 :
En utilisant un second tableau comme relai pour éviter toute itération sur une même plage.

VB:

Sub Matrice()
Dim T As Variant
Dim Tim!, D!
MsgBox "Départ Matricielle à Deux tableaux", vbOKOnly, "Matricielle"
Tim = Timer
With [G2:G1000000]
    .Name = "P" 'plage nommée
    T = Evaluate("TRIM(p)")
    .Value = T
    D = Format(Timer - Tim, "0.000")
MsgBox D, vbOKOnly, "Matricielle à Deux tableaux"
Cells(7, 2) = D
End With
End Sub

Ce qui est très rapide et fonctionne

Je laisse le nouveau classeur pour tests

Cordialement

patricktoulon · 31 Juillet 2021

un truc encore plus formidable
je re décoche l'option
je re teste la macro elle fonctionne toujours

je laisse décoché et je ferme le fichier et je le ré ouvre je re teste elle fonctionne toujours

je vais plus loin
je laisse décoché l'option ,j’éteins le PC, le rallume et re teste sans l'option ben ca marche plus
je re coche l'option et re teste ca remarche
A bon entendeur

@Efgé oui c'est une idée qui est bonne finalement

job75 · 31 Juillet 2021

Salut Efgé,

Ton code :

VB:

    T = Evaluate("TRIM(p)")
    .Value = T

fait a priori ce que fait ma macro du post #8.

Donc ça fonctionnerait sur toute version Excel avec simplement l'ajout de la variable T ?

A+

Efgé · 31 Juillet 2021

job75 à dit:
VB:

T = Evaluate("TRIM(p)") .Value = T

fait a priori ce que fait ma macro du post #8.

Donc ça fonctionnerait sur toute version Excel avec simplement l'ajout de la variable T ?

A+

Oui c'est le même pricipe de matricielle que ton post, j'ai d'ailleur dit que je partai de ton code.
Je lit patricktoulon et il parle d'un problème d'itération à cause de l'utilisation de la même plage dans l'EVALUATE et dans le .value

Ajouter un array T permet de mettre les résultats de la matricielle en mémoire sans aucun lien avec un range et d'éviter toute possible itération.
Ensuite il suffit de coller l'array dans la plage.

Enfin, tout ça c'est "à mon avis"

Cordialement

job75 · 31 Juillet 2021

Peux tu me confirmer Efgé que ton code fonctionne sur Excel 2007 ?

Efgé · 31 Juillet 2021

Mon code à été fait sous 2007.
Tu peux même tester le classeur laissé au poste #53

patricktoulon · 31 Juillet 2021

re
non c'est parfait le tableau intermédiaire
bon ben c'est @job75 qui gagne une tringle a rideau avec 0.19 sec sur 100000 lignes avec le tableau intermédiaire
comme je disais au départ avec evaluate ,c'est quand meme plus rapide que toute autre version y compris la plus rapide (celle qui boucle sur un tableau)
@job je garde ta trim en version simplifié et je modifie illico presto dans mon XLAM

Bernard_XLD · 31 Juillet 2021

Re,

ça fonctionne mais je n'ai pas de gain en temps sous 365

Bien cordialement, @+

XL 2019 Suppression espace de début et fin - Macro très rapide

Bastien43

XLDnaute Occasionnel

Efgé

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Discussions similaires