Excel VBA Tips: A Macro to Generate Line Chart Dynamically for Loan Schedule Trend Report

The entire code shared in this video is designed to automate the process of creating a dynamic line chart based on loan data in the "Loan Report" sheet and display the chart on the "Charts" sheet. The chart shows trends in accumulated principal, accumulated interest, and loan remaining balance over time. The code is also flexible enough to handle both yearly and monthly loan schedule reports.

The VBA codes as:

Sub CreateLineChart()  

    '1. Variable Declarations

    Dim srcSheet As Worksheet

    Dim chtSheet As Worksheet

    Dim lastRow As Long

    Dim chartObj As Shape

    Dim chartDataRange As Range

    Dim xAxisRange As Range

    Dim yAxisRange As Range

    Dim colPrincipal As Variant

    Dim colInterest As Variant

    Dim colRemainingBalance, ColMonth As Variant

    '2. set source and chart sheets

    Set srcSheet = ThisWorkbook.Worksheets("Loan Report")

    Set chtSheet = ThisWorkbook.Worksheets("Charts")

    

    '3.Clear chart sheet

    chtSheet.UsedRange.Clear

    For Each chartObj In chtSheet.Shapes

        chartObj.Delete

    Next chartObj

    

    '4. Find the last row of data in column A (Date)

    lastRow = srcSheet.Cells(srcSheet.Rows.Count, "A").End(xlUp).Row

    '5. Define the range of the data for x-axis (Date)

    Set xAxisRange = srcSheet.Range("A2:A" & lastRow)

    

    '6. Find the column numbers for the Y-axis data based on header names

    colPrincipal = Application.Match("Accumulated Principal", srcSheet.Rows(1), 0)

    colInterest = Application.Match("Accumulated Interest", srcSheet.Rows(1), 0)

    colRemainingBalance = Application.Match("Loan Remaining Balance", srcSheet.Rows(1), 0)

    '7. Define the range of the data for y-axis (Period Loan Principal, Period Loan Interest, Loan Remaining Balance)

    Set yAxisRange = srcSheet.Range(srcSheet.Cells(2, colPrincipal), srcSheet.Cells(lastRow, colPrincipal))

    Set yAxisRange = Union(yAxisRange, srcSheet.Range(srcSheet.Cells(2, colInterest), srcSheet.Cells(lastRow, colInterest)))

    Set yAxisRange = Union(yAxisRange, srcSheet.Range(srcSheet.Cells(2, colRemainingBalance), srcSheet.Cells(lastRow, colRemainingBalance)))

    '8. Combine both x-axis and y-axis ranges to create the chart data range

    Set chartDataRange = Union(xAxisRange, yAxisRange)

    

    '9. Create the chart in the charts sheet

        Set chartObj = chtSheet.Shapes.AddChart(xlLineMarkers, 100, 100, 500, 300)

        chartObj.Chart.SetSourceData Source:=chartDataRange

    '10. Adjust chart properties as needed (e.g., title, axis labels, etc.)

    chartObj.Chart.HasTitle = True

    chartObj.Chart.ChartTitle.Text = "Loan Schedule Trend"

    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True

    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Payment Period"

    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True

    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Amount"

  '11. check the report is yearly or monthly schedule report

    On Error Resume Next

        ColMonth = Application.Match("Month", srcSheet.Rows(1), 0)

    On Error GoTo 0

    

    '12. Add Series Names and Data Labels based on report type (monthly or yearly)

    If IsNumeric(ColMonth) Then

        ' Add names for series in the legend

        chartObj.Chart.SeriesCollection(1).Name = "Number of Months"

        chartObj.Chart.SeriesCollection(2).Name = "Monthly Loan Principal"

        chartObj.Chart.SeriesCollection(3).Name = "Monthly Loan Interest"

        ' Add labels to each marker (data point)

        'chartObj.Chart.SeriesCollection(1).ApplyDataLabels

        'chartObj.Chart.SeriesCollection(2).ApplyDataLabels

        'chartObj.Chart.SeriesCollection(3).ApplyDataLabels

        'chartObj.Chart.SeriesCollection(4).ApplyDataLabels

       Else

        ' Add names for series in the legend

        chartObj.Chart.SeriesCollection(1).Name = "Number of Years"

        chartObj.Chart.SeriesCollection(2).Name = "Yearly Loan Principal"

        chartObj.Chart.SeriesCollection(3).Name = "Yearly Loan Interest"

        ' Add labels to each marker (data point)

        'chartObj.Chart.SeriesCollection(1).ApplyDataLabels

        chartObj.Chart.SeriesCollection(2).ApplyDataLabels

        'chartObj.Chart.SeriesCollection(3).ApplyDataLabels

        'chartObj.Chart.SeriesCollection(4).ApplyDataLabels

    End If

        ' Add names for rest series in the legend

        chartObj.Chart.SeriesCollection(4).Name = "Loan Remaining Balance"

    '13 Optionally, you can adjust other chart properties like colors, styles, etc.

        ' For example, you can use the following lines to set a specific line color:

        chartObj.Chart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 255) ' Blue color

        chartObj.Chart.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 255, 0) ' Green color

        chartObj.Chart.SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red color

    ' 14. Display completion message

        MsgBox "Loan Schedule Trend Line Chart generated successfully! ===>> Pls check the 'Charts' tab"

   

End Sub