I am trying to use a formula to get a letter of the alphabet.
Formula:
=Keytable(RANDOM,ROW())
Function:
Function KeyTable(seed As Long, position As Long) As String
Dim i As Long
Stop
Dim calpha(1 To 26) As String
Dim alpha(1 To 26) As String
For i = 1 To 26
alpha(i) = Chr(i + UPPER_CASE - 1)
Next i
For i = 1 To 26
calpha(i) = alpha(seed Mod 27 - i)
Next i
Stop
KeyTable = calpha(position)
End Function
Result:
#Value!
When I step through the function, it never gets to the second stop. What is wrong?
RANDOM
is not a function in Excel. RAND()
is and it returns a float
between 0 and 1. You need an integer
to do modulus calculations.
To get a random integer, use:
INT ((upperbound - lowerbound + 1) * RAND() + lowerbound)
Then, once seed Mod 27 - i
becomes 0 or less, the function dies because arrays can't be indexed with 0 or less in VBA (or most languages).
But really all you need to do for a random letter is this:
=CHAR(RANDBETWEEN(65,90))