Como chamar um intervalo de nomeação usando uma variável para definir o nome

Question

De acordo com a documentação, qualquer nome de texto citado pode ser definido por uma variável Se eu definir

Set xlsDataSource = Application.Workbooks("New_TOR_V1.xlsm").Names("MyRange").RefersToRange

funciona como esperado Se eu usar

Dim S as string
S="MyRange"

Set xlsDataSource = Application.Workbooks("New_TOR_V1.xlsm").Names(S).RefersToRange

também funciona bem

mas usando

Dim Arr() as string
Arr(1)="MyRange"
Set xlsDataSource = Application.Workbooks("New_TOR_V1.xlsm").Names(Arr(1)).RefersToRange

apresenta uma mensagem de erro

e também

Set xlsDataSource = Application.Workbooks("New_TOR_V1.xlsm").Names(Cstr(Arr(1))).RefersToRange

apresenta uma mensagem de erro

Por que um elemento da matriz causa um erro e como posso resolver isso?

Eu preciso usar uma matriz?

    

Answer 1

Declare sua matriz com tamanho - Dim Arr(1) as String

Não sei ao certo qual é o seu aplicativo específico, mas o array precisa de um tamanho antes de poder ser usado -

Sub test()
'One number indicates one dimension and starts at **0** See footnote
Dim Arr(1) As String
Arr(1) = "MyRange2"
Range("A2").Name = Arr(1)
End Sub

nota de rodapé

Se você ainda não sabe, precisará redim :

Sub test()

Dim Arr() As String
'Do stuff to get a number 
ReDim Arr(1)
Arr(1) = "MyRange3"
Range("A3").Name = Arr(1)

End Sub

No seu exemplo, eu terminei, mas funciona assim:

Sub test()

Dim Arr() As String
ReDim Arr(1)
Arr(1) = "MyRange"
Range("A1").Name = Arr(1)

Set xlsdatasource = ThisWorkbook.Names("MyRange").RefersToRange
MsgBox (xlsdatasource.Address)

End Sub

Me dá $A$1 no msgbox.

Substitua o "A1" por "A1: C1" e o msgbox me fornece $A$1:$C$1 .

Um exemplo mais complexo com as mesmas restrições

Para A1: leitura A6 -

this
is
a
one
dimensional
array

Isso também é compilado e executado:

Sub test()

Dim Arr() As String

Dim j As Integer
j = [counta(A1:A10)]
ReDim Arr(j - 1) 

Dim i As Integer
  For i = 0 To 5
  Arr(i) = Cells(i + 1, 1)
  Next

Dim rng As Range

  For i = 0 To 5
  Set rng = Range(Cells(i + 2, i + 5), Cells(i + 2, i + 8))
  rng.Name = Arr(i)
  Next

Set xlsdatasource = ThisWorkbook.Names(Arr(3)).RefersToRange
MsgBox (xlsdatasource.Address)

End Sub

e msgbox exibe $H$5:$K$5 .

Alterando isso:

Set xlsdatasource = ThisWorkbook.Names(Arr(3)).RefersToRange 
MsgBox (xlsdatasource.Address)

para isso:

Set xlsdatasource = ThisWorkbook.Names("one").RefersToRange
MsgBox (xlsdatasource.Address)

também retorna msgbox exibindo $H$5:$K$5 .

Uma nota

Se a célula A4 leu 1 em vez de one , não consegui Dim Arr() as String porque "1" não começa com uma letra ou sublinhado .

Eu poderia usar _1 ou Dim Arr() as Variant , mas na verdade não consegui nomear um intervalo "1" porque é um valor inválido para nomeação .