Aide programmation VBA

Version imprimable

07/05/2011, 12h34
sébastien.R

Aide programmation VBA

Bonjour à tous,
Je suis étudiants en économie et je dois réaliser une petite application de gestion de ventes de produits en VBA..
Nous (moi et mon binome) nous en sommes pas trop mal sorti jusqu'au point 4) de notre projet mais nous blocons pour le petit calcul de rentabilité du point 5)..
Il faut en fait calculer la rentabilité de cette entreprise de la façon suivante:
Ventes de produit - achats de produits - frais fixes (350.000) - le salaire des employés.
(Chacun de ces postes se trouvant dans un userform différents..)
A la suite de cela, il faut calculer le bénéfice obtenu et voir si il se trouve au dessus d'une certaine valeur pour voir quel taxation de l impot nous devons appliquer.. après il faut affecter cette fonction à un bouton calcul rentabilité..
Voilà j'aimerais avoir votre aide et vos propositions pour faire ce calcul de rentabilité :)
(il faut savoir que nous n avons eu que 4 heures d'intro à VBA et c'est vraiment pas évident pour nous!!)
Un grand merci à celui qui pourrait nous aider :)
(je ne joins pas le fichier Excell de notre travail car apparemment ce n'est pas de bonne augure mais si quelqu'un est intéressé par ma question je le mettrai bien évidemment à votre disposition car je pense que ca peut aider à voir ce que je vous demande.)
Bonne journée.
07/05/2011, 14h53
carden752

Bonjour,

Qu'avez vous commencer à coder sur le calcul de rentabilité?
07/05/2011, 15h10
sébastien.R

Je suis au regret de vous répondre rien car justement nous ne savons pas par ou et comment commencer.. comme je vous l'ai dis, nous débutons vraiment et n'avons eu que quelques heures d'introduction à VBA..
C'est pour cela que toute aide est vraiment la bienvenue!
Merci.
07/05/2011, 15h17
mercatog

Même remarques ici et ici.
A ce rythme, toute la classe postera le même sujet dans ce forum.
Un peu de sérieux et lisez avant de poster.
Votre devoir surveillé reflétera l'effort que vous fournissez et non la quête d'une solution clé en main.
07/05/2011, 15h29
sébastien.R

Bonjour,
ma question ne porte pas sur la création de la partie "userform" et les différents boutons associés mais bien sur la partie du calcul de la rentabilité où nous bloquons. nous avons donc effectué une grande partie du travail seul mais là c'est vrai que pour la partie du calcul de rentabilité nous n'y arrivons pas..
Merci à celui qui pourra nous aider.
07/05/2011, 15h44
mercatog

Vous avez un listing de produits achetés et un autre pour ceux vendus et un 3ème pour les salaires.
Vous pouvez utiliser des somme ou somme.si ou sommeprod.
Sujet illustrant l'utilisation de sommeprod (on pouvait utiliser somme.si) ici (Encore c'est un sujet récent)
07/05/2011, 16h40
patricktoulon

heu!!
bonjour

Citation:

Ventes de produit - achats de produits - frais fixes (350.000) - le salaire des employés.

si tes userform ne sont pas affiché en même temps
il y a une chose a faire toute simple

tu ajoute un module standard

en haut tu déclare tes 5 variables du genre
Code:

1 2 3 4 5 public venteproduit as long public achatproduit as long public fraisfixe as long public salairesemployes as long public benefice as long
et dans chaque userform par exemple dans celui de vente quand le calcul est fait dans la même macro juste avant le

Code:

end sub

tu met

venteproduit=soit le nom du control ou ce trouve le résultat soit la variable dont tu t'est servie

et tu fait pareil avec le autre dans leur userform respectifs

tu aura donc a la fin tes variable public avec la valeurs que tu leur aura donné

voila si ça peut t'être utile

au plaisir
07/05/2011, 17h42
sébastien.R

Merci pour votre aide! je ne sais pas encore si cela pourra m aider mais je vais essayer. bonne soirée.
07/05/2011, 17h53
mercatog

