Function avec deux séries de données en argument
Bonjour à tous,
Je suis novice en codage VBA (je n'ai fait que du Python) et je suis bloqué sur une fonction que je dois définir.
Cette fonction doit prendre en argument deux séries de données puis renvoyer un réel calculé à partir des réels contenu dans les deux listes.
Je vous mets au cas où le programme que j'ai rédigé (il doit y avoir d'autres erreurs..)
'
Private Function calcul(List1, List2)
'
Dim var As Double
Dim res As Double
Dim i As Long
'Ce qui suit sert à calculer la moyenne des valeurs de la liste List1, mais il doit y avoir un problème aussi
N = count.List1
Moyenne = 0
For i = 1 To N
Moyenne = Moyenne + List1(i)
Next i
MeanT = MeanT / N
For i = 1 To N
var=List1(i)*(List2(i)^2)
Next i
res = var/Moyenne
End Function
'
Est-ce un problème d'enchaîner deux boucles for comme ceci ? Elles sont toutes deux indépendantes.
Merci par avance pour votre aide !
MLito
bonjour
pourquoi en VBA et pas avec des fonctions habituelles d'Excel ?
dans tous les cas, VBA ou pas, joins ton fichier, on va regarder
Je préfère ne pas utiliser les fonctions habituelles d'excel car je vais beaucoup utiliser cette fonction, et ça me paraissait plus propre comme méthode
Voici le fichier avec le programme en VBA et un exemple
Merci beaucoup !
re
donc pour faire = 1 + 1
tu préfères VBA parce que ça sera plus propre
on a tous les fonctions Excel plus ou moins complexes répétées des 100 000 fois dans certains fichiers
mais en fait quelle est la "fonction" en question ?
que veux-tu faire ? des soustractions, des écarts, des %
Bonjour jmd,
Je pense que le message que je t'ai envoyé avant d'aller manger n'est pas parti.
Alors pour résumer : je suis en stage et travaille sur des données confidentielles. Le fichier test.xlsm ne contient pas de données réelles. La fonction ne correspond pas à celle que j'utilise, mais elle synthétise les problèmes que je rencontre. Pour que tu vois un peu la tête de la fonction, je t'ai envoyé une capture d'un bout de la fonction que je code.
Je pense que faire une fonction est plus "simple" car je dois répéter cette fonction sur un grand nombre de jeux de données et de fichiers. Recopier dans chaque fichier la formule avec les bonnes cases me parait être davantage source d'erreur (et moins fastidieux à la longue pour ceux qui l'utiliseront après moi) que coder une fonction une fois pour toute et l'appliquer à mes différents classeurs.
Encore merci pour ton aide
Bonjour,
Il y a deux problèmes dans ta façon d'opérer
Tu veux bâtir une fonction personnalisée. OK ! Une fonction personnalisée n'est rien d'autre qu'une procédure VBA de type Function, aménagée de façon à pouvoir être utilisée en feuille de calcul. A ce titre, il convient notamment le cas échéant à la suite des déclaration de démarrer le code par l'instruction : Application.Volatile, qui permet à Excel de recalculer lors des recalculs, ou à l'inverse Application.Volatile False, pour éviter qu'elle soit recalculée (et ne le soit que si on la relance...)
Or tu déclares ta Function comme privée !
C'est là le premier problème, facilement soluble, il suffit de supprimer Private. Et inutile de mettre Public à la place, en VBA toutes les procédures sont publiques par défaut (sauf si tu les rends privées
A ce stade il convient de n'avoir qu'une seule procédure Function sous le même nom ! Avec tes trois cela risque sinon de sacrément cafouiller sans qu'on sache d'où ça vient !
Le deuxième problème est que pour faire quelque calcul que ce soit, tu utiliseras d'une part des fonctions VBA et/ou des fonctions de feuilles de calcul Excel en VBA. Je préconise toujours à cet égard de privilégier les fonctions VBA, voire les calculs au moyen d'opérateurs VBA, et de ne recourir aux fonctions Excel en VBA que lorsqu'il n'existe pas de fonction VBA équivalente ou lorsque la fonction Excel s'avère nettement plus commode et économique que les méthodes proprement VBA...
L'utilisation des fonctions VBA obéit à une syntaxe, de même que l'utilisation des fonctions Excel en VBA obéit à une syntaxe VBA (qui n'est pas identique à celle de leur utilisation dans Excel). Je l'indique pour ordre car je n'ai pas vu de fonctions du tout dans ton code.
D'autre part, tu utilises des données sur lesquelles tu opères lesdits calculs... ce qui te conduit tout naturellement à utiliser des variables dans tes manipulations. A cet égard il semble qu'il y ait un peu d'ordre à mettre...
On s'y retrouvera beaucoup mieux si les variables sont déclarées et autant que possible typées. Si tu en déclares 3, tu en utilises d'autres non déclarées... Certaines ne sont peut-être pas à tes yeux des variables d'ailleurs, mais pour VBA si ! dans la mesure où cela ne correspond à aucun mot-clé, méthode, propriété ou instruction valide !
Exemple :
N = Count.List1Count pourrait être la fonction Excel NB... mais non utilisé ici en tant que cette fonction (syntaxe inadéquate) et n'étant pas en position d'être la propriété Count dont toute collection dispose pour renvoyer le nombre de ses membres, Count sera interprétée comme variable non déclarée... Et comme tu fais suivre d'un point, on va pencher pour une variable objet, non initialisée, dont la valeur est Nothing (rien !!). Ce rien ne va pas pouvoir te renvoyer grand chose !
D'autant plus que List1 est lui-même une variable passée en argument à la fonction...
Là, on devine que tu souhaiterais recueillir dans N le nombre de valeurs contenues dans List1.
List1 est un Variant (non typé) mais tu lui passes une plage (objet Range), et à ce titre tu obtiendras le nombre par : List1.Cells.Count, mais sans doute vaudrait-il mieux le typer comme Range, à moins que tu ne veuilles pouvoir passer des matrices numériques concurremment à des plages (mais dans ce cas il te faudra tester préalablement ce qu'il en est de List1 pour adapter la syntaxe... le plus simple est effectivement de le typer Range, ce qui garantit la syntaxe à utiliser exclusivement).
Je ne m'étends pas plus... La question est maintenant de décrire aussi précisément que possible les calculs que tu entends faire. Si tu maîtrises les opérations que tu veux réaliser sur tes données, il devrait être relativement aisé de transcrire tout cela en VBA fonctionnel...
J'ai noté que tu voulais dans un premier temps obtenir la moyenne de List1, ce qui n'est pas très compliqué. Et ensuite ?
Cordialement.
Bonjour MFerrand,
Merci pour ta réponse très détaillée !
Je pense avoir globalement compris, mais je n'arrive pas à faire marcher le code de test même avec tes conseils.. Je n'ai jamais codé en VBA avant
J'ai donc un nouveau script qui est :
Function calcul(List1, List2)
Dim List1 As Range
Dim List2 As Range
Dim var As Double
Dim res As Double
Dim i As Long
Dim N As Integer
N = List1.Cells.Count
'Moyenne = 0 (ce n'est pas la peine d'initialiser, non ?)
For i = 1 To N
Moyenne = Moyenne + List1.Cells(1, i)
'Je pense que c'est la ligne précédente pour désigner la cellule qui pèche : mes plages de données sont des lignes, donc c'est bien Cells(1,i) ?
Next i
MeanT = MeanT / N
For i = 1 To N
var = List1.Cells(1, i) * (List2.Cells(1, i) ^ 2)
Next i
res = var / Moyenne
End Function
J'avais demandé que tu indiques ce que tu calculais et de quelle façon plutôt que de t'escrimer à coder de façon inadéquate !
Décoder à partir de ce code n'est pas aisé...
J'ai beau creuser dans mes souvenirs, je ne vois pas ce que l'on peut vouloir calculer ainsi...
Pour illustrer plus précisément :
List1 et List2 sont les arguments de la fonction, ils n'ont pas à être déclarés en variables locales de cette dernière... Par contre Moyenne et MeanT sont toujours des variables non déclarées...
Au fil de tes calculs :
Moyenne va prendre la valeur 10
MeanT (que tu n'utilise pas) plafonnera à 0
var s'arrêtera sur 5184
et par conséquent res =518,4
Valeurs qu'en outre la fonction va garder jalousement sans les communiquer à quiconque !
Ah, je comprends ! Je calcule des températures par la formule des moindres carrés.
J'ai complètement inventé la fonction que j'utilise, "calcul" ne calcule rien de concret. Mais si j'arrive à faire tourner ce programme, je ne devrais pas avoir trop de mal à arriver à mes fins en le transposant avec ma "vraie " formule. J'ai mis en image dans mes messages précédent un aperçu de la fonction que je code, mais je ne pense pas être autorisé à tout détailler malheureusement.
Le fait de diviser par N ne me donne t-il pas la moyenne ?
Donc j'essaye de comprendre comment rédiger la syntaxe sur ce code assez simple pour qu'il marche et pouvoir l'appliquer à ma formule à coder
ah oui, attends excuse moi, je n'avais pas lu ta deuxième partie de message. Mean et moyenne sont censées être les mêmes variables.
Je revois mon code, ne me reponds pas pour l'instant j'essaye de trouver
Merci pour ton aide
Alors :
le code devient
Function calcul(List1 as Range, List2 as Range)
Dim var As Double
Dim res As Double
Dim i As Long
Dim N As Integer
Dim Moyenne As Double
N = List1.Cells.Count
'Moyenne = 0
For i = 1 To N
Moyenne = Moyenne + Range.List1(1, i).Value
Next i
Moyenne = Moyenne / N
For i = 1 To N
var = List1.Cells(1, i) * (List2.Cells(1, i) ^ 2)
Next i
res = var / Moyenne
End Function
Excel me dit qu'il y a une erreur "d'argument non facultatif" dans la ligne de "Moyenne = Moyenne + Range.List1(1, i).Value"
Comment afficher la variable res comme résultat de ma cellule où je lance la fonction "calcul" justemement ?
Désolé que ce soit aussi fastidieux --"
Je ne vais faire un cours complet de VBA pour t'indiquer qu'en modifiant : List1.Cells(i, 1) qui était correct pour quelque chose qui ne l'est plus tu régresses !
Caluler des températures par la méthodes des moindres carrés, cela me laisse rêveurs
On a bien une droite ou une courbe de tendance qui se définit selon une telle méthode... On utilise la somme du carré des écarts pour le calcul de variance...
Mais là tu fais le carré d'une valeur (pas d'un écart) que tu multiplie par une autre, et tu ne sommes rien... je ne vois rien de ce qu'on cherche !
Bref ! Pour l'instant rien n'est clair !
re
salut MFerrand
en créant une formule "décomposée" par les fonctions Excel tu aurais fini depuis longtemps
exemple simpliste : je décompose la moyenne de la col A
C1=SOMME(A:A)
D1 = NBVAL(A:A)
E1 = C1/D1
pour une formule très longue comme la tienne, tu pourrais décomposer en 10 à 20 calculs intermédiaires
si tu les mets en colonnes (comme dans mon exemple), tu peux alimenter l'ensemble par une table de valeurs et en ressortir une table de résultats.