[VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Staple1600

Staple1600

XLDnaute Barbatruc
Bonsoir à tous


Je sollicite votre aide pour essayer d'améliorer un code VBA utilisé pour mettre en forme un grand nombre de cellules.

Je vous invite à regarder la pièce jointe pour tester et j'espère que vos propositions réduiront le temps d'exécution de la macro

(qui sur mon PC domestique s'exécute en 3 secondes)

Merci à tous ceux qui s'arrêteront dans ce fil.
 
Solution
Staple1600
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re


Bravo pour cette piste JNP


Ca marche !

Code: 
Sub test_ok()
Dim sFrag As String, sStart As String, sEnd As String
sStart = "<HTML><body>Ce <b>mot</b> est en gras.<p>" & "Ce " & "<u>mot</u> est souligné.<p>"
sFrag = "Ce " & "<FONT COLOR=RED>mot</FONT> est en rouge.<p>" & "Ce " & "<i>mot</i> est en italique.<p>"
sEnd = "<b><i><u><font color=green>Ce format est vert, gras, italique et souligné !</font></u></i></b></body><HTML>"
PutHTMLClipboard sFrag, sStart, sEnd
ActiveSheet.Range("A1").Select
ActiveSheet.PasteSpecial Format:="HTML"
End Sub

EDITION: Maintenant ca va être coton pour créer les strings
(car évidemment chaque cellule de la colonne...
Trier par date Trier par votes
Staple1600

Staple1600

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re



pierrejean: l'espace surnuméraire est une erreur de saisie de ma part
(que j'évite avec : X = Split(Trim(cel)) ;) )

En continuant de m'amuser avec ce code, j'ai une question
Que vaut-il mieux utiliser ? (1) - commence à 1 , 2) commence à 0)
1) ttt = [{30, 1, 45, 1, 23, 1, 12, 1, 12, 1, 1}]
ou
2) ttt = Array(30, 1, 45, 1, 23, 1, 12, 1, 12, 1, 1)

D'ailleurs pierrejean, pourrais-tu m'expliquer ta logique qui t' a permis de fusionner les deux arrays en un. Merci

Autre chose:
Si on met d'abord toute la colonne B en gras et souligné
qu'ensuite on applique les couleurs désirés aux mots choisis
puis qu'en on mette aux mots non choisis
.ColorIndex = xlAutomatic
.Bold = False
.Underline = False

le temps sera d'exécution sera t-il identique au supérieur ?
 
Vote positif 0 Vote négatif
pierrejean

pierrejean

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re

ttt = Array(30, 1, 45, 1, 23, 1, 12, 1, 12, 1, 1)
repertorie tout simplement les colorindex des mots dans l'ordre ou ils apparaissent

Je ne connaissais pas la seconde ecriture qui ne me semble rien apporter

d'autre part comme l'a dit ROGER la durée d'execution est due essentiellement a
.ColorIndex = .....
.Bold=true
.underline =true
Pour s'en convaincre tester en mettant en commentaire une des 3 lignes (on reduit le temps de pres d'un tiers
Par consequent je ne crois pas que la seconde solution evoquée amene une reduction du temps
Enfin X=split(trim(cel)) ote les espaces devant et derriere cel mais non aux elements du split quant a X=trim(split(cel)) cela ne fonctionne pas !!
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re :),
Sachant que le bold et le souligné est valable pour l'intégralité
Code: 
Sub mise_en_forme_roger()
Dim tt, ttt, d_l&, j&, k&
Dim t, M$, r As Range
Dim Debut As Currency, Fin As Currency, Freq As Currency
Debut = Timer
   Application.ScreenUpdating = False
   tt = Array(0, 2, 5, 7, 9)
   ttt = Array(30, 45, 23, 12, 12)
   With Feuil1
      d_l = .Cells(.Rows.Count, 2).End(xlUp).Row
      For j = 1 To d_l
         Set r = .Cells(j, 2)
         t = Split(r.Text)
         r.Font.Bold = True
         r.Font.Underline = True
         For k = 0 To UBound(tt)
            M = t(tt(k))
            With r.Characters(InStr(1, r.Text, M), Len(M)).Font
               .ColorIndex = ttt(k)
            End With
         Next k
      Next j
   End With
   Range("A13") = (Timer - Debut) & " sec"
   Application.ScreenUpdating = True
