Question

create vba procedure which prompts the user for the size of the data s/he is interested in generating. prompt the user for a mean and standard deviation for the normal distribution; store these in variables. prompt the user for a lambda value for the exponential distribution; store this in a variable. prompt the user for a lower and upper bound value for the uniform distribution; store these in variables. then call the following three functions: a. normal function: (pass size, mean, stdev) generate a column of random values from the normal distribution. b. exponential function: (pass size, lambda) generate another column of random values from the exponential distribution. c. uniform distribution:(pass size, lower and upper bound) generate another column of random values from the uniform distribution.

do this in vba please

Answer 1

VBA Code:

Sub statfuntions()
'variable declarations
Dim size As Integer
Dim mean As Double
Dim SD As Double
Dim Lambda As Double
Dim LoBound As Double
Dim UpBound As Double
Dim Out As Boolean
  
'getting input from the user
size = InputBox("Enter the size of data to be generated")
mean = InputBox("Enter the Mean of a Normal Distrubution")
SD = InputBox("Enter the SD of a Normal Distrubution")
Lambda = InputBox("Enter the Lamda of a Exponential Distrubution")
LoBound = InputBox("Enter the lower bound of a Uniform Distrubution")
UpBound = InputBox("Enter the upper bound of a Uniform Distrubution")
'calling functions to generate values
Out = normal(size, mean, SD)
Out = exp(size, Lambda)
Out = uniform(size, LoBound, UpBound)
End Sub
Function normal(size, mean, stdev)
For i = 1 To size
'generate normal function values in column A
Cells(i, 1) = WorksheetFunction.Norm_Dist(Rnd(), mean, stdev, False)
Next i
normal = True
End Function
Function exp(size, Lambda)
For i = 1 To size
'generate exponential function values in column B
Cells(i, 2) = WorksheetFunction.Expon_Dist(Rnd(), Lambda, False)
Next i
exp = True
End Function
Function uniform(size, lb, ub)
For i = 1 To size
'generate uniform function values in column C
Cells(i, 3) = Rnd() * (ub - lb + 1) + lb
Next i
uniform = True
End Function

Sample Output:

Entered values:

size = 10
mean = .5
SD = .2
Lambda = .5
LoBound = 1
UpBound = 10

Output:

1.605681972	0.316248	3.438452
1.627171553	0.403218	10.79078
1.871084548	0.356251	1.609162
0.264526537	0.388923	4.902915
1.90394053	0.386735	4.649954
1.243204969	0.396675	5.898947
0.234798297	0.419	2.556631
0.491259712	0.408377	5.744592
0.115551771	0.436917	3.572677
1.948378754	0.486293	7.287519