Fonction VBA pour renvoyer fonction INDIRECT

Bonjour à tous,

Dans mon fichier de compte (Excel 2016), j'utilise régulièrement la fonction INDIRECT dans mes cellules, généralement pour comparer la valeur en cours d'un compte en banque avec sa valeur le mois précédent. Ca donne qqch comme ceci (ici, je compare si mon compte en banque a grossi ou si j'ai trop dépensé en soustrayant l'un à l'autre) :

Code:

---

=Etat_CompteCourant-INDIRECT("'"&Var_MoisPrecedent&"'!Etat_CompteCourant")

---

où

• Etat_CompteCourant = Nom attribué au niveau de la feuille (pas du classeur) qui pointe vers la cellule comportant la valeur du compte en banque
• Var_MoisPrecedent = Nom de l'onglet contenant les chiffres du mois précédent

Ca fonctionne très bien mais le code est un peu long (d'autant plus que je compare toujours une valeur avec le mois précédent).

Je cherche à créer une fonction moisprecedent() que j'aimerais utiliser ainsi :

Code:

---

=Etat_CompteCourant-moisprecedent(Etat_CompteCourant)

---

Et là, dans VBA, je me fais jeter comme un malpropre... Je veux dire, je n'arrive même pas à renvoyer quoique ce soit qui ressemblerait à un INDIRECT :

Code:

---

1 2 3 4 5 6

Function MoisPrecedent() As String   MoisPrecedent = INDIRECT("Juin'19!E6")   End Function

---

Là, même en simplifiant dans un premier temps l'INDIRECT en retirant les variables (Var_MoisPrecedent, Etat_CompteCourant), ça me renvoie une erreur.

Savez-vous pourquoi et comment m'en sortir ?

Bonjour,
Pour répondre exactement à ta demande.

Toutes les fonctions natives d'excel, n'ayant pas leur équivalence en VBA, comme par exemple RECHERCHEV, INDEX, EQUIV, etc) sont utilisables dans le VBA à l'aide de la propriété WorksheetFunction de l'objet Application en utilisant la même syntaxe

Cependant, je ne vois pas l'intérêt d'utiliser cette fonction en VBA où il y a d'autres techniques bien plus rapide d'accéder à des feuilles, classeurs, etc. dont les noms varient.

Bonjour et merci pour votre réponse.

Effectivement, le problème n'aurait pas du être posé ainsi. La simple utilisation d'un Sheets(Var_MoisPrecedent).Range(Etat_CompteCourant) répond bien à mon problème. Ca donne donc qqch comme ceci :

Code:

---

1 2 3 4 5 6 7 8

Function MoisPrecedent(Information) As Integer   OngletPrecedent = Range("Var_MoisPrecedent_mmmm").Value & "'" & Range("Var_MoisPrecedent_aa").Value   MoisPrecedent = Sheets(OngletPrecedent).Range(Information)   End Function

---

OngletPrecedent va reconstruire le nom de l'onglet précédent, qui correspond au nom du mois précédent, en récupérant dans l'onglet en cours deux variables (Var_MoisPrecedent_mmmm et Var_MoisPrecedent_aa). MoisPrecedent, lui, va renvoyer directement la valeur de l'autre onglet.

Néanmoins, il se passe quelque chose d'étrange : sur les 20 utilisations de cette fonction, seules une grosse moitié va fonctionner, les autres sont dans les choux avec #Valeurs!. Comme vous pouvez le voir, la fonction renvoie un Integer. Quand je modifie en String, la grosse moitié passe en #Valeurs! et la petite moitié se met à fonctionner.

Concernant le type des champs récupéré, ils sont tous en Personnalisé (_-* # ##0 €_-;-* # ##0 €_-;_-* "-"?? €_-;_-@_-). J'ai alors tenté sans succès de :

• Modifier le format de la valeur à récupérer et celle où va être utilisé la formule en Monétaire : KO
• Modifier le format de la valeur à récupérer et celle où va être utilisé la formule en Compatibilité : KO
• Modifier le format de la valeur à récupérer et celle où va être utilisé la formule en Texte : KO
• Copier/Coller sur elle-même en Mode 123 les valeurs à récupérer : KO
• Ajout de +0 dans la formule de chaque cellule Excel utilisant MoisPrecedent : OK! (=MoisPrecedent("Support_PEL_Nico")+0)

J'avoue avoir rarement été confronté à un problème de format sous Excel.. donc je suis un peu perdu.

Avouez-vous une idée ?

Bonjour,
Il est difficile d'apporter une réponse pertinente avec les éléments dont on dispose.

Citation:

---

OngletPrecedent va reconstruire le nom de l'onglet précédent, qui correspond au nom du mois précédent, en récupérant dans l'onglet en cours deux variables (Var_MoisPrecedent_mmmm et Var_MoisPrecedent_aa). MoisPrecedent, lui, va renvoyer directement la valeur de l'autre onglet.

---

Quel est le type de donnée des cellules Var_MoisPrecedent_mmmm et Var_MoisPrecedent_aa ? un exemple de valeur serait un plus
Numérique, alphanumérique ?
Comment sont construits le nom des onglets concernés ?
Quel type de donnée pour l'argument Information de la fonction MoisPrecedent. je devine qu'il s'agit sans doute d'une cellule nommée mais pourquoi typer la fonction et pas son argument ?

Le format d'une cellule ne doit pas poser de problème, on travaille avec sa valeur.

"Le format d'une cellule ne doit pas poser de problème, on travaille avec sa valeur." : entendu, mauvaise compréhension de ma part.

Pour répondre à vos questions :
Quel est le type de donnée des cellules Var_MoisPrecedent_mmmm et Var_MoisPrecedent_aa ? un exemple de valeur serait un plus

J'imagine que Var_MoisPrecedent_mmmm doit être String et Var_MoisPrecedent_aa doit être Integer mais je n'ai pas de certitude : puis-je le vérifier avec plus de certitude ?

Pièce jointe 495351

Numérique, alphanumérique ?

Je ne connais pas l'opération pour vérifier ceci : pourriez-vous me la communiquer ?

Comment sont construits le nom des onglets concernés ?

Chaque nom d'onglet est construit avec le nom du mois et son année (sur 2 digits) : Juin'19, Juillet'19, Août'19.
Dans chaque onglet, j'ai un tableau de variables qui me donne le nom du mois précédent, et l'année du mois précédent (par exemple, positionné sur l'onglet Août'19, Var_MoisPrecedent_mmmm aura comme valeur "Juillet" et Var_MoisPrecedent_aa aura comme valeur 19; le tilde étant quant à lui ajouré dans la construction du nom de l'onglet.

Pièce jointe 495346
Pièce jointe 495348

Quel type de donnée pour l'argument Information de la fonction MoisPrecedent. je devine qu'il s'agit sans doute d'une cellule nommée mais pourquoi typer la fonction et pas son argument ?

Voici un exemple d'utilisation de la fonction :

Code:

---

1 2	=MoisPrecedent("Support_LDD_Nico")

---

Et Support_LDD_Nico est un nom (niv. feuille, pas classeur) qui renvoie sur une cellule avec un chiffre dedans

Pièce jointe 495350

Bonjour,
Alors pour résumer le nom des onglets à traiter est le nom du mois complet et des deux derniers chiffres de l'année
Donc la fonction qui renvoie le nom de l'onglet doit recevoir une ou deux valeurs numériques à traiter et renvoyer une chaîne numérique représentant le nom du mois et les deux derniers chiffres de l'année.

Voici une fonction ayant trois arguments dont un est facultatif

• YearNumber est le nombre représentant une année par exemple 2019
• MonthNumber est le nombre représentant un mois par exemple 2 (pour février)
• Ecart permet de renvoyer le xème mois qui précède MonthNumber si l'écart est négatif ou le xème mois qui suit si l'argument est positif. Si la valeur de Ecart est nulle ou omise la valeur renvoyée sera pour cet exemple Février'19

Code de la procédure

Code:

---

1 2 3

Function SheetName(YearNumber As Integer, MonthNumber As Byte, Optional Ecart As Integer) As String SheetName = Format(DateSerial(YearNumber, MonthNumber + Ecart, 1), "mmmm'yy") End Function

---

Exemple d'une procédure invoquant la fonction SheetName et qui renverra le mois qui précède février 2019

Code:

---

1 2 3

Sub TestSheetName() MsgBox SheetName(2019, 2, -1) End Sub

---

Procédure dont il suffit de remplacer les constantes 2019 et 2 par la valeur contenue dans des cellules

On pourrait pousser plus loin en prévoyant un argument supplémentaire à la fonction SheetName

Code:

---

1 2 3

Function SheetName(YearNumber As Integer, MonthNumber As Byte, Optional Ecart As Integer, Optional sFormat As String = "mmmm'yy") As String SheetName = Format(DateSerial(YearNumber, MonthNumber + Ecart, 1), sFormat) End Function

---

Exemple de la procédure qui l'invoque et qui renverra Janv 2019

Code:

---

1 2 3

Sub TestSheetName() MsgBox SheetName(2019, 2, -1, "mmm yyyy") End Sub

---

Il suffit maintenant de remplacer l'affichage en récupérant le nom renvoyé par la fonction dans une variable.

Merci pour toutes ces réponses mais concernant la dernière, vous me parlez de la gestion du nom de l'onglet mais ce bloc ne pose pas problème : il fonctionne bien.

Ce qui pose problème, il me semble, ici, c'est bien le type de valeur qui est renvoyé par la lecture de la cellule de l'onglet du mois précédent.

J'en veux pour preuve que pour une même fonction, MoisPrecedent(), elle fonctionne bien pour la majorité des cas; et qu'en plus quand dans cette même fonction je remplace tout ce qui doit l'être par Août'19, ça ne marche pas mieux pas pire.

Le problème se situe donc plutôt sur la nature de la valeur qui est renvoyée (string ou integer).

Et ce que je ne comprends pas, c'est que dans l'onglet Aout'19, j'ai bien des chiffres dans chacune des cases qui sont lues; et que malgré tout dans l'onglet Septembre'19 et plus précisement dans les cellules qui utlise la fonction MoisPrecedent(), une fois sur deux ça me renvoie bien un chiffre "lisible", une fois sur deux ça me renvoie #Valeurs!. Et que quand je change la nature de la fonction MoisPrecedent() de Integer à String, ce qui marchait ne marche plus et ce qui ne marchait pas remarche.

C'est comme ci mes cellule dans Aout'19 était parfois en String, parfois en Integer, et qu'à leur lecture, ça pose problème. Mais étonnement, quand je fais =Aout'19!Support_LDD_Nico depuis Septembre'19, j'ai aucun soucis pour lire la valeur de la cellule, l'affiche et l'ajouter ou la soustraire à une autre valeur..

Vous voyez ce que je veux dire ?

Bonjour,
Sans doute ai-je mal compris la question.
Revenons donc à mes questions précédentes.
Pour savoir si une cellule contient une valeur numérique ou alphanumérique (Texte), il existe les fonctions de la catégorie Information : ESTTEXTE et ESTNUM

S'il est question de reconstruire une date au départ de valeur de type String, la seule solution est de boucler sur la liste des mois. Dans certain cas, excel peut "deviner" une date construite en texte mais personnellement je ne m'aventure pas dans ce type de construction.

Bonjour,
J'espère maintenant avoir compris la question
C'est un peu à contre-cœur que j'ai construit cette fonction. Non pas de t'avoir aidé, tout au moins si elle répond à ton attente, mais parce-que pour moi c'est du bricolage et pas pérenne.
Il faut être certain que les mois soient construits correctement et sans abréviation (il est possible d'utiliser le deuxième argument de MonthName mais je ne l'ai pas fait) et en français.
Si un utilisateur a une version excel en anglais ou en allemand c'est foutu ou alors il faut créer des tables multilingues hors fonction standard). (J'ai un vague souvenir d'avoir utilisé une fonction renvoyant le nom des mois en anglais mais c'est peut-être un fantasme)

Scénario
Dans toutes feuilles ayant comme nom une chaîne de caractères ayant le nom d'un mois complet suivi d'un séparateur (l'apostrophe) et suivi des deux derniers chiffres d'une année (que l'on suppose dans le 21ème siècle)
Chaque cellule B2 des feuilles précitées doivent contenir une fonction personnalisée qui renvoie un texte construit de la même manière mais renvoyant le mois qui précède.

La fonction personnalisée

Code:

---

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

Function LastMonth(FullFileName As String, Optional Separator = "'") As String ' Renvoie un texte comprenant le mois qui précéde le nom de la feuille de l'argument ' Argument ' FullFileName Contient le chemin complet de la feuille ' Dim M As Byte Dim Name As String Dim Pos As Integer Dim Tbl() As String Dim pMonth As Byte ' Assignation Pos = InStrRev(FullFileName, "]") + 1 Name = LCase(Mid(FullFileName, Pos)) Tbl = Split(Name, Separator) M = 1 ' While M > 0 And M < 13 If MonthName(M) = Tbl(0) Then pMonth = M: M = 0 Else M = M + 1 End If Wend ' Renvoie de la valeur calculée LastMonth = Format(DateSerial(Tbl(1) + 2000, pMonth - 1, 1), "mmmm" & Separator & "yy") End Function

---

Formule à placer dans la cellule B2

Code:

---

=LastMonth(CELLULE("nomfichier";$A$1))

---

Illustration
Pièce jointe 495355

Désolé, c'est sans doute moi qui ne suis pas clair. Reprenons :)

1. Le problème ne se situe pas sur l'accès à un autre onglet. Pour ça j'ai déjà mon propre mécanisme et depuis la feuille ou depuis VBA, j'arrive bien à accéder à d'autres feuilles. Pas de soucis à ce niveau là
2. Le problème se situe sur les données récupérées et là je vais donner (bien) plus d'infos ci-dessous

Etape 1 : Les données à lire

Sur l'onglet Août'19, j'ai 6 cellules (première colonne) qui correspondent à 6 comptes en banques (les chiffres sont fantaisistes). Chaque cellule porte un nom (au niv feuille, pas classeur) du type Support_xxx_Nico. Pour chacune des valeurs de chaque compte en banque, j'ai testé sa nature via ESTNUM et ESTTEXTE. On voit donc ici que toutes les valeurs sont des numériques.

Pièce jointe 495414

Etape 2 : Lire ces données depuis un autre onglet (méthode simple)

Maintenant, dans l'onglet Septembre'19, j'ai le même tableau qui reprend les valeurs du mois précédent (août'19). Initialement, je faisais simplement =Août'19!Support_xxx_Nico qui fonctionnaient très bien pour toutes les cellules. Ca donne ceci :

Pièce jointe 495416

On remarque qu'en faisant ainsi, j'arrive (et c'est normal, voir extrêmement basique) que j'arrive bien à lire les infos de l'onglet précédent, mais qu'en plus le format des valeurs est le même (logique aussi).

Etape 3 : Lire ces données depuis un autre onglet (méthode plus flexible)

Ensuite, j'ai tenté de faire un truc un peu plus flexible (car j'ai un onglet "Template" que je duplique à chaque nouveau mois, lui attribuant un nom du type mmmm'aa avec des petites fonctions qui comprennent toute seule quel mois on est dans cet onglet - a partir du nom de l'onglet - puis pour cet onglet, quel est le mois précédent et suivant etc...). Et donc, j'ai modifié ma méthode de lecture des valeurs du mois précédent pour passer de =Août'19!Support_xxx_Nico à =INDIRECT("'"&Var_MoisPrecedent&"'!Support_LDD_Nico") où Var_MoisPrecedent est le nom de l'onglet du mois précédent. Et quand je fais ça, ça fonctionne super :

Pièce jointe 495443

On remarque par ailleurs que le type des valeurs restent toujours les mêmes : numérique.

Etape 4 : Lire ces données depuis un autre onglet (méthode super flexible)

Maintenant, je cherche à créer une fonction qui va me réduire le code basé sur INDIRECT pour passer de =INDIRECT("'"&Var_MoisPrecedent&"'!Support_LDD_Nico") à =MoisPrecedent("Support_LDD_Nico"). Et c'est là où les choses se corsent et que mon fichier Excel n'est pas (du tout) content :

Code:

---

1 2 3 4 5 6 7 8

Function MoisPrecedent(Information) As Integer   OngletPrecedent = Range("Var_MoisPrecedent_mmmm").Value & "'" & Range("Var_MoisPrecedent_aa").Value   MoisPrecedent = Sheets(OngletPrecedent).Range(Information)   End Function

---

Et là, en changeant tout ça dans l'onglet Septembre'19, c'est le drame :

Pièce jointe 495436

Et pour l'une des cellules qui merdouilles :

Pièce jointe 495439

Et là, on regarde le type de données à changé : ce ne sont plus des NUM... alors même que la fonction renvoie un INTEGER....

Le plus fameux ? Quand je modifie la fonction pour lui faire envoyer un String (hérésie mais bon, on teste...), regardez ce qui se passe :

Pièce jointe 495430

Pièce jointe 495435

Les valeurs deviennent des String (logique.. !?) sauf une qui devient un NUM (étrangement parce que dans la formule je lui additionne un chiffre (mon épargne mensuelle).

Pourquoi est-ce que pour des valeurs sources de même type, ma fonction ne se comporte pas de la même manière ? Pourquoi est-ce que le fait de passer en String semble corriger un problème... Je ne comprends pas...

Bonjour,
Donc finalement, la fonction nommée MoisPrecedent doit renvoyer la valeur (montant) de la cellule dont le nom est défini par l'argument Information, qui est Variant mais devrait idéalement être typée String de la feuille du mois précédent ?
Si c'est bien le cas, il eut été plus judicieux de nommer cette fonction ValeurMoisPrecedent ce qui aurait évité la confusion

Alors si c'est une valeur numérique à renvoyer, il serait préférable de typer la fonction comme Double et pas Integer dont la valeur maximale est +32767 et ne renvoie que des entiers
C'est la raison du renvoi du message #VALEUR si la valeur renvoyée dépasse 32767 ce qui n'explique pas la chaîne de caractères renvoyée mais peut-être faut-il vérifier le type de la cellule source (se trouvant donc dans la feuille du mois précédent)

Bonsoir,

C'était effectivement bien ça : Double a résolu tous les problème... Et vous me voyez platement confus de la confusion : je cherchais effectivement à récupérer les valeurs et non le nom de l'onglet

Un ÉNORME merci pour votre patience, et votre connaissance, parce qu'il fallait le savoir qu'Integer avait une limite à 32000...

C'est bien grâce à des gens comme vous qu'Internet est aussi super!

Bien cordialement,

Nicolas

Bonsoir Nicolas,

Citation:

---

Un ÉNORME merci pour votre patience, et votre connaissance, parce qu'il fallait le savoir qu'Integer avait une limite à 32000...

---

Il n'y a pas beaucoup de mérite, c'est le cours de base du VBA maisi il est évident que si le nom de la fonction avait été plus explicite, je n'aurais pas cherché dans la mauvaise direction.

Citation:

---

C'est bien grâce à des gens comme vous qu'Internet est aussi super!

---

Je donne ce que j'ai reçu. Il y a 20 ans, je savais juste faire une somme avec Excel et c'est grâce à des contributeurs et beaucoup de travail que j'en suis là aujourd'hui.

Un peu de lecture sur le sujet Utiliser les variables en VBA Excel et particulièrement le chapitre II. Les types de données

En tous les cas, merci pour ton retour et n'oublie pas de marquer ce sujet comme :resolu:.