Looping through range names [VBA]

[TenKittens]

Context: Medium skill VBA user - Old poster that lost it's previous email

Hi,

I’ve been coming across a VBA question that would simplify my codes and I can’t find the answer. Maybe you can help me out:

Let’s say that I have three ranges that I have name in Excel: ARoot BRoot and CRoot.
I would like to run the same procedure using the three ranges I defined, without making a copy paste

For example
Copying-Pasting

A=Me.[ ARoot].Value
B=Me.[ BRoot].Value
C=Me.[CRoot].Value

Is there a way to do this with a for loop?
This is how i tried:

NameStr(0)=ARoot
NameStr(1)=BRoot
NameStr(2)=CRoot

 
For i_x=0 To 2
   RootName= NameStr(i_x)
   A=Me.[RootName].Value          ''Incorrect
Next i_x

Obviously the second code above is not working, I don't know how to appropriately define the variables
Thanks in advance

TK

 

[pgc01]

Hi

For ex.:

A=Me.Range(RootName).Value

 

[TenKittens]

Thanks,

This is the way I ended up doing it. I was just wondering if I could use the bracketed ranges. I shouldn't be so OCD about my programming

 

[pgc01]

I was just wondering if I could use the bracketed ranges.

Well, you can, kind of

The bracketed expression is just a shorthand for the Evaluate method,

A= [ARoot].Value

is the shorthand for

A= Evaluate("ARoot").Value

the advantage of the shorthand is that it's shorter (imagine!), the disadvantage is that it will not evaluate a vba expression, you just write what you'd write in a worksheet formula.

If you use Evaluate(), you can evaluate an expression. This means that you could also use in your code:

A=Evaluate(RootName).Value

Evaluate() has no problem in first evaluating RootName as a vba variable, getting its value and then evaluate the expression as a worksheet formula would.

Ex., evaluating the same expression, but using a variable in the second case:

Write some number value in A1 in the active sheet and run:

Sub Test()
Dim k As Long

MsgBox [2+3*A1]

k = 2

MsgBox Evaluate(k & "+3*a1")

End Sub

In the second case you cannot use the shorthand because you want to evaluate the vba expression first.

HTH

 

[Kenneth Hobson]

I prefer the Range method as well. Evaluate() might be an option.

Sub ken()
  Dim NameStr(0 To 2) As String, i_x As Integer
  Dim RootName As String, A As Variant
  
  NameStr(0) = [ARoot].Name
  NameStr(1) = [BRoot].Name
  NameStr(2) = [CRoot].Name
   
  For i_x = LBound(NameStr) To UBound(NameStr)
     RootName = NameStr(i_x)
     'A = Range(RootName).Value
     A = Evaluate(RootName).Value
     Debug.Print i_x, RootName, A
  Next i_x
End Sub

Sub ken2()
  Dim NameStr As Range, i_x As Integer
  Dim A As Variant
  
  Set NameStr = Union([ARoot], [BRoot], [CRoot])
   
  For i_x = 1 To NameStr.Cells.Count
     A = NameStr(i_x).Value
     Debug.Print i_x, A, NameStr(i_x).Address, _
      NameStr(i_x).Address(External:=True), NameStr(i_x).Name
  Next i_x
End Sub