Peux tu dans un premier temps faire tes calculs sur Excel sans vba?
Puis après, tu traduiras en vba.
07/05/2011, 18h40
patricktoulon

re

voir sans vba du tout avec une celulle hors aera pour le print avec une formule
07/05/2011, 18h55
sébastien.R

Citation:

Envoyé par mercatog

Peux tu dans un premier temps faire tes calculs sur Excel sans vba?
Puis après, tu traduiras en vba.

Non malheureusement je ne peux utiliser que VBA pour ce calcul de rentabilité.
07/05/2011, 19h14
mercatog

Je sais que ton devoir est ainsi (utilisation de vba).
Si tu réussi manuellement à faire ce que tu veux obtenir, vba sera pour formaliser ton devoir en ayant compris ton problème.

Bonsoir,
J'ai commencé un début de programme pour mon calcul de rentabilité..
Mais le problème c'est que je ne sais pas en fait jusqu ou je dois aller dans ma colonne.. Je veux dire par la que je ne peux définir la grandeur de ma colonne à calculer puisque je peux en fait continuellement ajouter des données et donc je ne peux fixer ce nombre.. j'ai fixé ce nombre à n dans l'idée que je ne connaissais pas sa valeur mais je ne pense pas que ce soir correct..
Ce que j'ai pensé à faire c'est de demander à mon programme d arreter de sommer les nombres de ma colonne lorsque la cellule est vide..
mais je ne vois pas comment faire ça et où le placer dans mon code..
J'ai pensé au codage : If i = " " Then
On arrete de sommer

Mais je ne sais pas comment traduire ça..

Je vous donne ce que j'ai fait jusqu'a présent. Bonne soiré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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 Function LireVentes() As Variant Dim Ventes(n) As Variant Dim i As Integer For i = 1 To n Ventes(i) = Worksheets("Ventes").Cells(i + 1, 5) Next LireVentes = Ventes End Function Function LireAchats() As Variant Dim Achats(n) As Variant Dim i As Integer For i = 1 To n Achats(i) = Worksheets("Achats").Cells(i + 1, 5) Next LireAchats = Achats End Function Function LireSalaires() As Variant Dim Salaires(n) As Variant Dim i As Integer For i = 1 To n Salaires(i) = Worksheets("Employés").Cells(i + 1, 10) Next LireSalaires = Salaires End Function Sub Rentabilité() Dim Rentabilité As Variant Dim Ventes As Variant Dim Achats As Variant Dim Salaires As Variant Dim sumVentes As Double Dim sumAchats As Double Dim sumSalaires As Double Dim Bénéfice As Double Dim i As Integer Ventes = LireVentes() Achats = LireAchats() Salaires = LireSalaires() sumDepenses = 0 sumRecettes = 0 sumSalaires = 0 For i = 1 To n sumVentes = sumVentes + Ventes(i) sumRecettes = sumAchats + Achats(i) sumSalaires = sumSalaires + Salaires(i) Next Bénéfice = sumVentes - sumAchats - sumSalaires - 350000 If Bénéfice > 150000 Then Rentabilité = 0.33 * Bénéfice Else If Bénéfice <= 150000 Then Rentabilité = 0.15 * Bénéfice Else If Bénéfice < 0 Then Rentabilité = Bénéfice End If End If End If Worksheets("Résultat").Range("B2").Value = Rentabilité End Sub

07/05/2011, 23h58
mercatog
Bonsoir
Un essai encourageant ainsi

pour avoir la ligne de la dernière cellule remplie d'une colonne, utilise ceci
Code:

1 2 3 4 5 6 Dim LastLig as long with sheets("Feuil1") LastLig=.cells(.rows.count,"F").end(xlup).row Msgbox LastLig end with
lastlig donnera la ligne de la dernière cellule remplie de la colonne F
08/05/2011, 00h14
sébastien.R

Le problème c'est que je dois définir dès le départ dans ma fonction lireVentes par exemples quel est la grandeur de ma liste que je lui fais passer en revue (au programme).. en fait je dois juste pouvoir lire toutes les données appartenants à une colonne précise d'une feuille (ici la feuille Ventes).. N'y a-t-il pas une fonction qui fasse cela?
Ou bien ne pourrait-on pas adapter un peu le programme que je vous ai proposé (comment??)..

