Update spreadsheets with blazing fast speed. Use arrays to easily write and update thousands of rows at sub-second speeds. Easily convert ranges (congruent, non-congruent or filtered) to arrays, use column headers and row values to find and update data and then write the array back to the sheet. Don’t wait another second for your macros to finish processing. Go from time consuming, complex data search and updates to lighting fast performance with just a few lines of code. AWE_Array only requires a few lines of code to implement. Best yet, it’s FREE.
AWE_Array Functions
The below table is an overview of AWE_Array Functions
| Category | Function | Description | Example |
|---|---|---|---|
| Initialize | Initialize | Initialize the AWE_Array Class | Dim AWEArr As new AWE_Array: AWEArr.Initialize Rng, iHeaderRow |
| Columns | ColNbr | Search for and return a Column's number on the header row | Dim iCol As Long: iCol = AWEArr.ColNbr("Column Name") |
| FirstCol | Return the header row’s first column number | Dim iCol As Long: iCol = AWEArr.FirstCol | |
| LastCol | Return the header row’s last column number | Dim iCol As Long: iCol = AWEArr.LastCol | |
| Rows | FirstDataRow | Return the first data row’s number (1st row under the header) | Dim iRow As Long: iRow = AWEArr.LastDataRow |
| HdrRow | Return the header row’s number | Dim iRow As Long: iRow = AWEArr.HdrRow | |
| LastDataRow | Return the last data row’s number | Dim iRow As Long: iRow = AWEArr.LastDataRow | |
| RowNbr | Find the first or last row number in a range by column header and row value | Dim iRow As Long: iRow = AWEArr.RowNbr(“RowIdxOrVal”, “ColIdxOrNm”) | |
| Cell | Cell | Find the first/last cell range by column header name and row value | Dim rng As Range: set rng = AWEArr.Cell (“RowIdxOrVal”, “ColIdxOrNm”, xlNext) |
| SearchFor | SearchFor | Searches through a range (by column or row) and returns the first or last cell found | Dim rng As Range: set rng = AWEArr.SearchFor (SrchRange, "Search Value", xlNext) |
| Filter | IsFiltered | Returns true if there is a filter on the sheet | Dim bIsFiltered As Boolean: bIsFiltered = AWEArr.IsFiltered |
| IsRowVisible | Returns True if the role is visible; otherwise, False. | Dim bIsRowVisible As Boolean: bIsRowVisible = AWEArr.IsRowVisible(iRow) | |
| WriteArray | WriteArray | Write the array back to the range that was intially set in the Initialize Function | AWEArr.WriteArray |
Note, though AWE_Array is the perfect companion of AWE_Sheet it can also be used as a stand alone class.
AWE_Array Example
The below examples would typically require “Named Ranges” a “Table”, dozen lines of code, and minutes of processing time. AWE_Array only requires only a few lines of code, without using “Named Ranges” or Tables, and a few seconds of processing time!
To use the below examples create a worksheet called "Timecard", with columns "Employee Name", "Date", "Week", "TaskID", Task Desc", "Hours", "Rate", "Revenue", and "Email Address" on row 3. Copy and paste code from the below Implementation section into a AWE_Sheet class in your workbook. Finally, copy, paste and run the below macros from your worksheet. Run the function Create100KRows to create 100,000 rows of data in just 3 seconds. Alternatively, download the example workbook which includes code to create test data.
Fast Easy Processing - Load data from your entire sheet or just individual columns into the AWE_Array. Once loaded, retrieve data and update data using column header names. This means your sheet column order can dynamically change without breaking your Macro code. You are also no longer bound to Named Ranges or Tables.
Function FastProcessing()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Timecard")
'--- Initialize AWE_Array - Load data from the entire sheet into the array or indvidual columns ---
Dim iRow As Long, AWEArr As New AWE_Array: AWEArr.Initialize HdrRowNb:=3, _
rng:=ws.Range(ws.Cells(4, 1), ws.Cells(ws.UsedRange.Rows.Count, 9))
'--- Quickly loop through the array looking for criteria, make updates... ---
For iRow = AWEArr.FirstDataRow To AWEArr.LastDataRow
'--- Use column names to retrieve and update data ---
If AWEArr.Cell(iRow, "TaskID") = "Task-127" Or AWEArr.Cell(iRow, "TaskID") = "Task-145" Then
AWEArr.Cell(iRow, "Rate") = 227.5
AWEArr.Cell(iRow, "Revenue") = AWEArr.Cell(iRow, "Hours") * 227.5
AWEArr.Cell(iRow, "Employee Name") = "*" & AWEArr.Cell(iRow, "Employee Name")
End If
Next iRow
'--- Write the array back to the sheet ---
AWEArr.WriteArray
End Function
Flexible - Load filtered rows, non-congruent columns into an array, performs updates, and write it back to the sheet.
Enum Cols: EmpNm = 1: dates: Week: TaskID: TaskDesc: hours: Rate: Revenue: EmailAddr: End Enum
Function UpdateData()
Dim iRow As Long, ws As Worksheet: Set ws = ThisWorkbook.Sheets("Timecard")
Dim iHdrRow As Long, iFirstDataRow As Long: iHdrRow = 3: iFirstDataRow = 4
'--- Filter on the "TaskID" column with rows equal to "Task-127" or "Task-1iFirstDataRow5" ---
ws.Cells(iHdrRow, Cols.TaskID).AutoFilter field:=Cols.TaskID, _
Criteria1:=Array("Task-127", "Task-145"), Operator:=xlFilterValues
'--- Load multiple filtered and non-congruent column into AWE_Array ---
'--- Note, AWE_Sheet::Columns ("ColNm1", ColNm5",...) loads multiple columns in a single call ---
Dim AWEArr As New AWE_Array: AWEArr.Initialize HdrRowNb:=iHdrRow, _
rng:=Union(ws.Range(ws.Cells(iFirstDataRow, Cols.EmpNm), ws.Cells(ws.UsedRange.Rows.Count, Cols.EmpNm)), _
ws.Range(ws.Cells(iFirstDataRow, Cols.TaskID), ws.Cells(ws.UsedRange.Rows.Count, Cols.TaskID)), _
ws.Range(ws.Cells(iFirstDataRow, Cols.Rate), ws.Cells(ws.UsedRange.Rows.Count, Cols.Rate)), _
ws.Range(ws.Cells(iFirstDataRow, Cols.hours), ws.Cells(ws.UsedRange.Rows.Count, Cols.hours)), _
ws.Range(ws.Cells(iFirstDataRow, Cols.Revenue), ws.Cells(ws.UsedRange.Rows.Count, Cols.Revenue)))
'--- Perform Updates. Get and set data in the AWE_Array using header names ---
For iRow = 1 To AWEArr.LastDataRow
If AWEArr.IsRowVisible(iRow) = True Then
AWEArr.Cell(iRow, "Rate") = 227.5
AWEArr.Cell(iRow, "Revenue") = AWEArr.Cell(iRow, "Hours") * AWEArr.Cell(iRow, "Rate")
AWEArr.Cell(iRow, "Employee Name") = "*" & AWEArr.Cell(iRow, "Employee Name")
End If
Next iRow
'--- IMPORTANT, Clear the filters before calling AWE_Array::WriteAttay ---
ws.ShowAllData
'--- Write the array back to the sheet ---
AWEArr.WriteArray
End Function
Fast - Create fictitious timesheet data - 1,000 rows in 0 seconds, 100,000 rows in 3 seconds
and 1 million rows in 30 seconds. This would typically take 10 minutes or more.
Function Create100KRows()
Dim dt As Date, iNm As Long, sNm As String, iRndm As Long, iHr As Long, iTtlHrs As Long
Dim iRow As Long, iHdrRow As Long, iMaxRows As Long: iHdrRow = 3: iMaxRows = 100000
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Timecard")
'--- Turn ScreenUpdating Off, Clear filters, delete used rows, create range ---
Application.ScreenUpdating = False
If ws.FilterMode = True Then ws.ShowAllData
ws.Range(ws.Cells(iHdrRow + 1, 1), ws.Cells(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1, 1)).EntireRow.Delete
Dim rng As Range: Set rng = ws.Range(ws.Cells(iHdrRow + 1, 1), ws.Cells(iHdrRow + 1 + iMaxRows, 9))
'--- Create AWE_Array Object ---
Dim AWEArr As New AWE_Array: AWEArr.Initialize rng:=rng, HdrRowNb:=iHdrRow
'--- Iterate through all rows, populating names, dates, tasks... with ficticious data ---
Dim iTmr As Single: iTmr = Timer
iRow = AWEArr.FirstDataRow: Do While iRow <= iMaxRows
iNm = iNm + 1: sNm = "Name-" & Format(CStr(iNm), "0000")
'--- Add weekdays for an entire year ---
For dt = CDate("1/1/2021") To CDate("12/31/2021")
If Weekday(dt, vbMonday) > 5 Then
iTtlHrs = 0
Do While iTtlHrs < 8
'--- Update Employee Name thru Week Columns ---
AWEArr.Cell(iRow, Cols.EmpNm) = sNm
AWEArr.Cell(iRow, Cols.EmailAddr) = sNm & "@Domain.com"
AWEArr.Cell(iRow, Cols.dates) = dt
AWEArr.Cell(iRow, Cols.Week) = WorksheetFunction.WeekNum(dt)
'--- Create a random number from 1 to 100 for the TaskID, Task Desc, and Rate ---
iRndm = Int((100 * Rnd) + 1) + 100
AWEArr.Cell(iRow, Cols.TaskID) = "Task-" & iRndm
AWEArr.Cell(iRow, Cols.TaskDesc) = "Desc for Task-" & iRndm
AWEArr.Cell(iRow, Cols.Rate) = iRndm
'--- Create random hours from 1 to 8. Ensure only 8 hours are worked in a day ---
iHr = Int((8 * Rnd) + 1): If iTtlHrs + iHr > 8 Then iHr = 8 - iTtlHrs
iTtlHrs = iTtlHrs + iHr
AWEArr.Cell(iRow, Cols.hours) = iHr
'--- Update Revenue ---
AWEArr.Cell(iRow, Cols.Revenue) = iHr * iRndm
'--- Increment iRow and display progress on the StatusBar every 1000 rows---
iRow = iRow + 1: If iRow > (iMaxRows + 1) Then GoTo Exit_Create100KRows
If iRow Mod 10000 = 0 Then
Application.StatusBar = Int((iRow / iMaxRows) * 100) & "% Complete"
End If
Loop
End If
Next dt
Loop
Exit_Create100KRows:
'--- Write the array to the sheet ---
AWEArr.WriteArray
Application.ScreenUpdating = True
MsgBox "Created " & iMaxRows & " rows in " & _
Format((Timer - iTmr) / 86400, "hh:mm:ss") & " seconds."
End Function
Implementation
Create the AWE_Array class in your workbook (see How to Create a Class for more information) and copy the below code into it. All function definitions, parameters and examples are in the source code. Additionally, Visual Basics Class IntelliSense will provide a code completion aid for AWE_Array by listing its functions and parameters as you type. For even more flexibility and power, implement AWE_Sheet. Though AWE_Array is a stand alone class, it was created to be a companion class for AWE_Sheet.
'*****************************************************************************************************
'*****************************************************************************************************
'* AWE_ARRAY: Manipulate millions of lines of code within seconds not minutes. AWE_Array converts **
'* ranges to arrays, allows you to find and update the data in arrays by column name or **
'* row value. It can be used as a stand alone class or as the perfect companion to the **
'* AWE_Sheet class. **
'* SITE: www.AutomationWithExcel.com **
'* Author: Mike Libby **
'*****************************************************************************************************
'*****************************************************************************************************
Option Explicit
'====================================================================================================
'== PRIVATE VARIABLES ===============================================================================
'====================================================================================================
Private m_Worksheet As Worksheet
Private m_Range As Range
'--- Array Variables ---
Private m_HdrArr As Variant
Private m_RngArr As Variant
Private m_ColArr As Variant
Private m_VisRowArr As Variant
'====================================================================================================
'== INITIALIZE ======================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' Initialize - Initialize the AWE_Array
' Params:
' rng as Variant - A Worksheet range
' Optional HdrRowNb as Long - The header's row number. Default = 1
' Optional MaxRows - The number of rows to create the array with. Default = rng.rows.count.
' Examples: Dim AWESh as new AWE_Array: AWESh.Initialize SheetRange, 3, 1000000
' Note: Do not include the header row in the rng. Column header names will be retrieved so that
' you can reference them by name in subsequent calls.
'----------------------------------------------------------------------------------------------------
Public Function Initialize(rng As Range, Optional HdrRowNb As Long = 1, Optional MaxRows As Long = 0)
Set m_Worksheet = rng.Parent: Set m_Range = rng
Dim rArea As Range, rCol As Range, icol As Long, sColNms As String, sRng As Variant, rCols As Range
Dim ColMax As Long, RowMax As Long, iRow As Long, rRow As Range
'--- If MaxRows > 0 then resize the array to MaxRows ---
If MaxRows > 0 Then Set m_Range = m_Range.Resize(MaxRows + 1)
'--- Create m_HdrArr, Header Array ---
For Each sRng In Split(m_Range.Address, ",")
Set rCols = m_Worksheet.Range(sRng)
For Each rCol In rCols.Columns
If sColNms <> "" Then sColNms = sColNms & ";"
sColNms = sColNms & rCol.EntireColumn.Cells(HdrRowNb).Value
Next rCol
Next sRng
m_HdrArr = Split(sColNms, ";"): ReDim Preserve m_HdrArr(LBound(m_HdrArr) + 1 To UBound(m_HdrArr) + 1)
'--- Create m_ColArr, Data Array ---
ReDim m_ColArr(LBound(m_HdrArr) To UBound(m_HdrArr)): icol = 0
ReDim m_RngArr(LBound(m_HdrArr) To UBound(m_HdrArr))
For Each sRng In Split(m_Range.Address, ",")
Set rCols = m_Worksheet.Range(sRng)
For Each rCol In rCols.Columns
icol = icol + 1
m_ColArr(icol) = rCol.Value
Set m_RngArr(icol) = rCol
Next rCol
Next sRng
'--- Create m_VisRowArr, Visible Row Array ---
If IsFiltered Then
ReDim m_VisRowArr(LBound(m_ColArr(1)) To UBound(m_ColArr(1)))
For Each rRow In m_RngArr(1).SpecialCells(xlCellTypeVisible)
m_VisRowArr(rRow.row - HdrRowNb) = True
Next rRow
End If
End Function
'====================================================================================================
'== COLUMNS =========================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' ColNbr As Long - Search for and return a Column's number on the header row
' Params: ColNbrOrNameOrRange as Variant. A column number, name, or Range.
' Example: Dim iCol as Long: iCol = AWESh.ColNbr("Column Name").
' Note: a critical message displays if the column is not found and then processing stops.
'----------------------------------------------------------------------------------------------------
Public Function ColNbr(ColIdxOrNm As Variant) As Long
If IsNumeric(ColIdxOrNm) Then ColNbr = CLng(ColIdxOrNm) _
Else: ColNbr = SearchFor(CStr(ColIdxOrNm), m_HdrArr)
If ColNbr = -1 Then
'--- Fatal Error: display message and end processing if the ColIdxOrNm was not found ---
MsgBox "AWE_Array::ColNbr::m_HdrArr does not contain header: " & ColIdxOrNm, vbCritical
End
End If
End Function
'----------------------------------------------------------------------------------------------------
' FirstCol As Long - Return the header row's first column number
' Example: Dim iCol as Long: iCol = AWESh.FirstCol
'----------------------------------------------------------------------------------------------------
Public Property Get FirstCol() As Long: FirstCol = LBound(m_HdrArr): End Property
'----------------------------------------------------------------------------------------------------
' LastCol As Long - Return the header row's last column number
' Example: Dim iCol as Long: iCol = AWESh.LastCol
'----------------------------------------------------------------------------------------------------
Public Property Get LastCol() As Long: LastCol = UBound(m_HdrArr): End Property
'====================================================================================================
'== ROWS ============================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' FirstDataRow As Long - Return the first data row's (the row under the header row) number.
' Example: Dim iRow as Long: iRow = AWESh.FirstDataRow
'----------------------------------------------------------------------------------------------------
Public Property Get FirstDataRow() As Long: FirstDataRow = LBound(m_ColArr(1)): End Property
'----------------------------------------------------------------------------------------------------
' LastDataRow As Long - Return the last data row's number
' Example: Dim iRow as Long: iRow = AWESh.LastDataRow
'----------------------------------------------------------------------------------------------------
Public Property Get LastDataRow() As Long: LastDataRow = UBound(m_ColArr(1)): End Property
'----------------------------------------------------------------------------------------------------
' RowNbr As Long - Find and return the row number in a specifical column on the sheet
' Params: RowNbrOrSrchVal As Variant - A sheet's row number or value to find within the column.
' ColNbrOrName As Variant - A column number or name on the sheet.
' Optional SearchDirection As Long = xlNext - Search direction (xlNext or xlPrevious) to use.
' Example: Dim iRow as Long: iRow = AWESh.RowNb("SearchVal", "Column Name")
'----------------------------------------------------------------------------------------------------
Public Function RowNbr(RowIdxOrVal As Variant, ColIdxOrNm As Variant) As Long
Dim icol As Long: icol = ColNbr(ColIdxOrNm)
If IsNumeric(RowIdxOrVal) Then RowNbr = CLng(RowIdxOrVal) _
Else: RowNbr = SearchFor(CStr(RowIdxOrVal), m_ColArr(icol))
End Function
'====================================================================================================
'== CELL ============================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' Cell As Variant - Searches for and returns a cell's value.
' Params: RowNbrOrSrchVal As Variant - A sheet's row number or value to find within the column.
' ColNbrOrName As Variant - A column number or name on the sheet.
' Optional SearchDirection As Long = xlNext - Search direction (xlNext or xlPrevious) to use.
' Example: Dim var As Variant: set var = AWESh.Cell ("Search Value", "Column Name")
'----------------------------------------------------------------------------------------------------
Public Property Get Cell(RowNbrOrSrchVal As Variant, ColIdxOrNm As Variant) As Variant
Dim icol As Long, iRow As Long
If IsNumeric(ColIdxOrNm) Then icol = CLng(ColIdxOrNm) Else icol = ColNbr(ColIdxOrNm)
If IsNumeric(RowNbrOrSrchVal) Then iRow = CLng(RowNbrOrSrchVal) _
Else iRow = RowNbr(RowNbrOrSrchVal, icol)
If iRow = -1 Then Cell = Nothing Else Cell = m_ColArr(icol)(iRow, 1)
End Property
'----------------------------------------------------------------------------------------------------
' Cell - Searches for and sets a cell's value.
' Params: RowNbrOrSrchVal As Variant - A sheet's row number or value to find within the column.
' ColNbrOrName As Variant - A column number or name on the sheet.
' Optional SearchDirection As Long = xlNext - Search direction (xlNext or xlPrevious) to use.
' Example: Dim var As Variant: set var = AWESh.Cell ("Search Value", "Column Name")
' Note, Prompts user on whether to continue if the the SrchVal is not found in the column.
'----------------------------------------------------------------------------------------------------
Public Property Let Cell(RowNbrOrSrchVal As Variant, ColIdxOrNm As Variant, Value As Variant)
Dim icol As Long: icol = ColNbr(ColIdxOrNm)
Dim iRow As Long: iRow = RowNbr(RowNbrOrSrchVal, icol)
If iRow = -1 Then
If MsgBox("AWE_Array::Cell::" & ColIdxOrNm & " does not contain RowNbrOrSrchVal: " _
& RowNbrOrSrchVal & ", Continue?", vbCritical, vbYesNo) = vbNo Then End
End If
If iRow <> -1 Then m_ColArr(icol)(iRow, 1) = Value
End Property
'====================================================================================================
'== SEARCHFOR =======================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' SearchFor as long - Returns the array occurance where the search string was found; otherwise, -1
' Params: SrchStr as String - Value to search for
' Arr as Variant - An array of values
' Example: Dim filter as Boolean: filter = AWESh.IsFiltered
'----------------------------------------------------------------------------------------------------
Public Function SearchFor(SrchStr As String, Arr As Variant) As Long
On Error Resume Next
SearchFor = -1: SearchFor = Application.Match(SrchStr, Arr, 0)
End Function
'====================================================================================================
'== FILTER ==========================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' IsFiltered As Boolean - Returns True if there is a filter on the sheet; otherwise, False.
' Example: Dim bFiltered as Boolean: bFiltered = AWESh.IsFiltered
'----------------------------------------------------------------------------------------------------
Public Property Get IsFiltered() As Boolean
If m_Worksheet.FilterMode = True Then
If m_Worksheet.AutoFilter.Range.Areas.Count > 0 Then
IsFiltered = True
End If
End If
End Property
'---------------------------------------------------------------------------------------------------------------------
' IsRowVisible - Returns True if a role is visible; otherwise, False.
' Example: Dim rng As Range: set rng = AWESh.HeaderRange
'---------------------------------------------------------------------------------------------------------------------
Public Property Get IsRowVisible(iRow As Long) As Boolean
IsRowVisible = m_VisRowArr(iRow)
End Property
'====================================================================================================
'== WRITE ARRAY =====================================================================================
'====================================================================================================
'----------------------------------------------------------------------------------------------------
' WriteArray - Write the array back to the range that was intially set in the Initialize Function.
' Example: AWESh.WriteArray ()
'----------------------------------------------------------------------------------------------------
Public Function WriteArray()
Dim icol As Long: icol = 0
If IsFiltered Then
MsgBox "Critical Error: Clear Filters before calling AWE_Array::WriteArray", vbCritical: End
End If
For icol = LBound(m_ColArr) To UBound(m_ColArr)
m_RngArr(icol).Value = m_ColArr(icol)
Next icol
End Function
Download Example Workbook
Add comment
Comments