How to use SYD function in VBA?

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

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:

VBA


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.

facebooklinkedin