The [] bracket notation is actually a shortcut for Application.Evaluate, so
you can use it to evaluate formulae as well as Range Names.
Its also interesting to note that it evaluates Formulae as though they were
array formulae.

So Jim's function is evaluating
arr = [Sales - Costs]
as an array formula returning all the results to a variant containing an
array.

See also
http://www.decisionmodels.com/calcsecretsh.htm

for some details of the limitations of the Evaluate method.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Jim Cone" wrote in message
...
James,
You are welcome.
'--
Using range shortcut notation brackets may be the only instance
where you can use range names without enclosing them in quote marks.
Also, using that method may be slower than other types of range
references.
If you have a sheet with 10,000 of those functions you might be unhappy.
In addition, please note Charles Williams' post about function
calculation.
--
Jim Cone
Portland, Oregon USA



"James Buist"
wrote in message
Jim
Thats Brilliant.
I had no idea you could refernce names in VB by just using them. I have
been
explicitly defining the names i need in VB by declaring a range variable
and
setting its value with a Range Name from the workbook.
Many thanks for that!!
James



"Jim Cone" wrote:
Charles,
Noted and thanks.
--
Jim Cone
Portland, Oregon USA



"Charles Williams"
wrote in message
Jim,
I like your use of evaluate, but I think you would have to make the
function
volatile otherwise it will sometimes not recalculate properly, because it
is
using cells via the names which are not being passed as parameters.
regards
Charles
The Excel Calculation Site
http://www.decisionmodels.com