# xlf QandA series

## WS VBA range array comparison

### QUESTION

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

## 1. Excel

### 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

### 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)]