Une proposition

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Rent()
Dim Ventes As Double, Achats As Double, Salaires As Double
Dim FraisFixes As Double, Benefice As Double, Rentabilite As Double
 
Ventes = Application.Sum(Sheets("Ventes").Range("E:E"))
Achats = Application.Sum(Sheets("Achats").Range("E:E"))
Salaires = Application.Sum(Sheets("Employes").Range("J:J"))
FraisFixes = 350000
 
Benefice = Ventes - (Achats + Salaires + FraisFixes)
Select Case Benefice
    Case Is > 150000: Rentabilite = 0.33 * Benefice
    Case Is > 0: Rentabilite = 0.15 * Benefice
    Case Else: Rentabilite = Benefice
End Select
Sheets("Resultat").Range("B2").Value = Rentabilite
End Sub

08/05/2011, 00h42
sébastien.R

Merci beaucoup pour votre proposition.
Lorsque j'exécute le programme il me met
"erreur d exécution 9 : l indice n appartient pas à la sélection".
Comment cela se fait-il?

Votre fonction "Application.Sum(Sheets("Ventes").Range("E:E"))"
calcule donc la somme de ma colonne E mais dois-je adapter cette fonction
à mon programme ou pas..
Pour le moment ça ne marche pas mais encore merci de votre proposition.
08/05/2011, 00h47
mercatog

Citation:

erreur d exécution 9 : l indice n appartient pas à la sélection"

Adapte les noms exacts de tes feuilles (j'ai mis Employes et Resultat alors que tu as Employés et Résultat! Personnellement, j'évite les accents en vba)
Je calcule pour les ventes et les achats la somme des montants de la colonne E et les salaires de la colonne J.
08/05/2011, 00h52
sébastien.R

En effet,
J'avais simplement mis des accents sur mes feuilles Excell.
Tout marche très bien à présent.
Merci et bonne nuit.

J'ai encore une interrogation quand à la facon dont je dois faire fonctionner cette application.. Je vais devoir affecter un bouton pour mon calcul de rentabilité et je vais devoir spécifier l'année sur base de laquelle je veux que ce calcul soit effectué.. le prrogramme ne devra donc prendre en compte que la année 2008 par exemple..

Je vous soummetrai demain ma proposition pour ce problème et j'espère que vous pourrez me guider si je me trompe. bonne nuit.
08/05/2011, 01h46
mercatog

Vous n'aurez donc plus une somme mais une somme avec condition.
Regardez le lien que je vous ai donnée dans ma 2ème réponse en utilisant SommeProd (entre autre). Tu peux aussi utiliser Somme.Si.

Apparemment, vous lisez en diagonale ce qu'on vous propose. C'est une mauvaise habitude pour avancer ou une attente passive d'une solution prête.
Bonne courage pour la suite, et le courage ça se mérite.

Je n avais effectivement pas prévu qu'il faudrait modifier mon programme lorsque j'aurais à choisir le calcul en fonction de mes dates de ventes et achats de produits..

J'ai essayé d'inclure cette composante dans mon programme mais il me marque que j'ai une erreur d'exécution 13: incompatibilité de type"

je vous montre ce que j'ai essayé:
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 27 28 29 30 31 32 33 34 35 Private Sub CommandButton1_Click() Dim Ventes As Double Dim Achats As Double Dim Salaires As Double Dim FraisFixes As Double Dim Benefice As Double Dim Rentabilite As Double Dim annee As String ((annee = Mid(Range("F"), 6, 4))) annee = InputBox("L'année désirée est: ") If Val(annee) > 0 Then Ventes = Evaluate("SUMPRODUCT((Year(Ventes!E:E)=" & annee & ")*(Ventes!F:F))") Achats = Evaluate("SUMPRODUCT((Year(Achats!E:E)=" & annee & ")*(Achats!F:F))") Salaires = Application.Sum(Sheets("Employes").Range("J:J")) FraisFixes = 350000 End If Benefice = Ventes - (Achats + Salaires + FraisFixes) Select Case Benefice Case Is > 150000: Rentabilite = 0.33 * Benefice Case Is > 0: Rentabilite = 0.15 * Benefice Case Else: Rentabilite = Benefice End Select Sheets("Resultat").Range("A2").Value = Rentabilite Sheets("Resultat").Range("B2").Value = annee End Sub
Je dois donc en effet créer un bouton qui m invite à enter l'année pour ne prendre en compte que les ventesb et achats d'une année spécifique..
Mon problème est également que la cellule dans laquelle mon programme va devoir aller chercher ma date est une cellule sous la forme JJ/MM/annee.
Je pense donc à la focntion
Code:

