XL 2016 Formule trop longue en VBA

dodineau

XLDnaute Occasionnel
Bonjour à toutes et tous.
J'ai beau chercher je ne trouve pas de solution pour contourner la limitation des formules en VBA.
En effet j'ai une formule matricielle qui fonctionne très bien en direct sur le classeur mais si je veux la mettre par VBA ça créé une erreur car elle fait plus de 255 caractères.
Y aurait-il des petits malins qui auraient une solution pour contourner ce problème ?
Merci.
A+
 
Solution
Maintenant pour résoudre le problème posé par la limitation, le plus simple en effet est de nommer la formule.

Fichier (5) avec la macro :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
Dim f$
f = "=SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])"
ThisWorkbook.Names.Add "MaFormule", RefersToR1C1:=f...

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonsoir Dodineau,
Je ne savais même pas qu'une formule était limitée à 255 caractères en VBA. :rolleyes:
Peut être une piste en découpant la formule.
Avec cette macro la formule générée fait 336 caractères :
VB:
Sub Dodineau()
    Dim Chaine(7), i%
    ' On découpe la formule
    Chaine(1) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    Chaine(2) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    Chaine(3) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    Chaine(4) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    Chaine(5) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    Chaine(6) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    Chaine(7) = "somme(A1:A1000)+somme(B1:B1000)+somme(C1:C1000)"
    ' On recolle les morceaux
    For i = 1 To 7
        Formule = Formule & Chaine(i) & "+"
    Next i
    ' On rajoute le =
    Formule = "=" & Left(Formule, Len(Formule) - 1)
    ' on colle la formule
    [H1].FormulaLocal = Formule
End Sub
 

job75

XLDnaute Barbatruc
Bonjour dodineau, sylvanu, le forum,

On peut stocker le texte de la formule dans un commentaire, voyez cette macro :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
'=SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)
Dim cel As Range, i&, f$
Set cel = [D1] 'cellule cible
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule 'mettre le nom du module
    For i = 1 To .CountOfLines
        f = .Lines(i, 1) 'texte de la ligne de code
        If Left(f, 2) = "'=" Then 'si commentaire adéquat
            cel.FormulaLocal = Mid(f, 2)
            Exit For
        End If
    Next
End With
End Sub
On peut traiter ainsi une ou plusieurs formules trop longues.

Pour accéder au VBAProject Il faut avoir coché l'option :

- sur Excel 2003 et versions antérieures Faire confiance au projet Visual Basic (menu Outils-Macro-Sécurité-Editeurs approuvés)

- sur Excel 2007 et versions suivantes Accès approuvé au modèle d'objet du projet VBA (onglet Fichier-Options-Centre de gestion de la confidentialité-Paramètres...-Paramètres des macros).

A+
 

Pièces jointes

  • Formule en VBA(1).xlsm
    16.9 KB · Affichages: 3

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Dodineau, Job,
Il me semble que FormulaArray fonctionne, voir PJ.
En A1 on a bien :
1644073513231.png

Pour la limitation à 255 caractères, peut être une piste en nommant les plages.
En PJ la formule devrait faire 288 caractères, elle n'en fait que 108, avec :
VB:
Sub Essai()
    Dim F$
    F = "=SUM(ColB)+SUM(ColC)+SUM(ColD)+SUM(ColE)+SUM(ColF)+SUM(ColG)+SUM(ColH)+SUM(ColI)+SUM(ColJ)"
    [A1].FormulaArray = F
End Sub
 

Pièces jointes

  • Classeur1.xlsm
    42.4 KB · Affichages: 1

eriiic

XLDnaute Barbatruc
suite...
Pour les formules trop longues il y a aussi la possibilité d'en mettre un bout dans une 2nde chaine et de faire un Replace dans la cellule :
VB:
Sub test()
    Dim laFormule As String, xxx As String
    laFormule = "=A1+B1+xxx"
    xxx = "C1+D1"
    [A2].Formula = laFormule
    [A2].Replace What:="xxx", Replacement:=xxx, LookAt:=xlPart
    [A2].FormulaArray = [A2].Formula
End Sub
eric
 

job75

XLDnaute Barbatruc
Il me semble que FormulaArray fonctionne, voir PJ.
Bien sûr puisque sur la PJ la chaîne ne dépasse pas 255 caractères.

Mais avec la formule de mon post #4 ceci ne fonctionne pas :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
'=SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)
Dim cel As Range, i&, f$
Set cel = [D1] 'cellule cible
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule 'mettre le nom du module
    For i = 1 To .CountOfLines
        f = .Lines(i, 1) 'texte de la ligne de code
        If Left(f, 2) = "'=" Then 'si commentaire adéquat
            cel.FormulaArray = Mid(f, 2)
            Exit For
        End If
    Next
End With
End Sub
La formule (en anglais) fait 294 caractères

Salut eriiic.
 

job75

XLDnaute Barbatruc
Bon j'ai mieux testé et je constate 2 choses :

- fichier (2), pour les formules normales il n'y a pas de limite à 255 caractères en VBA :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
[D1].FormulaLocal = "=SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)+SOMME(A1:A1000)+SOMME(B1:B1000)+SOMME(C1:C1000)"
MsgBox Len([D1].FormulaLocal) & " caractères pour la formule VBA entrée en D1"
End Sub
- fichier (3), pour les formules matricielles le VBA est limité ici à 146 caractères :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
'Pour tester agrandissez la formule ci-dessous avec des 1
[D1].FormulaArray = "=SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+SUM(B1:B1000)+SUM(C1:C1000)+SUM(A1:A1000)+11111"
MsgBox Len([D1].Formula) & " caractères pour la formule VBA entrée en D1"
End Sub
Edit : en notation R1C1 la formule contient ici 246 caractères.
 

Pièces jointes

  • Formule en VBA(2).xlsm
    16.5 KB · Affichages: 2
  • Formule en VBA(3).xlsm
    17.2 KB · Affichages: 3
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour à tous,

Ah mais voilà on y arrive, c'est formidable.

En entrant la formule en notation R1C1 la limite pour FormulaArray est bien 255 caractères :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
'Pour tester agrandissez la formule ci-dessous avec des 1
[D1].FormulaArray = "=SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+11111111111111"
MsgBox Len([D1].FormulaR1C1) & " caractères pour la formule VBA R1C1 entrée en D1"
End Sub
C'est quand même très bizarre cette différence de comportement entre fichier (3) et fichier (4).

A+
 

Pièces jointes

  • Formule en VBA(4).xlsm
    17.4 KB · Affichages: 1

job75

XLDnaute Barbatruc
Maintenant pour résoudre le problème posé par la limitation, le plus simple en effet est de nommer la formule.

Fichier (5) avec la macro :
VB:
Sub Formule()
'Touches Ctrl+F pour lancer la macro
Dim f$
f = "=SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])+SUM(RC[-3]:R[999]C[-3])+SUM(RC[-2]:R[999]C[-2])+SUM(RC[-1]:R[999]C[-1])"
ThisWorkbook.Names.Add "MaFormule", RefersToR1C1:=f 'formule nommée
[D1:D5].FormulaArray = "=MaFormule"
MsgBox Len(ThisWorkbook.Names("MaFormule").RefersTo) & " caractères pour la formule nommée ""MaFormule"""
End Sub
La formule du nom défini MaFormule est limitée comme toutes les formules à 8192 caractères.
 

Pièces jointes

  • Formule en VBA(5).xlsm
    17.4 KB · Affichages: 2

Discussions similaires

Réponses
17
Affichages
599

Statistiques des forums

Discussions
311 709
Messages
2 081 754
Membres
101 812
dernier inscrit
trufu