Excel VBA 从命名范围填充数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19038697/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA populate array from named range
提问by
I need to populate an array's values with cells from a named range in another workbook. So far what I have is not working for me:
我需要用另一个工作簿中命名范围的单元格填充数组的值。到目前为止，我所拥有的对我不起作用：
Dim vArray() as Variant
vArray = Workbooks("Book2").Worksheets("Sheet1").Range("myRange")
Debug.Print vArray(1) 'yields error
Also no luck with:
也没有运气：
vArray = Workbooks("Book2").Names("myRange")
or
或者
vArray = Workbooks("Book2").Names("myRange").RefersToRange
采纳答案by Portland Runner
Try changing the print line to this:
尝试将打印行更改为：
Debug.Print vArray(1, 1)
Here is how you can loop through them:
您可以通过以下方式遍历它们：
Sub Test()
Dim vArray() As Variant
vArray = Range("myRange")
Dim i As Long
For i = LBound(vArray, 1) To UBound(vArray, 1)
Debug.Print vArray(i, 1)
Next
End Sub
*Edit*
*编辑*
To use 'Book2' without having to activate it you can do this:
要使用“Book2”而不必激活它，您可以执行以下操作：
Sub Test()
Dim vArray() As Variant
Dim rng As Range
Dim wbk As Workbook
Set wbk = Excel.Application.Workbooks("Book2.xls")
Set rng = wbk.Worksheets("Sheet1").Range("myRange")
vArray = rng
Dim i As Long
For i = LBound(vArray, 1) To UBound(vArray, 1)
Debug.Print vArray(i, 1)
Next
End Sub
To open book2 from another book change line 5 to this:
要从另一本书中打开 book2，请将第 5 行更改为：
Set wbk = Excel.Application.Workbooks.Open("C:\Users\myname\Desktop\Book2.xls")
回答by Sathish Kothandam
One more method.. TESTED
另一种方法.. TESTED
Sub Test()
Dim vArray As Range
Dim rng As Range
With ActiveSheet
Set vArray = .Range("myRange")
For Each rng In vArray
Debug.Print rng.Value
Next rng
End With
End Sub
