Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
3123 connectés 

  FORUM HardWare.fr
  Programmation
  VB/VBA/VBS

  [résolu] Automatiser une fonction excel sur vba

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[résolu] Automatiser une fonction excel sur vba

n°1658113
othmer
Posté le 14-12-2007 à 10:17:21  profilanswer
 

Bonjour a tous,
 
Je suis débutant en vba, et j'ai un problème pour automatiser une formule d'excel sur vba. Cette formule s'appelle polyA et elle permet de calculer les coefficients d'une courbe par la méthode des moindres carrés. Dans mon cas je veux une droite horizontale donc un polynome de degré 0.
4 parametre doivent etre entrés pour cette fonction :
Mat x : la colonne des abcisses
Mat y : la colonne des ordonnées
N : le degré du polynome (ici 0)
I : un coefficient qui vaut 1 par defaut
 
Mes abcisses se trouvent dans la colonne G et mes ordonnées dans la colonne K.  
J'arrive a appliquer la formule en entrant une plage de données précises (ex : Formula = "=PolyA(G2:G38, K2:K38, 0, 1)" ), mais ce que je voudrais c'est que ca s'enchaine sur les plages de données suivantes.
 
Voici mon code :
 

Code :
  1. Sub PolyA()
  2. Dim index As Long
  3. Dim nb As Integer
  4. Dim start As Long (ligne de début pour un vehicule)
  5. Dim fin As Long (ligne de fin pour le meme vehicule)
  6. Dim Val As Integer
  7.     index = 2
  8.     nb = 0
  9.     start = 2
  10.     fin = 2
  11.     Val = 0
  12.     Dim nbligne As Long
  13.     nbligne = 66000
  14.     Do
  15.         Val = Cells(index, 2).Value
  16.         Do
  17.              index = index + 1
  18.              nb = nb + 1
  19.         Loop While Cells(index, 2).Value = Val
  20.         If Cells(index, 2).Value = "" Then
  21.             nb = nb - 1
  22.         End If
  23.         fin = start + nb - 1
  24. 'start et fin correspondent aux indices de 1ère et dernière ligne d'une série de données, donc une fois que la formule est appliquée a la série de start à fin, le start devient le fin+1 et le nouveau fin est déterminé avec la boucle du dessus'
  25.         For i = start To fin
  26.                  Plage = Cells(i, 7).Value
  27.                  Set Plage2 = Range("start,11:fin,11" )
  28.                        
  29.                  Formula = "=PolyA(plage, plage2, 0, 1)"
  30.         Next
  31. 'Ce qui est en rouge est du grand n'importe quoi car je nage, et donc c'est ce qui demande à être travaillé'
  32. Cells(start, 12).Value = Formula
  33.         start = index
  34.         nb = 0
  35.         If IsEmpty(Cells(index, 2)) Then
  36.              index = nbligne + 1
  37.         End If
  38.     Loop While index < nbligne
  39. End Sub


 
Donc voilà si quelqu'un pouvait eclairer ma lanterne sur cette fonction ca m'aiderait grandement.
J'espere avoir été clair, merci d'avance


Message édité par othmer le 18-12-2007 à 08:05:45
mood
Publicité
Posté le 14-12-2007 à 10:17:21  profilanswer
 

n°1658272
olivthill
Posté le 14-12-2007 à 13:29:31  profilanswer
 

Citation :

Cette formule s'appelle polyA
[...]une droite horizontale  
[...]Formula = "=PolyA(G2:G38, K2:K38, 0, 1)" )
[...]Sub PolyA()  
[...]Formula = "=PolyA(plage, plage2, 0, 1)"


Qu'est-ce que PolyA ? Est-ce une formule, un graphique, une fonction pour une formule, ou une subroutine VBA ? Avoir le même nom pour ces différentes choses ne me permet pas de bien comprendre la question.
 
Je ne vois pas PolyA dans la liste des fonctions que me donne Excel quand je cherche à rentrer une formule, et je n'arrive pas à reproduire le cas Formula = "=PolyA(plage, plage2, 0, 1).
 
