Guides

Read and write Excel ranges efficiently

Keep Excel object-model calls outside the record-processing loop.

One read and one write#

Read a contiguous block with Value2, process its array in VBA, then assign the complete result to a correctly sized range. This avoids making an Excel object-model call for each individual cell. It is a workflow recommendation derived from the code’s array-based interface; no speedup was measured here.

SEE THE TRANSFORMATIONIllustrative rows: sort amount ascending

Before

Example worksheet values2D · 3 × 3

Rows 1..3 · Columns 1..3

row / col	1	2	3
1	“North”	120	“Posted”
2	“South”	80	“Pending”
3	“West”	200	“Posted”

After

Sorted values2D · 3 × 3

Rows 1..3 · Columns 1..3

row / col	1	2	3
1	“South”	80	“Pending”
2	“North”	120	“Posted”
3	“West”	200	“Posted”

This small dataset illustrates the same sort operation. The macro reads A2:C100: 99 × 3 in, 99 × 3 out. Actual values depend on the worksheet.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub ProcessWorksheetBlock()
    Dim ws As Worksheet
    Dim data As Variant, result As Variant
    Dim rowCount As Long, colCount As Long
    Set ws = ThisWorkbook.Worksheets(1)
    ' Fixed data block; row 1 contains headers and is not processed.
    data = ws.Range("A2:C100").Value2
    result = Arr.Sort(data, 2, 1)
    If Not IsArray(result) Then Exit Sub
    rowCount = UBound(result, 1) - LBound(result, 1) + 1
    colCount = UBound(result, 2) - LBound(result, 2) + 1
    ws.Range("E2").Resize(rowCount, colCount).Value2 = result
End Sub

This macro writes to columns E:G and leaves the original A:C data in place. Its fixed block includes blank rows if present. Choose the data range deliberately; do not rely on the library to detect a header, totals row, or dataset boundary. Value2 gives values rather than cell formulas or formatting. A write of Value2 overwrites formulas in the destination.

Application settings and cleanup#

The macro-mode helpers set global Excel settings. They do not remember previous settings. DisableMacroMode forces automatic calculation, events, alerts, and screen updating on. If the calling environment must be preserved, capture its settings and restore them in an error path as well as on success:

Sub ProcessWithCleanup()
    Dim priorUpdating As Boolean, priorEvents As Boolean, priorAlerts As Boolean
    Dim priorCalculation As XlCalculation
    Dim errorNumber As Long, errorText As String, errorSource As String
    Dim ws As Worksheet, data As Variant, result As Variant
    priorUpdating = Application.ScreenUpdating
    priorEvents = Application.EnableEvents
    priorAlerts = Application.DisplayAlerts
    priorCalculation = Application.Calculation
    On Error GoTo Failed
    LO.SetMacroMode
    Set ws = ThisWorkbook.Worksheets(1)
    data = ws.Range("A2:C100").Value2
    result = Arr.Sort(data, 2, 1)
    If IsArray(result) Then
        ws.Range("E2").Resize(UBound(result, 1), _
            UBound(result, 2)).Value2 = result
    End If
Cleanup:
    On Error Resume Next
    Application.Calculation = priorCalculation
    Application.DisplayAlerts = priorAlerts
    Application.EnableEvents = priorEvents
    Application.ScreenUpdating = priorUpdating
    On Error GoTo 0
    If errorNumber <> 0 Then Err.Raise errorNumber, errorSource, errorText
    Exit Sub
Failed:
    errorNumber = Err.Number
    errorText = Err.Description
    errorSource = Err.Source
    Resume Cleanup
End Sub

The code restores the captured values directly; the library’s DisableMacroMode would impose its fixed defaults. Worksheet lookup helpers use ThisWorkbook, which means the workbook containing the VBA project, not necessarily ActiveWorkbook.

Value2 and numeric values#

Value2 does not return the Currency and Date Variant subtypes used by Value. Such cell values can be represented as floating-point numbers. See Microsoft’s Range.Value2 reference. A date serial read as a number can therefore participate in these numeric statistics.