Fonction VBA équivalente à la fonction INDIRECT Excel

[Ethiryn - Glarilak]

Bonjour,

Je cherche une fonction VBA, ou une macro qui me permet de récupérer une adresse de cellule qui est entrer en valeur d'une autre cellule.
Dans mon fichier j'ai écrit une macro qui me permet de changer la valeur des toutes les cellules texte pour changer de langue. Mais petit désavantage, dès que j'ajoute un nouveau texte dans une nouvelle cellule, il faut ajouter une ligne dans la macro pour bien faire la traduction lors du changement de langue.

Je me suis donc dit que je pouvais crée une procédure qui traduira les cellules en faisant une boucle sur toutes les textes à échanger. J'ai donc insérer à gauche une colonne dans lequel je veux mettre l'adresse de la cellule à traduire. Comme cela quant la macro passera sur la ligne elle n'aura qu'à lire la ligne, pour avoir la traduction et l'adresse ou mettre la traduction.

Le problème est donc comment faire pour dire à Excel VBA de transformer la valeur de la cellule contenant l'adresse en adresse de réception.

J'ai cherché un peu partout des équivalent des la fonction INDIRECT sur le net, mais je n'ai pas réussi à trouver mon bonheur.
J'ai tout de même réussi à faire la formule suivante Range(Range("A3")) qui m'affiche bien la valeur de la cellule B3 quant je rentre en donner "B3" dans la cellule A3. Mais le soucis c'est que la cellule à modifier est sur une autre feuille et que je ne peut pas entrer l'adresse complète de la cellule à modifier dans la cellule qui sert de référence.
Le code suivant Sheets("Feuil1").Range(Sheets("Feuil2").Range("A3")) pourrait fonctionner si il n'y avais qu'une feuille à traduire, mais il y en à plusieurs, pour le moment déjà 7 ou 8.

Quelqu'un aurait-il une formule qui permet de transformer une adresse complète comme celle présenter en-dessous, en adresse utilisable pour un calcul ?
ThisWorkbook.WorkSheets("Feuil1").Range("A1")

Je joins un fichier d'exemple, en Feuil1 un exemple de texte à traduire, Feuil2 la mise en page de ma feuille de traduction sur mon fichier. Chaque langue est représenter par un colonne à partir de la seconde colonne.
La première colonne contient les adresses, toutes les cellules ne sont pas forcément renseigner, par exemple les cellule A1(Nom de langue) et A2(Image des drapeau de chaque pays) ne le sont pas.

J'espère avoir expliquer mon problème en mettant le plus de détails et sans en avoir mis trop. Je suis disposition si vous voulez un complément d'information sur mon problème.

J'espère avoir une réponse précises et expliqué, me donner un code tout craché ne m'aidera sans doute pas à le comprendre et le réutiliser pour pouvoir progresser en VBA, merci d'avance pour votre aide.

Ethiryn - Glarilak

 

[Staple1600]

Bonsoir le fil, Ethiryn - Glarilak

Ethiryn - Glarilak

Dans mon fichier j'ai écrit une macro qui me permet de changer la valeur des toutes les cellules texte pour changer de langue

L'est où, la petite macro?

 

[Ethiryn - Glarilak]

Merci de intéresser à mon problème Staple1600

Je n'avais pas mis la macro dans le fichier d'exemple.

Mais voici l'équivalent qui fonctionne pour le ficher exemple.

Public Sub Traduction(Colonne_Traduction As Integer)
    With Feuil1
        .Unprotect
        .Range("B4") = Feuil2.Cells(3, Colonne_Traduction)
        .Range("B6") = Feuil2.Cells(4, Colonne_Traduction)
        .Protect
    End With
End Sub

La Variable Colonne_Traduction correspond à la colonne de la langue sélectionné.

