VBA Arrays And Worksheet Ranges
来源:互联网 发布:网络教育统考成绩 编辑:程序博客网 时间:2024/06/05 01:07
This page describes how to transfer data between VBA arrays and worksheet ranges.
Introduction
Data transfer between worksheet cells and VBA variables is an expensive operation that should be kept to a minimum. You can considerably increase the performance of your Excel application by passing arrays of data to the worksheet, and vice versa, in a single operation rather than one cell at a time. If you need to do extensive calculations on data in VBA, you should transfer all the values from the worksheet to an array, do the calculations on the array, and then, possibly, write the array back to the worksheet. This keeps the number of times data is transferred between the worksheet and VBA to a minimum. It is far more efficient to transfer one array of 100 values to the worksheet than to transfer 100 items at a time.
This page shows you how to transfer data between worksheet cells and VBA ararys. You will find that with large amounts of data being transferred between the worksheet and the array, working with the array is much faster than working directly with worksheet cells.
Reading A Worksheet Range To A VBA Array
It is very simple to read a range on a worksheet and put it into an array in VBA. For example,
Dim Arr() As Variant ' declare an unallocated array.Arr = Range("A1:C5")' Arr is now an allocated array
When you bring in data from a worksheet to a VBA array, the array is always 2 dimensional. The first dimension is the rows and the second dimension is the columns. So, in the example above,Arr is implicitly sized as Arr(1 To 5, 1 To 3) where 5 is the number of rows and 3 is the number of columns. A 2 dimensional array is created even if the worksheet data is in a single row or a single column (e.g,Arr(1 To 10, 1 To 1)). The array into which the worksheet data is loaded always has an lower bound (LBound) equal to 1, regardless of whatOption Base directive you may have in your module. You cannot change this behavior. For example,
Dim Arr() As VariantArr = Range("A1:A10")
Here, Arr is dimensioned automatically by VBA asArr(1 to 10, 1 To 1).
You can use code like the following to loop through the array of the worksheet values:
Dim Arr() As VariantArr = Range("A1:B10")Dim R As LongDim C As LongFor R = 1 To UBound(Arr, 1) ' First array dimension is rows. For C = 1 To UBound(Arr, 2) ' Second array dimension is columns. Debug.Print Arr(R, C) Next CNext R
There is a special case when the range on the worksheet is a single cell. Expanding on the code above, you should use the code below if it is possible that the range is a single cell:
Dim Arr() As VariantDim RangeName As StringDim R As LongDim C As LongDim RR As RangeRangeName = "TheRange"Set RR = Range(RangeName)If RR.Cells.Count = 1 Then ReDim Arr(1 To 1, 1 To 1) Arr(1, 1) = RR.ValueElse Arr = Range(RangeName)End If
Writing A One Dimensional VBA Array To The Worksheet
Once you have calculated an array with the appropriate values, you can write it back to the worksheet. The array may be 1 or 2 dimensional.
To write a one dimensional array back to the worksheet, you must create a Range object, resize that range to the size of your array, and then write to the range.
Suppose we have a one dimensional array and want to write that out to the worksheet starting at cellK1. The code must first resize the destination range. For example,
' one row spanning several columnsDim Destination As RangeSet Destination = Range("K1")Set Destination = Destination.Resize(1, UBound(Arr))Destination.Value = Arr
This code will write the values of Arr to range that is one row tall byUBound(Arr) columns wide, starting at rangeK1. If you want the results passed to a range that is one column wide spanning several rows, use code like the following to resize the range and set the values.
Dim Destination As RangeSet Destination = Range("K1")Set Destination = Destination.Resize(UBound(Arr), 1)Destination.Value = Application.Transpose(Arr)
NOTE that the parameters toResize are reversed and that the arrayArr is transposed before being written to the worksheet.
Writing A Two Dimensional VBA Array To The Worksheet
If you have a 2 dimensional array, you need to useResize to resize the destination range to the proper size. The first dimension is the number of rows and the second dimension is the number of columns. The code below illustrates writing an arrayArr out to the worksheet starting at cellK1.
Dim Destination As RangeSet Destination = Range("K1")Destination.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr
You can transpose the array when writing to the worksheet:
Set Destination = Range("K1")Destination.Resize(UBound(Arr, 2), UBound(Arr, 1)).Value = Application.Transpose(Arr)
Here, the parameters to Resize are reversed and the arrayArr is transposed.
Array Sizing
When you read from a worksheet to an array variable, VBA will automatically size the array to hold the range on the worksheet. You don't have to concern yourself with sizing the array. However, when writing an array from VBA to the worksheet, you must resize the destination range to hold the array. We saw this earlier in the examples. Basically, you use code like the following.
Dim NumRows As LongDim NumCols As LongNumRows = UBound(Arr,1) - LBound(Arr,1) + 1NumCols = UBound(Arr,2) - LBound(Arr,2) + 1Set Destination = Range("K1").Resize(NumRows, NumCols).Value = Arr
If the array being passed to the worksheet is smaller than the Range to which it is written, the unused cells get a#N/A error. If the array being passed is larger than the range to which it is written, the array is truncated on the right or bottom to fit the range.
As you've seen in the examples, passing array between the worksheet and VBA is really quite simple. Used correctly, the code snippets above can have a strong effect on increasing the performance of your VBA application.
- VBA Arrays And Worksheet Ranges
- Select arrays between date ranges with Ruby
- Excel VBA入门(5) - Worksheet对象常用属性
- Excel VBA入门(6) - Worksheet对象常用方法事件
- 数组 Arrays and associative arrays
- Ranges
- How to automate Excel using MFC and worksheet functions
- Excel VBA 在工作表(Worksheet)中动态添加按钮控件并添加事件的示例
- Locking and Unlocking Byte Ranges in Files
- C++ Arrays and Algorithms
- Arrays and Pointers
- Arrays and Strings----1
- Arrays and Strings----3
- Arrays and Strings----4
- Arrays and Strings----7
- list and Arrays sort
- Arrays and Pointers部分
- 1341: String and Arrays
- 欢迎使用CSDN-markdown编辑器
- jquery.pagination分页反复提交的问题
- hdoj 1568 (斐波那契数列+数学)
- xshell配置linux密钥
- 算法学习之路(冒泡排序)
- VBA Arrays And Worksheet Ranges
- Java 面向对象 : 封装
- hdu - Problem 1175 连连看 【bfs】
- NRedis-Proxy 高性能Redis 中间件服务
- BZOJ1806: [Ioi2007]Miners 矿工配餐
- 1015: [JSOI2008]星球大战starwar
- 第十二周项目3----图遍历算法实现
- 关于我们写的第一个hello world 的由来
- 【最短路】CODE[VS] 1557 热浪 (Dijkstra模板)