End Sub
me permet de descendre à 2.46 secondes :p...
Pas forcément le but, mais pas idiot non plus par rapport au cahier des charges :D.
A +
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re :),
Y a une piste que j'essaie d'explorer, mais sans succès :eek:...
Soit ça ne peut pas marcher, soit il ne me manque pas grand chose :p !
Le principe serait de coder la mise en forme (avec des balises, je n'ai pas de soucis dans d'autre logiciels, mais avec Excel, je coince un peu...), de la charger en variable, puis de l'envoyer vers Excel via le presse-papier.
Quelque chose de ce type
Code: 
Sub test()
Dim MyData As DataObject
Dim MyString As String
MyString = "<HTML><body>Ce <b>mot</b> est en gras.<p>" _
        & "Ce " & "<u>mot</u> est souligné.<p>" & "Ce " _
        & "<FONT COLOR=RED>mot</FONT> est en rouge.<p>" _
        & "Ce " & "<i>mot</i> est en italique.<p>" & _
        "<b><i><u><font color=green>Ce format est vert, gras, italique et souligné !</font></u></i></b></body><HTML>"
Set MyData = New DataObject
MyData.SetText MyString
MyData.PutInClipboard
Cells(1, 1).Select
ActiveSheet.PasteSpecial Format:="HTML"
End Sub
Nécessite l'ouverture de la référence "Microsoft Forms 2 Object Library".
Si vous voyez une piste de ce côté là...
A + :cool:
 
Vote positif 0 Vote négatif
Staple1600

Staple1600

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Bonjour JNP


le bold et le souligné est valable pour l'intégralité
Cliquez pour agrandir...
Non ce n'est pas exact

Seuls certains mots seront en gras et soulignés.

Je me demandais juste si au niveau du temps d'exécution si

mettre tout en gras et souligné puis ensuite mettre les mots non choisis en non gras et non souligné revenait au même que de mettre les mots choisis et seulement ceux-ci en gras et souligné.

Je me posais la question de savoir si découper la procédure (par exemple appliquer le format par groupe de 100 cellules) pouvait améliorer le temps d'exécution ?
 
Dernière édition:
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re :),
Staple1600 à dit:
Non ce n'est pas exact
Seuls certains mots seront en gras et soulignés.
Je me demandais juste si au niveau du temps d'exécution si
mettre tout en gras et souligné puis ensuite mettre les mots non choisis en non gras et non souligné revenait au même que de mettre les mots choisis et seulement ceux-ci en gras et souligné.
Cliquez pour agrandir...
Vu que c'est la mise en forme qui prends du temps, tester avant de mettre en forme va gagner un temps phénoménal à mon avis... le tout est de mettre la balance du bon côté (modification massive si nécessaire, puis exceptions).
A + :cool:
Ajout : "Seuls certains mots seront en gras et soulignés." ne faisait pas partie du cahier des charges exploité par ta première macro :p...
 
Dernière édition:
Vote positif 0 Vote négatif
Staple1600

Staple1600

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re JNP

Ajout : "Seuls certains mots seront en gras et soulignés." ne faisait pas partie du cahier des charges exploité par ta première macro :p...
Cliquez pour agrandir...
Encore une fois ce n'est pas exact :rolleyes:

Regardes le code de ma première pièce jointe

Les mots en mettre en forme sont désigné dans un Array
Code: 
tt = Array(1, 3, 6, 8, 10)

tt= position des mots à colorier dans la chaine de caractère présente
dans les cellules de la colonne B
 
Vote positif 0 Vote négatif
Staple1600

Staple1600

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re


Connaissant ton addiction pour Excel 2007, j'ai eu peur que ton code ne soit pas compatible avec Excel 2000...

Je vais tester de ce pas.

EDITION: cela ne fonctionne pas sous Excel 2000
Erreur d'exécution 1004 sur cette ligne
Code: 
ActiveSheet.PasteSpecial Format:="HTML"
J'ai essayer en modifant à partir de l'enregistreur de macro avec:
Code: 
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False

Même punition.
 
Dernière édition:
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re :),
Mon addiction est peut-être un terme un peu fort, mais c'est vrai que j'aime bien :p...
Oui, je sais que mon code plante sur cette ligne (sous 2007 aussi, comme Félicie :D), mais cela signifie (je pense) que la string envoyée dans le presse-papiers reste String et non HTML... Pourtant, quand tu fais du copier/coller entre applications, c'est un des formats utilisé par le presse-papiers :confused:...
C'est pour cela que la piste m'intéresse, mais j'avoue que j'ai pas trouvé grand chose à me mettre sous la dent sur la gestion du ClipBoard...
Si tu trouves quelque chose...
A + :cool:
 
Vote positif 0 Vote négatif
Staple1600

Staple1600

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re

Par contre ceci fonctionne

