CODEWITHSUNDEEP
vbaexcel
JungleDiff
JungleDiff

Reputation: 3485

simulating standard normal distribution in VBA

I have written a VBA code that iterates columns and rows and generate standard normal distribution:

Sub simulate_log_normal()
Dim i As Integer
Dim dblNorm_S_Dist As Double
ActiveSheet.Cells(34, 4).Select
For i = 34 To 64
    For j = 4 To 23
        Set current_cell = ActiveSheet.Cells(i, j).Select
        Set dlbNorm_S_Dist = WorksheetFunction.Norm_S_Dist()
        Set current_cell = dlmNorm_S_Dist
        Next j
Next i


End Sub

So it start at cell(34,4) and should generate a standard normal value for each column (column 4 to 23) and then for each row (row 34 to 64).

When I run it on Excel, it says:

Compile Error:
Argument not optional

What am I doing wrong?

Upvotes: 0

Views: 5994

Answers (2)

Alex-
Alex-

Reputation: 1

Do not forget to use Randomize before using Rnd, otherwise you'll see the same numbers over and over again, each time you'll run the code.

More info: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rnd-function

They say:"Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer."

Upvotes: 0

PeterD
PeterD

Reputation: 1511

Not sure what exactly you want to do with the Norm_S_Dist function but try something like this:

Sub simulate_log_normal()
Dim i As Integer
Dim dblNorm_S_Dist As Double
For i = 34 To 64
    For j = 4 To 23
        Cells(i, j) = WorksheetFunction.Norm_S_Dist(1, True)
    Next j
Next i

End Sub

Upvotes: 2

Related Questions