Contents
The DDB (Double Declining Balance) function is part of the Financial functions category in Excel VBA. It calculates the depreciation of an asset for a specified period using the Double-Declining Balance Method or some other method you specify.
Here’s how to use the DDB function in VBA:
Syntax:
DDB(Cost, Salvage, Life, Period, [Factor])
- Cost is the initial cost of the asset.
- Salvage is the value of the asset at the end of depreciation (salvage value).
- Life is the number of periods over which the asset is being depreciated (useful life of the asset).
- Period is the period for which you want to calculate the depreciation.
- [Factor] is an optional argument that specifies the rate at which the balance declines. If omitted, it assumes the double-declining rate (2).
Here’s an example of using the DDB function in VBA:
Sub CalculateDepreciation()
Dim initialCost As Double
Dim salvageValue As Double
Dim usefulLife As Double
Dim period As Double
Dim factor As Double
Dim depreciationAmount As Double
' Assign values to variables
initialCost = 10000 ' The cost of the asset
salvageValue = 1000 ' The salvage value of the asset
usefulLife = 5 ' The useful life of the asset in years
period = 1 ' The period for which to calculate depreciation
factor = 2 ' The factor to use (optional, default is 2 for double-declining)
' Calculate depreciation using DDB function
depreciationAmount = Application.WorksheetFunction.DDB(initialCost, salvageValue, usefulLife, period, factor)
' Print the result
MsgBox "The depreciation for period 1 is: " & depreciationAmount
End Sub
When this VBA code runs, it will use the DDB function to calculate the depreciation for the first period of the asset’s life and then display the result in a message box.
Note that when using Application.WorksheetFunction, if an error occurs (such as invalid arguments being supplied), VBA will throw a run-time error. To handle this gracefully, you can use error handling within your code blocks, or consider a different approach, such as using Application.Evaluate which can avoid run-time errors and return an error value instead that you can test for and handle accordingly.
Unlock Your Potential
Excel
Access
Power BI