Je ne vois pas l'intérêt d'avoir un programme qui met des formules dans des cellules pour votre automatisation. On peut mettre une formule via le VBA en faisant, par exemple : ActiveCell.FormulaR1C1 = "=RC[1]+RC[2]" Mais ensuite, il faudrait lancer l'activation de la formule. Il est plus facile et plus pratique d'avoir une petite subroutine VBA qui va faire un calcul et qui va mettre le résultat dans une cellule.
 
Bref, je ne comprends pas bien, mais peut-être que les autres aussi, car personne ne répond.

n°1658306
tegu
Posté le 14-12-2007 à 13:59:52  profilanswer
 

Il faut bien différencier les cas :
La variable plage semble être une valeur de cellule alors que plage2 est un objet Range.
La propriété .Formula attend une valeur de type String.
start et fin sont des entiers Long

 

Ton objectif est donc de remplir .Formula, donc une variable String.
C'est pour cela que tu utilises les guillemets : "=PolyA(plage, plage2, 0, 1)"
Sauf qu'en les utilisant tu transformes tes variables en données non interprétées.
Et c'est là que les débutants ont du mal en général, parce que les formules Excel ne reconnaissent pas directement les variables VBA !
On ne peut donc pas les utiliser comme ça dans une formule.

 

Déjà pour résoudre ton problème il faut harmoniser plage et plage2 (soit des String contenant l'adresse des Range, soit les objets Range eux-même). Ensuite il faut utiliser une syntaxe de concaténation pour remplir .Formula
Par exemple, si plage et plage2 sont des String et contiennent directement les adresses (càd. "G2:G38" ), tu auras un truc du genre :
"=PolyA(" & plage & ", " & plage2 & ", 0, 1)"
À toi de trouver comment remplir plage et plage2 ;) (un indice : propriétés .Address et .AddressLocal des objets Range)

 

Pas sûr d'avoir été clair, mais j'espère que ça t'aidera.


Message édité par tegu le 14-12-2007 à 14:03:55
n°1658461
othmer
Posté le 14-12-2007 à 16:51:31  profilanswer
 

PolyA est une formule qui n'est pas par défaut sur excel, je l'ai "téléchargée si on peut dire". Mais ce n'est qu'une formule comme une autre permettant de déterminer les coefficient de polynomes.
 
Plage et Plage2 sont de même type, mais je n'arrive pas à les définir correctement. Et comme je suis extremement debutant c'est pas facile de se faire comprendre, et c'est pas facile pour moi de vous comprendre totalement.
 
Comment je définis mes plages de données pour que la formule polyA fonctionne en boucle? en gros sur une feuille de calcul j'ai 500 blocs de ligne allant de start a fin, soit autant de fois polyA à appliquer.  
 

n°1659162
othmer
Posté le 17-12-2007 à 09:03:21  profilanswer
 

J'ai progressé un peu sur le sujet mais ce n'est pas encore ça.
 
J'ai fait ça (en lieu et place de ce qui etait en rouge dans mon premier post) :
 
Dim coef As String
Dim valeur As Integer
 
For i = start To fin
coef = "=PolyA(" & Cells(i, 7) & ";" & Cells(i, 11) & ";0;1)"
Next
 
coef = valeur
Cells(start, 12).Value = valeur

 
Quand je lance la macro je n'ai pas d'erreur ce qui est déjà une bonne chose; mais par contre j'ai un problème en ce qui concerne le résultat de ma formule, il me met 0 pour chaque série de données, comme si ca ne calculait pas.
QUand je fais le pas a pas, j'ai bien les bonnes valeurs de mes parametres dans ma formule polyA, mais après je sais pas. Est ce qu'il faut que je modifie coef pour mettre quelque chose de type .Formula?

n°1659171
devil_k
Posté le 17-12-2007 à 09:32:06  profilanswer
 

- Ta procédure polyA ne prend pas de paramètres
- Dans ton code du message juste au dessus, tu donnes une valeur à coef dans la boucle x fois, une fois sorti de cette boucle, tu affectes à coef une valeur vide, puis tu utilises cette variable comme valeur dans une cellule de la colonne L
Bref, dans cette portion de code, tu ne fais... rien

 

Pour t'aider, une fonction personnalisée n'est, comme son nom l'indique, pas une procédure (donc Function, pas Sub)
ça veut dire également, qu'elle retourne une valeur, et qu'elle est typée

 

