Reference Names via VBA
 

We are converting some of our calculating in our spreadsheets to VBA
due to some of excel limitations. We have numerous 2 dimensional
defined names. If (for example) I have a defined name of "CUTS" that
is 300 x 200 and I wanted to access element (240, 45) how would I do
this in VBA using the defined name "CUTS". Also lets say the user
picks item 6 off of a validation list how could I use VBA to determine
they picked item 6 from the list of 20.

Thank You

Reference Names via VBA
 

x = Range("Cuts").Cells(245, 45).Value


For your 2nd question, I think you need to retrieve the Formula1 property of
the Validation object for this cell, then compare the cell value with the
items on that last.

On 25 Oct 2004 13:27:15 -0700, (Network Admin) wrote:

We are converting some of our calculating in our spreadsheets to VBA
due to some of excel limitations. We have numerous 2 dimensional
defined names. If (for example) I have a defined name of "CUTS" that
is 300 x 200 and I wanted to access element (240, 45) how would I do
this in VBA using the defined name "CUTS". Also lets say the user
picks item 6 off of a validation list how could I use VBA to determine
they picked item 6 from the list of 20.

Thank You

Reference Names via VBA
 

Be aware that range names can be defined
on workbook level
and worksheet level...

dim r as range

This will set the range for the ACTIVEWORKBOOK!

set r = Range("CUTS")(240,45)

is the shortest way to refence the range objects' default
CELLS method. More often it's used like:

set r = Range("CUTS").Cells(240,45)


It you need to code regardless of whether the book is active...

Set r = Range(Workbooks(1).Name & "!CUTS")(240, 45)
OR
Set r = Workbooks(1).Names("CUTS").RefersToRange(240, 45)

(the first method is slightly faster)

To reference a sheet level name:
either activate the sheet or precede the NAME with the sheetname.

Set r = Range("'[" & Workbooks(1).Name & "]" & 'sheet1!CUTS")(4, 4)
Set r = Workbooks(1).Names("Sheet1!CUTS").RefersToRange(24 0, 45)
Set r = Workbooks(1).Sheets(1).Names("CUTS").RefersToRange (240, 45)

Note again the RANGE method is fastest (approx 30%)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Network Admin) wrote:

We are converting some of our calculating in our spreadsheets to VBA
due to some of excel limitations. We have numerous 2 dimensional
defined names. If (for example) I have a defined name of "CUTS" that
is 300 x 200 and I wanted to access element (240, 45) how would I do
this in VBA using the defined name "CUTS". Also lets say the user
picks item 6 off of a validation list how could I use VBA to determine
they picked item 6 from the list of 20.

Thank You

Reference Names via VBA
 

I noticed one thing I left out in my question. The second question
being about validation lists. My list also has a named defined to it
because the list is used elsewhere in multiple places.

Thank You