Lire et ecrire dans une cellule nommée avec VBA/Excel [Résolu]
jjnouiphp33
Messages postés lundi 19 avril 2004Date d'inscription
18 août 2010
Dernière intervention
-
15 août 2010 à 19:50 - Dernière réponse : jjnouiphp33
Messages postés lundi 19 avril 2004Date d'inscription
18 août 2010
Dernière intervention
- 18 août 2010 à 09:17
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 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)
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! .....
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)
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?
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
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 ?
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.
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.
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 :
Cô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) :
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.
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....
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 Sub
et 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)
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?
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
? maVariable
pour qu'elle s'inscrive dans la fenêtre.
F8 pour avancer d'une ligne de code
F5 pour continuer normalement
a aidé 101 internautes ce mois-ci