XL 2021 Réduction temps d'exécution boucle macro

[treza88]

Bonjour à tous,

Je cherche comment je pourrais réduire le temps d'exécution d'une macro qui modifie environ 700 formules.
Dans le fonctionnement, j'ai un tableau de environ 700 lignes avec des formules dans un colonne par exemple la F, ce tableau est actualiser toutes les semaines et on y ajoute des colonnes en bout de tableau.
Avec la boucle suivante, j'actualise les formules pour qu'elles prennent en compte la nouvelle fin du tableau, mais cette actualisation de formules prends 28s.

Je voudrais si c'est possible réduire ce temps d'exécution.

Voici la boucle qui tourne pendant 28s :

For Each cell In Sheets(feuilleActive).Range("F7" & ":F" & DernCel)
    If cell.HasFormula Then
        If Left(cell.FormulaLocal, 6) = "=SOMME" And Not regCell2.test(Replace(Replace(Split(cell.FormulaLocal, ":")(1), "$", ""), ")", "")) Then
            cell.FormulaLocal = Replace(cell.FormulaLocal, Split(cell.FormulaLocal, ":")(1), _
            Cells(cell.Row, numerodecolonne2).Address & ")")
            'MsgBox Cells(cell.Row, nbrColAColler).Address
            'Stop
        End If
        'MsgBox Replace(Replace(Split(cell.FormulaLocal, ":")(1), "$", ""), ")", "")
    End If
Next cell

 

[Dranreb]

Bonsoir.
Deux voies à explorer :
1) — N'examiner que les cellules d'une plage renvoyée par Sheets(feuilleActive).Range("F7" & ":F" & DernCel).SpecialCells(xlCellTypeFormulas.
2) — Affecter Cell.FormulaLocal à un String pour diminuer le nombre de consultations de cette propriété.
Remarque: le problème ne se poserait plus si la formule était à référence structurée dans un tableau Excel.

 

[TooFatBoy]

Bonjour,

Je cherche comment je pourrais réduire le temps d'exécution d'une macro qui modifie environ 700 formules.
Dans le fonctionnement, j'ai un tableau de environ 700 lignes avec des formules dans un colonne par exemple la F, ce tableau est actualiser toutes les semaines et on y ajoute des colonnes en bout de tableau.

Avec aussi peu de renseignements, pas facile de voir ce qui est le mieux adapté à ton problème.

Mais si je comprends bien, c'est 700 fois la même formule, ce qui fait que tu pourrais peut-être utiliser FormulaArray ?
Ou peut-être pourrais-tu utiliser un TS, ce qui ferait que tu n'aurais que la formule en F7 à modifier ?
Ou éventuellement pourrais-tu insérer les nouvelles colonnes au lieu de les ajouter à la fin du tableau, ce qui fait que là tu n'aurais rien à modifier ?

 

[treza88]

Merci pour vos proposition à vous deux, vous êtes toujours de bon conseil.

Je vais tester l'affectation à un string, je ne pensais même pas que ça pouvais se faire.

FormulaARRAY (que je ne connaissais pas) ne conviendra pas je pense, car ce n'est pas la même formule sur chaque lignes.
Le TS ne conviendra pas non plus dans ce cas.
Quand à l'insertion, ça peut être une bonne piste à creuser.

Par contre suite à la lecture de vos proposition, je viens de penser à autre chose, dans une formule, n'est il pas possible de lui indiquer que la dernière colonne de la formule est la dernière colonne du tableau ?
Un peu comme en VBA on code "

Columns.Count).End(xlToLeft)

Et étant utilisateur de base en formule, il me semble que l'on peut le faire, mais comment ?

 

[TooFatBoy]

Le numéro de la dernière cellule non vide de la ligne 3 (par exemple) est :

{=MAX(COLONNE(3:3)*(3:3<>""))}

 

[treza88]

Bonjour et merci TooFatBoy pour la piste de réflexion.
J'en suis arrivé à cette formule :

