UDF recalculation and speed

[Andrew Fergus]

Hi

I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line

Application.Volatile

to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :

Sub DropDown4_Change()

Application.CalculateFull

End Sub

but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.

So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?

And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?

TIA, Andrew

 

[Andrew Fergus]

Update : I think I have a partial solution. The UDF's are only used for the regional reports. It turns out the Application.Volatile statement is working and making the workbook performance really awful. So I've removed those and put a button onto the relevant worksheets with a recalc function so the user can dictate when the recalcs occur for the regional reports - everything else is auto and still works ok.

But the performance of the recalc is still pretty bad due to the sheer volume of data and the number of UDF's. Following is an example of 1 of 12 UDF's - each one is used about 100 times because I'm trying to transform the data from the raw data format (bbbbaaaasss where bbbb is the branch code, aaaa is the account code and sss is the subaccount code) to a summarised / grouped format. To put this into context, there are 2 hundred lines of raw data for the budget for each month and up to 2 thousand lines of raw data for each month for the actual numbers (for both this year and last year). Unfortunately the volume data is what it is and I have to work with it in this way.

Here is one of the UDF's. Is there any way I can speed it up?

Function YTDNorthBudget(AcctCode As String) As Single

Dim MonthNum As Integer, _
    MyValue As Single, _
    OuterLoop As Integer, _
    InnerLoop As Single, _
    InnerLoopLimit As Single, _
    MyRefCol As String, _
    MyAddCol As String

'Turned this off because it was killing the spreadsheet
'Have put a recalc button onto the regional P&L worksheets instead
'Application.Volatile

MyValue = 0
'Get the month number selected by the drop down box
MonthNum = ThisWorkbook.Sheets("Monthly_P&L").Range("I1").Value

With ThisWorkbook.Sheets("RawData_Budget")

    'Loop through the months
    For OuterLoop = 1 To MonthNum
    
        'Get the column of account codes (e.g. month 1 = A, month 2 = C etc)
        MyRefCol = Chr((2 * OuterLoop) + 63)
        'Get the column of values (e.g. month 1 = B, month 2 = D etc)
        MyAddCol = Chr((2 * OuterLoop) + 64)
        'Find the last row for the current column of data
        InnerLoopLimit = .Range(MyRefCol & "2").End(xlDown).Row
        
        'Loop through the rows
        For InnerLoop = 2 To InnerLoopLimit
        
            'Match the account code
            If Mid(.Range(MyRefCol & InnerLoop).Value, 5, 4) = AcctCode Then
                
                'Match the branch numbers
                If Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5701" _
                Or Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5702" _
                Or Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5711" _
                Or Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5712" _
                Or Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5713" _
                Or Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5722" _
                Or Left(.Range(MyRefCol & InnerLoop).Value, 4) = "5724" Then
                    
                    'Store the matched value
                    MyValue = MyValue + .Range(MyAddCol & InnerLoop).Value
                
                End If
                
            ElseIf Int(Mid(.Range(MyRefCol & InnerLoop).Value, 5, 4)) > Int(AcctCode) Then
                
                'Given the accounts are in ascending order, don't bother testing the rest of the range
                InnerLoop = InnerLoopLimit
            
            End If
        Next
    Next
End With

YTDNorthBudget = MyValue

End Function

I'd be happy to replace this with a suitable formula but the main issue is I don't know in advance exactly how many lines of data there will be and secondly the proprietary ODBC / query programme used to extract the data from the mainframe has the awful habit of inserting the raw data into new rows which forces the dependant formulae to grow in size unnecessarily. So this comes back to the second of my original questions, can a UDF be consrtucted to work faster than an array formula?

TIA, Andrew

 

[erik.van.geit]

Hi, Andrew,

perhaps this will have to be solved in team
some remarks to do my part of the job

REMARK 1
7 times within each inner-loop you are retrieving a cellvalue and searching for the left part

Left(.Range(MyRefCol & InnerLoop).Value, 4)

if this needs to be done for some 1000 rows multiplied by the outerloop multiplied by the number of cells using this UDF, I wouldn't like to be your computer

Sub test()
Dim i As Long
Dim timit As Double
timit = Timer
    For i = 1 To 10000
    myval = Left(Range("A1"), 4)
    Next i
MsgBox Timer - timit
End Sub

this take 1/3 of a second for me (could be faster on more recent machine)

solution
1. put the value in a variable
or better
2. use match (put different numbers in an array)

REMARK 2
just noticed you are using the same range before those OR-lines

If Mid(.Range(MyRefCol & InnerLoop).Value, 5, 4) = AcctCode Then

and after

ElseIf Int(Mid(.Range(MyRefCol & InnerLoop).Value, 5, 4)) > Int(AcctCode) Then

so anyway put that in a variable

cellvalue =.Range(MyRefCol & InnerLoop).Value

REMARK 3
the most important remark
putting the "range to loop" in an array before searching the match would be much faster

arr = .Range(MyRefCol & "2:" & MyRefCol & InnerLoop).Value

see http://puremis.net/excel/code/053.shtml

kind regards,
Erik

 

[Andrew Fergus]

Hi Erik

Thank you very much for the link and the info. I think the array will make a huge difference. This is what I have so far:

