c_c_lai

¦^´_ 4# sunnyso
VBA Function Example

The RND function can only be used in VBA code in Microsoft Excel.
Here are some examples of what the RND function would return:

1.     Int ((10 - 1 + 1) * Rnd + 1)     '   ¶Ç¦^¤¶©ó 1 ¨ì 10 ¶¡ªº¶Ã¼Æ­È
   
2.     Int ((200 - 100 + 1) * Rnd + 100)   '  ¶Ç¦^¤¶©ó 100 ¨ì 200 ¶¡ªº¶Ã¼Æ­È
   
3.     Int ((600 - 300 + 1) * Rnd + 300) '  ¶Ç¦^ ¤¶©ó 300 ¨ì 600 ¶¡ªº¶Ã¼Æ­È

½Æ»s¥N½X

For example:

1. Sub Demo()
   
2.     Dim retNum As Integer
   
3. 
   
4.     retNum = Int ((300 - 200 + 1) * Rnd + 200)
   
5.     MsgBox "retNum ªº¶Ã¼Æ­È = " & retNum
   
6. End Sub

½Æ»s¥N½X

In this example, the variable called retNum would now contain a random number between 200 and 300.

c_c_lai

¦^´_ 14# stillfish00
³o¬O§Ú´ú¸Õªºµ²ªG¡G
A.  rng = myRnd(sngVal)

¤U¸ü (18.04 KB)

2013-11-1 09:09

B.  rng = myRnd(sngVal, 2 ^ 32)

¤U¸ü (17.97 KB)

2013-11-1 09:09

²¤¥[¼W­×³¡¤À¡G

1. Option Explicit
   
2. 
   
3. Sub Ex()
   
4.     Dim sngVal As Single, rng As Range, xi As Long
   
5.    
   
6.     With Sheets(1)
   
7.         .Cells.Clear
   
8.         
   
9.         For xi = 1 To 5
   
10.             Set rng = .Range(Chr(64 + xi) & "1")
    
11.             For sngVal = 1 To 20
    
12.                 '  rng = myRnd(sngVal)
    
13.                 rng = myRnd(sngVal, 2 ^ 32)
    
14.                 Set rng = rng.Offset(1)
    
15.             Next sngVal
    
16.         Next xi
    
17.         .Columns.AutoFit
    
18.     End With
    
19. End Sub
    
20. 
    
21. Function myRnd(Optional Number, Optional m As Double = 2 ^ 24) As Single
    
22.     Dim tmp As Double
    
23.     '  LCG Algorithm¡G
    
24.     '      X(n+1) = (a * X(n) + c) mod m
    
25.     Const a As Long = 1140671485
    
26.     Const c As Long = 12820163
    
27.     '  Const m As Long = 16777216    '  2^24
    
28.     Const x0 As Long = 1         '  ®Ú¾Ú§A¨Ï¥Î­þ²Õ°Ñ¼Æ¡Ax0¦³®É¦³»Ý­n²Å¦Xªº±ø¥ó¡C

½Æ»s¥N½X