Home » Forums » Open Source Learning Center » Question and Answer

Thank you very much

Submitted by sky_19 on 4 December, 2012 - 01:53

Thank you very much

‹ Pivot Table / Graph help How to merge cells based on the value of adjacent cell ›
»

Thank you very much for your

Submitted by sky_19 on 4 December, 2012 - 12:31.

Thank you very much for your help!

»

Well, You'll need a random

Submitted by AndyLitch on 4 December, 2012 - 08:20.

Well, You'll need a random number generator so I'll start you off with a function for generating them :-

Function Random(Lower As Integer, Upper As Integer) As Integer
Random = Int((Upper - Lower + 1) * Rnd + Lower)
End Function

Sub Main()
Dim X As Integer
X = Random(1, 10)
MsgBox X
End Sub

»

Random number

Submitted by manny on 4 December, 2012 - 09:56.

Hi,

Please, let me to improve your example. Use Randomize function along with Rnd to generate a random numbers:

Function Random(Lower As Integer, Upper As Integer) As Integer

    Randomize
    Random = Int((Upper - Lower + 1) * Rnd + Lower)

End Function

 

Best regards.

»

Good idea

Submitted by AndyLitch on 4 December, 2012 - 10:55.

Good idea - i forgot to put the randomize statement in

»

Thank you for your help! I

Submitted by sky_19 on 4 December, 2012 - 12:36.

Thank you for your help! I attached a file similiar with my final work. Can you give me an example of this?

»

Copy all of the following

Submitted by AndyLitch on 4 December, 2012 - 13:27.

Copy all of the following code into module 1 of the VBA and run the monte carlo sub routine. I've not annotated anything - that's up to you..

Function Random(Lower As Integer, Upper As Integer) As Integer

Randomize
Random = Int((Upper - Lower + 1) * Rnd + Lower)

End Function

Sub MonteCarlo()
Dim IniMoney As Single, MaxPlays As Integer, StopProfit As Single
Dim ColumnNum As Integer, CurrentProfit As Single, Y As Integer, X As Integer
Dim Result As Integer, Stake As Integer, CumTot As Integer

For X = 7 To 16
Cells(13, X).Select
ColumnNum = ActiveCell.Column
IniMoney = Sheet1.Cells(18, ColumnNum).Value
MaxPlays = Sheet1.Cells(19, ColumnNum).Value
StopProfit = Sheet1.Cells(20, ColumnNum).Value

Stake = 1
CumTot = IniMoney

For Y = 21 To 21 + MaxPlays
Result = Random(1, 2)
Sheet1.Cells(Y, ColumnNum).Value = Result

Select Case Result
Case 1 'WIN
CumTot = CumTot + Stake
Stake = 1
If cumtot = stopprofit then exit for
Case 2 'LOSE
CumTot = CumTot - Stake
Stake = Stake * 2
End Select

Next Y

Sheet1.Cells(13, ColumnNum).Value = CumTot
Next X

End Sub

»

Thank you very much for your

Submitted by sky_19 on 4 December, 2012 - 13:45.

Thank you very much for your availability to help me!

»

You're welcome

Submitted by AndyLitch on 4 December, 2012 - 13:52.

You're welcome

»

I analysed your code, but I

Submitted by sky_19 on 4 December, 2012 - 16:37.

I analysed your code, but I have some parts that I have difficults in this context. Can you give me an idea of the logic behinh your code? Thank you and I'm sorry bother you again, but I am a really beginner in VBA.

»

Sure, no problem.. I'd rather

Submitted by AndyLitch on 4 December, 2012 - 16:38.

Sure, no problem.. I'd rather explain it than just present a solution :-)

CumTot is a procedure level variable for the cumulative total (winnings)... When this value equals the maximum allowed the subroutine exits.

Stake is another procedure level variable amounting to the bet value for each gamble.. This value changes dependent on the results of the preceding result so needs to be a variable.

»