Named Range Lambda Formula to Create UDF

[Jeffrey Mahoney]

This may be hard to describe. I need opinions. I have created a Named range Lambda formula that accepts 2 parameters to create a dynamic range with varying rows. It works.
=LAMBDA(FullRange,MaxCol,LET(ANCHOR,INDEX(FullRange,1,1),MAXRNG,INDEX(FullRange,,MaxCol),ANCHOR:INDEX(FullRange,MAX((MAXRNG<>"")*ROW(MAXRNG))-ROW(ANCHOR)+1,)))
I know, I know, it's kind of wieldy.

You give it the full range starting at your top most row including all the columns you want but specify many more rows that you ever expect. You also provide the column that you want to calculate the last row in the set. It calculates the top left anchor cell; the last non-empty cell in the column you provide; and returns the dynamic range based on the number of rows.

I named it DynamicRange and it gets called like: DynamicRange($A$6:$AN$1000,1). I usually only have about 400 rows of data in this set and column 1 has the project IDs in it.

My questions are:
* Do you think that calling this one named range Lambda will be a bottle neck and slow things down?
* How would I go about calculating this potential slowness?

Jeff

 

[felixstraube]

With a range in the 1000th i don't think that you are going to notice. But i'm not sure.

I would create a dataset 10 times bigger than you ever will actually work with and test it.

 

[felixstraube]

Ok with a range of 100k rows it takes about 2 seconds to calculates

 

[Vogateer]

* How would I go about calculating this potential slowness?

I wish I could remember where I found this, but I have used this before to discover which calculations are taking a while. Calling "TimeAllSheets()" should create that ExecutionTimes sheet and give you a list of time spent calculating columns. If others know of better methods, please let me know.

Even if you don't need it for this formula, it might help later.

Option Explicit
Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Function timeSheet(ws As Worksheet, routput As Range) As Range
Dim ro As Range
Dim c As Range, ct As Range, rt As Range, u As Range

ws.Activate
Set u = ws.UsedRange
Set ct = u.Resize(1)
Set ro = routput

For Each c In ct.Columns
 Set ro = ro.Offset(1)
 Set rt = c.Resize(u.Rows.Count)
rt.Select
ro.Cells(1, 1).value = rt.Worksheet.Name & "!" & rt.Address
ro.Cells(1, 2) = shortCalcTimer(rt, False)
 Next c
 Set timeSheet = ro

End Function

Sub TimeAllSheets()
Application.ScreenUpdating = False
 Call timeloopSheets
Application.ScreenUpdating = True
End Sub

Sub timeloopSheets(Optional wsingle As Worksheet)
'Need a worksheet called "ExecutionTimes"
Dim ws As Worksheet, ro As Range, rAll As Range
Dim rKey As Range, r As Range, rSum As Range
Const where = "ExecutionTimes!a1"

 Set ro = Range(where)
ro.Worksheet.Cells.ClearContents
 Set rAll = ro
 'headers
rAll.Cells(1, 1).value = "address"
rAll.Cells(1, 2).value = "time"

If wsingle Is Nothing Then
' all sheets
For Each ws In Worksheets
Set ro = timeSheet(ws, ro)
Next ws
Else
' or just a single one
 Set ro = timeSheet(wsingle, ro)
End If

'now sort results, if there are any

If ro.row > rAll.row Then
Set rAll = rAll.Resize(ro.row - rAll.row + 1, 2)
Set rKey = rAll.Offset(1, 1).Resize(rAll.Rows.Count - 1, 1)
' sort highest to lowest execution time
With rAll.Worksheet.Sort
.SortFields.Clear

.SortFields.Add Key:=rKey, _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal

.SetRange rAll
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'  sum times
Set rSum = rAll.Cells(1, 3)
rSum.Formula = "=sum(" & rKey.Address & ")"
' %ages formulas
For Each r In rKey.Cells
r.Offset(, 1).Formula = "=" & r.Address & "/" & rSum.Address
r.Offset(, 1).NumberFormat = "0.00%"
 Next r

 End If
rAll.Worksheet.Activate

End Sub

Function shortCalcTimer(rt As Range, Optional bReport As Boolean = True) As Double
Dim dTime As Double
Dim sCalcType As String
Dim lCalcSave As Long
Dim bIterSave As Boolean
'
On Error GoTo Errhandl


' Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If

' Switch off iteration.
If Application.Iteration <> False Then
Application.Iteration = False
End If

' Get start time.
dTime = MicroTimer
If Val(Application.Version) >= 12 Then
rt.CalculateRowMajorOrder
Else
rt.Calculate
End If


' Calc duration.
sCalcType = "Calculate " & CStr(rt.Count) & _
 " Cell(s) in Selected Range: " & rt.Address
dTime = MicroTimer - dTime
On Error GoTo 0

dTime = Round(dTime, 5)
 If bReport Then
MsgBox sCalcType & " " & CStr(dTime) & " Seconds"
End If

shortCalcTimer = dTime

Finish:

' Restore calculation settings.
 If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Calculation = bIterSave
End If
Exit Function
Errhandl:
On Error GoTo 0
MsgBox "Unable to Calculate " & sCalcType, _
vbOKOnly + vbCritical, "CalcTimer"
GoTo Finish

End Function
'
Function MicroTimer() As Double
'

' Returns seconds.
'
Dim cyTicks1 As Currency
 Static cyFrequency As Currency
'
MicroTimer = 0

 ' Get frequency.
 If cyFrequency = 0 Then getFrequency cyFrequency

 ' Get ticks.
getTickCount cyTicks1

' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

 

[Jeffrey Mahoney]

Ok, I have been working through lockups and freezing in my workbook after I changed all my dynamic named ranges to reference that Lambda. I had to simplify some of my column named ranges from being dynamic to static. When I did this, my WB started taking way less time to calculate; from 1 minute or more to about 5 seconds.

I may have to revisit this, but for now, this method is a failure for many tables and named ranges.