Discussion :

[Invité]

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 :

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

=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 :

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

=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 :

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

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 ?

[Philippe Tulliez]

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.

[Invité]

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 :

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

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 ?

[Philippe Tulliez]

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

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.

[Invité]

"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 ?



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.




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 :

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

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

[Philippe Tulliez]

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 :

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

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 :

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

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 :

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

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 :

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

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.