准蒙特卡洛分析中整行数据存储至数组失败求助

Hey there! Let's work through this issue with your quasi-Monte Carlo analysis. You nailed storing single-cell values to an array, but shifting to a full row of 10 cells is breaking your curvearray setup—this is a super common gotcha with Excel/VBA array handling, so let's break down the fixes.

Common Issues & Solutions #

1. Mismatched Array Dimensions #

When you grab a single cell with Range.Value, you get a scalar value (or a 1x1 array). But a full row (like A1:J1) returns a 2-dimensional array (even if it's only one row). If your original code was built for 1D arrays, this mismatch will cause errors.

Fix: Adjust Your Array to Handle 2D Data

If you want to keep each sample as a separate row in your array, declare and resize it as a 2D array:

Dim curvearray() As Variant
Dim sampleIndex As Integer
sampleIndex = 0

' Inside your sampling loop:
sampleIndex = sampleIndex + 1
' Resize the array to hold the new row (preserve existing data)
ReDim Preserve curvearray(1 To sampleIndex, 1 To 10)
' Assign the full row to the new array row
curvearray(sampleIndex, 1 To 10) = Range("A1:J1").Value

2. Forgetting to Resize the Array Properly #

If you're trying to append each row's data to a single 1D array, you need to calculate the correct size for ReDim Preserve and map the 2D row data to 1D indices.

Fix: Convert the Row to a 1D Array First

Dim curvearray() As Variant
Dim tempRow As Variant
Dim sampleIndex As Integer
Dim col As Integer
sampleIndex = 0

' Inside your sampling loop:
tempRow = Range("A1:J1").Value ' This is a 2D array (1 to 1, 1 to 10)
sampleIndex = sampleIndex + 1

' Resize the main array to fit 10 new values
ReDim Preserve curvearray(1 To sampleIndex * 10)

' Map the 2D row data to the 1D main array
For col = 1 To 10
    curvearray((sampleIndex - 1) * 10 + col) = tempRow(1, col)
Next col

3. 1-Based vs 0-Based Indexing #

Excel's range arrays default to 1-based indexing (starts at 1 instead of 0). If your original code used 0-based arrays, this can cause off-by-one errors when assigning row data.

Quick Check:

Double-check that your array indices match Excel's range indices. For example, tempRow(1, 1) corresponds to cell A1, not tempRow(0,0).

Final Tip #

If you're still getting errors, add a Debug.Print statement to inspect the array's structure after assignment:

Debug.Print UBound(curvearray, 1), UBound(curvearray, 2) ' For 2D arrays

This will help you confirm if the array is resizing correctly and holding the row data as expected.

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