Appel d'une Sub dans une Function

Bonjour,

j'ai un fichier nommé "NFS 09-16" par exemple et un fichier "Rap00Siege"

certaines cellules de NFS sont des sommes de cellules de Rap00 sauf que ces cellules peuvent bouger car il y a souvent des insertions de lignes.

C'est pourquoi je souhaite créer une macro qui selon un critère va additionner ces cellules même si elles ont bougé.

pour le moment j'ai une macro qui fonctionne pour une cellule de NFS c'est pourquoi j'aimerai en faire une formule afin de la glisser sur les autres cellules. Voilà ce que j'ai fait pour le moment :

Function Rapp(code)

Application.Volatile

Call RAP

End Function

'------------------------------------------------------------------------------------------

Sub RAP()

Siege = Windows("Rap00Siege")

If Month(Date) < 11 Then

    NDF = Windows("NDF 0" & Month(Date) - 1 & "-16")

        Else

          NDF = Windows("NDF " & Month(Date) - 1 & "-16")

End If

        Siege.Activate

            DVoy = Columns("A").Find("104ADM08", LookIn:=xlValues, LookAt:=xlPart).Row + 1

            FVoy = Columns("A").Find("104ADM09", LookIn:=xlValues, LookAt:=xlPart).Row - 1

For i = DVoy To FVoy

            If Left(Range("A" & i), 6) = code Then 

'Les 6 premiers caractères doivent être identiques à la variable "code" qui prendrait sa valeur dans la formule

        NDF.Activate

            mois = Range("V1").Value + 2

        Siege.Activate

            montant = Cells(i, mois).Value

        NDF.Activate

            l = Range(code).Row

            Range("AE" & l).Value = montant

            tot = Range("AA" & l).Value

            Range("AA" & l).Value = tot + Range("AE" & l).Value

            End If

Next

End Sub

Voilà, je pense qu'en ayant une formule sous la forme "Rapp(C5)" par exemple, la variable "code" prenne la valeur C5 et exécute la macro et c'est ici que je bloque ^^

En espérant être assez clair !

Je vous remercie par avance !

Bonsoir,

tout d'abord, C5 dans RAPP(C5) est une adresse de cellule donc dans la fonction il faut le stipuler :

Function Rapp(Cel As Range)

on dimensionne la variable Cel comme étant un objet Range

Ensuite lorsque vous faite l'appel de la sub suivante il faut "transférer" cette variable

Call Rap(Cel)

Et dans la Sub Rap(code As Range)

C'est une idée comme ça... je ne sais si ça marche mais pour moi au niveau "structure" cela me semble mieux...

@ bientôt

LouReeD

Salut LouReed ! (2e)

Pas d'accord avec toi sur le premier point ! Si on met un argument Range à une fonction personnalisée, c'est qu'on exige que l'argument soit fourni exclusivement sous cette forme. Cela peut se justifier pour simplifier dans le cas d'une utilisation en fonction de feuille de calcul dans la mesure où l'on peut appliquer un traitement sans test préalable à des données fournies sous forme de plage de cellules... Mais dans le cas général, surtout si l'argument attendu n'est pas un tableau, il demeure préférable de s'adapter au type de données attendu. Ici :

Function RAPP(code As String)

serait mieux... Et lors de l'utilisation, si l'on met : =RAPP(C5), c'est le contenu de C5 qui sera pris en argument de la fonction.

Après, il est certain que l'argument doit être communiqué, sinon on ne voit pas sa raison d'être...

Mais pour tout dire, quand j'ai lu le sujet peu après son émission, j'ai été incapable de tout lire ! Code indenté de façon aléatoire, lignes sautées systématiquement qui empêchent d'avoir tout le code sous les yeux en même temps, aucune variable déclarée... j'ai considéré qu'en l'état c'était illisible. Et comme pour comprendre la question il faudrait au moins lire le code... !

Ceci dit, rien n'empêche une fonction d'utiliser une Sub et de renvoyer son résultat, à condition que la Sub n'opère pas d'action incompatible avec l'utilisation en fonction de feuille de calcul.