MyValue = 0
'Get the month number selected by the drop down box
MonthNum = ThisWorkbook.Sheets("Monthly_P&L").Range("I1").Value

With ThisWorkbook.Sheets("RawData_Budget")

    'Loop through the months
    For OuterLoop = 1 To MonthNum
    
        'Get the column of account codes (e.g. month 1 = A, month 2 = C etc)
        MyRefCol = Chr((2 * OuterLoop) + 63)
        'Get the column of values (e.g. month 1 = B, month 2 = D etc)
        MyAddCol = Chr((2 * OuterLoop) + 64)
        'Find the last row for the current column of data
        LastRow = .Range(MyRefCol & "2").End(xlDown).Row
        
        If LastRow < 65000 Then
            'Get the array of values
            ArrayValues = .Range(MyRefCol & "2:" & MyAddCol & LastRow).Value
            'Loop through the array
            For InnerLoop = LBound(ArrayValues, 1) To UBound(ArrayValues, 1)
                
                'Store the branch and account codes
                TempBranchCode = Left(ArrayValues(InnerLoop, 1), 4)
                TempAccountCode = Mid(ArrayValues(InnerLoop, 1), 5, 4)
                
                If TempAccountCode = AcctCode Then
                    'Can this be like if Code =/In {"5701", "5702"...etc}???
                    If TempBranchCode = "5701" _
                        Or TempBranchCode = "5702" _
                        Or TempBranchCode = "5711" _
                        Or TempBranchCode = "5712" _
                        Or TempBranchCode = "5713" _
                        Or TempBranchCode = "5722" _
                        Or TempBranchCode = "5724" Then
                            'Store the value
                            MyValue = MyValue + ArrayValues(InnerLoop, 2)
                    End If
                ElseIf Int(TempAccountCode) > Int(AcctCode) Then
                    'Don't check any more accounts
                    InnerLoop = UBound(ArrayValues, 1)
                End If
            Next
        Else
            'Exit the main loop - the data is missing from here on
            OuterLoop = MonthNum
        End If
    Next
End With

YTDNorthBudget = MyValue

I also found your thread here:
http://www.mrexcel.com/board2/viewtopic.php?t=153508

Will I need to use another loop if I store the 7 branch codes in another array, or can I use something like = {"5701", "5702"...etc}?

I will make these changes to the other UDF's and see what difference it makes.

Thanks again
Andrew

 

[parry]

Hi Andrew, from a speed perspective I would have a separate data column that details the account code rather than having to pull this value from other data using MID etc.

With that separated it would be easier to Filter either using standard filtering , advanced filtering or even a SQL query of the sheet (you can have a connection string to Excel, with each Ws representing a table).

Let me know if you want any assistance.

regards,
Graham

 

[Fazza]

Hi,

Not sure that I adequately understand your requirement.

I'm wondering if the UDF approach is required at all.

Query tables offer database functionality. Can you use query tables or pivot tables (or maybe advanced filtering and SUMIF?) to do the summaries. This could entirely eliminate the UDF and avoid array formulae. Large numbers of array formulae can really bog down speed.

HTH, Fazza

 

[Andrew Fergus]

Hi Graham

I have used exactly the approach you described in other areas of the workbook. Oftentimes the user is looking at one branch (or all of them at one time) - so I have used a 'helper' sheet to pull just the data I want and used 'helper' columns (like the account code) to strip the formulae back. This reformatted / filtered data is used to create an entire smorgasboard of reports.

Unfortunately the latest requirement (for regional reports) was the proverbial straw that broke the camels back. I couldn't use the formulae already created (because they are filtered by branch and month) and I had to go back to the actual raw data itself. This workbook already has over 25 worksheets and I'm loathe to create any more. If the user has to wait 30 seconds for a regional refresh then I can live with that (prior to Erik's suggestions the recalc was resulting in this PC becoming incredibly drunk - but my users have higher spec PC's).

Thanks for the offer of help - I will let you know when I get over my head.

Regards, Andrew

Hi Fazza

The query tables pull through all of the data and the user filters it for their requirements. To filter the queries using their proprietary ODBC / query methods is much slower given the servers reside off-site overseas. As I mentioned to Graham, the final request for regional reports (based on non-filtered data) is what has caused the problem.

Can you believe my client installed an entire GL system without reporting functionality? At this point I am their saviour - so a minor wait will be acceptable, PC's hanging is not.

Thanks for the suggestions.

Cheers, Andrew

 

[Andrew Fergus]

Update : using the arrays, a full recalc now takes 3 minutes which isn't that bad considering the UDF's are used over 1400 times and collectively they look at over 130k pieces of data. But I suspect the full recalc is also recalculating other unrelated parts of the spreadsheet - there are many array formulas (that are not part of this UDF and not affected by it). Given my users' PCs have twice the RAM of this test PC, and much bigger processors, I suspect this will be noticably quicker on their PC's.

Thanks again
Andrew

 

[Krishnakumar]

Hi Andrew,

This link might be a useful one.

 

[Andrew Fergus]

Hi Kris

That's a very useful link. Thank you very much! Thanks to Erik I have already made a number of suggested improvements but I will implement a couple more suggestions from that site tomorrow (ie the error handling and the function wizard check).

Thanks again
Andrew