VBA循环单步结果存入数组及调试查看值的技术问题

Let's break down what's going wrong and how to fix it so you can properly store all your dXi values and inspect them during debugging.

The Core Problem #

Your current line ArraydXi() = Array(dXi) replaces the entire array with a new single-element array every time the loop runs. That's why only the last dXi value gets kept—all previous entries are overwritten. Additionally, you're missing explicit declarations for some variables (like n and SumElements), which can lead to unexpected behavior in VBA.

Step-by-Step Fixes #

• Explicitly Declare All Variables
  VBA defaults undeclared variables to Variant, which can cause type mismatches. Always use Dim for every variable to avoid bugs.
• Properly Initialize & Populate the Array
  Since you know the number of iterations (n = T/dt), pre-size the array before the loop for efficiency. If you ever need dynamic sizing later, use ReDim Preserve, but pre-sizing is better here.
• Enable Debugging Inspection
  Once the array is correctly populated, use VBA's built-in tools to view its contents during debugging.

Corrected Code #

Sub Montecarlo()
    ' Explicitly declare all variables with their types
    Dim X0 As Double, Xi As Double, T As Double, dt As Double
    Dim m As Double, s As Double, Zi As Double, dXi As Double
    Dim n As Integer, i As Integer
    Dim ArraydXi() As Double ' Use Double array for type safety instead of Variant
    Dim SumElements As Double

    ' Initialize your parameters
    X0 = 10
    T = 5
    dt = 1
    m = 0.01
    s = 0.2
    n = T / dt ' Calculate number of iterations

    ' Pre-size the array to match the number of iterations
    ReDim ArraydXi(1 To n) As Double

    For i = 1 To n
        ' Generate random normal variable
        Zi = Application.WorksheetFunction.Norm_S_Inv(Rnd())
        ' Calculate dXi for this iteration (use Sqr() for clearer square root)
        dXi = m * dt + s * Sqr(dt) * Zi
        ' Store dXi in the array at index i
        ArraydXi(i) = dXi
    Next i

    ' Optional: Calculate sum of array elements
    SumElements = Application.WorksheetFunction.Sum(ArraydXi)
    
    ' Add a breakpoint here to inspect the array
End Sub

How to Debug & View the Array #

1. Set a Breakpoint: Click in the margin next to the line after the loop (e.g., the SumElements line) to pause execution.
2. Open the Local Window: Go to View > Local Window in the VBA editor. You'll see ArraydXi listed—click the expand arrow next to it to view every stored dXi value.
3. Inspect Mid-Loop: If you want to check values during the loop, set a breakpoint inside the loop (e.g., after ArraydXi(i) = dXi) and use the Local Window to see the current state of the array.

Bonus Tip #

Instead of (dt)^(1/2), use VBA's built-in Sqr(dt) function—it's more readable and optimized for square root calculations.

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