Option Explicit
Public Sub Beispiel_Test()
Call Range("A1:B5").Clear
With Range("A1:B1")
.Cells(2).NumberFormat = "#,##0.00 €"
.Value = Array("Anfangskapital:", 2000)
End With
With Range("A2:B2")
.Cells(2).NumberFormat = "0.00"" %"""
.Value = Array("Zinssatz:", 3)
End With
With Range("A3:B3")
.Cells(2).NumberFormat = "0"" Jahre"""
.Value = Array("Laufzeit", 2)
End With
With Range("A4:B4")
.Cells(2).NumberFormat = "#,##0.00 €"
.Value = Array("Ke_VBA:", Zinseszins(Range("B1"), Range("B2"), Range("B3")))
End With
With Range("A5:B5")
.Cells(1).Value = "Ke_Formel:"
.Cells(2).NumberFormat = "#,##0.00 €"
.Cells(2).Formula2 = "=Zinseszins(B1, B2, B3)"
End With
Range("A4:B5").Font.Bold = True
Columns("A:B").AutoFit
End Sub
Public Function Zinseszins(ByVal Ka, ByVal p, ByVal n) As Variant
Dim Ke As Currency
Ka = CCur(Ka)
p = CSng(p)
n = CByte(n)
If p < 0 Then
Call Err.Raise(XlCVError.xlErrValue)
End If
Ke = Ka * (1 + p / 100) ^ n
Zinseszins = Ke
End Function