XL 2010 Table structurée avec formules==> Tableau VBA

vgendron · 6 Mars 2023

Hello

Je viens vers vous pour un souci de faisabilité:
avec PJ
1) Sur ma feuille, j'ai une table structurée
Celle-ci a sa ligne, d'entetes, la ligne total (avec des formules) ET une colonne avec des formules (dernière colonne pour l'exemple, mais il y a en a plusieurs dans le fichier original)

2) dans mon code, je place TOUTE cette table dans un tableau VBA (TabData() as variant) afin de pouvoir effectuer un traitement sur les données
si je fais un habituel TabData=Table.range.value ==> les formules disparaissent et sont remplacées par leur résultat
par contre. si je fais un TabData=Table.range.formula ==> LA, les formules sont stockées "intactes" sous forme de string

je peux donc faire mon traitement : pour l'exemple: si le contenu n'est pas une formule, je fais *10 (formule = commence par "=")

3) le problème arrive ici: lorsque je souhaite coller le résultat du tableau vba dans la table structurée... Table.Range = TabData
les nouvelles valeurs calculées sont ok, MAIS les formules disparaissent..

donc la question: Comment coller le contenu du tableau vba dans la table en gardant les formules..?
c'est possible dans un sens.. pourquoi pas dans l'autre??

Merci

fanch55 · 6 Mars 2023

Salut,
Code à valider:

VB:

Sub TestFormules()
Dim TabData() As Variant
Dim Table As ListObject

    Set Table = ActiveSheet.ListObjects(1)
   '.formula pour garder les formules sous leur forme
    TabData = Table.DataBodyRange.Formula

    For i = 1 To UBound(TabData, 1)
        For j = 1 To UBound(TabData, 2)
            If IsNumeric(TabData(i, j)) Then TabData(i, j) = TabData(i, j) * 10
        Next j
    Next i
    
    Table.DataBodyRange = TabData

End Sub

sylvanu · 6 Mars 2023

Bonjour Vgendron,
Je pense qu'un Array ne peut contenir que des valeurs, ni formules, ni mise en forme.
Donc dans un sens ça marche, mais dans l'autre non.

Phil69970 · 6 Mars 2023

Bonjour Vincent et Sylvain

sylvanu à dit:
un Array ne peut contenir que des valeurs, ni formules, ni mise en forme.

Pourtant j'ai vu ceci

Range.FormulaArray, propriété (Excel)

Rubrique de référence sur Office VBA

learn.microsoft.com

Avec cet exemple :

Worksheets("Sheet1").Range("E1:E3").FormulaArray = "=Sum(A1:C3)"

@Phil69970

fanch55 · 6 Mars 2023

Salut,
Code à valider:

VB:

Sub TestFormules()
Dim TabData() As Variant
Dim Table As ListObject

    Set Table = ActiveSheet.ListObjects(1)
   '.formula pour garder les formules sous leur forme
    TabData = Table.DataBodyRange.Formula

    For i = 1 To UBound(TabData, 1)
        For j = 1 To UBound(TabData, 2)
            If IsNumeric(TabData(i, j)) Then TabData(i, j) = TabData(i, j) * 10
        Next j
    Next i
    
    Table.DataBodyRange = TabData

End Sub

TooFatBoy · 6 Mars 2023

Bonjour,

Phil69970 à dit:
Pourtant j'ai vu ceci

Salut Phil
Je pense que quand le camarade sylvanu parlait d'un "array", il parlait en réalité d'un tableau en mémoire et non d'une plage de cellules sur une feuille.

vgendron : il semble étrange de travailler sur tout le TS s'il contient des formules, vu qu'elles sont là pour effectuer elles-mêmes des calculs.
Je suppose que ton but est de n'utiliser qu'une seule instruction plutôt que de travailler séparément sur plusieurs parties du TS. N'est-il pas ?

vgendron · 6 Mars 2023

Hello tous
merci pour vos retours
le code de @fanch55 est nickel !
ca garde les formules, et ca garde la forme "Table structurée" sur ma feuille

de ce que je vois de différent par rapport à mon code, c'est que tu ne charges QUE le databodyrange
alors que je chargeais le range entier
et à priori. il ne faut pas toucher aux entetes

vgendron · 6 Mars 2023

Je suppose que ton but est de n'utiliser qu'une seule instruction plutôt que de travailler séparément sur plusieurs parties du TS. N'est-il pas ?

@TooFatBoy , oui c'est ca

le databodyrange contient des colonnes editées par l'utilisateur, mais AUSSI des colonnes qui contiennent des formules
si je n'avais eu qu'une seule table, j'aurais effectivement traité chaque partie séparémment.. mais la...le nombre de tables est conséquent, sur plusieurs onglets...

!! Aïe !! .. en écrivant.. je suis en train de me rendre compte que, dans mon tableau vba.. je mets le contenu de plusieurs tables présentes sur la feuille...

Sauf à faire boucler le code de fanch55, je pense que je vais devoir Revoir la structure des feuilles pour n'avoir qu'une seule table....
bon bah. j'ai encore quelques neurones a faire chauffer...

vgendron · 6 Mars 2023

bon. j'ai trouvé la solution. du moins.. une solution
==>restituer la table cellule par cellule...sur l'exemple, ca reste rapide... à voir au final..

fanch55 · 6 Mars 2023

vgendron à dit:
bon. j'ai trouvé la solution. du moins.. une solution
==>restituer la table cellule par cellule...sur l'exemple, ca reste rapide... à voir au final..

Je ne pense pas que ce soit la solution la plus adaptée, elle est trop adhérente à l'existant .
Si jamais les "entre-tables" sont modifiés avec des zones numériques, il faudra tout reprendre.

Il vaut mieux boucler sur tous les listobjects, voire toutes les tables du classeur,
avec cette méthode chaque table peut avoir sa propre structure ...

VB:

Sub Lob_Book() ' on traite toutes les feuilles du classeur
Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        Lob_Sheet Sh
    Next
End Sub
Sub Lob_Sheet(Optional Sh As Worksheet) ' on traite toutes les tables de la feuille
Dim Lob As ListObject
    If Sh Is Nothing Then Set Sh = ActiveSheet
    Application.ScreenUpdating = True
    For Each Lob In Sh.ListObjects
        Traite_Table Lob
    Next
End Sub
Sub Traite_Table(Table As ListObject)
Dim TabData() As Variant
   '.formula pour garder les formules sous leur forme
    TabData = Table.DataBodyRange.Formula

    For i = 1 To UBound(TabData, 1)
        For j = 1 To UBound(TabData, 2)
            If IsNumeric(TabData(i, j)) Then TabData(i, j) = TabData(i, j) * 10
        Next j
    Next i
    
    Table.DataBodyRange = TabData

End Sub

vgendron · 6 Mars 2023

effectivement, j'ai été embêté avec les entre tables. que j'ai géré en testant le contenu de la première colonne
normalement, l'entre table est toujours vide (dans l'application concernée). mais.. normalement ca commence par N et pourtant.. c'est un P

XL 2010 Table structurée avec formules==> Tableau VBA

vgendron

XLDnaute Barbatruc

Pièces jointes

fanch55

sylvanu

XLDnaute Barbatruc

Phil69970

XLDnaute Barbatruc

Range.FormulaArray, propriété (Excel)

fanch55

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

Pièces jointes

fanch55

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

Discussions similaires