VBA Macro for Debt Sculpting for Solar Energy Projects

[AbacusPF]

Hello,

We have created a free-to-use, open-source, financial model. In the current model, we have fully-functioning VBA macros which can 1) sculpt debt by cutting and pasting the calculated values until the difference between the calculated and pasted value equals zero, and 2) maximizes the usage of leverage to stay within target metrics. I'm happy to say the macros work very well, but given my knowledge of VBA is intermediate, I can't help but think there is a faster or more streamlined way we could perform the calculations, and suggestions would be greatly appreciated.

Here is the code we are using for our VBA macro. The Maximize_Leverage macro takes about 30 seconds, with the Debt Sculpting Macro takes approx. 10 seconds.

Sub Debt_Sculpt()

' Debt Sculpting Macro

Dim TPC_Copy, TPC_Paste, FeeIDC_Copy, FeeIDC_Paste, Debt_Copy, Debt_Paste As Range
Dim x As Long

Set TPC_Copy = Worksheets("Calculations").Range("TPC_Copy")
Set TPC_Paste = Worksheets("Inputs").Range("TPC_Paste")
Set FeeIDC_Copy = Worksheets("Calculations").Range("FeeIDC_Copy")
Set FeeIDC_Paste = Worksheets("Calculations").Range("FeeIDC_Paste")
Set Debt_Copy = Worksheets("Calculations").Range("Debt_Copy")
Set Debt_Paste = Worksheets("Calculations").Range("Debt_Paste")
Set Macro_Test = Worksheets("Inputs").Range("Macro_Test")

x = 1

Do Until x = 20 Or Macro_Test.Value = 0
        TPC_Paste.Value = TPC_Copy.Value           'Copy & Paste Total Project Cost Amount
        FeeIDC_Paste.Value = FeeIDC_Copy.Value     'Copy & Paste Fees and IDC
        Debt_Paste.Value = Debt_Copy.Value         'Copy & Paste Debt Amount
        x = x + 1
    Loop

End Sub

Sub Maximize_Leverage()

' Macro to maximize leverage based on the Debt-Sculpting target DSCR

Application.ScreenUpdating = False
Application.DisplayStatusBar = False

Dim Max_Sculpt_Leverage_Copy, Max_Sculpt_Leverage_Paste, Max_Leverage, Sculpt_DSCR_Paste, LLCR, Target_DSCR As Range
Dim y, z As Long

Set Max_Sculpt_Leverage_Copy = Worksheets("Inputs").Range("Max_Sculpt_Leverage_Copy")
Set Max_Sculpt_Leverage_Paste = Worksheets("Inputs").Range("Max_Sculpt_Leverage_Paste")
Set Max_Leverage = Worksheets("Inputs").Range("Max_Leverage")
Set Sculpt_DSCR_Paste = Worksheets("Inputs").Range("Sculpt_DSCR_Paste")
Set LLCR = Worksheets("Inputs").Range("LLCR")
Set Target_DSCR = Worksheets("Inputs").Range("Target_DSCR")

For y = 1 To 4
    If Max_Sculpt_Leverage_Copy.Value = Max_Leverage.Value Then
        Max_Sculpt_Leverage_Paste.Value = Max_Sculpt_Leverage_Copy.Value
        If LLCR.Value >= Target_DSCR.Value Then
            Sculpt_DSCR_Paste.Value = LLCR.Value
        End If
    Else
        For z = 1 To 3
            Max_Sculpt_Leverage_Paste.Value = Max_Sculpt_Leverage_Copy.Value
        Next z
            If LLCR.Value > Target_DSCR.Value Then
                Sculpt_DSCR_Paste.Value = LLCR.Value
            ElseIf LLCR.Value <= Target_DSCR.Value Then
                Sculpt_DSCR_Paste.Value = Target_DSCR.Value
            End If
    End If
    Call Debt_Sculpt
    Next y

Application.ScreenUpdating = True
Application.DisplayStatusBar = True

End Sub

Thank you to anyone in advance!

AbacusPF

Also posted Debt Sculpting VBA for Solar Energy Project Financing [Request for general code review]