Je crois après relecture que tu n'as vraiment pas compris le principe, si tu veux la fonction dans ta feuille Excel...
Dans ta fonction, tu ne t'occupes pas de la partie affichage, tu t'occupes juste de la partie fonctionnelle, l'affichage dans la cellule, c'est Excel qui s'en occupe

 

Voici un petit exemple, une fonction très simple qui calcule l'aire d'un rectangle

 

Public Function aireRectangle(longueur As Single, largeur As Single) As Single
    aireRectangle = longueur * largeur
End Function

 


Là, si tu tapes dans ta feuille Excel par exemple "=aireRectangle(5;6)", ça va t'afficher 30, pourtant la partie affichage n'est pas gérée dans la fonction ;)
Il faut déclarer les variables en entrée de ta fonction


Message édité par devil_k le 17-12-2007 à 09:52:20
n°1659201
othmer
Posté le 17-12-2007 à 10:32:21  profilanswer
 

Merci devil_k pour la reponse.  
Donc si j'ai bien compris, il faut que je crée un fichier function et que je l'appelle ensuite dans ma procédure sub.
 
Il y a un fichier function qui existe déjà dont voici le code :
 

Code :
  1. Function PolyA(ByVal MatX As Range, ByVal MatY As Range, ByVal N As Long, Optional ByVal i As Variant = 1)
  2. 'Calcul du coefficient "Ci" de l'équation polynomiale de degré n
  3. 'calculé par les moindres carrés des points donnés Pt(xi,yi)
  4. 'Soit xi = matx et yi = maty
  5. 'Y = C1*X^n + Ci*X^(n-1) + ... + Cn.
  6. 'Traitement de l'index
  7. i = CLng(i)
  8. 'Résolution matricielle
  9. Dim tmp As Range
  10. Set tmp = MatX
  11. 'Tailles matrices
  12. Dim L As Long, L2 As Long, C As Long, C2 As Long
  13. L = MatX.Rows.Count
  14. L2 = MatY.Rows.Count
  15. C = MatX.Columns.Count
  16. C2 = MatY.Columns.Count
  17. 'Erreur de taille
  18. If C > 1 Or C2 > 1 Then PolyA = "#COLONNE!": Exit Function
  19. If L <> L2 Then PolyA = "#LIGNE!": Exit Function
  20. If L < N - 1 Then PolyA = "#DEGRE!": Exit Function
  21. If i - 1 > N Then PolyA = "#INDICE!": Exit Function
  22. 'calcul la matrice rectangulaire en X
  23. ReDim coefa(1 To L, 1 To N + 1) As Double
  24. Dim t As Long, tt As Long, X As Double
  25. For t = 1 To L
  26.     X = MatX.Cells(t)
  27. For tt = 1 To N + 1
  28.     coefa(t, tt) = X ^ (N + 1 - tt)
  29. Next tt, t
  30. 'matrice Y
  31. ReDim coefb(L) As Double
  32. For t = 1 To L
  33.     coefb(t) = MatY.Cells(t)
  34. Next t
  35. 'Redéfinition matricelle carré selon
  36. 'la méthode des moindres carrés
  37. 'Matrice X
  38. ReDim MatA(1 To N + 1, 1 To N + 1) As Double
  39. Dim M As Long, S As Double
  40. For tt = 1 To N + 1
  41.     For M = 1 To N + 1
  42.         S = 0
  43.         For t = 1 To L
  44.             S = S + coefa(t, tt) * coefa(t, M)
  45.         Next t
  46.     MatA(tt, M) = S
  47. Next M, tt
  48. 'Redéfinition matricielle carré
  49. 'Matrice Y
  50. ReDim MatB(N + 1, 1) As Double
  51. For tt = 1 To N + 1
  52.     S = 0
  53.     For t = 1 To L
  54.         S = S + coefa(t, tt) * coefb(t)
  55.     Next t
  56.     MatB(tt, 1) = S
  57. Next tt
  58. 'inverse matA et garde la ligne i
  59. ReDim mataa(1, N + 1) As Double
  60. For t = 1 To N + 1
  61.     mataa(1, t) = InverseTabMat(MatA(), i, t)
  62. Next t
  63. 'produit deux lignes
  64. PolyA = ProduitTabMat(mataa(), MatB(), 1, 1)
  65. End Function


 
