Discussion :

[patricktoulon]

bonjour a tous
j'ai un petit schmilblick avec un TS avec des formules

quand j'ajoute une ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub AddRow()
    Range("Tableau2").Select
    ActiveSheet.ListObjects("Tableau2").ListRows.Add
End Sub

j'ai les formules qui me donnent #VALEUR!
je suis obligé de les selectionner pour aficher la formule et taper enter
j'ai essayé calculate sous toutes ces moutures (application,activesheet,activesheet.cells(x,y))mais rien n'y fait
mon excel est fainéant il veux pas calculer

une idées du comment on fait cette bouteille de lait??????

[patricktoulon]

re
j'ai contourner le problème en désactivant le calculation avant le addrow puis en la réactivant après
c'est pas top ca re calcule tout

[marot_r]

Bonjour.

Une idée pas forcément la plus élégante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
dim Cellule as range
dim TaLigne as range
set Taligne= 'ici ta nouvelle ligne dans la table de données.
 
for each Cellule in TaLigne.Cells
   Cellule.Formula=Cellule.Formula
next Cellule
 
set TaLigne=Nothing

A+

[patricktoulon]

re
bonjour marot_r
j'ai testé c'est plus long que la désactivation du calculate

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub AddRow()
    'Application.Calculation = xlManual
    ActiveSheet.ListObjects("Tableau2").ListRows.Add
    'Application.Calculation = xlAutomatic
For Each Cellule In ActiveSheet.ListObjects("Tableau2").DataBodyRange.Cells
   Cellule.Formula = Cellule.Formula
Next Cellule
End Sub

merci pour le retour

[patricktoulon]

re
y a t il un moyen de désactiver le calculate sur la plage déjà calculée
si oui comment??
je voudrai désactivé celle qui son déjà avant le addrow

[Pierre Fauconnier]

Pour tester l'existence de la barre, plutôt que le on error, j'utilise une fonction. Ca évite de bloquer sur une pseudo-erreur, notamment en phase de test. Débuguer un code truffé de On Error inutiles est agaçant au plus haut point.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Function CommandBarExists(Name As String) As Boolean
  Dim Counter As Long
 
  Counter = 1
  Do While Counter <= CommandBars.Count And Not CommandBarExists
    If StrComp(Name, CommandBars(Counter).Name, vbTextCompare) = 0 Then CommandBarExists = True
    Counter = Counter + 1
  Loop
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If CommandBarExists("test") Then CommandBars("Test").Delete

[Pierre Fauconnier]

Salut.

[Pierre Fauconnier]

Tu pourrais aussi utiliser SIERREUR(Calcul; ValeurSiErreur)... C'est un peu à cela que la fonction sert: contourner l'erreur...

[patricktoulon]

bonsoir Pierre
a ben oui j'y ai pas pensé
après j'ai toujours du mal il me manque toujours une parenthèses quelque par quand des formule sont imbriquées

en l'etat ma formule c'est:

