Store in array single value produced by each step in a for loop VBA

Question

I have exactly the problem stated in this discussion:

Storing and Printing values in Array - VBA

but I need to see them through debug, not only print them.

Substantially, I created this code:

Sub Montecarlo()

Dim X0, Xi, T, dt, m, s, Zi, dXi As Double
Dim ArraydXi() As Variant

X0 = 10
T = 5
dt = 1
m = 0.01
s = 0.2

n = T / dt

For i = 1 To n
    Zi = Application.WorksheetFunction.Norm_S_Inv(Rnd())
    dXi = m * dt + s * (dt) ^ (1 / 2) * Zi
    ArraydXi() = Array(dXi)
Next

SumElements = Application.WorksheetFunction.Sum(ArraydXi())

Xi = X0 + SumElements

End Sub

Anyway there are two problems here:

1) when I go through debug, into ArraydXi() I see it is stored only the last (the fifth in this case) dXi value of the for loop, while I want to be stored all the five dXi values of each step of the for loop and

2) consequently in the SumElements variable, there is no trace of the sum of the five elements that should be stored into the array.

May I ask any idea to solve this problem?

score 1 · Answer 1 · answered Apr 05 '18 at 16:48

1

Guessing a little, but at no point do you declare the size of ArraydXi, and hence you are not storing each value in a separate element. Try this.

Sub Montecarlo()

Dim X0, Xi, T, dt, m, s, Zi, dXi As Double
Dim ArraydXi() As Variant

X0 = 10
T = 5
dt = 1
m = 0.01
s = 0.2

n = T / dt

ReDim ArraydXi(1 To n)

For i = 1 To n
    Zi = Application.WorksheetFunction.Norm_S_Inv(Rnd())
    dXi = m * dt + s * (dt) ^ (1 / 2) * Zi
    ArraydXi(i) = dXi
Next

SumElements = Application.WorksheetFunction.Sum(ArraydXi)

Xi = X0 + SumElements

End Sub

answered Apr 05 '18 at 16:48

SJR

22,986
6
18
26

Hi SJR, thanks, I tried your solution, but when running it gives me the run-time error '9': "Subscript out of range" at the ArraydXi(i) = dXi line. How I can fix this? – GPace Apr 05 '18 at 16:56
Hm, what is the value of `n` and what is the value of `dXi` when it errors? – SJR Apr 05 '18 at 16:59
The value of n is 5, while dXi is 0,118084708163063, it stucks at the first iteration. – GPace Apr 05 '18 at 17:05
I can't replicate that. Have you stepped through the code to check what's going on? Perhaps comment out the Zi line, hard-code the dXi line as equal to, say, 1 and check that that works. Are you definitely using the code as posted above? – SJR Apr 05 '18 at 17:10
Code is working *(despite missing declarations if `Option Explicit` is not set; BTW most variables should be of type `Double` and not `Variant`)* – T.M. Apr 05 '18 at 17:21

score 0 · Accepted Answer · answered Apr 05 '18 at 17:22

0

Please let me know wheter this solves the problem or not.

Sub Montecarlo()

Dim X0, Xi, T, dt, m, s, Zi, dXi As Double
Dim ArraydXi() As Variant

X0 = 10
T = 5
dt = 1
m = 0.01
s = 0.2

n = T / dt

ReDim ArraydXi(n)
For i = 1 To n
    Zi = Application.Norm_S_Inv(Rnd())
    dXi = m * dt + s * (dt) ^ (1 / 2) * Zi
    ReDim Preserve ArraydXi(i)
    ArraydXi(i) = dXi
    Debug.Print ArraydXi(i)
Next

SumElements = Application.Sum(ArraydXi)

Xi = X0 + SumElements

End Sub

answered Apr 05 '18 at 17:22

Moreno

608
1
9
24

Hi Moreno, your correction worked out perfectly, that's what I wanted! Substantially I had to declare the i counter into the array variable, that's what I missed. Thank you very much for your help and support! – GPace Apr 05 '18 at 17:43
Hint: if you want to write `ArraydXi` back to a given sheet (instead of `Debug.Print`) just use this line: `ThisWorkbook.Worksheets("Sheet1").[A2].Resize(n, 1) = WorksheetFunction.Transpose(ArraydXi)` – T.M. Apr 05 '18 at 17:50
I'm mystified as to how this code can work but mine doesn't. – SJR Apr 05 '18 at 18:12

Store in array single value produced by each step in a for loop VBA

2 Answers2