使用 VBA 将单元格值范围分配给变量数组

Using VBA to assign range of cell values to array of variables

提问by SydneyJ

I'm very new to VBA, to bear with me here.

我对 VBA 很陌生，请耐心等待。

I want to assign a set of variables the value of a set of ranges ie. run a brief code to simplify the following

我想为一组变量分配一组范围的值，即。运行一个简短的代码来简化以下内容

Dim Sample 1 as string
Sample1 = activeworksheet.range("C17").value

Dim Sample 2 as string
Sample2 = activeworksheet.range("C18").value}

and so on

等等

Following an excelfunctions.net tutorial, I know that I can shorten the declaration to

按照 excelfunctions.net 教程，我知道我可以将声明缩短为

Dim Sample(1 to 20) as a string

But the tutorial drops it there(because it's a tutorial about names), suggesting I populate it as follows

但是教程把它放在那里（因为它是一个关于名字的教程），建议我按如下方式填充它

sample(1)=activesheet.range("C7").value
sample(2)=activesheet.range("C7").value

and so on

等等

I found the discussion below to be on the right track to answer my quest, but I am having trouble applying it to my situation. (Excel VBA Array Ranges for a loop)

我发现下面的讨论是在正确的轨道上回答我的任务，但我无法将它应用于我的情况。（循环的 Excel VBA 数组范围）

As a follow up note, I am ultimately trying to assign values to these variables for use in the following procedures, rather than declaring and assigning them each time.

作为后续说明，我最终尝试为这些变量分配值以在以下过程中使用，而不是每次都声明和分配它们。

Thanks!

谢谢！

采纳答案by Maxime Porté

You should :

你应该 ：

• Define the range you want to retrieve data

• For each cell of the range, retrieve your datas

  dim tab() As string, cell as range, i as integer
  i = 0
  redim tab(0)
  for each cell in ActiveWorksheet.Range("C1:C20")
      tab(i) = cell
      i = i + 1
      redim preserve tab(i)
  next
  

• 定义要检索数据的范围

• 对于范围的每个单元格，检索您的数据

  dim tab() As string, cell as range, i as integer
  i = 0
  redim tab(0)
  for each cell in ActiveWorksheet.Range("C1:C20")
      tab(i) = cell
      i = i + 1
      redim preserve tab(i)
  next
  

edit : I indent the code to display it correctly

编辑：我缩进代码以正确显示它

回答by BruceWayne

Try something like this:

尝试这样的事情：

Sub test()
Dim sampleArr(1 To 20) As String
Dim i As Integer
Dim rng As Range, cel As Range

i = 1
Set rng = Range("C1:C20") 

For Each cel In rng
    sampleArr(i) = cel.Value
    i = i + 1
Next cel
For i = LBound(sampleArr) To UBound(sampleArr)
    Debug.Print sampleArr(i)
Next i

Also, if you know the range you want to put into an array, you can simply set an array to that range:

此外，如果您知道要放入数组的范围，则可以简单地将数组设置为该范围：

Sub test()
Dim sampleArr() As Variant
Dim i As Integer
Dim rng As Range, cel As Range

i = 1
Set rng = Range("C1:C20") ' Note, this creates a 2 Dimensional array

sampleArr = rng ' Right here, this sets the values in the range to this array.

For i = LBound(sampleArr) To UBound(sampleArr)
    Debug.Print sampleArr(i, 1) ' you need the ",1" since this is 2D.
Next i

End Sub

回答by Mohammed Almudhafar

Additional way to the above you can only use:

上面的额外方法你只能使用：

Arr = ActiveWorksheet.Range("C1:C20").Value

Then you can directly use:

然后就可以直接使用：

Arr(i,1)where iis C1 to C20 range!

Arr(i,1)iC1 到 C20 范围在哪里！