VBA & tableaux structurés : NE PAS mettre une formule sur toute une colonne

Version imprimable

28/01/2020, 16h23
Aymeline

VBA & tableaux structurés : NE PAS mettre une formule sur toute une colonne

Bonjour à tous,
Je rencontre un petit souci : je créé une macro qui, à partir d'une fiche produit, reporte certaines données dans une base de données. Fiche produit : 1 feuille Excel = 1 produit, base de données : 1 ligne = 1 produit
Comme la fiche produit est évolutive, je souhaite mettre des liaisons et non copier les valeurs. Jusque là tout va bien, la macro (une boucle) fonctionne bien et les liaisons aussi.

Sauf que la base de données est un tableau structuré, et que par défaut Excel affecte une formule à toute une colonne de tableau structuré. Donc toutes les lignes de la base pointent vers le dernier produit ajouté !

D'ordinaire je trouve cette fonction très pratique mais là... :? Dans Excel on peut le contourner en faisant "Annuler" et la formule ne s'applique qu'à la cellule dans laquelle on l'a saisie, mais évidemment en VBA ce n'est pas aussi simple. L'enregistreur de macro ne me retourne rien quand j'essaie.

Et sur le net on trouve plein d'infos pour tirer une formule sur toute une colonne, mais pour faire l'inverse je n'ai pas trouvé.

Bref je suis à court de pistes, si quelqu'un pouvait m'éclairer...

Merci d'avance :)
28/01/2020, 16h54
Philippe Tulliez

Bonjour,
Si l'analyse des listes de données est bien faite, il n'y a aucune raison d'avoir des formules différentes dans une colonne. C'est le but d'un tableau structuré.
Il faut juste trouver la formule qui réponde au cas de la ligne en question, les fonctions SI, RECHERCHEV entre-autres servent à cela

Bonjour …

de façon générale, à tester :

Code:

1
2
3
4
5
6
Private Sub Workbook_Open()
    Application.AutoCorrect.AutoFillFormulasInLists = 0
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.AutoCorrect.AutoFillFormulasInLists = 1
End Sub

Attention, tous les tableaux du classeur sont touchés !

29/01/2020, 12h24
Aymeline

Citation:

Envoyé par Philippe Tulliez

Bonjour,
Si l'analyse des listes de données est bien faite, il n'y a aucune raison d'avoir des formules différentes dans une colonne. C'est le but d'un tableau structuré.
Il faut juste trouver la formule qui réponde au cas de la ligne en question, les fonctions SI, RECHERCHEV entre-autres servent à cela

Citation:

Fiche produit : 1 feuille Excel = 1 produit, base de données : 1 ligne = 1 produit

Etant donné que le nom d'une feuille est modifiable (liaison créée lors de la création de la fiche produit, donc l'utilisateur est amené à le modifier), je ne vois pas trop comment faire, sinon effectivement il aurait "suffi" de mettre le nom des différentes feuilles dans une colonne. Alors que là le but est justement que les données se mettent à jour automatiquement sans avoir de saisie à faire.
Si tu sais comment faire une formule Excel avec l'index d'une feuille au lieu de son nom je veux bien essayer ;)

En attendant la solution d'OrDonc fonctionne en mettant la private sub dans le module thisworkbook (je n'utilise pas de private sub habituellement, j'ai dû un peu chercher ;) ) OU en mettant

Code:

Application.AutoCorrect.AutoFillFormulasInLists = 0

en début de macro et

Code:

Application.AutoCorrect.AutoFillFormulasInLists = 1

en fin de macro, ce qui laisse l'autofill dans le classeur au cas où on en aie besoin pour d'autres tableaux ;)

Merci :)
29/01/2020, 13h32
Philippe Tulliez

Bonjour Aymeline,
Je crée régulièrement des liens vers des feuilles excel d'un même classeur depuis une feuille nommée [Home]
Voici donc une idée et qui fonctionne sans VBA
Je nomme la cellule A1, mais on peut bien entendu nommé une plage de cellules, de la feuille liée à l'aide du gestionnaire des noms
Dans la feuille nommée [Home], un tableau structuré avec au minimum deux colonnes, l'une (Name- avec le nom donné à la cellule liée et l'autre avec la formule ci-dessous

Code:

=LIEN_HYPERTEXTE("#" & [Name])

Donc dès que l'on insère une nouvelle feuille, il suffit de créer le nom pour la cellule et ensuite il suffit de taper le nouveau nom dans la colonne [Name]
Cette opération peut bien entendu être automatisée par le VBA mais beaucoup plus simplifiée et si je reprends ce que tu as écris

Citation:

Etant donné que le nom d'une feuille est modifiable (liaison créée lors de la création de la fiche produit,

.
Dans la procédure événementielle Workbook_SheetChange du module ThisWokbook on détecte par exemple la modification de la cellule A1 avec tous les contrôles d'usage et ensuite création du nom et ensuite copie de ce nom dans la colonne A (Name) du tableau structuré, la formule sera automatiquement recopiée.
Quatre ou cinq lignes de code, c'est emballé et c'est structuré
07/02/2020, 11h55
Pierre Fauconnier

Salut.

Je plussoie les réponses de Philippe, et notamment sa remarque concernant le fait que les tableaux structurés sont intéressants justement à cause de cette propriété de colonne formulée (voir mon tuto à ce sujet). Modifier cette option d'application comme cela te l'a été proposé est une abberration.

Citation:

Envoyé par OrDonc

[...]
Attention, tous les tableaux du classeur sont touchés !

Ce n'est pas exact. Les colonnes existantes, si elles sont déjà réputées colonnes formulées ne sont pas impactées. Par contre, les nouveaux tableaux, dans le classeur mais également dans les autres classeurs de la même instance, sont touchés par la modif. Apparemment, la modification de la propriété ne s'étend pas aux autres instances d'Excel. Pour moi, cette possibilité de désactiver les colonnes formulées ne devrait même pas exister.

Ce besoin d'avoir des formules différentes dans une même colonne de table de données relève d'un défaut de conception qu'il faut résoudre à la source.

Citation:

Envoyé par Aymeline

Etant donné que le nom d'une feuille est modifiable (liaison créée lors de la création de la fiche produit[...]

Si tu modifies le nom d'une feuille, les formules s'adaptent à ce nouveau nom (sauf en cas d'utilisation de la fonction INDIRECT utilisant une chaine "en dur" contenant le nom de la feuille). Mais justement, les tableaux structurés permettent de se détacher de cette liaison aux feuilles. Si tu utilises les tableaux structurés, et notamment les références structurées, tu verras qu'il n'y a aucune notion de nom ou de position de feuille...

Il serait à mon avis judicieux de revoir la structure de ton classeur ... ;)