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 25th of February 2023 - 09:39 AM, [Australian Eastern Time (AET)]
Note: comments welcome. Use the Ask a Question button on the Home page
- An expression in an assignment statement: