Contents
The SYD function in Excel VBA (Visual Basic for Applications) is used to calculate the depreciation of an asset for a specified period using the Sum-of-Years’ Digits depreciation method. This is not a built-in function in VBA itself but can be used by accessing Excel’s worksheet functions from VBA.
Here’s how to use it in VBA
Function CalculateSYD(Cost As Double, Salvage As Double, Life As Integer, Period As Integer) As Double
CalculateSYD = Application.WorksheetFunction.SYD(Cost, Salvage, Life, Period)
End Function
This custom VBA function, CalculateSYD, takes the cost of the asset, the salvage value at the end of the asset’s life, the life of the asset in periods (usually years), and the period for which you want to calculate depreciation.
To use the CalculateSYD function, you would call it from another VBA subroutine or function:
Sub UseSYDFunction()
Dim initialCost As Double
Dim salvageValue As Double
Dim assetLife As Integer
Dim depreciationPeriod As Integer
Dim depreciationAmount As Double
' Example values
initialCost = 10000 ' The initial cost of the asset
salvageValue = 1000 ' The salvage value of the asset at the end of its life
assetLife = 5 ' The useful life of the asset in years
depreciationPeriod = 1 ' The period for which to calculate depreciation
' Calculate depreciation
depreciationAmount = CalculateSYD(initialCost, salvageValue, assetLife, depreciationPeriod)
' Output result
Debug.Print "Depreciation for period " & depreciationPeriod & ": " & depreciationAmount
End Sub
Remember to adjust the initialCost, salvageValue, assetLife, and depreciationPeriod with the actual values for your specific case.
Please note that VBA does not check the type of variables being passed like strongly typed languages. If you mismatch the input parameters, for example, passing a string instead of an integer, it may lead to errors or incorrect results. Always make sure you pass the right parameters to functions, such as CalculateSYD in this case.
Unlock Your Potential
Excel
Access
Power BI
