VBA coding - Generating Random Variable

Question

I want to write a code into a VBA excel. I have prepared the algorithm that is shown below, but I do not know exactly how I will write it in VBA :/ Can someone help me with this?

1) Assign initial values: Current is 1, Year is 0, Result is empty string.

2) Generate a random variate u from U(0,1);

3) If Current is 1, and u ≤ 0.23, assign 0 to Current. If Current is 0, and u > 0.86, assign 1 to Current. Otherwise do nothing.

4) Increase Year by 1. Write Current at the end of the Result string.

5) If Year less than cell("A2").value, go to step (2). Otherwise return Result

score 2 · Answer 1 · answered Jan 03 '16 at 17:03

2

This translates your English to VBA

Sub Jona()
   Dim Current As Long, Yearr As Long
   Dim u As Double, Result As String

   Current = 1
   Yearr = 0

   While Yearr < Range("A2").Value
      u = Rnd()
      If Current = 1 And u <= 0.23 Then
         Current = 0
      ElseIf Current = 0 And u > 0.86 Then
         Current = 1
      End If
      Yearr = Yearr + 1
      Result = Result & Current
   Wend

   MsgBox Result
End Sub

answered Jan 03 '16 at 17:03

Gary's Student

95,722
10
59
99

1

Nice -- when I saw your code I realized that I had accidentally shadowed `Year()` in an essentially harmless but nevertheless ill-advised way. – John Coleman Jan 03 '16 at 17:11
Also If i want the result to appear in a range in excel(for example in column C) what I have to write in the last sentence? – Jonathan Jan 03 '16 at 17:13

score 2 · Accepted Answer · answered Jan 03 '16 at 17:06

Here is a UDF (user-defined function) that can be used directly on the spreadsheet:

Function Chain(years As Long) As String
    Dim i As Long, result As String
    Dim u As Single
    Dim current As Long

    Randomize
    Application.Volatile

    current = 1
    For i = 1 To years
        u = Rnd()
        If (current = 1 And u <= 0.23) Or (current = 0 And u > 0.86) Then
            current = 1 - current
        End If
        result = result & current
    Next i
    Chain = result
End Function

year is just a counter and your algorithm in effect describes a simple for-loop. In VBA it is more idiomatic to loop from 1 to n than 0 to n-1 for a simple counter -- though this is mostly a matter of taste. Also -- I changed year to i to prevent accidental shadowing of the built-in function year()

VBA has natural default values for variables, hence I didn't need to explicitly initialize result.

I made the function volatile -- so it recalculates whenever the spreadsheet does. This matches how RAND() works in Excel. If you don't want that -- remove the line Application.Volatile.

A Screenshot:

If i want the result to be in separate cells? i.e each number to be in a different cell, in column B as your screenshot? — Jonathan, Jan 03 '16 at 17:16
Possibilities: 1) it is possible to have the function return an array and use it as an array formula 2) it is possible to use a sub rather than a function if you pass the sub the range where the output is to be. 3) It is possible to leave it as is and use spreadsheet functions to extract the successive digits. 4) It is possible to bypass VBA entirely. In B2 enter 1 and then in C2 enter the formula `=IF(B2=1,IF(RAND()<=0.23,0,1),IF(RAND()>0.86,1,0))` and copy it for the desired number of columns, though without modification this wouldn't update with A2. — John Coleman, Jan 03 '16 at 18:18

VBA coding - Generating Random Variable

2 Answers2