Lag functions or faster code
I have a large spreadsheet (several sheets with hundreds
of equations) that is used for economic modeling and simulation. I use global range names to refer to chronological data arranged in rows. The names make formulas more readable for analysis and debugging. Several equations required lagged values (data from previous time periods located in columns to the left of the calling cell), so I wrote a VB macro function to select the appropriate lagged data. Unfortunately, it appears that the macro is slowing the calculation process substantially. Is there a built in Excel function that will perform the task more quickly. If not, does anyone have suggestions for improving the macro to speed up the calculation. The function is listed below. Thanks in advance. Function Lag(ByVal strVName As String, lngNLags As Long) As Variant Dim lngCol As Long 'Holds column of calling cell Dim lngRow As Long 'Holds row of named range Dim strSheetName As String 'Holds sheet name of the named range Dim lngCut As Long 'Used to trim reference string lngCol = Application.Caller.Column - lngNLags 'Set column lngRow = ActiveWorkbook.Names (strVName).RefersToRange.Row 'Isolate named range's sheet name strSheetName = ActiveWorkbook.Names (strVName).RefersTo 'Get reference lngCut = Len(strSheetName) - 1 strSheetName = Right(strSheetName, lngCut) 'Remove = sign lngCut = InStr(strSheetName, "!") - 1 strSheetName = Left(strSheetName, lngCut) 'Remove cell reference strSheetName = Replace (strSheetName, "'", "") 'Remove single quotes Lag = ActiveWorkbook.Worksheets(strSheetName).Cells (lngRow, lngCol).Value End Function |
Lag functions or faster code
Frank Fuller -
Is there a built in Excel function that will perform the task more quickly. < OFFSET - Mike Middleton, www.usfca.edu/~middleton |
All times are GMT +1. The time now is 08:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com