How to Speed Up the File Processing i.e Formulas Vs VBA ?

Hmm.. That is interesting. I never noticed that. Perhaps I have always used fixed references and never thought of the opportunity of relative references in named values. I didn't think that the cell that is calling the named range would in any way affect the named range value.

I can see that being very useful.

Thanks.

As a test of the calculation of named values, I wrote this UDF, which will return 0 if trigger ="start" or will incriment every time it is calculated otherwise.

Function myFtn(trigger As String) As Double
    Static value As Long
    If trigger = "start" Then
        value = 0
    Else
        value = value + 1
    End If
    myFtn = value
End Function

Then I defined a name
Name: myNamedFunction RefersTo: =myFtn(Sheet1!$A$1)

Then I put =myNamedFunction in B3 and B4.

When I put "start" in A1, both cells showed 0.
When I then put "x" in A1, B3 showed 2 and B4 showed 1
When I put "y" in A1, B3 and B4 became 3 and 4 respectivly.

Apparently myNamedFunction re-cacluates each time a cell calls it.

Clearing B3:B4,
I put =myNamedFunction+myNamedFunction in a cell.
"start" 0
"x" 3
"y" 7

So a Name is called each time it occurs in a formula, not just once a cell.

Use of a named range won't speed up a worksheet, but using dynamic ranges will.
Implimenting those dynamic ranges with a Name is about the only way to go if you want to be able to edit the cell in the future.