XL 2021 Pourquoi EXCEL affiche les formules en texte ?

[Dudu2]

Bonjour,
Je ne comprends pas pourquoi EXCEL ne veut pas traiter Range.formula comme des formules !!!

 

[Dan]

Bonjour @mapomme,

Dans ce cas alors pourquoi ai-je 70 en B5 & B6 et pas 120 ? C'est la dernière formule affectée qui devrait prendre le dessus non ?
!

Je pense que vous n'avez pas compris la fonctionnalité du tableau structuré comme Mapomme vous le mentionne dans son post.
Si vous mettez une formule en B5, le tableau structuré la recopiera automatiquement en B6

Oubliez votre macro et faite ceci manuellement

A1 = 70 et A2 = 120
Mettez ceci en B5 --> = A1+A2 (190)
Appuyez sur Entrée et vous verrez qu'en B6 vous avez la formule =A2+A3 (120 puisque A3 =0)

Maintenant effacez B5 et B6 puis retournez en B5 et mettez ceci =$A$1+$A$2
Vous verrez que le résultat est 190 en B5 et B6

Dans les deux cas le tableau structuré a étendu la formule de B5 à B6 automatiquement

Donc avec votre macro et cette ligne --> .formula = Tabformules
Excel met 70 en B5 et le tableau structuré recopie automatiquement la même chose en B6 sans tenir compte de la deuxième valeur 120 de votre tableau tabformules

 

[mapomme]

Ce n'est pas le comportement normal

J'avais mis l'adjectif entre apostrophe. Ce n'était pas pour rien...

En effet, je ne cherche plus à savoir quel est le comportement normal ou pas d'Excel. Nous n'avons ni le cahier des charges d'Excel, ni le code. Tout ce qu'on fait au mieux c'est de constater un comportement, décider de le considérer comme préjudiciable ou non par rapport à ce qu'on fait puis éventuellement de trouver un pis-aller. Nous n'avons absolument pas les billes pour décider de la normalité ou non d'un comportement d'Excel.

De plus les évolutions d'Excel peuvent rendre des comportements passés 'normaux' caducs.
Exemple : soit une colonne avec un TS en colonne A alors Cells(rows.count ,"a").end(xlUp) adressera toujours la dernière cellule du TS que cette cellule soit vide ou non (avant les TS il n'y avait pas ce cas).

Le code d'Excel est devenu sans doute si volumineux que chaque nouvelle fonctionnalité est susceptible (ou pas) d'entrainer des "effets de bord" pas toujours prévues ou prévisibles.

nb : c'est là aussi que l'expérience du codeur devient importante. Tout cas tordu rencontré (ici le pb. des variants) est un atout pour les codes futurs.

 

[Dudu2]

@Dan,
Je pense que tu n'as pas compris la 2ème partie de ma réponse du Post #30 à @mapomme. Faut tout lire avant de dire "t'as pas compris".

 

[Dudu2]

Un petit récap (copié du fichier joint avec code).

 

[laurent950]

Bonsoir

tous simplement :

Option Explicit

Sub a()
    Dim Tbl As ListObject
    Dim TabFormules() As String
   
    Set Tbl = ActiveSheet.ListObjects(1)
   
    ReDim TabFormules(1 To 1, 1 To 2)
    TabFormules(1, 1) = " =12+13" ' Créer un espace avant égale
    TabFormules(1, 2) = " =22+23" ' Créer un espace avant égale
   
    With Tbl
        .DataBodyRange.Formula = TabFormules
        .DataBodyRange.Value = Application.Trim(.DataBodyRange.Value) ' Supprimer les espaces avant égale = Résultat
    End With
End Sub

 

[Dudu2]

Bonsoir @laurent950,

Même si les formules en Tableau String passent grâce à ton artifice d'espace avant, tu te heurtes au problème de l'affectation d'un tableau sur des cellules de Tableau Structuré. C'est la propagation automatique des formules que dans ce cas, même l'option Application.AutoCorrect.AutoFillFormulasInLists = False ne peut empêcher (ce qui n'est pas normal de mon point de vue).

Le résultat est:

au lieu de

 

[laurent950]

Même si les formules en Tableau String passent grâce à ton artifice d'espace avant

il n'y a plus d'espace avant ! vous avez tester ma VBA ?

 

[Dudu2]

J'ai testé ce que tu as mis en Post #35.
Alors en effet si on fait ça:

Dim Tbl As ListObject
    Dim TabFormules() As String
 
    Set Tbl = ActiveSheet.ListObjects(1)
 
    ReDim TabFormules(1 To 1, 1 To 2)
    TabFormules(1, 1) = "=12+13"
    TabFormules(1, 2) = "=22+23"
 
    With Tbl
        .DataBodyRange.Formula = TabFormules
        .DataBodyRange.Value = .DataBodyRange.Value
    End With

