VBA 和私有函数

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1597136/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:54:30  来源:igfitidea点击:

VBA and Private Functions

excelvba

提问by Nick Heiner

For some reason, nothing happens when I run this macro. What might I be doing wrong?

出于某种原因,当我运行这个宏时没有任何反应。我可能做错了什么?

(I'm trying to fill each cell in the range with a randomly selected string.)

(我试图用随机选择的字符串填充范围内的每个单元格。)

Sub ktr()

    Dim dataRange As Range

    Set dataRange = Range("A1:V35")

    For Each cell In dataRange
        response = azerothSays()
        cell.Value = response
    Next cell

End Sub

Private Function azerothSays()

    Dim result As String

    Select Case Rnd(6)
        Case 1
            result = "CELIBACY"
        Case 2
            result = "WORMS"
        Case 3
            result = "AGING"
        Case 4
            result = "MARRIAGE"
        Case 5
            result = "CHEMISTRY"
        Case 6
            result = "DISINTIGRATE"
    End Select

    azerothSays = result

End Function

回答by JohnK813

Rnd() always produces a (decimal) number between 0 and 1. So, if you wanted to get a random number from 1 to 6, try replacing your Select Case line with this:

Rnd() 总是产生一个介于 0 和 1 之间的(十进制)数。所以,如果你想得到一个从 1 到 6 的随机数,试着用这个替换你的 Select Case 行:

Select Case Int((6 * Rnd) + 1)

The "math part" finds a number between 1 and 6, and Int() converts it to a whole number.

“数学部分”查找 1 到 6 之间的数字,Int() 将其转换为整数。

In general, the formula is

一般来说,公式是

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

回答by user151019

John beat me to the comment re RND but that is not the only problem.

约翰击败了我对 RND 的评论,但这不是唯一的问题。

First is a stylistic issue. If you have a select case statement the always include a case else. In you code this is what would have been executed. If you stepped through the code with a debugger you would have seen the issue for yourself.

首先是文体问题。如果您有一个 select case 语句,则始终包含一个 case else。在您的代码中,这就是执行的内容。如果您使用调试器单步调试代码,您就会亲眼看到这个问题。

Also for each cell in range does not seem to act as you wish. In Excel 2004 you get an empty value back

此外,范围内的每个单元格似乎都没有按您的意愿行事。在 Excel 2004 中,您会得到一个空值

I would really look at the examples in the Excel help. It has one for cells property example shhowing how to set the values in a range.

我真的会看看 Excel 帮助中的示例。它有一个单元格属性示例,展示了如何在一个范围内设置值。

In this case more like (depending on what option base is set to)

在这种情况下更像是(取决于设置的选项基数)

for row = 1 to 21
  for col = 1 to 35
      dataRange.Cells(Row, col).Value = azerothSays()
  next
next

To make debugging easier I would have coded the rando bit in the function as

为了使调试更容易,我会将函数中的rando位编码为

Dim r as integer
r = Int((6 * Rnd) + 1)
Select Case (r)

Then you can see what the random number is in the debugger

然后就可以在调试器中看到随机数是什么了