Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

arrays

excel

vba

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

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

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)

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!

like image 291
SydneyJ Avatar asked Jul 29 '15 17:07

SydneyJ


People also ask

How do you assign a range of cells to a variable in VBA?

To use a range or a single cell as a variable, first, you need to declare that variable with the range data type. Once you do that you need to specify a range of a cell to that variable using the range object. This also gives you access to all the properties and methods that you can use with a range.

How do you add cells to an array in VBA?

Add a New Value to an Array in VBA First, you need to use the “ReDim” statement with the “Preserve” keyword to preserve the two elements including the new element for which you want to add the value. Next, you need to define the elements that you want to have in the array.

Can VBA functions accept ranges of cells as inputs?

As written, your function accepts only two ranges as arguments. To allow for a variable number of ranges to be used in the function, you need to declare a ParamArray variant array in your argument list. Then, you can process each of the ranges in the array in turn.


2 Answers

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
like image 199
BruceWayne Avatar answered Nov 15 '22 06:11

BruceWayne


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
    

edit : I indent the code to display it correctly

like image 45
Maxime Porté Avatar answered Nov 15 '22 06:11

Maxime Porté