Excel VBA将范围值复制到数组， [英] Excel VBA Copying range values to an Array,

问题描述

我有以下代码提取，我正在尝试将一系列值复制到声明的数组上，它不断给我不能分配到数组错误'，

  Dim allowedCurve（0至7000）As Variant 
 
 allowedCurve = activeWorkbook.Worksheets（Origin）。Range（AB6 ：AB7006）value 
  

我也试过这个，但给我一样的错误..

  Dim allowedCurve（7000）As Variant 
 
 allowedCurve = application.transpose（activeWorkbook.Worksheets（原产地）范围（AB6：AB7006）。价值）
  

有人可以帮忙吗？我没有看到任何错误的两种方法。 ： - （

===========================

更新

我尝试了以下内容，

  Dim allowedCurve（4）As Variant 
 Dim indicationCurve（4）As Variant 
 
 indicationCurve（0）= 1 
 indicationCurve（1）= 10 
 indicationCurve（2）= 100 
 indicationCurve（3）= 1000 
 indicationCurve（4）= 10000 
 
'复制曲线
 allowedCurve = indicationCurve 
  

这仍然会产生相同的无法分配到数组错误...为什么？ / p>

解决方案

从工作表的单元格批量加载时，您总是获取一个二维数组。第一等级可以被认为是列，而第二列是列。

  dim allowedCurve As Variant 
'以下与redim allowedCurve相同（1到3，1到6）
 allowedCurve = Range（A1：F3）。Value2 
 debug.print l bound（allowedCurve，1）& ：& ubound（allowedCurve，1）
 debug.print lbound（allowedCurve，2）& ：&来自立即窗口的ubound（allowedCurve，2）
'结果：
 1：3 
 1：6 
  

考虑到使用本机 TRANSPOSE功能，坚持使用2-D数组，如果您打算从工作表中大量使用值。。 p>

可以通过更改变量声明来解决更新的问题。

  Dim allowedCurve As Variant'< ~~只是一个变体，不是具体的5个元素的变量数组
 Dim indicationCurve（4）As Variant 
 
 indicationCurve（0）= 1 
指示Curve（1）= 10 
指示Curve（2）= 100 
指示Curve（3）= 1000 
指示Curve（4）= 10000 
 
'复制曲线
 allowedCurve = indicationCurve 
'现在它是一个具有5个元素的变量数组
  

I've got the following code extract, I'm trying to copy a range of values onto the declared array and it keeps on giving me the 'can't assign to array' error',

Dim permittedCurve(0 To 7000) As Variant

permittedCurve = activeWorkbook.Worksheets("Origin").Range("AB6:AB7006").value

I've tried this too,but giving me the same error..

Dim permittedCurve(7000) As Variant

permittedCurve = application.transpose(activeWorkbook.Worksheets("Origin").Range("AB6:AB7006").value)

Could someone please help? I don't really see anything wrong with both approaches. :-(

=============================

Updated

I've tried the following,

Dim permittedCurve(4) As Variant
Dim indicationCurve(4) As Variant

indicationCurve(0) = 1
indicationCurve(1) = 10
indicationCurve(2) = 100
indicationCurve(3) = 1000
indicationCurve(4) = 10000

'Copying the curves
permittedCurve = indicationCurve

This would still prodcue the same "Can't assign to array" Error... WHY?

解决方案

When you bulk load from a worksheet's cells, you always get a two-dimensioned array. The first rank can be considered the 'rows' and hte second rank as the 'columns'.

dim permittedCurve As Variant
'the following is the same as redim permittedCurve(1 to 3, 1 to 6)
permittedCurve = Range("A1:F3").Value2
debug.print lbound(permittedCurve, 1) & ":" & ubound(permittedCurve, 1)
debug.print lbound(permittedCurve, 2) & ":" & ubound(permittedCurve, 2)
'results from the Immediate window:
1:3
1:6

Given the problems (and overhead) of working with the native TRANSPOSE function, stick with 2-D arrays if you plan to shovel values back and forth from the worksheet en masse.

The updated issue can be resolved with a change in variable declaration.

Dim permittedCurve As Variant  '<~~ just a variant, not specifically a variant array with 5 elements
Dim indicationCurve(4) As Variant

indicationCurve(0) = 1
indicationCurve(1) = 10
indicationCurve(2) = 100
indicationCurve(3) = 1000
indicationCurve(4) = 10000

'Copying the curves
permittedCurve = indicationCurve
'now it's a variant array with 5 elements

这篇关于Excel VBA将范围值复制到数组，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！