PDA

View Full Version : Query-Style Custom Function needs speed boost!



cyber553
08-12-2009, 10:09 AM
I have created a custom function (GetData) that I use to pull data from a "database" (which I update in the Fin_Data tab). It works well, however the report I need requires that I use requires that I call this function in 10,000+ cells, and as a result the report takes over 4 hours to recalculate. I would really appreciate any help/advice/code-tweaks/changes that could be offered to cut down on this run-time, thanks in advance!

I've included the function below and attached a sample spreadsheet with report to facilitate/encourage tinkering. The data source is normally ~11,000 rows and the report itself is 1,000 rows and has an additional 50 columns but has been reduced to accommodate the file size limitations to upload here.


Function GetData(IInum As String, Scenario As String, Version As String, FinCat As String, _
FinType As String, Yr As String, Func As String, Acct As String, Region As String, OngComp As String, _
RunInc As String, Vw As String, Mnth As String) As Double
' This is some help on this function!
Application.Volatile (True)
Dim FinalDataRow As Double, DataColumn As Double
Dim IIidCol As Double, ScenarioCol As Double, VersionCol As Double, FinTypeACol As Double
Dim FinTypeBCol As Double, YearCol As Double, FunctionCol As Double, AcctCol As Double
Dim RegionCol As Double, OngCompCol As Double, RunIncCol As Double
Dim PLViewCol As Double, CashViewCol As Double, CapViewCol As Double, BusUnitCol As Double
Dim i As Integer, MnthNum As Integer

