créer une fonction avec un nombre indéterminé de variables

Version imprimable

Bonjour,

Je débute en VBA (2 semaines), j'ai créé une sous routine permettant de calculer la VAN :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Sub VAN() Dim n As Integer, t As Double, I As Double, j As Integer, VAN As Double, flux() As Double n = InputBox("Saisir le nombre de flux") t = InputBox("saisir le taux d'actualisation") I = InputBox("Saisir le montant investi") ReDim flux(n) For j = 1 To n flux(j) = InputBox("saisir le flux " & j) Next VAN = -I For j = 1 To n VAN = VAN + flux(j) / (1 + t) ^ j Next VAN = FormatNumber(VAN, 2) MsgBox ("La VAN est de ") & VAN
Je voudrais faire la même chose mais en fonction =VANa(taux_actualisation; investissement; flux1; flux2; flux3; ...)
Je ne sais pas comment mettre flux1, flux2 ..., Il peut avoir 1 flux comme 10, 20 ou plus
Merci d'avance pour votre aide.

15/03/2016, 19h59
joe.levrai

Bonjour,

la fonction Excel VAN() ne suffit pas ?

à titre subsidiaire, quand on veut un nombre indéfini d'arguments dans une fonction Excel, le plus simple est d'utiliser un seul argument de Type Range qui représente la plage de cellule où sont écrites les valeurs. Ensuite on a accès au nombre d'arguments (Plage.Cells.Count) et à l'ensemble des valeurs (Plage.Cells(l,c).Value ou une boucle For Each Cell in Plage.Cells ou encore en mettant tout dans un tableau ... Tabl = Plage.Value)

L'idéal étant de présenter une plage en une seule colonne ou une seule ligne (sinon c'est un peu plus lourd à gérer dans la fonction)
15/03/2016, 20h43
panebfred

Non la fonction VAN intégré à Excel ne me suffit pas, l'investissement initiale n'est pas prise en compte. Il suffit de retrancher la somme aux résultat, mais je trouve plus intéressant de refaire une fonction.

Merci pour ta réponse, ça va bien m'aider :)

Bonsoir,
L'Instruction ParamArray permet de passer un nombre de termes indéfini dans une fonction! Tu trouveras un exemple ci dessous!

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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
Function Highlander(Init As Boolean, ParamArray Plage()) As Boolean
'..................................................
'La méthode Highlander, il ne peut en rester qu'un.
'Retourne True si doublon.
'..................................................
 
Static CollectDoublon As Collection
Dim T As String
Dim PlageIndex As Long
Dim myPlage As Range
Dim Col As Integer
Dim Tableau
If Init = False Then
Init = True
   Set CollectDoublon = Nothing
   Set CollectDoublon = New Collection
End If
 
 
T = "T"
For PlageIndex = 0 To UBound(Plage)
If TypeName(Plage(PlageIndex)) = "Range" Then
       Set myPlage = Plage(PlageIndex)
 
       For Col = 1 To myPlage.Columns.Count
        T = T & "_" & Trim("" & myPlage(1, Col))
       Next
    Else
        If TypeName(Plage(PlageIndex)) = "Variant()" Then
           Tableau = Plage(PlageIndex)
        Else
            If TypeName(Plage(PlageIndex)) Like "*()" Then
                Tableau = Plage(PlageIndex)
            Else
                Tableau = Split(Plage(PlageIndex) & ";", ";")
            End If
        End If
         For Col = 0 To UBound(Tableau)
           If Trim("" & Tableau(Col)) <> "" Then T = T & "_" & Trim("" & Tableau(Col))
         Next
    End If
Next
On Error Resume Next
CollectDoublon.Add T, T
If Err <> 0 Then Highlander = True
On Error GoTo 0
End Function
Sub test()
Dim Init As Boolean
Dim MyRange As Range
Dim L As Long
 
Set MyRange = ActiveSheet.UsedRange
Debug.Print "********************"
Debug.Print "Sur une colonne"
For L = 1 To MyRange.Rows.Count
    Debug.Print Highlander(Init, MyRange(L, 1))
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur Deux colonnes"
For L = 1 To MyRange.Rows.Count
    Debug.Print Highlander(Init, MyRange(L, 1), MyRange(L, 3))
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur une plage colonne"
For L = 1 To MyRange.Rows.Count
    Debug.Print Highlander(Init, ActiveSheet.Range(MyRange(L, 1), MyRange(L, 2)))
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur deux plage colonne"
For L = 1 To MyRange.Rows.Count
    Debug.Print Highlander(Init, ActiveSheet.Range(MyRange(L, 1), MyRange(L, 2)), ActiveSheet.Range(MyRange(L, 3), MyRange(L, 4)))
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur (une plage colonne) et 3 collones"
For L = 1 To MyRange.Rows.Count
    If Highlander(Init, ActiveSheet.Range(MyRange(L, 1), MyRange(L, 2)), MyRange(L, 3), MyRange(L, 4), MyRange(L, 5)) = True Then Debug.Print "Doublon"
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur 1 Text"
For L = 1 To 10
    Debug.Print Highlander(Init, "Test")
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur 2 Text"
For L = 1 To 10
    Debug.Print Highlander(Init, "Test", "test")
