lire et ecrire dans une cellule nommée avec VBA/ExcelRésolu

Question

Bonjour

Un problème de débutant mais je n'ai rien trouvé de simple (ni sur ce site ni avec google)

je souhaite dans une macro VBA faire un cycle lecture du contenu d'une cellule nommée, modifier ce contenu, le relire et l'afficher pour contrôle.
Bien sûr il ne s'agit que d'une trame mais autant commencer par simple

Mon code

function test()
  A = Range("Essai").Value
  Sheets("Feuill1").[Essai] = 10
  B =  Range("Essai") ' même sans value cela fonctionne si l'instruction précédente est mise en commentaire
  Test = A & " , " & B
End Function

Dans une autre cellule j'ai rentré l'appel à la fonction : test()
 
j'ai bien une seule cellule qui s'appelle 'Essai' avec un contenu (la valeur 5) mais en appelant la fonction j'obtiens :
         #VALEUR! dans la cellule qui contient l'appel à la fonction et la valeur reste à 5 dans la zone 'Essai'
En mettant en commentaires la ligne :   Sheets("Feuill1").[Essai] = 10  , je n(ai pas d'erreur, je pense donc que c'est bien elle qui est en cause.

J'ai, bien entendu essayé diverses variantes pendant un après-midi entier !
Hélàs je sèche maintenant.

Si quelqu'un pouvait m'aider ...

Merci d'avoir pris le temps de me lire

cs_Jack · Answer

Salut

Si tu lis le contenu de ta cellule nommée avec 
A = Range("Essai").Value 
pourquoi ne pas réutiliser la même syntaxe pour l'écrire ?
Range("Essai").Value = 10

Il me semble que la syntaxe utilisant les crochets a une autre fonction, Evaluate de mémoire, donc en lecture seule, je pense.

Si tu as une erreur dans le code, laquelle ?
Surtout, vérifie bien qu'il n'y ait pas de "On Error Resume Next" qui traine quelque part et qui pourrait masquer l'erreur, donc le problème, donc la solution.

Vala
Jack, MVP VB
NB : Je ne répondrai pas aux messages privés

Le savoir est la seule matière qui s'accroit quand on la partage (Socrate)

jjnouiphp · Answer

Salut Jack et merci

je viens de tester avec 
Range("Essai").value = 10
et avec
Range("Essai") = 10 
et dans les 2 cas j'obtiens toujours #VALEUR! dans la cellule qui contient l'appel à la fonction et la valeur reste à 5 dans la zone 'Essai'
Il n'y a pas d'On Error dans mes fonctions
je ne comprends pas ta phrase : 'Si tu as une erreur dans le code, laquelle ? ' , si je connaissait l'erreur
je pourrais tenter de la corriger mais à part #VALEUR! .....

Jean-Jacques

cs_Jack · Answer

L'attribut .Value est l'attribut par défaut quand on l'omet. Tu peux donc t'en passer, mais mieux vaut écrire les syntaxes complètes (recommandé).

Bah chez cela fonctionne correctement.
Est-ce que la valeur que tu tentes d'écrire dans ta cellule nommée est bien aussi simple que "10" ?
Est-ce que ta cellule nommée a un formatage spécial, genre date ou unités ?
Essaye alors d'écrire dans .Value2 qui représente la même chose que .Value, mais sans formatage; par exemple, si ta cellule est formatée en date/heure, .Value renverra la date et l'heure formatée, alors que .Value2 renverra un Double (partie entière Nb jours depuis 1/1/1900, partie décimale Nb secondes depuis minuit)

jjnouiphp · Answer

Bonjour

Après lecture de ta réponse j'ai repris depuis le début :

Démarrage Excel 2007 (à jour)

click sur onglet développeur
click sur 1er icône (Visual Basic)
dans la fenêtre Projet
 click droit sur VBAProject(Classeur1)
click sur insertion
click sur module

dans la fenêtre ouverte (titre = Classeur1 - Module1(Code) je tape :
Function test()
  Range("essai").Value = "reussi"
End Function

Sur la feuille en L1C1 je tape:
=test()

je me positionne sur L2C1 puis rentre dans la zone nom (à gauche de la zone de saisie) la valeur essai

en sélectionnant L1C1 je vois bien L1C1 dans la zone nom et =test() dans la zone de saisie

en sélectionnant L2C1 je vois bien essai dans la zone nom et rien dans la zone de saisie

je sélectionne à nouveau la cellule L1C1
en fin de la zone de saisie je tape sur Entrée

J'obtiens #VALEUR! dans la cellule L1C1

Je confirme que j'ai fait en parallèle les manips que je te décris, il n'y a rien eu d'autre.

La méthode pour créer la fonction n'est peut-être pas la bonne, dans ce cas peux-tu me guider?

Merci
Jean-Jacques

cs_Jack · Answer

Ah mais je n'avais pas compris que tu voulais lancer ta Sub depuis une cellule.
Quand, dans une formule, tu mets "=" quelque chose, ce quelque chose doit fournir la donnée.
--> Déinir le type de données renvoyée : Chaine ?
Function test() As String
Ensuite, c'est ta fonction qui doit renvoyer le résultat.
Bien sûr tu peux mettre des commandes dans ta fonction, mais le plus important est de fournir le résultat : Ce n'est pas à ta fonction de définir la destination :
test = "reussi"

Ce qui donne
Function test() As String
test = "reussi"
End Function

jjnouiphp · Answer

Ce qui veut dire que l'instruction d'affectation à une autre cellule :
Range("essai").Value = "reussi"
passe à travers la 'compilation' mais n'est pas valide ?

cs_Jack · Answer

Je ne comprends pas bien pourquoi tu veux affecter une constante, "réussi", à ta cellule.
D'une part, (je pense qu') on ne peut pas appeler une Sub ou Function personnelle depuis une formule de cellule, et d'autre part, le recalcul permanent des cellules appellera sans cesse ta function alors qu'elle est sensée renvoyer toujours la même valeur.

Si tu dois faire une action lors de la modification d'une cellule, mets ça dans Worksheet_Change de ta feuille. Là, tu pourras tester si le _Change concerne ta cellule avec l'utilisation de Target.Address ou Target.Row et .Column, par exemple.

Explique où tu veux en venir, car tu es parti dans une mauvaise direction.

jjnouiphp · Answer

Bonsoir Jack

En effet je crois que je fais fausse route, je ne souhaitais pas exécuter ma procédure à chaque recalcul de la feuille mais seulement sur commande.
Bien sûr il ne s'agit pas dans la réalité d'affecter une constante, c'était pour simplifier le problème que je faisais le test de cette manière.
Disons qu'après avoir entré des données dans plusieurs cellules je voulais lancer une série de calculs et afficher les résultats dans plusieurs cellules.
Comme je débute en VBA je n'ai pas même pas pensé a utiliser un bouton pour déclencher la procédure.
Pour rendre l'exemple plus près de la réalité tout en restant simple :
J'entre des valeurs dans 2 cellules, lorsque c'est fait je veux afficher dans 3 cellules (nommées?) la somme, la différence et le produit de ces 2 valeurs; tout ceci en utilisant VBA.

Merci de ta patience.

Jean-Jaacques

cs_Jack · Answer

Ok, merci d'avoir levé le voile.

Les cellules nommées sont en effet très pratique.
Elles ne représentent que des cellules fixes.
Suppose que tu aies une feuille avec, horizontalement, en A1, la valeur A, B1, la valeur B, en C1 on mettra la somme, en D1, la soustraction et en E1, la multiplication.
Nommons chacune de ces cellules, respectivement, "ValeurA", "ValeurB", "maSomme", "maSoustraction" et "maMultiplication".
Dans ta fonction, il te suffira de désigner Range("ValeurA") au lieu du traditionnel Range("A1").
Ainsi, le calcul de la multiplication pourra s'écrire facilement :
Range("maMultiplication").Value = _
    Range("ValeurB").Value * Range("ValeurB").ValueCôté programmation, pour quelqu'un qui reprendrait le boulot après toi, c'est aussi plus facile à comprendre.

Ajout de lignes :
Supposons maintenant dupliquer cette ligne sur un millier d'autres lignes.
Tu t'arrangeras pour que les valeurs de A et de B diffèrent, comme si tu voulais faire une table de multiplication (exemple).
Il n'y aura que la cellule en tête de chaque colonne A à E qui auront des noms.
Pour 'indexer' par ligne, il te suffira de reprendre tes équations, et d'y ajouter une propriété très pratique : Offset (exemple de multiplication, toujours) :
Range("maMultiplication").Offset(No, 0).Value = _
    Range("ValeurB").Offset(No, 0).Value * Range("ValeurB").Offset(No, 0).Valueoù No représente le numéro de la ligne.

Cerise sur le gâteau :
Si tu décides de déplacer une colonne, exemple C1, l'addition', tu veux la placer après la colonne des multiplications.
Si tu utilises une notation classique, Range("C1"), ton programme ne se mettra pas à jour tout seul : ce sera à toi d'aller rechercher partout dans ton code si tu fais référence à cette colonne, et faire les décalages qui s'imposent (respiration).
Alors que, en nommant les cellules, le nom va suivre la cellule en tête de colonne : le programme faisant référence à ce nom, il n'y aura rien à modifier dans ton code, rien.

Alors, bénéfice : ça va pas servir tous les jours.
C'est pas faux.
Mais le jour où tu travailleras à plusieurs sur un projet ou lorsque tu auras besoin de déplacer une cellule parce que tu as oublié quelque chose, là tu apprécieras cette technique, je t'assure.

Fin du roman. Bonne nuit.

jjnouiphp · Answer

Bonjour Jack,
Je suis tout à fait d'accord avec ton 'roman' (plutôt un 'exposé' sur les bonnes habitudes à acquérir). C'est bien pour les raisons que tu indiques que je souhaite utiliser des cellules nommées.
Car sans parler d'équipe, reprendre bien plus tard un projet en quelque langage qu'il soit devient difficile s'il n'est pas écrit de façon lisible et agrémenté de commentaires.
Pour info je reprends quelquefois des programmes qui ont plus de 10 ans! 

Cependant ta réponse, pour didactique qu'elle soit, ne me permet pas de répondre à la question : comment affecter une valeur à une autre cellule.
Pourrais-tu me confirmer que la méthode : 
Range("maMultiplication").Value = _
    Range("ValeurA").Value * Range("ValeurB").Value
fonctionne chez-toi et si oui comment as-tu fait?

Je suis désolé d'insister mais je n'aime pas rester sur une question, alors si tu peux encore me consacrer du temps....

Merci beaucoup

Jean-Jacques

cs_Jack · Answer

Oui, je viens de revérifier :
Feuille toute neuve
Cellule A1 nommée ValeurA (en effet, il y avait un coquille dans ta syntaxe)
Cellule A2 nommée ValeurB
Cellule A3 nommée maMultiplication
Dans le code de la feuille, j'ai créé un sub bidon
Sub xxx()
    Range("maMultiplication").Value = _
        Range("ValeurA").Value * Range("ValeurB").Value
End Subet je l'ai lancé avec F5 (mon curseur était dedans)
La cellule A3 a bien fait la multiplication de A1 par A2.

Quel est ton souci ?
Si ta Sub n'est pas dans la feuille, pense à désigner le nom de la feuille :
    With Sheets("ma feuille")
        .Range("maMultiplication").Value = _
            .Range("ValeurA").Value * .Range("ValeurB").Value
    End With(tu remarqueras le point supplémentaire devant chaque Range)

cs_Jack · Answer

(*) dans MA syntaxe, pas la tienne

jjnouiphp · Answer

Bonsoir Jack

D'abord un grand merci de m'avoir guidé jusque là.
En fait mon erreur était de mettre l'appel à la formule dans une case.
Ensuite j'ai installée ma routine dans un module (et non dans la feuille proprement dite.
dans la fenêtre Projet
click droit sur VBAProject(Classeur1)
click sur insertion
click sur module

dans la fenêtre ouverte (titre = Classeur1 - Module1(Code) je tape la routine 

En suivant pas à pas ta dernière réponse j'ai en effet obtenu le même résultat que toi.


Il me reste une dernière question :
En cliquant sur F5 l'exécution n'est pas immédiate, une fenêtre s'ouvre avec en titre Macros et une ligne surlignée avec Feuil1.xxx et je dois cliquer sur Exécuter
est-ce normal?

Bonne nuit

Jean-Jacques

cs_Jack · Answer

Pour qu'une Sub ou Function dans un module soit accessible depuis d'autres lieux (feuilles ou autre modules), il suffit qu'elle soit déclarée en Public

Oui c'est une réaction normale.
Il faut que ton curseur soit à l'intérieur du code pour qu'il soit exécuté sans question de ce genre (avec quelques conditions supplémentaires)

Dernière chose, le débugage :
F9 sur une ligne de code
Le programme s'y arrêtera au prochain passage
Survole avec la souris tes variables pour visualiser leur contenu, ou va dans la fenêtre de debogage (Ctrl-G) et tape
? maVariablepour qu'elle s'inscrive dans la fenêtre.
F8 pour avancer d'une ligne de code
F5 pour continuer normalement

Te voilà paré !

jjnouiphp · Answer

Bonjour Jack

je te cite : 'Te voilà paré !'

C'est grâce à toi

GRAND MERCI

Jean-Jacques

Lire et ecrire dans une cellule nommée avec VBA/Excel

15 réponses

Votre réponse

Discussions similaires