Donc il faut que je me serve de ça, en entrant MatX, MatY et N comme parametre, sans toucher à ce fichier?


Message édité par othmer le 17-12-2007 à 14:37:39
n°1659334
olivthill
Posté le 17-12-2007 à 13:33:54  profilanswer
 

Ca devient plus clair. PolyA est donc une function VBA prenant 4 paramètres.
Pour l'utiliser dans du VBA, il faut l'appeler sans le "=" et les ";".
Par exemple :

For i = start To fin  
  coef = PolyA(Cells(i, 7), Cells(i, 11), 0, 1)
Next


n°1659356
othmer
Posté le 17-12-2007 à 14:05:53  profilanswer
 

Je viens d'essayer comme ce que tu m'as proposé, mais il m'affiche un message d'erreur :Erreur de compilation : nombre d'arguments incorrects ou affectation de propriété incorrecte
 
J'ai essayé ça aussi mais ca marche pas :
 
 

Code :
  1. For i = start To fin
  2.          Range(Cells(start, l2), Cells(start, l2)).FormulaLocal = "=PolyA(" & Cells(i, 7) & ";" & Cells(i, 11) & ";0;1)"
  3. Next


 

Code :
  1. Dim coef As Integer
  2.     Dim plage1 As Range
  3.     Dim plage2 As Range
  4.     Set plage1 = Range("G" & start, "G" & fin)
  5.     Set plage2 = Range("K" & start, "K" & fin)
  6.     For i = start To fin
  7.         coef = "=PolyA(" & plage1 & ";" & plage2 & ";0;1)"
  8.     Next
  9.     Cells(start, 12).Value = coef


Message édité par othmer le 17-12-2007 à 15:24:08
n°1659409
devil_k
Posté le 17-12-2007 à 15:27:22  profilanswer
 

Sur le bout de code que tu nous donnes, c'est normal que ça ne marche pas :
Ta fonction attend un Range (ou tout du moins son addresse), tu lui donnes sa valeur
 
Je pense que  
 
Range(Cells(start, l2), Cells(start, l2)).FormulaLocal = "=PolyA(" & Cells(i, 7).Address & ";" & Cells(i, 11).Address & ";0;1)"
 
devrait passer, à tester

mood
Publicité
Posté le 17-12-2007 à 15:27:22  profilanswer
 

n°1659419
othmer
Posté le 17-12-2007 à 15:35:40  profilanswer
 

Erreur d'execution 1004
Je commence à désespérer, y'a rien qui veut bien marcher.

n°1659430
devil_k
Posté le 17-12-2007 à 15:46:37  profilanswer
 

Il faut que tu vérifies que tes variables soient bien initialisées
(start, l2 et i à priori)
Si elles le sont, mets un point d'arrêt sur la première ligne de ton code et vois exactement à quel moment ça merde
 
Sinon, ta fonction, tu peux l'utiliser à la main dans une feuille Excel, et dérouler :)

n°1659446
othmer
Posté le 17-12-2007 à 16:05:59  profilanswer
 

Le reste du code est dans le premier poste du topic et je pense qu'il n'y a pas de probleme en dehors de cette ...... de fonction :) . Car ce code je l'utilise dans d'autres macro qui ont un autre bur mais dont le principe reste le même et ça marche.
 
Je crois que c''est pas possible de l'utiliser à la main, car je dois l'appliquer environ 10000 fois, d'ou ma volonté que ça fonctionne avec une macro, j'ai pas envie de rater noel a cause de ça  :D
 
j'édite car je crois que c'est bon, ce que tu m'a donné tout à l'heure était correct, sauf que moi j'ai fait un copié collé de ce que tu as mis, mais il y avait une petite erreur, moi je voulais 12 et pas l2, hihi. Quand tu as parlé de variable l2 j'ai trouvé ça bizarre.
Je vérifie que ce soit vraiment bon et je reviens confirmer. Merci beaucoup en tout cas.


Message édité par othmer le 17-12-2007 à 16:08:17
n°1659455
othmer
Posté le 17-12-2007 à 16:18:21  profilanswer
 

