Reference Names via VBA

[Network Admin]

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

 

[Myrna Larson]

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.

 

[keepITcool]

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(240, 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]

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