With ThisWorkbook.Worksheets("Fin_Data")
'Define Columns
IIidCol = .Range("1:1").Find(What:="ITM_ID", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
ScenarioCol = .Range("1:1").Find(What:="SCENARIO_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
VersionCol = .Range("1:1").Find(What:="VERSION_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
FinCatCol = .Range("1:1").Find(What:="FIN_CAT_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
FinTypeCol = .Range("1:1").Find(What:="FIN_TYPE_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
YearCol = .Range("1:1").Find(What:="YR_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
FunctionCol = .Range("1:1").Find(What:="FXNL_AREA_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
AcctCol = .Range("1:1").Find(What:="ACCT_TYPE_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
RegionCol = .Range("1:1").Find(What:="RGN_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
OngCompCol = .Range("1:1").Find(What:="CPLT_ONG_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
RunIncCol = .Range("1:1").Find(What:="FIN_SUB_TYPE_NM", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
PLViewCol = .Range("1:1").Find(What:="PL_VW", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
CashViewCol = .Range("1:1").Find(What:="CASH_VW", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
CapViewCol = .Range("1:1").Find(What:="CAP_VW", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
BusUnitCol = .Range("1:1").Find(What:="Bus Unit", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column

If Mnth = "Q1" Then
DataColumn = .Range("1:1").Find(What:="Q1 Total", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
ElseIf Mnth = "Q2" Then
DataColumn = .Range("1:1").Find(What:="Q2 Total", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
ElseIf Mnth = "Q3" Then
DataColumn = .Range("1:1").Find(What:="Q3 Total", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
ElseIf Mnth = "Q4" Then
DataColumn = .Range("1:1").Find(What:="Q4 Total", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
ElseIf Mnth > 0 And Mnth < 13 Then
MnthNum = CInt(Mnth)
DataColumn = MnthNum - 1 + .Range("1:1").Find(What:="JAN", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
Else 'Month # is invalid, give FY number
DataColumn = .Range("1:1").Find(What:="FY Total", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchByte:=False).Column
End If
FinalDataRow = .Cells(Application.Rows.Count, 1).End(xlUp).Row
GetData = 0

For i = 2 To FinalDataRow
If .Cells(i, IIidCol).Value = IInum Or IInum = "ALL" Or .Cells(i, BusUnitCol).Value = IInum Then 'Check II Number
If .Cells(i, ScenarioCol).Value = Scenario Or Scenario = "ALL" Then 'Check Scenario
If .Cells(i, VersionCol).Value = Version Or Version = "ALL" Then 'Check Version
If .Cells(i, FinCatCol).Value = FinCat Or FinCat = "ALL" Then 'Check Financial Category (Dir, Indr, Res)
If .Cells(i, FinTypeCol).Value = FinType Or FinType = "ALL" Then 'Check Fin Type (Revenue, Save, Spend)
If .Cells(i, YearCol).Value = Yr Or Yr = "ALL" Then 'Check Year
If .Cells(i, FunctionCol).Value = Func Or Func = "ALL" Then 'Check Functional Area
If .Cells(i, AcctCol).Value = Acct Or Acct = "ALL" Then 'Check Account
If .Cells(i, RegionCol).Value = Region Or Region = "ALL" Then 'Check Region
If .Cells(i, OngCompCol).Value = OngComp Or OngComp = "ALL" Then 'Check Ongoing/Completion
If .Cells(i, RunIncCol).Value = RunInc Or RunInc = "ALL" Then 'Check Run-Rate/Incremental
If (Vw = "PL" And .Cells(i, PLViewCol).Value = 1) Or (Vw = "Cash" And .Cells(i, CashViewCol).Value = 1) Or (Vw = "Cap" And .Cells(i, CapViewCol).Value = 1) Then 'Check View to be shown
If (Vw = "Cap" And .Cells(i, AcctCol).Value Like "*Credit*") Then
GetData = GetData - .Cells(i, DataColumn).Value
Else
GetData = GetData + .Cells(i, DataColumn).Value
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next i
End With

End Function

cyber553
08-12-2009, 10:54 AM
Sorry, forgot to attach the sample file originally and didn't realize I could edit the post, so I created this one and now can't figure out how to delete this one (after including all the needed info/attachments in the original post)...

Paul_Hossler
08-12-2009, 07:19 PM
Just thinking here, but why does it have to be a UDF?

Could you write a Sub that goes through the cells on 'Report Stuff' and just puts in the value.

That way there is no worksheet calculation since there are no formulas and UDFs.

I did try a few things that seemed to help a little

1. commented out .Volatile
2. Used Longs instead of Double for the column nums
3. used Match() instead of Find() since I think it's faster
4. Since the Fin Data seems to be sorted, I did a Match() to find the first instance of IInum, and the last so I didn't have loop 2 to Rows.count


' Application.Volatile (True) #1

Dim IIidCol As Long #2

With ThisWorkbook.Worksheets("Fin_Data")
'Define Columns
IIidCol = Application.WorksheetFunction.Match("ITM_ID", .Rows(1), 0) #3

'needs error checking, doesn't handle ALL, assumes FinData Col A is sorted #4
j = Application.WorksheetFunction.Match(IInum, .Columns(IIidCol), 0)
k = j
While .Cells(k, IIidCol).Value = IInum
k = k + 1
Wend
k = k - 1

GetData = 0

For i = j To k




Paul

cyber553
11-24-2009, 12:01 PM
Thanks Paul, that is the sort of help I was looking for, I really appreciate it. While I see what you're saying about writing a SUB to fill in all of the numbers in the worksheet when run, the way I see it, making a SUB that is versatile enough to cover everything I would need it to do would probably make the SUB prohibitively big/complicated (at least for me to code reliably).

As for speeding up my UDF, it seems like suggestion #4 would help the most in terms of speeding it up, unfortuantely users occassionally do add data rows at the end of the data set, so I can't assume the IDs will always be sorted in order. If it weren't for that this is an elegant fix.

Not sure about #1 - I think .Volatile is needed to force the funciton to recalculate when changes are made elsewhere in the workbook.

I agree with #3, don't know why I didn't use Match instead - I appreciate you pointing that out, it does help a fair amount on the scale I'm working with.

As for #2 - I had no idea that Longs were faster than Doubles! I just assumed those types require the same amount of space (in bytes) and therefore wouldn't make a difference in how fast they opporated. I'll give this a try!

Paul_Hossler
11-24-2009, 05:29 PM
As for #2 - I had no idea that Longs were faster than Doubles! I just assumed those types require the same amount of space (in bytes) and therefore wouldn't make a difference in how fast they opporated. I'll give this a try


1 or 2 or a 100 wouldn't show up, but if you had a lot of data it might make a difference. My understandiing is that the complier or interpeter has to spend a tiny bit of time to convert data types, so it seems like and easy enough thing to do

I believe that if a UDF changes a cell that is used in another formula that was already calculated, the re-calc gets redone. Not sure, so that was why I suggested a Sub to assign a fixed value

Something to consider is breaking 'GetData' in more single-purpose UDF's 'GetRevenue( )' , 'GetSaves ( )' in order to simply things and code, and also to avoid having a worksheet change affect the general purpose GetData uncesessarily since there are 13 parameters in the GetData call, but I doubt that all of them are used in every one of your sections (Revenue, Save, P&L, etc.)



Good luck

Paul