FormulaR1C1 en utilisant une variable tableau ?

Le 20/03/2017 à 16:05

Bonjour le forum !

Je ne maitrise pas (encore) les variables tableaux, mais je me demande si ça ne serait pas la solution à mon problème.

J'ai un tableau... assez grand (~25 000 lignes, une petite trentaine de colonnes), et j'ai des formules à étendre sur les colonnes entières, ce qui prend très longtemps.

La première est dans la cellule A2 et à étendre pour toute la colonne :

=D2&NB.SI.ENS($D$1:D2;D2)

Ce qui donne, en langage VBA :

.Range("A2:A" & derligne).FormulaR1C1 = "=RC[3]&COUNTIFS(R1C4:RC[3],RC[3])"

Et pour la seconde formule :

=B2&"______"&ANNEE(P2)&DROITE(0&MOIS(P2);2)&DROITE(0&JOUR(P2);2)&"______"&K2&"______"&G2&"______"&"OP"&E2&"______"&"Qty"&F2&"______"&L2&"______"&H2&"______"&P2

Soit en VBA :

.Range("O2:O & derligne").FormulaR1C1 = "=RC[-13]&""______""&YEAR(RC[1])&RIGHT(0&MONTH(RC[1]),2)&RIGHT(0&DAY(RC[1]),2)&""______""&RC[-4]&""______""&RC[-8]&""______""&""OP""&RC[-10]&""______""&""Qty""&RC[-9]&""______""&RC[-3]&""______""&RC[-7]&""______""&RC[1]"

Une idée de comment je pourrais mettre ça en variable tableau pour accélérer un peu (voir même beaucoup ) la macro ?

Merci d'avance !

Le 20/03/2017 à 19:43

Bonjour,

Regardes plutôt du côté de la propriété Fill de Range pour étendre les formules !

Le 20/03/2017 à 20:30

Bonjour,

mettre des formules par macro ne sera pas plus rapide que le double-clic pour les étendre. Possiblement plus lent d'ailleurs.

Calculer pour ne mettre que le résultat serait plus rapide.

eric

Le 21/03/2017 à 11:46

Bonjour,

J'ai aussi essayé avec filldown, mais ce n'est pas sensiblement plus rapide.

En début de procédure j'ai mis le code suivant pour ralentir le moins possible :

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Qu'entends tu pas "calculer directement le résultat" ?

Le 21/03/2017 à 11:52

Bonjour,

mettre le résultat calculé par la macro au lieu de la formule.

Mais si si tu as besoin que cette formule soit présente...

eric

Le 21/03/2017 à 14:38

Ça serait plus rapide ? Du coup je vais voir pour traduire ces formules en code VBA, je vous tiens au courant de l'évolution du temps d’exécution.

Le 21/03/2017 à 15:24

Donc, j'ai transformé ma

ThisWorkbook.Worksheets("RNC21").Range("A2:A" & derligne).FormulaR1C1 = "=RC[3]&COUNTIFS(R1C4:RC[3],RC[3])"

en

For i = 2 To derligne
ThisWorkbook.Worksheets("RNC21").Cells(i, 1).Value = Application.CountIf(ThisWorkbook.Worksheets("RNC21").Range(Cells(2, 4), Cells(i, 4)), Cells(i, 4))

Next i

Je gagne peu ou prou 50% de temps. Maintenant au lieu de passer ligne par ligne j'aimerais utiliser une variable tableau qui, je pense, me ferai gagner bien plus de temps. Cependant je n'en ai encore jamais utiliser et je ne sais pas trop par quel bout le prendre.

Le 21/03/2017 à 16:06

Ben oui, là tu fais toujours cellule par cellule...

Pour le reste il faudrait qq dizaines de lignes avec les explication de ce que tu veux.

Le 21/03/2017 à 16:16

Bonsoir,

En utilisant un bloc With, tu allèges le code, et surtout tu accélères...

    With ThisWorkbook.Worksheets("RNC21")
        For i = 2 To derligne
            .Cells(i, 1).Value = WorksheetFunction.CountIf(.Range(.Cells(2, 4), .Cells(i, 4)), .Cells(i, 4))
        Next i
    End With

Nb- Si tu n'utilises qu'un seul classeur, tu peux te dispenser de ThisWorkbook.

Application.CountIf est une simplification de Application.WorksheetFunction.CountIf...

En éliminant Application plutôt que WorksheetFunction a l'avantage dès la frappe du point, l'assistant VBA liste les fonctions, ce qui peut faire gagner du temps car on n'aura généralement pas à taper le nom de la fonction en entier...

Si on utilise un tableau, au lieu d'affecter à la cellule, on affecte à un tableau préalablement dimensionné sur le nombre de lignes :

    Dim tablo(2 To derligne, 0)
    With ThisWorkbook.Worksheets("RNC21")
        For i = 2 To derligne
            tablo(i, 0) = WorksheetFunction.CountIf(.Range(.Cells(2, 4), .Cells(i, 4)), .Cells(i, 4))
        Next i
        .Cells(2, 1).Resize(derligne - 1).Value = tablo
    End With

nb- Dimensionner de 2 à derligne permet de faire correspondre les indices avec les index de ligne, pas de correctif à apporter...

La 2e dimension évite d'avoir le tableau à transposer lors de l'affectation.

Cordialement.