J'ai remis le fichier avec la macro intégré et un bouton pour l'activer et demander la colonne de traduction. (La colonne n'est pas sensés être demander).

 

[Staple1600]

Re

Un truc vite fait sur le pouce (la veille d'un 15 août)

Sub LostInTranslation()
Dim langue As Range, col As Long
Set langue = Feuil1.[B2]
x = Array("Français", "English", "Español")
col = Application.Match(langue.Text, x, 0) + 1
With Feuil1
.Unprotect
.[B4] = Feuil2.Cells(3, col)
.[B6] = Feuil2.Cells(4, col)
End With
End Sub

 

[Dranreb]

Bonsoir.
Ne pourriez vous mettre pour chaque texte dans un feuille quelconque une formule d'intersection entre la ligne du texte en Feuil2 et la colonne de la langue ?
Genre en B4 :

=Feuil2!$3:$3 Langue

Avec Langue: un nom dans le classeur se référant momentanément à :

=Feuil2!$B:$B

Comme ça la macro n'aurait plus qu'à changer la référence de ce nom :

Public Sub Traduction(ByVal ColonneTraduction As Integer)
   ThisWorkbook.Names.Add "Langue", Me.Columns(ColonneTraduction)
   End Sub

 

[Dranreb]

Remarquez: si pour automatiser la mise en place de ces formules vous voulez aussi pouvoir demander la traduction d'un texte fixe existant en tapant, dans une cellule de la bonne colonne dans Feuil2, une formule temporaire de renvoi vers cette cellule, ce n'est pas un problème :

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cel As Range
   If Target.HasFormula Then
      Set Cel = Evaluate(Target.Formula)
      Target.Value = Target.Value
      Cel.Worksheet.Unprotect
      Cel.Formula = "=" & Target.EntireRow.Address(True, True, xlA1, True) & " Langue"
      Cel.Worksheet.Protect
      End If
   End Sub
Public Sub Traduction(ByVal ColonneTraduction As Integer)
   ThisWorkbook.Names.Add "Langue", Me.Columns(ColonneTraduction)
   End Sub

 

[Ethiryn - Glarilak]

Re

Un truc vite fait sur le pouce (la veille d'un 15 août)

Sub LostInTranslation()
Dim langue As Range, col As Long
Set langue = Feuil1.[B2]
x = Array("Français", "English", "Español")
col = Application.Match(langue.Text, x, 0) + 1
With Feuil1
.Unprotect
.[B4] = Feuil2.Cells(3, col)
.[B6] = Feuil2.Cells(4, col)
End With
End Sub

Désoler Staple1600, mais la solution que tu me propose reviens à la même chose que ce que j'avais fais, il faut entrer pour chaque cellule à traduire la formule .[XX] = Feuil2.Cells(X, col).

Ce à quoi j'avais penser était une boucle For sur toutes les cellules à traduire avec l'adresse de la cellule à traduire indiquer sur la colonne A de chaque ligne.

Mais je vois que Dranreb, à trouver une solution que j'ai réussi à adapter à mon problème.

 

[Ethiryn - Glarilak]

Bonsoir.
Ne pourriez vous mettre pour chaque texte dans un feuille quelconque une formule d'intersection entre la ligne du texte en Feuil2 et la colonne de la langue ?
Genre en B4 :

=Feuil2!$3:$3 Langue

Avec Langue: un nom dans le classeur se référant momentanément à :

=Feuil2!$B:$B

Comme ça la macro n'aurait plus qu'à changer la référence de ce nom :

Public Sub Traduction(ByVal ColonneTraduction As Integer)
   ThisWorkbook.Names.Add "Langue", Me.Columns(ColonneTraduction)
   End Sub

Merci Dranreb, la solution que tu as donner me convient, je vais donc mettre une formule dans chaque cellule à traduire comme cela je n'aurai plus qu'a changer le nom Langue pour pouvoir changer la langue.

Je vous remercie tout les deux de vous être intéresser au problème.

 

[Ethiryn - Glarilak]

J'ai 2 questions sur le code qu'a envoyer Dranreb.
- Quels est l'utilité du code ByVal dans la déclaration des variables dans la déclaration de procédure ?

Public Sub Traduction(ByVal ColonneTraduction As Integer)

- Quels est l'utilité du code Set dans le second message ?

Set Cel = Evaluate(Target.Formula)

Si quelqu'un à les réponses à ces questions je suis ouvert aux réponse.

 

[Dranreb]

Bonjour
ByVal initialise une variable locale dans la procédure appelée tandis que ByRef (hélas pris par défaut) y aboutit à du code plus complexe puisqu'elle ne dispose que de l'adresse de la variable, celle ci restant à sa place, extérieure à la procédure.
Le code est d'ailleurs aussi plus complexe du coté de la procédure appelante si c'est une expression telle qu'une constante qui est transmise et non pas une variable. En effet, comme il n'a pas le droit d'en transmettre l'adresse (l'exposant ainsi à une possible modification par la procédure appelée) il est obligé dans tous les cas d'en déposer une copie dans une zone temporaire et alors de transmettre l'adresse de cette zone. Avec ByVal pas de problème: la zone temporaire en est une de la pile, volatile de toute façon, ce qui lui confère l'exact statut d'une variable locale. L'appelant n'a pas besoin d'en transmettre l'adresse, puisque celle ci a un offset fixe, connu dès la compilation, directement spécifié dans les instructions machine qui l'utilisent, par rapport au départ de pile courant commun au moment de l'exécution.
Pour une expression objet, ce que j'appelle sa valeur c'est l'adresse de l'exemplaire d'objet qu'elle désigne et non les propriétés de ce dernier: celles ci sont de toute façon accessibles, durablement modifiables, qu'elle soit transmise ByRef ou ByVal.
Et même pour une variable, le ByRef par défaut cache un piège potentiel, et beaucoup n'ayant pas voulu se casser la tête à essayer de comprendre comment ça marche, ni devoir se fatiguer à mettre ByVal quand c'était pertinent, ont fini par se heurter à l'erreur de compilation 'Argument ByRef incompatible'. Car si c'est une adresse qu'il faut transmettre, ce doit être celle d'une variable du type de donnée exact attendu. Une exception est tolérée, bizarrement: une variable As Double est acceptée en tant qu'argument ByRef X As Variant, à condition que la procédure appelée ne tente pas de changer son sous-type de donnée. L'inverse n'est pas vrai.
Avec ByVal, une conversion de type de donnée est toujours possible au passage. Spécifier un Variant/Double en guise d'argument à une procédure attendant un ByRef X As Double ne passe pas, mais attendant un ByVal X As Double passe !
De plus, et ce n'est pas négligeable, ByVal a une valeur de documentation du code: elle indique que, donné juste pour information utile, la procédure n'a pas vocation d'apporter de modification pérenne à l'argument (ou à l'exemplaire désigné par un objet dans la cas où c'en est un: elle ne fait pas de Set dessus, ou alors seulement pour son confort personnel, et ça ne doit pas être retenu à l'extérieur).

Set permet de changer une variable objet en lui attribuant l'adresse d'une expression objet du même type. Il est nécessaire, pour pouvoir utiliser une variable objet, qu'elle ait été initialisée par un Set. Sinon elle est Nothing, et la tentative d'utiliser une de ses méthodes ou propriétés plante à l'exécution. Il faut en somme, avant de pouvoir travailler avec, avoir préalablement dit précisément ce que c'est, sa déclaration n'ayant annoncé que son espèce.

 

[Ethiryn - Glarilak]

Ok merci Dranreb pour ta réponse, maintenant j'en sais un tout petit peu plus sur le vaste monde d'excel...

Ethiryn - Glarilak