Excel VBA从命名范围填充数组 [英] Excel VBA populate array from named range
本文介绍了Excel VBA从命名范围填充数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要使用另一个工作簿中命名范围的单元格填充数组的值。到目前为止,我没有为我工作:
Dim vArray()as Variant
vArray = Workbooks( Book2)。工作表(Sheet1)。范围(myRange)
Debug.Print vArray(1)'yield error
也没有运气:
vArray =工作簿(Book2 ).Names(myRange)
或
名称(myRange)。RefersToRange
解决方案
尝试将打印行更改为:
Debug.Print vArray(1,1)
以下是如何循环使用它们:
Sub Test()
Dim vArray()As Variant
vArray = Range(myRange)
Dim i As Long
对于i = LBound(vArray,1)到UBound(vArray,1)
Debug.Print vArray(i,1)
下一个
End Sub
* 编辑 * / p>
要使用Book2而不必激活它,您可以这样做:
Sub Test()
Dim vArray()As Variant
Dim rng As Range
Dim wbk As Workbook
设置wbk = Excel.Application.Workbooks Book2.xls)
设置rng = wbk.Worksheets(Sheet1)。范围(myRange)
vArray = rng
Dim i As Long
对于i = LBound(vArray,1)到UBound(vArray,1)
Debug.Print vArray(i,1)
下一个
End Sub
要从另一本书更改行5打开book2,请执行以下操作:
设置wbk = Excel.Application.Workbooks.Open(C:\Users\myname\Desktop\Book2.xls)
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
解决方案
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:
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:
Set wbk = Excel.Application.Workbooks.Open("C:\Users\myname\Desktop\Book2.xls")
这篇关于Excel VBA从命名范围填充数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文