=SI(Tableau2[[#Cette ligne];[FR]]<>"";NOMPROPRE(TRANSLAte(Tableau2[[#Cette ligne];[FR]]; "fr"; "en"));"en attente")

;[FR]]<>"";NOMPROPRE(TRANSLAte(Tableau2[[#Cette ligne];[FR]]; "fr"; "en"));"en attente");SI(Tableau2[[#Cette ligne];[FR]]<>"";NOMPROPRE(TRANSLAte(Tableau2[[#Cette ligne];[FR]]; "fr"; "en"));"en attente"))

et c'est la mémé pour la colonne ITA et ALL

autrement dit quand j'ajoute une nouvelle ligne j'ai les colonnes EN ITA ALL qui reçoivent "en attente" et des que j'inscris en FR j'ai les traductions

la fonction qui est utilisée dans la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
Option Explicit
 
Sub test()
    MsgBox Translate(Cells(4, 3), "fr", "it")
    'formule:   "=Translate(cellule; "fr"; "en")
End Sub
'
Public Function Translate(rng As Range, Optional From As String = "en", Optional ToLang As String = "fr") As String
    Dim Req As Object, URL As String, code As String, elem As Object, x As Long
    Set Req = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "https://translate.google.pl/m?hl=fr&sl=" & From & "&tl=" & ToLang & "&ie=UTF-8&prev=_m&q=" & rng.Text
    'Debug.Print URL
    Req.Open "GET", URL, False
    Req.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    Req.send ("")
    code = Req.responsetext
    'Debug.Print code
       With CreateObject("htmlfile")
        .body.innerhtml = code
         For Each elem In .all
            If Not IsNull(elem.getattribute("dir")) And elem.tagname = "DIV" Then x = x + 1
            'Debug.Print elem.innertext
            If x = 3 Then Translate = elem.innertext: Exit For
        Next
    End With
End Function

EDIT:
j'ai testé ca marche pas j'ai encore du rater quelque chose
surtout que je veux garder la condition sur si FR<>""

=SIERREUR(TSI(Tableau2[[#Cette ligne];[FR]]<>"";NOMPROPRE(TRANSLAte(Tableau2[[#Cette ligne];[FR]]; "fr"; "en"));"en attente");SI(Tableau2[[#Cette ligne];[FR]]<>"";NOMPROPRE(TRANSLAte(Tableau2[[#Cette ligne];[FR]]; "fr"; "en"));"en attente"))

[Pierre Fauconnier]

Il y a pour moi deux problèmes dans ce que tu as mis en place.

Sur le plan fonctionnel, aller rechercher les traductions sur Google à chaque fois que tu en as besoin est tout sauf productif (sauf à s'amuser à le faire, mais alors autant le faire avec des exercices qui ont du sens). On traduit une fois les libellés et on les stocke. Si demain tu veux utiliser ton classeur et que pour une raison ou l'autre tu n'es pas connecté, ça va pas le faire.


Pour la structure de tes tableaux, le problème est qu'elle va devoir être modifiée ( a minima, ajout d'une colonne et, sauf construction alambiquée, modification de la formule) si tu ajoutes une nouvelle langue.

Je t'avais donné dans une autre discussion une structure à deux tableaux pour réaliser cela facilement. Ca permet de ne pas mélanger la responsabilité de tes tableaux (un donne un texte en fonction d'une langue, l'autre décrit un contrôle) et ça permet surtout de n'avoir à toucher à rien lors de l'ajout d'une nouvelle langue.

Voici deux tableaux, un qui récupère une version linguistique de ce que tu veux (contrôle, message, ...) et l'autre qui définit les contrôles de ta commandbar. INDEX-EQUIV en matricielle permet de récupérer facilement le texte d'un contrôle en fonction de la langue, que ce soit en Excel ou en VBA.




Si tu veux ajouter l'italien, tu saisis simplement les données dans le tableau des textes et le tour est joué, sans que rien d'autre doive être modifié dans ta structure ou dans ton code.






Au passage, la version 2007 est puante pour ce qui concerne les références structurées, et je doute que tes références structurées passent en 2010 et dans les suivantes.

[patricktoulon]

re
oui c'est vrai apres ca devient lourd

si tu me parle des formule en disant "les références structurées" sache que ca passe sur 2013 32 bits
mais tu a raison je vais faire sans formule pour le tableau créateur j'ai même pensé a utiliser l'évènement change pour toutes des colonnes langues

et je vais ajouter un tableau que je mettrais a jour par un bouton ou quelque chose comme ca

juste une question en ce qui concerne l'organisation de mes données actuelles

pour une plage classique on peut dire "range ("A:F") peut on faire la même chose avec un TS du genre[[FR]:[AL]] ????

edit ok c'est bon je l'ai fait avec l'enregistreur de macro et ca ma donner la syntaxe

[patricktoulon]

re
@pierre
finalement les formules c'etaient pas top

j'ai donc changé mon fusil d'épaule
j'ai utiliser l'évènement change mais pour toute la partie langue
autrement que ce soit un Français ,un anglais, italien allemand qui tape dans sa colonne la traduction se fera sur les autre

je pourrait ajouter autant de colonnes langue que je veux a partir du moment ou elle se trouve avant "DE" et je n'aurais pas besoins de changer le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Long, ligne As Variant, l1 As String, i As Long
    col = Target.Column
    ligne = WorksheetFunction.Index(Range("Tableau2[[#Headers],[FR]:[DE]]").Value, 1, 0)    'on recupere les valeur du header de "FR" a "DE"
    Set Cel = Cells(Target.Row, "C").Resize(1, UBound(ligne))    'on determine la cells  1 pour la premiere langue on la dimention au nombre de colonne
    If Target.Count = 1 And Target.Row > 3 Then    'condition apres header et count 1
        If Target.Value <> "" Then    'si valeur différente de vide
            l1 = Cells(3, Target.Column).Text    'on recupere la langue de depart  de la colonne du target  dans le header
            'boucle sur tout les item de ligne et construction de l'url et apel de translate et insertion du resultat
            For i = LBound(ligne) To UBound(ligne)
                ligne(i) = Translate(urlI:="https://translate.google.pl/m?hl=" & l1 & "&sl=" & l1 & "&tl=" & ligne(i) & "&ie=UTF-8&prev=_m&q=" & Target.Text)
            Next
            Cel.Value = ligne    'insertion de l'array ligne  a partir de "c" sur la ligne du target
            Debug.Print Join(ligne, vbCrLf)    ' juste une verif
        Else
            Cel.Value = ""
        End If
    End If
End Sub

[Pierre Fauconnier]

[...]
finalement les formules c'etaient pas top[...]

Si tu le dis (On ne sait pas en quoi ce n'était pas top, mais bon...)

Ou "l'art" de réinventer la roue, mais c'est ton choix

Cela t'oblige à insérer tes nouvelles langues entre FR et DE et t'amène à des modifs structurelles (dans Excel et à mon avis aussi dans ton code à brève échéance) mais c'est toi qui fait la manip.

Perso (critique technique et pas du tout personnelle), cette technique n'est pas professionnelle. Je ne comprends toujours pas l'intérêt d'aller lire sur Google/Translate à chaque création de la barre, car je doute fort que Ouvrir se dise un jour autrement que Open en anglais ou Aprire en italien, réduisant à zéro l'intérêt d'aller le rechercher à chaque fois). La construction de ton tableau amène une obligation de modification de ta structure (ajout d'une colonne) et amène également à devoir faire attention à l'insérer entre la première et la dernière colonne. Ton code impose que la première colonne linguistique soit le français et la dernière l'allemand. De plus, ton code utilise un évènement Change inutile. C'est juste du code pour rien du tout. A quoi ça sert? A rien...

A part t'amuser (ce qui est très bien en soi), à quoi sert ce fatras dans un classeur professionnel conçu selon les règles? Tu n'as pas encore répondu, mais perso, l'intérêt d'une telle construction est nul. J'ai illustré une solution rapide à mettre en place, pérenne quant à la structure, mais aussi générique car sa structure peut être portée sur n'importe quel xlam (ou autre, mais c'est souvent dans un xlam que l'on créera cela) qui a besoin de la construction d'une commandbar (linguistique ou non).

[patricktoulon]

@pierre
les texte de caption ne s'arrêteront pas a "ouvrir" ou autre mot relativement connus dans beaucoup de langues

ensuite non justement mon code évènementiel sheets_change ne changera pas d'un yota même si j'ajoute des colonne langues entre FR et DE

ceci remplace les formules

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Long, ligne As Variant, l1 As String, i As Long
    col = Target.Column
    ligne = WorksheetFunction.Index(Range("Tableau2[[#Headers],[FR]:[DE]]").Value, 1, 0)    'on récupère les valeur du header de "FR" a "DE"
    Set Cel = Cells(Target.Row, "C").Resize(1, UBound(ligne))    'on détermine la cells  1 pour la première langue on la dimension au nombre de colonne
    If Target.Count = 1 And Target.Row > 3 Then    'condition après header et count 1
        If Target.Value <> "" Then    'si valeur différente de vide
            l1 = Cells(3, Target.Column).Text    'on récupère la langue de départ  de la colonne du target  dans le header
            'boucle sur tout les item de ligne et construction de l'url et apel de translate et insertion du résultat
            For i = LBound(ligne) To UBound(ligne)
                ligne(i) = Translate(urlI:="https://translate.google.pl/m?hl=" & l1 & "&sl=" & l1 & "&tl=" & ligne(i) & "&ie=UTF-8&prev=_m&q=" & Target.Text)
            Next
            Cel.Value = ligne    'insertion de l'array ligne  a partir de "c" sur la ligne du target
            'Debug.Print Join(ligne, vbCrLf)    ' juste une verif
        Else
            Cel.Value = ""
        End If
    End If
End Sub

peut être devrais-je lui mettre une condition intersect pour que la traduc soit déclenchée uniquement dans ces colonnes
en tout cas le principe est facile a saisir

[Pierre Fauconnier]

Comme je l'ai dit, ce que j'ai illustré ici, c'est le mécanisme que l'on met en place, et je n'ai pas mis ici le module que j'utilise pour mes développements profesionnels (faut pas pousser bobonne , peut-être le ferai-je dans un tuto sur les applis Excel multilingues). Normalement, le cas que tu cites ne devrait pas exister, et cela procède des tests que l'on effectue avant de livrer l'appli avec la nouvelle langue. Dans Excel, il est facile de vérifier que le texte existe pour chaque langue avant mise en prod.

Pour le reste, à chacun d'adapter ce qu'il souhaite pour ses besoins propres. Je me répète, mais ce qui m'importe ici, c'est qu'une architecture réfléchie soit mise en place selon les standards de la programmation, de manière à ce l'appli soit stable, facile à maintenir, "versionnalisable" dans une nouvelle langue à moindre coût, avec une architecture qui n'est pas déroutante pour un programmeur qui devrait "mettre son nez dedans".

[patricktoulon]

bonjours a tous

je voudrais bien connaitre la syntaxe pour récupérer la plage entre 2 colonnes dans un tableau structuré
mais attention je ne veux que le header

exemple ici je récupère la ligne 1 du DataBodyRange concernant ces colonnes sauf que je veux le header

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test()
 ligne = WorksheetFunction.Index(Range("Tableau2[[FR]:[DE]]").Value, 1, 0)    'exemple de récupération de la ligne 1 de la plage en paramètre
    Debug.Print Join(ligne, ";")
End Sub

edit

bon j'ai trouvé une solution simple je ne sais pas si c'est propre mais bon
en fait j'utilise l'offset tout simplement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test()
 ligne = WorksheetFunction.Index(Range("Tableau2[[FR]:[DE]]").Offset(-1).Value, 1, 0)    'exemple de récupération de la ligne 1 de la plage en paramètre
 Debug.Print Join(ligne, ";")
End Sub

[Philippe Tulliez]

Bonjour Patrick,
Solution rapide (je n'ai pas creusé plus loin)
La ligne ci-dessous te renvoie l'adresse de la plage

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Debug.Print Evaluate("=Tableau1[[#Headers],[FR]:[EN]]").Address

donc le code ci-dessous permet de définir un Range partiel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
  Dim rng As Range, addr As String
  addr = Evaluate("=Tableau1[[#Headers],[FR]:[EN]]").Address(external:=True)
  Set rng = Range(addr)
  rng.Select

[patricktoulon]

merci Philippe
comme d'habitude j'avais oublié la virgule
qué galère ces TS

PS:

donc le code ci-dessous permet de définir un Range partiel

la première sans ".Address"aussi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test154() 'Archive
'récupérer une partie d'un range du header(tableau structurés)
Debug.Print TypeName(Evaluate("=Tableau2[[#Headers],[FR]:[EN]]")) '.Address
End Sub

je vais essayer de trouver la formule pour les non contigües pour mes archives merci encore

[Philippe Tulliez]

Bonjour Patrick,
Ce n'est pas le TS qui est en cause mais tout simplement la syntaxe qui sépare les arguments entre Excel et le VBA. Ceux qui ont la même Option de région dans Windows que les américains n'ont pas ce soucis parce-que dans ce cas la séparation des arguments en Excel est la virgule

[patricktoulon]

re
1°question1
y a t-il plus simple en terme de syntaxe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test155() 'Archive
'récupérer une partie d'un range du tableau structurés  non contigües sans le header(DataBodyRange)
Debug.Print Range("Tableau2[[FR]], Tableau2[[IT]],Tableau2[[DE]]").Address
End Sub

'
2°question2
ne donne pas le resultat que je souhaite
je souhaiterais récupérer les colonnes en entier (headers inclus)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test156() 'Archive
'récupérer une partie d'un range (tableau structurés) non contigües avec le header!!!
Debug.Print Range("Tableau2[[#HEADERS],[FR]], Tableau2[[#HEADERS],[IT]],Tableau2[[#HEADERS],[DE]]").Address
End Sub

merci pour les retours je galère terrible avec le TS