NAMED RANGE Value(s) in VB 1

[nat101]

[In vb,]How do I let a variable = a cell in a named range? I need to get the value of a series of cells in a named range into an array.

As a matter of fact, come to think of it, I don't even know how to do it plain Excel via a formula.

TIA
-Nat

 

[DaleWatson123321]

Hi Nat,

For getting a value from a named cell into a variable...

Sub Cell_To_Variable()
var1 = Range("num1&quot.Value
MsgBox var1
End Sub

...where "num1" is a named cell.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[nat101]

Dale, there is more than one cell in the named range. In your example, if 'num1' was A1:A10, how would you get the values of each cell in 'num1'?
Thanks
-Nat

 

[GlennUK]

To get the values of all the cells in a named range, use the For Each statement, like this ...

Option Base 1
Sub Cell_To_Variable()
Dim myvar()
For Each c In ThisWorkbook.Sheets(1).Range("var1&quot
vcount = vcount + 1
ReDim Preserve myvar(vcount)
myvar(vcount) = c.Value
Next
End Sub

Glenn.

 

[beniez]

You can refer to an individual cell in a range using range(rowindex, colindex), like so:

Dim r As Range
Dim s As String

Set r = Range("Test&quot
i = r(3, 2).Value
MsgBox s

This gives you the value of the cell that's 3 down in the range, and 2 across. Bizarrely, you can extend *outside* the named range: even if the range Test is only 3 by 2, r(7,7) will work perfectly well. If you need to limit yourself to cells really within the range, then use the For each c in r routine described in an earlier post

 

[nat101]

Thank you both for the useful info. But listen to this. I stumbled upon this by 'mistake'. If you have a variant it will 'take' the entire range (multiple dimensions too!) with a simple "variant = range" statement!

Suppose a named range "test" covers A1:A10.

Dim vTest As variant
vTest = Range(&quot;test&quot. <------- 1 line does it!

Suddenly vTest becomes an array, in this case 2 dimensions (don't know -or care- why) and the values of A1 thru A10 are in vTest(1,1) thru vTest(10,1).

Note, that this holds true ragrdless if vTest was DIM'd as an array (Dim vTest() as Variant) or simply Dim'd as in the example above.

Hey, is this interesting stuff, or what? Worth a FAQ, or is it common knowladge?

Thank you all
-Nat

 

[beniez]

Nat

Works fine, EXCEPT:

It uses the Variant type, which is b-a-d (and disappears in VB.NET); and

You can't extend beyond the limits of the named range (you get a &quot;Subscript out of range&quot; error)... although admittedly this might be a good thing, since it enables you to ensure that you only address the cells within the range; and

You can't get at any other properties of the cell except its value - no good if you want to add them all up and format the biggest ones in red or something (ie, you can't do something like var(1,1).font).

But all that's just sour grapes because I didn't actually know the trick you've found!

Regards

Ben

 

[nat101]

Well, let 'em fix vb.net. (I suspect though, that some type of variable will do the same.)

You know, now I realize the usefullness of your method, if other attributes are needed.

TA
-Nat