Code: 
Sub JNP_test()
Dim s$, Data As DataObject
Set Data = New DataObject: s = "JNP loves Excel 2007"
    With Data: .SetText s: .PutInClipboard: .GetFromClipboard
        MsgBox .GetText(1), vbExclamation, Split(.GetText(1))(0)
    End With
End Sub
 
Vote positif 0 Vote négatif
pierrejean

pierrejean

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re

Une petite amelioration de ma macro me permet d'approcher le score de ROGER
Mais ce n'est que pour le fun !!
Par ailleurs , je constate qu'il y a plus de mots sans mise en forme que l'inverse par consequent remettre en standard apres avoir massivement modifié ne devrait pas etre plus rapide (Toutefois je rappelle que la meilleure façon de le savoir est de l'experimenter)
 

Pièces jointes

  • testOptimize.zip
    57.3 KB · Affichages: 44
  • testOptimize.zip
    57.3 KB · Affichages: 44
  • testOptimize.zip
    57.3 KB · Affichages: 42
Dernière édition:
Vote positif 0 Vote négatif
Staple1600

Staple1600

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Bonjour pierrejean


Chez moi ta nouvelle version est plus rapide que celle de ROGER2327
2.71 s

Une question: si on relance plusieurs fois , les macros (celle de ROGER2327 ou la tienne): le temps d'exécution renvoyé n'est jamais le même.
(Cela oscille en entre 2.71 et 2.80 )

Pourquoi cela ?

PS: Pour l'utilisateur final les données sont de ce type:
C'est une concaténation du contenu de plusieurs cellules (séparé par un espace) obtenu par un RECHERCHEV
qui est copié ensuite en valeurs seules
exemple:
Code: 
="FACT "&RECHERCHEV(A2;datas;2;0)&" MONT "&TEXTE(RECHERCHEV(A2;datas;3;0);"# ##0,00 €")
FACT : 12345 MONT: 123,50 € etc etc

(il y aura théoriquement 7 mots en gras souligné)
 
Dernière édition:
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : [VBA] Optimiser macro pour utilisation sur PC ancien (Array vs Boucles)

Re :),
Staple1600 à dit:
Par contre ceci fonctionne
Code: 
Sub JNP_test()
Dim s$, Data As DataObject
Set Data = New DataObject: s = "JNP loves Excel 2007"
    With Data: .SetText s: .PutInClipboard: .GetFromClipboard
        MsgBox .GetText(1), vbExclamation, Split(.GetText(1))(0)
    End With
End Sub
Cliquez pour agrandir...
Je vais l'envoyer à ma femme, pour la St-Valentin, elle va A DO RER :D...
Plus sérieusement, le format 1 est texte pur, mais quelle est la valeur de l'HTML ?
Code: 
Sub test2()
Dim MyData As DataObject, I As Double
Set MyData = New DataObject
MyData.GetFromClipboard
For I = -10000000 To 10000000
If MyData.GetFormat(I) Then MsgBox I
Next I
End Sub
après avoir copié un peu de texte sur le forum me donne plein de formats valide, mais lequel est le bon pour HTML :confused:...
Pierre-Jean, tu n'as pas une idée :rolleyes: ?
A +
 
Vote positif 0 Vote négatif

Discussions similaires

Orson83
  • Résolu(e)
XL 2010 Erreurs macro code machine pour PC unique
Réponses
11
Affichages
735
Orson83
Orson83
J
  • Question
Mon code VBA bug quand je verrouille ma session Windows 10 message d'erreur 1004
Réponses
1
Affichages
2 K
JoelR
J
B
  • Question
XL 2010 Macro qui fait grossir fichier Excel
Réponses
6
Affichages
1 K
Banjounet
B
S
  • Question
XL 2013 Aide pour codage d'une macro VBA
Réponses
1
Affichages
808
vgendron
vgendron
Y
  • Question
XL 2016 Help : Macro filtrage multicritères sur des lignes
Réponses
11
Affichages
2 K
ynx69
Y
J
  • Question
Macro VBA de sauvegarde des pièces jointes + objets et corps de mail par dossiers
Réponses
0
Affichages
3 K
jibi23
J
I
  • Question
(VBA) Optimiser utilisation BDD pour éviter le recalcul
Réponses
2
Affichages
1 K
Int0x
I
T
  • Question
[Besoin de méthode d'optimisation de temps de traitement, macro VBA][RESOLU]
2
Réponses
22
Affichages
5 K
Thibault LB
T
jayroom
  • Question
macro pour modifier une cellule déverouillée par mot de passe
Réponses
7
Affichages
2 K
jayroom
jayroom

Membres actuellement en ligne

5 membres et 45 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 247
Messages
2 086 591
Membres
103 248
dernier inscrit
Happycat