Mais ici, cela n'a aucune chance d'aboutir ! La fonction est volatile, elle sera recalculée... mais ne renvoie rien. Je ne sais si la Sub se justifie (j'en doute un peu !), ou si son contenu pouvait être inclus dans la procédure Function, en tout cas le fait qu'il n'y ait pratiquement rien dans cette dernière ne me paraît pas justifié.

Cordialement.

Bonjour à tous les deux,

Veuillez excuser mon amateurisme en terme d'optimisation de macro !

Mais même sans comprendre ma macro, vous avez cerné le problème !

Par exemple : Je veux mettre Rapp(C5) en AA5 sur un classeur "NFS 09-16". C5 étant sous la forme 104ADM par exemple.

Cette fonction doit aller chercher dans un classeur "Rap00Siege" tous les montant d'une colonne K ("Septembre" dans le fichier) qui ont, en colonne A les 6 premiers caractères correspondant à C5. Exemple : 104ADM_626300

D'où l'utilisation de la fonction LEFT.

J'espère vous avoir élairer, et merci déjà pour vous être penchés sur le sujet !

Bonjour,

Au regard de ton code, tes explications sont incomplètes...

Ce qui est sûr, c'est que ce code est inopérant, et non conforme aux règles régissant ce que peut faire une fonction de feuille de calcul.

En utilisant ta fonction pour insérer une formule en AA5, celle-ci renverra son résultat en AA5.

Vu le calcul à faire, dépendant d'un autre classeur, il est recommandé qu'elle ne soit pas volatile ! Et le passage par une Sub n'est pas justifié.

Pour la bâtir, il est cependant nécessaire de détailler le calcul à faire : à partir de quelle feuille ? sur quels critères ? On ne peut le déduire de ton code car sa syntaxe est totalement défectueuse ! L'utilisation d'objets Windows est une mauvaise idée ! Outre la dépendance à l'affichage qu'il convient d'éliminer, la dépendance à la date du jour est un autre aspect méritant une explication détaillée pour qu'on sache ce que l'on fait précisément.

Bonjour MFerrand,

Merci de m'aider !

Je t'ai fait un petit fichier test ou les feuilles représentent des classeurs mais on retrouve la même mise en forme à quelques choses près.

J'ai fait une petite macro qui me permet d'obtenir le résultat souhaité. en espérant que cela te convienne !

En te remerciant par avance !

MFerrand bonsoir !

Pas d'accord avec toi sur le premier point !

Ca fait toujours plaisir de voir une personne qui n'est pas d'accord avec moi mais avec le sourire !!!

Et je vois que vous n'êtes jamais loin de moi

Dois-je en conclure que sur certaines réponses données, s'il n'y a pas d'intervention de votre part c'est que j'ai mis dans le mille ?

Après pour ce qui est de ce qui a fait que vous avez "survoler" le post : ce n'est pas faux, mais ma foi heureusement que quelque fois je suis là pour "déterrer" un post et en faire un UP, voir même susciter de l'intérêt !!!

Sur ce il est vrai que d'avoir une fonction qui appel une sub... autant tout écrire dans la fonction...

Pour le reste cela me dépasse un peu.

@ bientôt

LouReeD

Re-salut LouReed !

Dois-je en conclure que sur certaines réponses données, s'il n'y a pas d'intervention de votre part c'est que j'ai mis dans le mille ?

Tu sais fort bien si tes réponses aboutissent au résultat demandé ou non ! C'es généralement le cas ! On peut toujours ajouter des variantes ou des options, mais je n'ai pas de raison d'intervenir si je n'apporte rien de plus : méthode alternative utile, etc.

Ici, ce sont tes remarques qui m'ont incité à venir sur ce sujet ! Il me semblait que la question méritait d'être soulevée et discutée. Mais comme je l'ai indiqué, il y a deux cas principaux : les arguments ne représentant qu'une seule valeur, qu'il est préférable de typer sur le type de données attendu (ce qui constitue un filtre initial) et que l'utilisateur peut passer en tant que valeur directement ou en invoquant une plage (cellule) pour passer sa valeur à la fonction ; et les arguments tableaux ou matrices pour lesquels un argument de type Variant permettrait de les accueillir passés sous diverses formes... mais qui exige alors d'évaluer les dimensions et indices des tableaux passés, dans ce cas obliger le passage en tant qu'objet Range simplifie l'écriture de la fonction car on sait au départ qu'une plage correspond à un tableau à deux dimensions dont l'indice minimal de chacune est 1...

Il m'est arrivé de faire une fonction utilisant une Sub (et même une autre fonction auxiliaire). C'était surtout pour pouvoir mieux s'y retrouver dans le code (Il s'agissait de gestion de congés de maladie : répartir chaque congé en indiquant les dates des jours subrogés à plein traitement, à demi-traitement, ou non subrogés, le résultat était du texte listant les dates et le calcul devait remonter tous les CM précédents sur un an pour évaluer la situation de chaque jour ; la fonction était complètement liée à la structure de la feuille et ne pouvait fonctionner que si le suivi était réalisé de façon rigoureuse...) Mais c'est un cas très particulier.

Par ailleurs Microsoft indique bien pour les fonctions personnalisées :

Une fonction de feuille de calcul définie par l'utilisateur ne doit pas modifier les données d'un classeur ou l'environnement de Microsoft Excel de l'une des manières suivantes :

• changer le mode de calcul ou le mode d'affichage.

Et j'ai testé à diverses reprises que Excel ne se laisse pas berner par l'utilisation d'une Sub activée par la fonction : si la Sub déroge, la fonction renvoie inévitablement #VALEUR!

Braters : j'ai vu ton classeur ! Il serait préférable que tu restes sur ta problématique de départ, un classeur n'est pas une feuille et vice-versa. Une fonction bâtie sur ton dernier modèle de classeur ne sera pas adaptable pour répondre au problème posé initialement. Et il serait bon que tu donnes les précisions pour que l'on puisse interpréter correctement les mentions auxquelles tu fais référence, et que les colonnes dans lesquelles il faut aller fouiller ne changent pas à chacune de tes interventions (de même que les feuilles dont le nom semble volatile, on n'est à peu près sûr que du nom des classeurs en présence).

Cordialement.

Oui, bon ben là je crois que je vais m'éclipser gentiment...

@ bientôt

LouReeD

bonjour à tous les deux,

désolé pour le délai de réponse. Je vous fais parvenir 2 fichiers qui représentent exactement la situation, nom, colonne, feuille etc...

Je n'ai pas résumé le problème une nouvelle fois dans les fichiers, je pense que vous avez compris ce que j'essayais de faire.

De plus, j'ai appelé une sub dans une fonction tout simplement parce que c'est la première chose qui m'est venu à l'idée, en aucun cas je considère cette idée comme étant la meilleure ou bien celle à utiliser absolument. Je suis ouvert à toutes propositions (ou presque ) Je veux simplement que les totaux et les contrôles se maintiennent à jour.

Si après cela, vous ne pouvez toujours pas m'aider, je pense que j'abandonnerai l'idée.

Merci encore !