CODEWITHSUNDEEP
vbaexcelmarkov-chains
Jonathan
Jonathan

Reputation: 37

VBA coding - Generating Random Variable

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

Upvotes: 0

Views: 398

Answers (2)

John Coleman
John Coleman

Reputation: 52008

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:

enter image description here

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

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

Upvotes: 2

Related Questions