0

I have some data in terms of a column that I want to store into an array using VBA. After storing it, I will reference the element in the array and make a comparison.

Dim tRange As Range

Set tRange = wb.Sheets("wbname").Range("A1:A5")

Lets say I want to store column A with 5 row into the array in VBA. May I know the way?

GSD
  • 1,252
  • 1
  • 10
  • 12
HeIsDying
  • 335
  • 5
  • 16
  • See here for further reading on arrays: https://excelmacromastery.com/excel-vba-array/ – Dean Jun 23 '19 at 13:32

2 Answers2

0

Here is one way:

Sub Dave()
    Dim tRange As Range, wb As Workbook, cell As Range
    Dim i As Long

    Set wb = ThisWorkbook
    Set tRange = wb.Sheets("wbname").Range("A1:A5")

    ReDim arr(1 To tRange.Count)
    i = 1
    For Each cell In tRange
        arr(i) = cell.Value
        i = i + 1
      Next cell
End Sub

NOTE:

This technique does not depend on the "shape" of the range. It will work if the range is a piece of a column, or a piece of a row, or a rectangle of cells, or even a disjoint set of cells.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

You can just declare a Variant data type and make it equal to the range.

Dim DirArray As Variant
DirArray = Range("a1:a5").Value

This was answered in a previous question by @vacip coincidentally for exactly the same range! Creating an Array from a Range in VBA

Earl_of_Duke
  • 358
  • 1
  • 6
  • Thanks for the answer, I have few things to ask 1. How can i reference the element in the array? Like DirArray(1) to get the first element assume the array start from 1. 2. How can I get the number of element in the array? As i know if I use range eg: Dim tRange As Range , I can use " tRange.rows.count " to get the total element in the range. Thanks @Earl_of_Duke – HeIsDying Jun 23 '19 at 13:32
  • @dave you can use `DirArray(i)` to reference the i'th element in your array. To get the number of elements you can use `UBound(DirArray)`. I recommend reading through the article I posted in the comments to your question. That will give you a better understanding to your questions. – Dean Jun 23 '19 at 13:46
  • @Earl_of_Duke Thanks dude, bless you<3 – HeIsDying Jun 23 '19 at 15:46
  • 1
    @Dean - small correction: as the above datafield array creates a **2-dimensioned** (1-based) array, you get the first element via `DirArray(1,1)`, the second via `DirArray(2,1)` etc. Otherwise in order to get a 1-dim array you'd have to *flatten* data coding `Application.Transpose(Range("A1:A5").Value)` first to be able to get `DirArray(1)` with one index Parameter only. - *Side note:* It's better practice to fully qualify your range references, e.g. using `ThisWorkbook.Worksheets("MySheetname").Range("A1:A5").Value`. – T.M. Jun 24 '19 at 19:17
  • 1
    Hi @T.M. Yes! That is what I did, but with ".Value2 ". So when I want to reference the element from the array I will reference it as **DirArray(I,1)** where **I** is the counter for loop. Hope I was correct about it. Thanks btw <3 – HeIsDying Jun 25 '19 at 01:37
  • 1
    @T.M. yes you are correct, thanks for the correction. – Dean Jun 25 '19 at 07:45