Quelle déception il semblerait que ce ne soit pas tout à fait ça.
En lieu et place de la valeur souhaitée, il m'affiche la valeur de cells(fin,11). En fait il applique la formule uniquement sur la derniere ligne donc celle qui a comme indice fin.
 
DOnc je pense qu'il faut effectivement faire comme tegu avait dit, c'est à dire :
 
Range(Cells(start, 12), Cells(start, 12)).FormulaLocal = "=PolyA(" & plage1 & ";" & plage2 & ";0;1)"
 
et donc bien définir plage1 et plage2 de telle sorte que la fonction s'applique une seule fois sur l'ensemble des données de start à fin, et non pas sur chque ligne ce qui amene a donner le resultat sur uniquement la dernier ligne.
 
J'ai fait ça :
 

Code :
  1. Dim plage1 As Range
  2.     Dim plage2 As Range
  3.     Set plage1 = Range(Cells(start, 7).Address, Cells(fin, 7).Address)
  4.     Set plage2 = Range(Cells(start, 11).Address, Cells(fin, 11).Address)
  5.     Range(Cells(start, 12), Cells(start, 12)).FormulaLocal = "=PolyA(" & plage1 & ";" & plage2 & ";0;1)"


 
Mais bien entendu ça ne fonctionne pas


Message édité par othmer le 17-12-2007 à 16:39:24
n°1659462
devil_k
Posté le 17-12-2007 à 16:36:43  profilanswer
 

ça ne serait pas plutôt  
 
cells(i,12).FormulaLocal = "=PolyA(" & Cells(i, 7).Address & ";" & Cells(i, 11).Address & ";0;1)"  
 
que tu voudrais dans ta boucle ?
 
Désolé, mais je dois y aller là, bon courage ;)

n°1659466
othmer
Posté le 17-12-2007 à 16:47:08  profilanswer
 

Non car si je met ça, je me retrouve avec une colone 12 identique à la 11, car il applique la fonction sur chaque ligne independament des autres, donc la boucle ne sert a rien, d'ou la nécessiter de définir les plages de donnée avant.
 
Dommage que tu doives partir, car tes conseils me sont bien précieux et je sens que je suis près du but, mais mes lacunes sur vba ne pardonnent pas.

n°1659526
kiki29
Posté le 17-12-2007 à 18:17:42  profilanswer
 

Soir Bon
Si cela peut aider :
http://fordom.free.fr/
http://fordom.free.fr/poly/POLYA.htm et les exemples
http://fordom.free.fr/poly/POLYAEX.htm
 
Restera à utiliser Option Explicit pour déclarer les variables, ce qui n'est pas fait à l'origine


Message édité par kiki29 le 18-12-2007 à 08:08:15
n°1659671
othmer
Posté le 18-12-2007 à 08:04:12  profilanswer
 

Merci pour les réponses, j'ai enfin réussi à exécuter correctement cette macro, après plusieurs heures de prise de tête.
 

Code :
  1. Dim plage1 As Range
  2.     Dim plage2 As Range
  3.     Set plage1 = Range(Cells(start, 7).Address, Cells(fin, 7).Address)
  4.     Set plage2 = Range(Cells(start, 11).Address, Cells(fin, 11).Address)
  5.    
  6.         Range(Cells(start, 12), Cells(start, 12)).FormulaLocal = "=PolyA(" & plage1.Address & ";" & plage2.Address & ";0;1)"


n°1659771
devil_k
Posté le 18-12-2007 à 11:07:15  profilanswer
 

Content que tu aies réussi à t'en sortir :)


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  VB/VBA/VBS

  [résolu] Automatiser une fonction excel sur vba

 

Sujets relatifs
[PHP](AJAX) - Pb Caractères spéciaux (apostrophe) ..euh RESOLU en faitOptimisation Comparer deux colonnes en VBA sous Excel
[C++] [resolu] Problème de templatescopie selon criteres sous excel
Exécution commande, pas de retour [Résolu]Résolu [Excel] colori selon critère
[RESOLU] checkBox et databinding[PHP] [ RESOLU ] Textarea et javascript \n ou \\n
[Resolu] Probleme de hauteur de tableau 
Plus de sujets relatifs à : [résolu] Automatiser une fonction excel sur vba


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR