# xlf QandA series

## WS VBA range array comparison

### QUESTION

WHAT IS THE DIFFERENCE BETWEEN A RANGE AND AN ARRAY IN BOTH EXCEL AND VBA?

### Answer

## 1. Excel

Includes additional ideas!

### 1.1 Excel range

- A range can be a group of cells, groups of cells, or an individual cell on a worksheet
- The individual elements of a WS are each of the 17 billion cells that exist in each worksheet. A group of cell is often referred to as a range of cells
- In Lotus 1-2-3, when a group of cells was named, the technique was to Create a Range Name with key strokes \RNC, Range > Name > Create
- Since the introduction of Excel and VBA we now name a range with a Defined Name Formulas > Defined Names
- A range can be a cell, a selection (individual cell or group of cells), an array (usually a group of cells), or current region of the active cell (in the context of Tables,Data Sort, …)
- Excel uses a number of range operators:
- : (colon) - range operator - top left cell:bottom right cell addresses
`= SUM(A1:C3)`

- , (comma) - union operator -
`= SUM(A1:C3,D6,E15:J100)`

- (space) - intersection operator -
`= SUM(C6:G7 E3:E10)`

- An Excel array normally has a maximum of 2 dimensions (rows x columns), but can be a maximum of 3 where the worksheet sheets form the next dimension

- : (colon) - range operator - top left cell:bottom right cell addresses

### 1.2 Excel array

- An array can be another name for a range of cells
- A number of WS functions have array arguments, but are entered as ordinary formula.
**COVARIANCE.S**(array1,array2) - The
**VAR.S**(Number1,Number2) simply uses Reference to the data. The data, arguments*Number1*, and*Number2*can also be described as an Array (Column array) or a Vector (Column vector) - A number of WS functions have array arguments that can be entered as arrays (groups of cells), and the formula is completed with the Control + Shift + Enter (CSE) sequence - an Array formula. For example VLOOKUP can be used with a multi-cell
*look_up*argument and entered with CSE - A number of WS functions have array arguments that
**must**be entered as arrays (groups of cells), and the formula**must**be completed with the Control + Shift + Enter (CSE) sequence - an Array formula. An example is**MMULT**(array1,array2)

## 2. VBA

### 2.1 VBA range

- A range object:

' The Set keyword is used to assign an object to a variable that has been declared as an object Dim inRng As Range Set inRng = Range("Data")

- An expression in an assignment statement:

Dim Name As String Dim NoRows as Long ' An assignment statement always includes an equals sign (=) ' Name and Count are properties (not objects), so the assignment statement must not include a Set statement Name = Range("Data").Name.Name NoRows = Range("Data").Rows.Count

### 2.2 VBA array

- An array is a named area of computer memory that stores multiple elements of data

'Declaration Dim Var1 As Double ' is a variable that stores 1 number (a memory location for a number) Dim Arr(1 to 10) as Double ' is an array with 10 elements that stores 10 number numbers ' Assignment or values ' Variable Var1 = 5# ' An array For i = 1 to 10 Arr(i) = Rnd Next i

- A VBA array has a maximum of 60 dimensions
- The elements of the array, can themselves be arrays
- A variant array element can even store an object

## 3. Mixed WS VBA range array

- Assign an Excel range / array to VBA array:

' Declaration Dim inData1 As Variant ' a variant array Dim inData2() As Variant ' an array of variants ' Assignment inData1 = Range("Sheet1!C10:D1010").Value ' Locals Type: Variant/Variant(1 to 1001, 1 to 2) inData2 = Range("Sheet1!C10:D1010").Value ' Locals Type: Variant(1 to 1001, 1 to 2) inData2() = Range("Sheet1!C10:D1010").Value ' Locals Type: Variant(1 to 1001, 1 to 2)

- Assign an Excel range / array to VBA range object:

' Declaration Dim inData As Range ' a range object ' Assignment Set inData = Range("Sheet1!C10:D1010") ' Locals Type: Range/Range ' Note: Range("Sheet1!C10:D1010").Value is not a range ' because the value property returns an array of Values ' not an Object

- A 3 dimensional worksheet array?:

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet1").Activate Range("B3:C5").Select ActiveCell.Value = 1 ' Not practical for data as each WS range (r x c) is identical

- A 3 dimensional worksheet array (quasi):

Sub Quasi3Darray() ' Requires 3 worksheets ' Writes the cell address (r,c,d) ''(1,1,3) (1,2,3) (1,3,3) (1,4,3) ''(2,1,3) (2,2,3) (2,3,3) (2,4,3) '' ... Dim r As Long, c As Integer, d As Integer If Worksheets.Count < 3 Then Exit Sub For r = 1 To 5 For c = 1 To 4 For d = 1 To 3 Worksheets(d).Range("b3")(r, c).Value = "(" & r & "," & c & "," & d & ")" Next d Next c Next r End Sub

- This example was developed in Excel 2016 Pro 64 bit.
**Published**: 8th June 2018**Revised:**Saturday 9th of June 2018 - 06:01 AM, [Australian Eastern Time (AET)]

**Note:** comments welcome. Use the Ask a Question button on the Home page