VBA and Private Functions

Question

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

Answer 1

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:

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

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

In general, the formula is

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

Answer 2

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

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.

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

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.

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

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

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