如何将非从索引1起始的数组范围正确赋值给Excel区域？

Hey there, let's break down your VBA array indexing problem and find a clean, efficient solution that avoids loops.

First, let's clarify why your initial code doesn't work: when you assign a Range directly to a Variant array, VBA automatically overwrites the array's dimensions to a 1-based 2D array—no matter how you ReDim it beforehand. That's why your ReDim Z(2 To 11) gets reset to Z(1 To 10, 1 To 1) after Z = range_test.

Now, here are two loop-free approaches that match your goal of aligning array logic with spreadsheet rows, while keeping performance high:

1. Use WorksheetFunction.Index for "array slicing" (like MATLAB) #

If you've already loaded the full range (including headers) into an array and need to extract a subset of rows, Excel's Index function lets you do this in one line—no loops required. It accepts an array of row numbers to pull exactly the elements you want:

Sub ArraySliceDemo()
    Dim fullRange As Range
    Dim dataArray As Variant
    
    ' Load the entire range (including header A1) into the array
    Set fullRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:A11")
    dataArray = fullRange.Value ' Now dataArray is (1 To 11, 1 To 1)
    
    ' Slice rows 2-11 and write directly to G2:G11
    ThisWorkbook.Worksheets("Sheet1").Range("G2:G11").Value = _
        WorksheetFunction.Index(dataArray, Evaluate("ROW(2:11)"), 1)
        
    ' Do the same for another array Y to H2:H11
    ' ThisWorkbook.Worksheets("Sheet1").Range("H2:H11").Value = _
    '     WorksheetFunction.Index(Y, Evaluate("ROW(2:11)"), 1)
End Sub

The Evaluate("ROW(2:11)") generates an array of {2, 3, ..., 11} which tells Index exactly which rows to extract. This runs on Excel's internal engine, so it's just as fast as direct array-Range assignment—way better than looping.

2. Skip the header directly in the Range (no array slicing needed) #

If your end goal is to work with data below the header, you can avoid loading the header into the array altogether by adjusting the Range upfront. This keeps your array aligned with the spreadsheet rows without extra work:

Sub ReadDataWithoutHeader()
    Dim dataRange As Range
    Dim dataArray As Variant
    
    ' Skip the header (A1) by offsetting 1 row and resizing to 10 rows
    Set dataRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:A11").Offset(1).Resize(10)
    dataArray = dataRange.Value ' Now dataArray is (1 To 10, 1 To 1)
    
    ' Write directly to D2:D11—perfect size match
    ThisWorkbook.Worksheets("Sheet1").Range("D2:D11").Value = dataArray
End Sub

A quick note on performance: you're absolutely right that loops are way less efficient than bulk array operations. VBA loops process each element individually in the VBA runtime, while array-Range assignments use Excel's optimized C++ backend. The Index method I showed above leverages this same backend, so it's just as fast as direct assignment.

内容的提问来源于stack exchange，提问作者Pherdindy