VBA and private functions
For some reason, nothing happens when I run this macro. What could I be doing wrong?
(I am trying to fill each cell in a range with a randomly selected row.)
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
source to share
Rnd () always produces a (decimal) number between 0 and 1. So, if you want to get a random number between 1 and 6, try replacing the Select Case line with the following:
Select Case Int((6 * Rnd) + 1)
The "math part" finds a number between 1 and 6, and Int () converts it to an integer.
In general, the formula
Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
source to share
John beat me on re RND's comment, but that's not the only problem.
This is a stylistic problem first. If you have a case case statement, always include the else case. In your code, this is what would be accomplished. If you stepped through the code with a debugger, you would see the problem yourself.
Also for every cell in the range doesn't seem to act the way you want. In Excel 2004, you will get a blank value
I would really look at examples in Excel Help. It has one for example properties of a cell that shows how to set values in a range.
In this case, I like it more (depending on which settings base is installed)
for row = 1 to 21
for col = 1 to 35
dataRange.Cells(Row, col).Value = azerothSays()
next
next
To make debugging easier, I would code the rando bit in the function as
Dim r as integer
r = Int((6 * Rnd) + 1)
Select Case (r)
Then you can find out what the random number is in the debugger
source to share