ou çà:

Sub a()
    Dim Tbl As ListObject
    Dim TabFormules() As String
 
    Set Tbl = ActiveSheet.ListObjects(1)
 
    ReDim TabFormules(1 To 1, 1 To 2)
    TabFormules(1, 1) = "=12+13"
    TabFormules(1, 2) = "=22+23"
 
    With Tbl
        .DataBodyRange.Formula = TabFormules
        .DataBodyRange.Formula = .DataBodyRange.Formula
    End With
End Sub

ça fonctionne. Mais le problème de base cité dans mon Post #36 reste entier. Tu ne fais pas de test ?

 

[Dudu2]

@TooFatBoy,
C'est la technique de @laurent950 appliquée cellule par cellule.
Pas besoin de maniper sur un espace comme je l'ai dit au Post #38.
Ce qui peut se transformer en:

Sub b()
' Test sur Tableau Structuré
Dim Tbl As ListObject
Dim TabFormules() As String

    Set Tbl = ActiveSheet.ListObjects(1)

    ReDim TabFormules(1 To 5, 1 To 1)
    TabFormules(1, 1) = "=12+13" 
    TabFormules(2, 1) = "=22+23"
    TabFormules(3, 1) = "=32+33"
    TabFormules(4, 1) = "=42+43"
    TabFormules(5, 1) = "=52+53"

    With Tbl
        .DataBodyRange.Formula = TabFormules  
  
        MsgBox "Affectation des .Value ou .Formula cellule par cellule" & vbCrLf & _
               "Application.AutoCorrect.AutoFillFormulasInLists = " & Application.AutoCorrect.AutoFillFormulasInLists

        Dim i As Integer
        For i = 1 To UBound(TabFormules, 1)
            .DataBodyRange(i).Value = .DataBodyRange(i).Value
            '.DataBodyRange(i).Formula = .DataBodyRange(i).Formula
        Next i
    End With
End Sub

Ce qui est intéressant dans ce cas c'est la non propagation automatique des formules sans inhiber le AutoCorrect.AutoFillFormulasInLists.

 

[laurent950]

C'est la technique de @laurent950 appliquée cellule par cellule.

multi cellules

Option Explicit

Sub a()
    Dim Tbl As ListObject
    Dim TabFormules() As String
    Dim LignesNecessaires As Long

    Set Tbl = ActiveSheet.ListObjects(1)

   ' 1. Préparation du tableau de String 2x2
    ReDim TabFormules(1 To 2, 1 To 2)
    LignesNecessaires = UBound(TabFormules, 1) ' = 2

    TabFormules(1, 1) = " =12+13" ' Créer un espace avant égale
    TabFormules(1, 2) = " =22+23" ' Créer un espace avant égale
    TabFormules(2, 1) = " =62+63" ' Créer un espace avant égale
    TabFormules(2, 2) = " =72+73" ' Créer un espace avant égale

    ' On ajoute le nombre de lignes manquantes au Tableau Structuré (ListObject)
    Do While Tbl.ListRows.Count < LignesNecessaires
        Tbl.ListRows.Add AlwaysInsert:=True
    Loop

    Tbl.DataBodyRange.Formula = TabFormules

    Tbl.Range.Offset(1, 0).Resize(Tbl.ListRows.Count, Tbl.ListColumns.Count).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

 

[TooFatBoy]

@TooFatBoy,
C'est la technique de @laurent950 appliquée cellule par cellule.

Oui, j'étais effectivement parti de sa macro qui ne fonctionne pas, puisque comme tu le montres sur ta capture d'écran, après avoir enlevé les espaces on se retrouve avec la même valeur dans toutes les cellules.

Mais je me suis aperçu, après avoir posté mon message, que je m'étais trompé dans le code VBA (j'avais oublié de modifier l'indice de la donnée à laquelle supprimer l'espace) donc j'ai supprimé mon message qui n'avait plus lieu d'être.
Désolé.

 

[Dudu2]

@laurent950,
Ramené au tableau de 2 lignes:

Sub bb()
    Dim Tbl As ListObject
    Dim TabFormules() As String

    Set Tbl = ActiveSheet.ListObjects(1)

   ' 1. Préparation du tableau de String 2x2
    ReDim TabFormules(1 To 2, 1 To 1)

    TabFormules(1, 1) = "=12+13"
    TabFormules(2, 1) = "=22+23"

    Tbl.DataBodyRange.Formula = TabFormules

    Tbl.Range.Offset(1, 0).Resize(Tbl.ListRows.Count, Tbl.ListColumns.Count).Replace What:="=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Alors ça marche aussi sachant que la boucle explicite précédente cellule par cellule est remplacée par une boucle implicite de Replace.
C'est une autre forme d'écriture originale.