1 2 3 Dim annee As String annee = Mid(Range("F"), 6, 4)
Mais je ne sais pas très bien où la placer dans mon code..
Un pti coup de pouce et j'aurai bientot terminé..
Merci.

Bonjour

Quand tu fais ça

Code:

1
2
3
4
5
6
Dim annee As String

annee = Mid(Range("a1"), 6, 4)
annee = InputBox("L'année désirée est: ")
If Val(annee) > 0 Then
Ventes = Evaluate("SUMPRODUCT((Year(Ventes!E:E)=" & annee & ")*(Ventes!F:F))")

, j'ai 4 remarques:
1. Annee doit être de type integer
2. tu récupère cette année de A1 puis tu l'entre à l'aide d'une inputbox. Pourquoi?
3. Dans Evaluate de ta sommeprod, tu sous entends que les dates sont en colonne E et les montants à sommer en colonnes F.
4. Somme prod nécessite des plages limités.

Ton code aurait l'allure suivantes (en tenant compte des remarques précédentes)

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
27
28
29
30
31
32
Private Sub CommandButton1_Click()
Dim Ventes As Double
Dim Achats As Double
Dim Salaires As Double
Dim FraisFixes As Double
Dim Benefice As Double
Dim Rentabilite As Double
Dim N As Long
Dim Annee As Variant
 
Annee = InputBox("L'année désirée est: ")
If Val(Annee) > 0 Then
    With Sheets("Ventes")
        N = .Cells(.Rows.Count, "E").End(xlUp).Row 'Ligne de la dernière cellule remplie de la colonne E de la feuille ventes
    End With
    Ventes = Evaluate("SUMPRODUCT((Year(Ventes!E2:E" & N & ")=" & Annee & ")*(Ventes!F2:F" & N & "))")
    With Sheets("Achats")
        N = .Cells(.Rows.Count, "E").End(xlUp).Row
    End With
    Achats = Evaluate("SUMPRODUCT((Year(Achats!E2:E" & N & ")=" & Annee & ")*(Achats!F2:F" & N & "))")
    Salaires = Application.Sum(Sheets("Employes").Range("J:J"))
    FraisFixes = 350000
    Benefice = Ventes - (Achats + Salaires + FraisFixes)
    Select Case Benefice
        Case Is > 150000: Rentabilite = 0.33 * Benefice
        Case Is > 0: Rentabilite = 0.15 * Benefice
        Case Else: Rentabilite = Benefice
    End Select
    Sheets("Resultat").Range("A2").Value = Rentabilite
    Sheets("Resultat").Range("B2").Value = Annee
End If
End Sub

A toi d'adapter, notamment les colonnes critères et celles des montants à additionner. Surtout pas de lecture en diagonale.

Edit: J'ai déclaré Annee en Variant au cas où l'utilisateur annulera la saisie par l'inputbox.

