Hi guys.

I am pmmd861, I am new here. Nice to meet you all!

Could anyone help me with this VBA Excel code, please?

So, i ve got the excel spreadsheet from someone else, and i need to nuderstand the code there so that i could slightly rebuild it in the future. I understand everything in the code, but there is some piece of code that i ve never seen and i have no idea how it works!

Here is the extract from the program.

In the Calibrate sub i have the code

Sheets("EL 1").Select
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "1"

This bit is simple.

But when

ActiveCell.FormulaR1C1 = "1"

is executed the code changes the value in the cell H9 AND after that CALLS the user defined FUNCTION in another module! My question is WHY? This is not how you call the function, right? Does it have something to do with windows programming language (i think it called API functions)?

Also, it repeats the function many times, as if it is driven by some external loop (but there is no any cpde the loop).

I tried to delete ALL the code in ALL modules and just to leave this bit - it still call the function in the same way.

Also, there ii the same code for another sheet, but on that sheet when one cell is changes it calls the functuion and when another cell is changed it does NOT call this function? PLease, give me a hint where to look for the answer! I searched for Application. properties in the code and in the ThisWorkbook module and didnt find anything! Also, there no any classes in this project.

Does this behaviour resemble something that you know?

The function that is called is:

Function fnRiskWeightedAssetsCardsOvers(EAD As Double, LGD As Double, PD As Double) As Double
    
    On Error GoTo Err_fnRiskWeightedAssetsCardsOvers
    
    Dim K As Double
    
    K = fnCapitalReqCardsOver_K(LGD, PD)
    
    fnRiskWeightedAssetsCardsOvers = K * 12.5 * EAD
    
    Exit Function

Err_fnRiskWeightedAssetsCardsOvers:
    
     fnRiskWeightedAssetsCardsOvers = 0

End Function

Function fnRiskWeightedAssetsLoans(EAD As Double, LGD As Double, PD As Double) As Double
    
    On Error GoTo Err_fnRiskWeightedAssets
    
    Dim K As Double
    
    K = fnCapitalReqLoans_K(LGD, PD)
    
    fnRiskWeightedAssetsLoans = K * 5 * EAD
    
    Exit Function

Err_fnRiskWeightedAssets:
    
     fnRiskWeightedAssetsLoans = 0

End Function
 
Function fnCapitalReqCardsOver_K(LGD As Double, PD As Double) As Double

    With WorksheetFunction
        fnCapitalReqCardsOver_K = LGD * Sqr(0.25) - PD * LGD
    End With

End Function

Function fnCapitalReqLoans_K(LGD As Double, PD As Double) As Double

    With WorksheetFunction
        fnCapitalReqLoans_K = LGD * .NormSDist(1 / Sqr(1 - Corr(PD)) - PD * LGD
    End With

End Function

Function fnPDLoans(PD As Double) As Double

    With WorksheetFunction
        fnPDLoans = .NormSDist(1 / Sqr(1 - Corr(PD)) * .NormSInv(PD) + Sqr(Corr(PD)) / Sqr(1 - Corr(PD)) * .NormSInv(0.999)) - PD
    End With

End Function

Function Corr(PD As Double) As Double
    Corr = (1 - Exp(-35 * PD)) / (1 - Exp(-35))

End Function

Thankx for your help guys!

pmmd861