Load Variant Arrays From Ranges Using the Evaluate Function
The Evaluate function provides a fast way to load a variant array from any adjacent range of cells. The resulting variant arrays are always 2 dimensional when two or more cells are loaded. The code examles below show various ways to load a Variant array.
Program Code
Option Explicit
' *******************************************************************
' All of the examples below in which the range is more than one cell
' create a 2-dimensional Variant array where:
' The first dimension is the row number relative to 1
' The second dimension is the column number relative to 1
'
' For example:
' varArray1 = Evaluate("=[" & ThisWorkbook.Name & "]Sheet1!E5:G14")
' Creates varArray1 with these dimensions:
' varArray1(1,1) through varArray1(10,3)
'
' To Test The Upper Bounds use this command in the Immediate Area
' ? UBound(varArray1,1) 'First Dimension of Rows
' ? UBound(varArray1,2) 'Second Dimension of Columns
' *******************************************************************
Public Sub Evalute01()
' ****************************************************************
' Use a Named Range To Load Array with Evaluate Function
' "ANamedRange" is defined by the Formulas Tab Name Manager
' ****************************************************************
Dim varArray1 As Variant
varArray1 = Evaluate("ANamedRange")
End Sub
Public Sub Evalute02()
' ****************************************************************
' Create A Temporary Named Range To Load An Array using Evaluate
' The Scope of the Named Range will be WORKBOOK
' ****************************************************************
Dim rngTestRange As Range
Dim varArray1 As Variant
Dim wkbEvaluateWorkbook As Workbook
Set wkbEvaluateWorkbook = ThisWorkbook
wkbEvaluateWorkbook.Names.Add _
Name:="TempRange", _
RefersTo:="=Sheet1!$E$1:$H$5"
varArray1 = Evaluate("TempRange")
Range("TempRange").Name.Delete
End Sub
Public Sub Evalute03()
' ****************************************************************
' Create A Temporary Named Range To Load An Array using Evaluate
' The Scope of the Range Name will be WORKSHEET
' ****************************************************************
Dim rngTestRange As Range
Dim varArray1 As Variant
Dim wkbEvaluateWorkbook As Workbook
Dim wksEvaluateWorksheet As Worksheet
Set wkbEvaluateWorkbook = ThisWorkbook
Set wksEvaluateWorksheet = wkbEvaluateWorkbook.Sheets("Sheet1")
wksEvaluateWorksheet.Names.Add _
Name:="TempRange", _
RefersTo:="=$E$1:$G$5"
varArray1 = Evaluate("TempRange")
Range("TempRange").Name.Delete
End Sub
Public Sub Evalute04()
' ****************************************************************
' Convert A Range Variable To An Address String for the Evaluate
' ****************************************************************
Dim rngTestRange As Range
Dim varArray1 As Variant
Dim wkbEvaluateWorkbook As Workbook
Dim wksEvaluateWorksheet As Worksheet
Dim strRangeName As String
Set wkbEvaluateWorkbook = ThisWorkbook
Set wksEvaluateWorksheet = wkbEvaluateWorkbook.Sheets("Sheet1")
Set rngTestRange = Range(wksEvaluateWorksheet.Cells(1, "E"), wksEvaluateWorksheet.Cells(5, "G"))
' ****************************************************************
' Use Worksheet Name Only
' ****************************************************************
varArray1 = Evaluate("=" & rngTestRange.Parent.Name & "!" & rngTestRange.Address)
' ****************************************************************
' Or Use Workbook and Worksheet Names
' ****************************************************************
varArray1 = Evaluate("=[" & rngTestRange.Parent.Parent.Name & "]" & rngTestRange.Parent.Name & "!" & rngTestRange.Address)
End Sub
Public Sub Evalute05()
' ****************************************************************
' Use String of Addresses To Load Variant Array using Evaluate
' This example only uses the worksheet name (no workbook name)
' ****************************************************************
Dim rngTestRange As Range
Dim varArray1 As Variant
Dim wkbEvaluateWorkbook As Workbook
Dim wksEvaluateWorksheet As Worksheet
Dim strRangeName As String
Set wkbEvaluateWorkbook = ThisWorkbook
Set wksEvaluateWorksheet = wkbEvaluateWorkbook.Sheets("Sheet1")
' ****************************************************************
' Hard Code Worksheet Name
' ****************************************************************
varArray1 = Evaluate("=Sheet1!E1:G5")
' ****************************************************************
' Or Use a Worksheet Variable and derive the Worksheet Name
' ****************************************************************
varArray1 = Evaluate("=" & wksEvaluateWorksheet.Name & "!E1:G5")
End Sub
Public Sub Evalute06()
' ****************************************************************
' Use String of Addresses To Load Variant Array using Evaluate
' Address using varArray1(1,1) and varArray1(1,2)
' This example uses both the workbook and worksheet names
' ****************************************************************
Dim rngTestRange As Range
Dim varArray1 As Variant
' ****************************************************************
' Use Full Workbook/Worksheet Notation - Hardcode Workbook Name
' ****************************************************************
varArray1 = Evaluate("=[EvaluateFunction.xlsm]Sheet1!E1:F1")
' ****************************************************************
' Use Full Workbook/Worksheet Notation - Use Workbook Variable
' ****************************************************************
varArray1 = Evaluate("=[" & ThisWorkbook.Name & "]Sheet1!E1:F1")
End Sub
Public Sub Evalute07()
' ****************************************************************
' Use String of Addresses To Load Double
' This does not produce an array, but a Double
' ****************************************************************
Dim rngTestRange As Range
Dim varArray1 As Variant
Dim wkbEvaluateWorkbook As Workbook
Dim wksEvaluateWorksheet As Worksheet
Dim strRangeName As String
Set wkbEvaluateWorkbook = ThisWorkbook
Set wksEvaluateWorksheet = wkbEvaluateWorkbook.Sheets("Sheet1")
varArray1 = Evaluate("=Sheet1!E1")
End Sub