Lag functions or faster code

[Frank Fuller]

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

 

[Michael R Middleton]

Frank Fuller -

Is there a built in Excel function that will perform the task more

quickly. <

OFFSET

- Mike Middleton, www.usfca.edu/~middleton