INDIRECT(ADRESSE(3;SIERREUR(MAX(COLONNE(I3:XFD3)*(I3:XFD3<>"");COLONNE(J3));4))

Intégré dans ma boucle, ça donne ça :

For Each cell In Sheets(feuilleActive).Range("F7" & ":F" & DernCel)
    If cell.HasFormula Then
        If Left(cell.FormulaLocal, 6) = "=SOMME" And Not regCell2.test(Replace(Replace(Split(cell.FormulaLocal, ":")(1), "$", ""), ")", "")) Then
            'MsgBox cell.Address
            'MsgBox Split(cell.FormulaLocal, ":")(0)
            formule = Split(cell.FormulaLocal, ":")(0) & ":" & "INDIRECT(ADRESSE(" & cell.Row & ";SIERREUR(MAX(COLONNE(I" & cell.Row & ":XFD3)*(I" & cell.Row & ":XFD" & cell.Row & "<>"");COLONNE(J" & cell.Row & "));4))"
            'MsgBox formule
            cell.FormulaLocal = formule
            MsgBox cell.FormulaLocal
        End If
    End If
Next cell

Par contre ce que je ne comprend pas c'est que " cell.FormulaLocal = formule" ne remplace pas la formule dans la cellule.
La formule reste inchangé.

 

[TooFatBoy]

MAX(COLONNE(I" & cell.Row & ":XFD3)*(I" & cell.Row & ":XFD" & cell.Row & "<>"");COLONNE(J" & cell.Row & "))

Si tu as voulu ici utiliser la formule de #5, c'est raté. 🙁

Comme je l'ai écrit précédemment, dans la formule de #5 c'est le numéro de ligne qu'il faut indiquer, et non une plage de cellules.
De plus la formule de #5 est matricielle, et on retombe donc sur FormulaArray, si je ne me trompe.

 

[treza88]

Bonjour,

Dans mon cas et après une journée de recherche le numéro de ligne ne fonctionne pas, car ma formule est sur la même ligne en colonne F.
Ce qui produit une formule a référence circulaire, donc c'est pour cela que je suis passé par une plage.
Et de plus la formule reste matricielle et fonctionne très bien, il n'y a pas de parenthèse matricielle car je suis sous 2021, donc plus nécessaire.

Mon soucis ne se situe plus au niveau de la formule, mais que la cellule en colonne F ne prend pas la nouvelle formule.
Malgré le :

cell.FormulaLocal = formule

c'est toujours l'ancienne formule qui est dans la cellule.

Et je ne comprend pas pourquoi, le code me parait simple, et pourtant il ne fonctionne pas.

 

[cathodique]

Bonjour,

Dans mon cas et après une journée de recherche le numéro de ligne ne fonctionne pas, car ma formule est sur la même ligne en colonne F.
Ce qui produit une formule a référence circulaire, donc c'est pour cela que je suis passé par une plage.
Et de plus la formule reste matricielle et fonctionne très bien, il n'y a pas de parenthèse matricielle car je suis sous 2021, donc plus nécessaire.

Mon soucis ne se situe plus au niveau de la formule, mais que la cellule en colonne F ne prend pas la nouvelle formule.
Malgré le :

cell.FormulaLocal = formule

c'est toujours l'ancienne formule qui est dans la cellule.

Et je ne comprend pas pourquoi, le code me parait simple, et pourtant il ne fonctionne pas.

Bonjour,

si tu avais joint un fichier. Ton problème aurait été déjà résolu.

Bonne journée.

 

[dysorthographie]

bonjour,
tu pourrai utiliser la fonction décaler qui permet de prendre la dernière cellule.

disons que la dernière cellule ce trouve en C5.
tu écris =DECALER(D6; -1; -1)
ainsi si tu insert une ligne ta formule passera en d7 automatique et si tu ajoutes une colonne tu passeras en e7
notes que le -1 indique la ligne -1 et le deuxième la colonne -1 soit C5 pour la formule initiale.

 

[Bernard_XLD]

Bonjour le fil, le forum

@treza88 , et pourquoi ne pas utiliser un tableau de formula ? ça fonctionne comme avec un tableau de Value (du moins sur mon 365) et c'est très rapide ! beaucoup plus que d'agir sur les cellules
Tu n'auras même pas besoin de tester si c'est une formule mais simplement la présence de la chaine et ensuite ton regCell2.test dont je ne sais pas à quoi il correspond.
Après, sans fichier exemple, je ne vais pas me triturer plus.


Cordialement,
Bernard_XLD

Dim Tab_Temp, Compteur&
Tab_Temp = Sheets(1).Range("A1:B4").Formula
For Compteur = LBound(Tab_Temp, 1) To UBound(Tab_Temp, 1)
    If Left(Tab_Temp(Compteur, 1), 6) = "=SOMME" Then
    'modifications
    End If
Next Compteur
Sheets(1).Range("A1:B4").Value = Tab_Temp

en fichier exemple joint un code qui copie valeurs et formules de la feuille 1 à la feuille 2 en passant par un tableau

Sub essai()
Dim Tab_Temp, Compteur&
Tab_Temp = Sheets(1).Range("A1:B4").Formula
Sheets(2).Range("A1:B4").Value = Tab_Temp
End Sub