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
| Public Function ExchangeRate(EnterDate As Date, EnterCurrency As String) As Double
Dim a As Integer
Dim varDate As Date
Dim varDateplus As Date
Dim varDatemoins As Date
Dim dNumCur As Single
Dim iNumCurDate As Integer
'principe : on arrondit les dates au premier du mois pour pouvoir ensuite parcourir le temps jusqu'à trouver la bonne
'date. Ceci implique par contre que les taux de change entrés le soient au 01/XX/XXXX
If EnterCurrency <> "" Then
varDate = EnterDate
a = 0
'initialise la variable date à la date d'investissement/sortie et on la ramène au premier du mois
varDate = DateSerial(Year(varDate), Month(varDate), 1)
'création de varibles qui vont s'incrémenter et se décrémenter pour parcourir la table des dates afin de trouver la date la plus prochce
If Month(varDate) = 12 Then
varDateplus = DateSerial(Year(varDate) + 1, 1, 1)
Else
varDateplus = DateSerial(Year(varDate), Month(varDate) + 1, 1)
End If
If Month(varDate) = 1 Then
varDatemoins = DateSerial(Year(varDate) - 1, 12, 1)
Else
varDatemoins = DateSerial(Year(varDate), Month(varDate) - 1, 1)
End If
'tant que date plus proche non trouvée (a=0) on rajoute/enlève un mois aux variables
Do While (a = 0)
If IsNull(DLookup("[NumAutoDate]", "DateCurrency", "[DateCur] = #" & Format(varDate, "mm/dd/yyyy") & "#")) = "True" Then
If IsNull(DLookup("[NumAutoDate]", "DateCurrency", "[DateCur] = #" & Format(varDateplus, "mm/dd/yyyy") & "#")) = "True" Then
If IsNull(DLookup("[NumAutoDate]", "DateCurrency", "[DateCur] = #" & Format(varDatemoins, "mm/dd/yyyy") & "#")) = "True" Then
If Month(varDatemoins) = 1 Then
varDatemoins = DateSerial(Year(varDatemoins) - 1, 12, 1)
Else
varDatemoins = DateSerial(Year(varDatemoins), Month(varDatemoins) - 1, 1)
End If
Else
a = 1
varDate = varDatemoins
End If
If Month(varDateplus) = 12 Then
varDateplus = DateSerial(Year(varDateplus) + 1, 1, 1)
Else
varDateplus = DateSerial(Year(varDateplus), Month(varDateplus) + 1, 1)
End If
Else
a = 1
varDate = varDateplus
End If
Else
a = 1
End If
Loop
iNumCurDate = (DLookup("[NumAutoDate]", "DateCurrency", "[DateCur] = #" & Format(varDate, "mm/dd/yyyy") & "#"))
ExchangeRate = (DLookup("[CurrencyeRateR]", "CurrencyRate", "[CurrencyDate] = " & iNumCurDate & "And [CurrencyR] ='" & EnterCurrency & "'"))
'sinon c'est deja en euro
Else
ExchangeRate = 1
End If |
Partager