Блог

Я хотел бы объявить переменную с именем «taux», чем дальше я буду использовать ее в своих вычислениях.

 Dim numLigne, pageClient
    numLigne = 2
    Set pageClient = Sheets.Add(After:=Sheets("paramètre"))
    
    '1ere étape: Création a client file from a "parameter" sheet
    
    If Range("paramètre!B" amp; numLigne) = "" Then
    Else
    pageClient.Name = Range("paramètre!B" amp; numLigne)
    End If


    '2ème étape Copy Value from sheet "paramètre"
    
    Range("A1:D7") = Range("paramètre!A1:D6").Value
    Range("D4") = "Mensualité"
    Range("D5") = "Trimestriel"
    Range("D6") = "Semestriel"
    Range("D7") = "Annuel"
    
    '3ème étape: Calculating monthly, quarter, semester, and annual
    
    Dim taux, capital, duree
    
    capital = Range("B4")
    duree = Range("B6")
    
Dim taux 

if Range ("B7") <> "" then 'For information the value range ("B7") depends on the value inserted by userform, this value is related to insurance fee, if the client doesn't take any insurance, the appeared value would be #N/A
taux = Range ("B7") 
Else
taux = Range ("B5")
End If 
    
    Dim mensualité
    
    mensualité = -Pmt(taux / 12, duree * 12, capital)
    Range("E4") = mensualité
    
    Dim trimestriel
    
    trimestriel = -Pmt(taux / 3, duree * 3, capital)
    Range("E5") = trimestriel
    
    Dim semestriel
    
    semestriel = -Pmt(taux / 2, duree * 2, capital)
    Range("E6") = semestriel
    
    Dim annuel
    annuel = -Pmt(taux / 1, duree * 1, capital)
    Range("E7") = annuel
    
    '4ème étape: faire un tableau d'amortissement
    
    Dim numPeriode, interest, rembCap
    
    For numPeriode = 1 To duree * 12
    
        Range("B" amp; numPeriode   11) = capital

        interest = capital * taux / 12
        rembCap = mensualité - interest
        capital = capital - rembCap

        Range("A" amp; numPeriode   11) = numPeriode
        Range("C" amp; numPeriode   11) = interest
        Range("D" amp; numPeriode   11) = rembCap
    
    Next

End Sub
  

Из того, что я вижу в вашем коде, у вас была опечатка и двойной dim. Я исправил эти и несколько других вещей:

• опечатка ( captial вместо capital ).
• удален двойной dim для taux .
• добавлены wb , и ws поэтому на диапазоны правильно ссылаются.
• определен каждый dim.
• Вместо использования <> "" или = "" , изменено на not(isempty()) / isempty() .
• изменены ссылки на "paramètre!B" to wb.WorkSheets("paramètre").range() .
• добавлено exit sub , когда "paramètreB" amp; numLigne пусто.

 Option Explicit                                                                                                     ' forces declaration of all variables

Sub Mechro()

    Dim numLigne As Integer
    Dim pageClient As Object
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet
    Dim capital As Integer
    Dim duree As String
    Dim taux As Integer
    Dim mensualité As Integer
    Dim semestriel As Integer
    Dim trimestriel As Integer
    Dim annuel As Integer
    Dim numPeriode As Integer
    Dim interest As Integer
    Dim rembCap As Integer
        
    numLigne = 2
    Set pageClient = wb.Sheets.Add(After:=wb.Worksheets("paramètre"))
    
    '1ere étape: Création a client file from a "parameter" sheet
    
    If Not (IsEmpty(wb.WorkSheets("paramètre").Range("B" amp; numLigne))) Then
        pageClient.Name = wb.WorkSheets("paramètre").Range("B" amp; numLigne)
        Set ws = wb.Worksheets(ActiveSheet.Name)
    Else
        MsgBox "paramètre!B" amp; numLigne amp; " is empty. Exiting sub"
        Exit Sub
    End If


    '2ème étape Copy Value from sheet "paramètre"
    
    ws.Range("A1:D7") = wb.Worksheet("paramètre").Range("A1:D6").Value
    ws.Range("D4") = "Mensualité"
    ws.Range("D5") = "Trimestriel"
    ws.Range("D6") = "Semestriel"
    ws.Range("D7") = "Annuel"
    
    '3ème étape: Calculating monthly, quarter, semester, and annual
       
    capital = ws.Range("B4")
    duree = ws.Range("B6")
    

    If Not (IsEmpty(ws.Range("B7"))) Then 'For information the value range ("B7") depends on the value inserted by userform, this value is related to insurance fee, if the client doesn't take any insurance, the appeared value would be #N/A
        taux = ws.Range("B7")
    Else
        taux = ws.Range("B5")
    End If
    
    mensualité = -Pmt(taux / 12, duree * 12, capital)
    ws.Range("E4") = mensualité
    
    trimestriel = -Pmt(taux / 3, duree * 3, capital)
    ws.Range("E5") = trimestriel
    
    semestriel = -Pmt(taux / 2, duree * 2, capital)
    ws.Range("E6") = semestriel
    
    annuel = -Pmt(taux / 1, duree * 1, capital)
    ws.Range("E7") = annuel
    
    '4ème étape: faire un tableau d'amortissement
    
    For numPeriode = 1 To duree * 12
    
        ws.Range("B" amp; numPeriode   11) = capital

        interest = capital * taux / 12
        rembCap = mensualité - interest
        capital = capital - rembCap

        ws.Range("A" amp; numPeriode   11) = numPeriode
        ws.Range("C" amp; numPeriode   11) = interest
        ws.Range("D" amp; numPeriode   11) = rembCap
    
    Next

End Sub