1

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?

Community
  • 1
  • 1
Arlen Beiler
  • 15,336
  • 34
  • 92
  • 135

2 Answers2

3

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))
zsalzbank
  • 9,685
  • 1
  • 26
  • 39
0

This code will return random letter of alphabet:

Function GetLetter()
    Dim letters As String
    Dim randomIndex As Byte

    letters = "abcdefghijklmnopqrstuvwxyz"
    randomIndex = Int((26 - 1 + 1) * Rnd() + 1) //Get random number between 1 and 26

    GetLetter = VBA.Mid$(letters, randomIndex, 1)
 End Function
Alex P
  • 12,249
  • 5
  • 51
  • 70
  • What is the `(26 - 1 + 1)` all about? – Arlen Beiler Dec 31 '10 at 17:56
  • The general formula for a random number between two numbers is: Int((High - Low + 1) * Rnd() + Low)). We want a random number bewteen 1 and 26 for the alphabet so just plug into the equation. NB - the Int is there to make sure number is a whole number. – Alex P Dec 31 '10 at 17:59