Le 21/03/2017 à 16:59

@eriiic : Oui, c'est bien pour ça que je pensais me tourner vers les variables tableaux (j'ai commencé à potasser le tuto de Développer.com traitant du sujet).

@MFerrand : Merci.

  • J'ignorais qu'utiliser With accélére le code, je pensais que ça améliorait juste la lisibilité. J'en tiendrais dorénavant compte.
  • Idem pour Application. Je vais creuser un peu de ce côter là pour comprendre POURQUOI ça va plus vite.
  • Ton code marche bien, j'ai juste remplacé Dim par ReDim. Résultat : macro effectuée en 35 secondes soit ~10 fois plus vite que ligne par ligne. Merci pour le modèle, je le réutiliserai ultérieurement.

Merci à tous les deux pour votre aide

Le 21/03/2017 à 17:38

Salut Eric !

J'ignorais qu'utiliser With accélére le code

C'est dû au fait que VBA met alors en mémoire et a donc un accès plus direct...

C'est Microsoft qui le dit... Je suis persuadé d'avoir scanné la page de recommandations destinées à accélérer le code (vieux bouquin)... J'ai dû la classer trop soigneusement, impossible de remettre la main dessus... (je referai le scan à mon prochain passage à Nice !)

Si on considère qu'avec With, il a directement la référence sous la main, en second lieu viendraient les variables pour la rapidité d'accès (elles sont mises en mémoire à la déclaration), se différenciant encore selon le type de variable (Variant étant le plus lent, et dépendant de leur taille en mémoire, Integer plus rapide que Long par exemple...)

Cordialement.

Le 21/03/2017 à 19:17

Salut MFerrand

Integer plus rapide que Long par exemple...

pour tous les langages en théorie.

Pour vba c'est moins sûr. Il semble que les integer ne soient qu'un artifice et qu'il les convertisse en Long.

Ex :

Sub test()
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Long, m As Long, n As Long
    Dim t As Single
    t = Timer
    For i = 1 To 30000
        For j = 1 To 30000
            k = i - j
        Next j
    Next i
    Debug.Print Timer - t
    t = Timer
    For l = 1 To 30000
        For m = 1 To 30000
            n = l - m
        Next m
    Next l
    Debug.Print Timer - t
End Sub

est chez moi 4.5% plus rapide en long qu'en integer

Fin de l'aparté

eric

Le 21/03/2017 à 21:19

Pour vba c'est moins sûr. Il semble que les integer ne soient qu'un artifice et qu'il les convertisse en Long.

Ça c'est Excel qui stocke en Long ou Double (je l'ai d'ailleurs lu explicitement sous la plume de Microsoft, c'est pas secret).

Mais avec VBA on est sur de l'occupation en mémoire vive durant l'exécution... L'aide fournit d'ailleurs le nombre d'octets utilisés par chaque type de données.

Je note ton essai : ce qui me gênait un peu c'est qu'ils soient faits dans la même proc.

J'ai donc refait avec 2 proc. : 3 variables i j k

2 boucles imbriquées de -30000 à +30000

à l'intérieur on fait simplement k=i...

Effectivement, ce qui confirmerait ton constat, sur deux essais environ 4 s (3,97) d'écart au profit des variables Long (opération d'environ 1 minute (58,59 à 62,64 s)

J 'ai fait un autre essai (variable Long) avec une seule boucle de -1800060001 à +1800060000 (même nombre d'opération qu'avec les 2 boucles imbriquées précédentes.

Alors là, j'obtiens 86,12 s, soit: 23,48 à 27,53 s de plus que précédemment !

Cela mérite sans doute d'autres essais, mais ça laisserait penser qu'avec des données de type Integer on gagnerait à utiliser des variables Long, et avec des données de type Long trouver le moyen de les traiter avec des Integer (exemple : sur une plage de la ligne 100001 à 130000 traiter en boucle de 1 à 30000 les lignes de la plage...)

Cordialement.

Le 21/03/2017 à 21:52

edit : je retire le temps de 86,12 s, erroné, j'avais omis d'initialiser le temps et la mesure s'est faite juste quand minuit venait de passer.

Deux mesures refaites donnent 58,11 et 60,71 (on est donc au niveau des premiers essais variables Long...

Mais j'ai voulu faire une opération à : k=1 pour qu'elle soit strictement équivalente dans tous les tests.

Pour les 2 premiers, les temps diminuent : 58,95 et 50,52, et l'écart s'accroît au profit des variables Long...

Par contre le 3e voit une augmentation conséquente à 92,48. Refait : 88,81, on reste dans le même ordre de grandeur.

Je m'arrête de chercher une interprétation pour ce soir !

Le 21/03/2017 à 22:47

Par curiosité j'ai testé avec les bytes, c'est pire encore : + 50% que les Long

Il y a peut-être certains cas où les Long sont moins intéressants mais je vais continuer comme d'hab : tout en Long

Good night

eric

Le 21/03/2017 à 23:24

Bon ! Je reste classique dans les cas standards mais je regarderai de plus près quand le temps joue un rôle important...

Bye !

Le 22/03/2017 à 10:42

Et moi qui m'astreins à n'utiliser les Long que quand je dépasse les 32000 et quelques

Du coup je pense que je passerais mes integer en long dorénavant

Rechercher des sujets similaires à "formular1c1 utilisant variable tableau"