I am working at a project where we implemented 10 different UDFs which gave us high flexibility and a clean code but basically screwed the file up as the file got super slow in the end: The problem is, that I have to load assumptions of 7 sheets for the calculation into VBA (I tried with select ranges first, but this was even slower). Then these assumptions are used to calculate some financial worksheet formulas. The biggest problem was that I had to make sure VBA knows which assumption block to use for each function accordingly to the calling cell. Hence, I implemented a header-selection process. But I am not sure whether it is smarter to have a select case in front of all the UDFs (which count up to about 1 million in the workbook in total) or to calculate the sheets one by one directly after one another and safe the select case at the beginning of each UDF.I lack the understanding of what causes the biggest issue due  to calculation speed here. I measured the calculation time for each section in the sheets (of which all 7 are from the exact same structure) and there are no significant differences that I could point out bottlenecks. The UDFs are all of a similar set up.I guess there might be an architectural problem of my code, which I just do not see. There must be a smarter way of setting everything up! Please, any help is much appreciated. The first way of setting this up looked like this:Public Sub LoadAllCntrAssumptions()Dim assumptions As Range, EndCell As RangeDim ModelC1 As Worksheet, ModelC2 As Worksheet, ModelC3 As Worksheet...Set ModelC1 = Worksheets(Model C1): Set ModelC2 = Worksheets(Model C2):  Set ModelC3 = Worksheets(Model C3)...With ModelC1        Set EndCell = ModelPL.Cells.Find(MODEL, LookIn:=xlValues, Searchorder:=xlByColumns) ' Setting the end of the range once as the Countrysheets are all of the sam structure        Set assumptions = .Range(.Cells(1, 1), .Cells(EndCell.Row, 130))        assumptions.Calculate        Set assumptions = .Range(.Cells(1, 1), .Cells(EndCell.Row, 130))        C1Array = assumptionsEnd WithWith ModelC2        Set assumptions = .Range(.Cells(1, 1), .Cells(EndCell.Row, 130))        assumptions.Calculate        Set assumptions = .Range(.Cells(1, 1), .Cells(EndCell.Row, 130))        C2Array = assumptionsEnd WithWith ModelC3        Set assumptions = .Range(.Cells(1, 1), .Cells(EndCell.Row, 130))        assumptions.Calculate        Set assumptions = .Range(.Cells(1, 1), .Cells(EndCell.Row, 130))        C3Array = assumptions                                                           ' I safed a certain range as assumptions, uploaded it and safed it as C()Array in order to use it for the following calculation.End With'...again same for seven modelsCalculateEnd SubThe assumptions I have to load are consisting formulas 9calculation set manually) that is why I have to calculate them first before loading as Array. After saving the model assumptions in different Arrays, I put the select case in front of the UDFs. The following shows the code of one of the 10 functions. They are all of a similar set-up. (There should be room for improvement with these functions but I simply do not know better)Function PerformingPrinc(Interest As Object, Default_1st As Object, Duration      As Object, Issuance As Object, TimeToRepay As Integer) As Double ' Function for     cumulative performing principal calculationIf TimeToRepay <= 0 Then  defining that there is only positive repayment timePerfPrinc = CVErr(xlErrValue)ElseA = 1 'setting case varibales to 1 -> will be used later to structure the     default probabilitiesB = 1C = 1D = 1E = 1F = 1PP = 0IntrRow = Interest.Row ' row 39DefRow = Default_1st.Row ' Row 59IssRow = Issuance.Row ' row 282LastColumn = Interest.Column - TimeToRepayDurRow = Duration.Row ' row 54LatestP = LastColumn - 60Pcount = 1Country = Interest.Worksheet.CodeName ' in order to specify which sheet is calling right nowSelect Case CountryCase Sheet37 ' Model C1MyArray = C1ArrayCase Sheet38 ' Model C2MyArray = C2ArrayCase Sheet39 ' Model C3MyArray = C3ArrayCase Sheet5 ' Model C4MyArray = C4ArrayCase Sheet36 ' Model C5MyArray = C6ArrayCase Sheet4 ' Model C7MyArray = C7ArrayCase Sheet3 ' Model C8MyArray = C8ArrayEnd Select    On Error Resume Next     For i = (LastColumn) To (3) Step -1  ' looping back from the current column of the calling cell until column C        Select Case Pcount            Case 1 ' for first i...            A = (1 - MyArray(DefRow, i)) ' Saving A as prob of defautlt            PP = PP + (MyArray(IssRow, i) + WorksheetFunction.CumPrinc(MyArray(IntrRow, i), MyArray(DurRow, i), MyArray(IssRow, i), 1, Pcount, 0)) * A            Pcount = Pcount + 1            Case 2 ' for second i...                For x = 0 To 1    ' calculating B prob for Default                B = B * (1 - MyArray(x + DefRow, i)) ' For x = 59 (*) for x = 60 = B                Next x            PP = PP + (MyArray(IssRow, i) + WorksheetFunction.CumPrinc(MyArray(IntrRow, i), MyArray(DurRow, i), MyArray(IssRow, i), 1, Pcount, 0)) * B            Pcount = Pcount + 1            Case 3 ' for 3rd. i ...                For x = 0 To 2  ' calculating C prob for Default                C = C * (1 - MyArray(x + DefRow, i))                Next x            PP = PP + (MyArray(IssRow, i) + WorksheetFunction.CumPrinc(MyArray(IntrRow, i), MyArray(DurRow, i), MyArray(IssRow, i), 1, Pcount, 0)) * C            Pcount = Pcount + 1            Case 4 ' for 4th. i...                For x = 0 To 3  ' calculating D prob for Default                D = D * (1 - MyArray(x + DefRow, i))                Next x            PP = PP + (MyArray(IssRow, i) + WorksheetFunction.CumPrinc(MyArray(IntrRow, i), MyArray(DurRow, i), MyArray(IssRow, i), 1, Pcount, 0)) * D            Pcount = Pcount + 1            Case 5  ' for 5th. i...                For x = 0 To 4  ' calculating E prob for Default                E = E * (1 - MyArray(x + DefRow, i))                Next x            PP = PP + (MyArray(IssRow, i) + WorksheetFunction.CumPrinc(MyArray(IntrRow, i), MyArray(DurRow, i), MyArray(IssRow, i), 1, Pcount, 0)) * E            Pcount = Pcount + 1            Case Is >= 6 'for  6th. i...                F = 1                For x = 0 To 4  ' calculating F prob for Default                F = F * (1 - MyArray(x + DefRow, i))                Next x            PP = PP + (MyArray(IssRow, i) + WorksheetFunction.CumPrinc(MyArray(IntrRow, i), MyArray(DurRow, i), MyArray(IssRow, i), 1, Pcount, 0)) * F * (1 - MyArray(DefRow + 5, i)) ^ (Pcount - 5)            Pcount = Pcount + 1        End Select    Next i 'closing the loopI changed the First Assumption loading structure to this Macro, which works with a For Each loop and is much faster. Accordingly, the UDFs do not have the Case Selection on top anymore, but other than that stay exactly the same. New code for loading the assumptions:    For Each ws In ActiveWorkbook.WorksheetsWith wsSet EndCell = ws.Cells.Find(MODEL, LookIn:=xlValues, Searchorder:=xlByColumns)       If EndCell Is Nothing Then GoTo Line1       Set assumptions = ws.Range(ws.Cells(1, 1), ws.Cells(EndCell.Row, 130))       assumptions.Calculate       Set assumptions = ws.Range(ws.Cells(1, 1), ws.Cells(EndCell.Row, 130))       MyArray = assumptions      ws.EnableCalculation = False      ws.EnableCalculation = True      ws.CalculateEnd IfEnd WithNext wsThe Workbook takes now about 6 minutes to calculate (before the latest change it was 30 minutes).  would need help with the general structure of solving the issue that I have 7 sheets with different assumptions and these UDFs where I did not find a faster way of designing them. I tried with simple Cell select Functions and later with array (range) functions but these are even slower as we always have to load ranges or cells into vba, calculate and write output back. Maybe there is a way of loading the 7 sheets in total into VBA and calculate everything of the sheets (including regular sums etc.) within VBA and writing it back after the calculation process? Or to set up the calculation of the functions much more efficient. But I do not know whether it is possible and how... I just started coding some weeks ago.Any help is much appreciated, Thanks in advance! If there is any clarification needed, I am happy to provide more details. BUT it is already a hell load of information! :)
UDFs with Header-Case selection is too slow
performance;beginner;array;vba;excel