08/05/2011, 12h54
sébastien.R
Tout marche à présent pafaitement..
J'ai simplement encore un problème avec le fait que à chaque fois je ne peux calculer la rentabilité que pour une année puisque lorsque j encode une nouvelle année le programme me met le résultat à la place de l'ancien résultat.. (normal puisque je n'incrémente pas mon résultat à la fin..)
J'aimerais donc savoir comment je pourrais adapter ce code pour qu il puisse m afficher le résultat de plusieurs années dictinctes et dans l'ordre chronologique.. (je pense à utiliser un espèce de compteur qui commence à
i=0 et qui permettrait d'incrémenter les cellules de ma feuille Resultat de cette façcon:
Code:

1 2 3 Sheets("Resultat").Range("A(2+i)").Value = Rentabilite Sheets("Resultat").Range("B(2+i)").Value = Annee
Merci.

Pour à chaque fois écrire dans la ligne suivante (tu as les ingrédients)

Code:

1
2
3
4
5
With Sheets("Resultat")
    N = .Cells(.Rows.Count, "A").End(xlUp).Row    'Ligne de la dernière cellule remplie de la colonne A de la feuille Resultat
    .Range("A" & N + 1).Value = Rentabilite
    .Range("B" & N + 1).Value = Annee
End With

08/05/2011, 13h35
sébastien.R

Si je veux pouvoir écrire ma date de mon calcul dans ma feuille Resultat, quel fonction dois-je utiliser?

Code:

.Range("C" & N + 1).Value = Date du calcul ??

Mais quel est la fonction à utiliser pour trouver la date à laquele le calcul à été éffectué?
08/05/2011, 19h38
AlainTech

Bonjour,

La date du jour (date système) est donnée par la fonction Date:

Code:

.Range("C" & N + 1).Value = Date
08/05/2011, 20h03
sébastien.R
Ha ben ca va cette fois ci j'étais pas loin :mrgreen:
Merci

Dernière question pour que mon programme marche parfaitement..
Pour le moment, il me refait mon calcul même si j'ai déjà éffectué ce calcul..
J'aimerais insérer une fonction qui me dise que j'ai déjà efectué le calcul pour cette année là..

Donc ce serait du genre:
Code:

1 2 3 If année (2009 par exemple) a déjà été entrée dans mon inputbox Then MsgBox "Le calcul a déjà été effectué pour cette année"
et le programme stop.
Une idée pour ça?
08/05/2011, 23h04
patricktoulon

re

bonjour pour cela tu pourrais utiliser une variable booleene

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
27
28
29
30
31
32
33
34
35
36
37
38
39
Private Sub CommandButton1_Click()
Dim Ventes As Double, Achats As Double, Salaires As Double
Dim FraisFixes As Double, Benefice As Double, Rentabilite As Double
Dim Annee As Variant
Dim c As Range
Dim N As Long
 
Annee = InputBox("L'année désirée est: ")
If Val(Annee) > 0 Then
    Set c = Sheets("Resultat").Range("B:B").Find(Annee, LookIn:=xlValues, lookat:=xlWhole)
    If c Is Nothing Then
        With Sheets("Ventes")
            N = .Cells(.Rows.Count, "E").End(xlUp).Row 'Ligne de la dernière cellule remplie de la colonne E de la feuille ventes
        End With
        Ventes = Evaluate("SUMPRODUCT((Year(Ventes!E2:E" & N & ")=" & Annee & ")*(Ventes!F2:F" & N & "))")
        With Sheets("Achats")
            N = .Cells(.Rows.Count, "E").End(xlUp).Row
        End With
        Achats = Evaluate("SUMPRODUCT((Year(Achats!E2:E" & N & ")=" & Annee & ")*(Achats!F2:F" & N & "))")
        Salaires = Application.Sum(Sheets("Employes").Range("J:J"))
        FraisFixes = 350000
        Benefice = Ventes - (Achats + Salaires + FraisFixes)
        Select Case Benefice
            Case Is > 150000: Rentabilite = 0.33 * Benefice
            Case Is > 0: Rentabilite = 0.15 * Benefice
            Case Else: Rentabilite = Benefice
        End Select
        With Sheets("Resultat")
            N = .Cells(.Rows.Count, "A").End(xlUp).Row    'Ligne de la dernière cellule remplie de la colonne A de la feuille Resultat
            .Range("A" & N + 1).Value = Rentabilite
            .Range("B" & N + 1).Value = Annee
            .Range("C" & N + 1).Value = Date
        End With
    Else
        MsgBox "Le calcul a déjà été effectué pour l'année " & Annee
        Set c = Nothing
    End If
End If
End Sub