Next
Init = False
Debug.Print "********************"
Debug.Print "Sur un Text et une colone"
For L = 1 To MyRange.Rows.Count
    Debug.Print Highlander(Init, "test", MyRange(L, 1))
Next
'***************************************************
'Dans cet exemple nous allons dé-doublonner en fonction de la colonne A :
Dim Supp() As Long
Dim IdSupp As Long
ReDim Supp(IdSupp)
Init = False
For L = 1 To MyRange.Rows.Count
    If Highlander(Init, MyRange(L, 1)) = True Then
        IdSupp = IdSupp + 1
        ReDim Preserve Supp(IdSupp)
        Supp(IdSupp) = L
    End If
Next
For L = UBound(Supp) To 1 Step -1
    MyRange(Supp(L), 1).EntireRow.Delete
Next
'***************************************************
'Dé-doublonnage d'un fichier CSV avant import :
Dim NumFichier As Integer
NumFichier = FreeFile
Dim TextLine
Init = False
 
Open "c:\MyRepp\My.csv" For Input As #NumFichier    ' Ouvre le fichier.
Do While Not EOF(NumFichier)    ' Effectue la boucle jusqu'à la fin du fichier.
    Line Input #NumFichier, TextLine        ' Lit la ligne dans la variable.
    TextLine = Split(TextLine, ";")
    If Highlander(Init, TextLine) = False Then
        L = ActiveSheet.UsedRange.Rows.Count + 1
        ActiveSheet.Range(ActiveSheet.Cells(L, 1), ActiveSheet.Cells(L, UBound(TextLine) + 1)) = TextLine
     End If
Loop
Close #NumFichier    ' Ferme le fichier.
'***************************************************
'Vérification de l'existant avant import CSV
Init = False
Set MyRange = ActiveSheet.UsedRange
Debug.Print "********************"
Debug.Print "Sur une colonne"
For L = 1 To MyRange.Rows.Count
    Debug.Print Highlander(Init, MyRange.Range(MyRange(L, 1), MyRange(L, MyRange.Columns.Count)))
Next
 
Open "c:\MyRepp\My.csv" For Input As #NumFichier    ' Ouvre le fichier.
Do While Not EOF(NumFichier)    ' Effectue la boucle jusqu'à la fin du fichier.
    Line Input #NumFichier, TextLine        ' Lit la ligne dans la variable.
    TextLine = Split(TextLine, ";")
    If Highlander(Init, TextLine) = False Then
        L = ActiveSheet.UsedRange.Rows.Count + 1
        ActiveSheet.Range(ActiveSheet.Cells(L, 1), ActiveSheet.Cells(L, UBound(TextLine) + 1)) = TextLine
     End If
Loop
Close #NumFichier    ' Ferme le fichier.
 
End Sub

15/03/2016, 20h53
joe.levrai

Les fonctions Excel étant déjà compilées, elles sont nettement plus performantes qu'une réécriture de la fonction complète en VBA (sans parler des erreurs à prévoir).

A la limite, s'il manque une étape, pourquoi ne pas utiliser la fonction VAN dans ta fonction personnalisée.
Regarde du côté de Application.WorksheetFunction.NPV() qui représente l'utilisation de la fonction VAN() directement en VBA si besoin

Bonsoir
On peut utiliser ParamArray

Exemple

Code:

1
2
3
4
5
6
7
8
9
10
Function VAN(ByVal Tx As Double, ByVal Invest As Double, ParamArray Flux()) As Double
Dim S As Double
Dim j As Integer
 
S = -Invest
For j = 0 To UBound(Flux)
    S = S + Flux(j) / (1 + Tx) ^ j
Next j
VAN = S
End Function

Edit: En retard

15/03/2016, 23h40
panebfred

Merci à tous, j'ai compris comment fonctionné ParamArray
J'ai repris ton code Mercator mais en changeant 1 ligne

Code:

S = S + Flux(j) / (1 + Tx) ^ (j + 1)

vu que j commence à 0, le premier flux actualisé est divisé par 1,1^0=1, au lieu de 1,1^1=1,1

Tu compris ParamArray, pas tout à fait!
Si ParamArray permet de passer plusieurs paramètres à une fonction, il permet de passé plusieurs types de paramètres à une fonction!
A1:C1;Y1:AZ1;ZY1! Et oui plutôt que d'énumérer toutes les cellules on énumère plusieurs plage de cellule!

La proposition de Joe était très bonne, mais elle occultait le fait que les cellule ou plage de cellules pouvais ne pas être contigu. Mais il ne faut pas tomber dans l'excès inverse et énumérer le cellules même quand elles sont contigus!
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Function VAN(ByVal Tx As Double, ByVal Invest As Double, ParamArray Flux()) As Double Dim S As Double Dim j As Integer,J2 as integer S = -Invest For j = 0 To UBound(Flux) for j2 =1 to Flux(j).count S = S + Flux(j)(J2).value / (1 + Tx) ^ j next Next j VAN = S End Function