vba 在 Excel 中计算一个静态随机数（计算一次）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8011032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compute a static random number (compute it once) in Excel
提问by studgeek
I'm looking for a way to compute a random number once in Excel. So its computed the first time its called, but then it doesn't change afterwards.
我正在寻找一种在 Excel 中计算一次随机数的方法。所以它在第一次被调用时计算，但之后它不会改变。
So for example, if I had something like this in B1 RANDONCE(A1), then the first time I put a value in A1 it would compute a random value but then it wouldn't change again. Or at least not until I changed A1 again.
例如，如果我在 B1 中有这样的东西，那么当我RANDONCE(A1)第一次在 A1 中放入一个值时，它会计算一个随机值，但它不会再次改变。或者至少在我再次更改 A1 之前不会。
I would like to do this without manually recopying B1 to turn it from a formula to a value as described here. Use of macros is fine.
我想这样做，而无需手动重新复制B1所描述从式把它的值在这里。使用宏没问题。
采纳答案by chris neilsen
You need a UDF with memory, so it knows if the cell has changed
您需要一个带内存的 UDF，以便它知道单元格是否已更改
This UDF will return a new random value when the refered to call changes, otherwise returns the last random value (ie no change)
Also return blank if source cell is blank (may or may not be what you require?)
当引用的调用发生变化时，此 UDF 将返回一个新的随机值，否则返回最后一个随机值（即没有变化）
如果源单元格为空，则返回空白（可能是也可能不是您需要的？）
Note: it has the problem that the Staticvalues are lost when the sheet is closed, so the value will change each time the sheet is opened.
注意：存在Static关闭表单时值丢失的问题，因此每次打开表单时该值都会改变。
Function randonce(r As Range)
Static trigger As Variant
Static v As Double
If r <> trigger Then
v = Rnd
trigger = r
End If
If Len(r) <> 0 Then
randonce = v
Else
randonce = vbNullString
End If
End Function
回答by Adam
I think I have a much easier way to do this. Complete your spreadsheet, then apply the =RANDBETWEEN function for that column of numbers. Then do this:
我想我有一个更简单的方法来做到这一点。完成您的电子表格，然后对该列数字应用 =RANDBETWEEN 函数。然后这样做：
- Copy the results from that column.
- Highlight the column and select "paste special" and select "values".
- 从该列复制结果。
- 突出显示该列并选择“选择性粘贴”并选择“值”。
Now you have the values most recently created and they are static.
现在您拥有最近创建的值并且它们是静态的。
回答by Scott Forbes
You can also use circular references to make a purely formula-driven "toggle switch," allowing the user to calculate a set of random numbers and then turn off further recalculations. Turn off circular reference warnings, and then put this formula in cell B3:
您还可以使用循环引用来制作纯粹由公式驱动的“切换开关”，允许用户计算一组随机数，然后关闭进一步的重新计算。关闭循环引用警告，然后将此公式放在单元格 B3 中：
=IF($B="YES",RAND(),B3)
If cell B1 contains "YES", B3 will generate a new random number with each spreadsheet recalculation; if B1 contains any other value, the current value of B3 will be retained.
如果单元格 B1 包含“是”，则 B3 将在每次电子表格重新计算时生成一个新的随机数；如果 B1 包含任何其他值，则将保留 B3 的当前值。
回答by JMax
You can create a UDF (User-Defined Function):
您可以创建一个 UDF（用户定义函数）：
Public Function Rand_once(ByVal r As Range)
Rand_once = Rnd
End Function
In the cell where you want the result, you can put:
在您想要结果的单元格中，您可以放置：
=Rand_once(A1)
The value will change (actually, being recalculated) only when the source value changes (aka A1).
仅当源值更改（又名A1）时，该值才会更改（实际上是重新计算）。
回答by aevanko
This obviously won't be the best solution if you have to track many cells, but if it's justA1 you need to track for changes, you can use an event to do your function in B1, then at the end, assign it the value it was given. I find this the most simple solution and it works as you require it to.
这显然不会是最好的解决办法，如果你要跟踪多个细胞，但如果它只是A1您需要跟踪更改，您可以使用事件做你的功能B1，然后在最后，分配给它的价值这是给的。我发现这是最简单的解决方案，它可以按您的要求工作。
Example:
例子：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A" Then
With Cells(1, 2)
.Value = "=rand()" 'or whatever
.Value = .Value
End With
End If
End Sub
