software

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc… The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND()) 1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which I’d like that
column to be randomly chosen.

Entries in Column A110%0.1
Entries in Column B220%0.2
Entries in Column C330%0.3
Entries in Column D440%0.4

TOTAL 10100%1

So for example, I’d like column A to be picked at random 10% of the time,
column B 20% etc…

Does anybody know how to weight the RAND function in this way please? I’d be
really grateful for any help.
Many thanks in advance, Neil Goldwasser

First, John Walkenbach has a very nice Hangman game at:
www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets(quot;sheet1quot;)
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = quot;quot; Then
'do nothing
Else
iCtr = iCtr 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr gt; 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) 1) * Rnd LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox quot;no words!quot;
End If

End SubNeil Goldwasser wrote:
gt;
gt; Hi! I am adapting a hangman game for my literacy students. The wordbank is
gt; stored in a different sheet to the game, arranged in columns by the length of
gt; the words. So for example, 5-letter words would be stored in column A,
gt; 6-letter ones in column B etc… The students can either choose how many
gt; letters there are in the word, or this can be randomly chosen. If so, a
gt; column will be randomly selected, and a word is then randomly selected from
gt; this column. Selecting the word is not a problem, but I am having difficulty
gt; randomly selecting a column, because though random, I want the probabilities
gt; of the columns being chosen to be relative to the number of entries in it,
gt; i.e. I want the random process for selecting a column to be weighted.
gt;
gt; I could have simply selected a column at random, with each column having an
gt; equal chance of being picked:
gt; =INT(([Number of columns]*RAND()) 1)
gt; However, this means that words appearing in a column with very few entries
gt; will occur far more often than words in a column ten times the size, for
gt; example.
gt;
gt; The probabilities for each column being picked need to be weighted, as in
gt; the following example:
gt; The percentage shows the percentage of the whole wordbank that appears in
gt; that column, the decimals represent the probability with which I’d like that
gt; column to be randomly chosen.
gt;
gt; Entries in Column A 1 10% 0.1
gt; Entries in Column B 2 20% 0.2
gt; Entries in Column C 3 30% 0.3
gt; Entries in Column D 4 40% 0.4
gt;
gt; TOTAL 10 100% 1
gt;
gt; So for example, I’d like column A to be picked at random 10% of the time,
gt; column B 20% etc…
gt;
gt; Does anybody know how to weight the RAND function in this way please? I’d be
gt; really grateful for any help.
gt; Many thanks in advance, Neil Goldwasser

--

Dave Peterson

Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
one that I have been adapting! (with his permission) Because I work in
Learnng Support, I needed to alter the wordbank a bit to suit the level of my
students, and this involved adapting the code to allow for 5-letter words, as
well as some particular spellings that are 13-letters long etc...

John very kindly left the code open so I have been able to edit parts of it,
and it functions just fine, but I haven't been able to rejig the random
choosing of the column. In John's version, all the columns have 200 entries,
so each word has an exactly equal probability of coming up, but the wordbank
I intend to use would not be uniform. That's why I'd tried to weight the
random process, but I'm at a loss for ideas on how to.

Thank you very much for the code though. I apologise for not forgetting to
mention that it was John's version that I'm adapting, but thank you for your
help.

Any ideas on how to weight the probabilities?
Cheers, Neil
quot;Dave Petersonquot; wrote:

gt; First, John Walkenbach has a very nice Hangman game at:
gt; www.j-walk.com/ss/excel/files/general.htm
gt; (look for Hangman)
gt;
gt; But have you thought about just building one giant array from all the words and
gt; then randomly choosing a word from that giant array.
gt;
gt; It seems to me that this would be equivalent and easier to code.
gt;
gt; Option Explicit
gt; Sub testme()
gt;
gt; Dim myArr() As String
gt; Dim iCtr As Long
gt; Dim iCol As Long
gt; Dim iRow As Long
gt; Dim FirstRow As Long
gt; Dim FirstCol As Long
gt; Dim LastRow As Long
gt; Dim LastCol As Long
gt; Dim wks As Worksheet
gt; Dim myChosenValue As Long
gt; Dim myChosenWord As String
gt;
gt; Set wks = Worksheets(quot;sheet1quot;)
gt; With wks
gt; FirstRow = 1
gt; FirstCol = 1
gt;
gt; LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
gt; iCtr = 0
gt; For iCol = FirstCol To LastCol
gt; For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
gt; If .Cells(iRow, iCol).Value = quot;quot; Then
gt; 'do nothing
gt; Else
gt; iCtr = iCtr 1
gt; ReDim Preserve myArr(1 To iCtr)
gt; myArr(iCtr) = .Cells(iRow, iCol).Value
gt; End If
gt; Next iRow
gt; Next iCol
gt; End With
gt;
gt; If iCtr gt; 0 Then
gt; Randomize
gt; myChosenValue _
gt; = Int((UBound(myArr) - LBound(myArr) 1) * Rnd LBound(myArr))
gt; myChosenWord = myArr(myChosenValue)
gt; MsgBox myChosenWord
gt; Else
gt; MsgBox quot;no words!quot;
gt; End If
gt;
gt; End Sub
gt;
gt;
gt; Neil Goldwasser wrote:
gt; gt;
gt; gt; Hi! I am adapting a hangman game for my literacy students. The wordbank is
gt; gt; stored in a different sheet to the game, arranged in columns by the length of
gt; gt; the words. So for example, 5-letter words would be stored in column A,
gt; gt; 6-letter ones in column B etc… The students can either choose how many
gt; gt; letters there are in the word, or this can be randomly chosen. If so, a
gt; gt; column will be randomly selected, and a word is then randomly selected from
gt; gt; this column. Selecting the word is not a problem, but I am having difficulty
gt; gt; randomly selecting a column, because though random, I want the probabilities
gt; gt; of the columns being chosen to be relative to the number of entries in it,
gt; gt; i.e. I want the random process for selecting a column to be weighted.
gt; gt;
gt; gt; I could have simply selected a column at random, with each column having an
gt; gt; equal chance of being picked:
gt; gt; =INT(([Number of columns]*RAND()) 1)
gt; gt; However, this means that words appearing in a column with very few entries
gt; gt; will occur far more often than words in a column ten times the size, for
gt; gt; example.
gt; gt;
gt; gt; The probabilities for each column being picked need to be weighted, as in
gt; gt; the following example:
gt; gt; The percentage shows the percentage of the whole wordbank that appears in
gt; gt; that column, the decimals represent the probability with which I’d like that
gt; gt; column to be randomly chosen.
gt; gt;
gt; gt; Entries in Column A 1 10% 0.1
gt; gt; Entries in Column B 2 20% 0.2
gt; gt; Entries in Column C 3 30% 0.3
gt; gt; Entries in Column D 4 40% 0.4
gt; gt;
gt; gt; TOTAL 10 100% 1
gt; gt;
gt; gt; So for example, I’d like column A to be picked at random 10% of the time,
gt; gt; column B 20% etc…
gt; gt;
gt; gt; Does anybody know how to weight the RAND function in this way please? I’d be
gt; gt; really grateful for any help.
gt; gt; Many thanks in advance, Neil Goldwasser
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Only the one I suggested--and that weights each word the same. But that seems
equivalent to me.

Neil Goldwasser wrote:
gt;
gt; Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
gt; one that I have been adapting! (with his permission) Because I work in
gt; Learnng Support, I needed to alter the wordbank a bit to suit the level of my
gt; students, and this involved adapting the code to allow for 5-letter words, as
gt; well as some particular spellings that are 13-letters long etc...
gt;
gt; John very kindly left the code open so I have been able to edit parts of it,
gt; and it functions just fine, but I haven't been able to rejig the random
gt; choosing of the column. In John's version, all the columns have 200 entries,
gt; so each word has an exactly equal probability of coming up, but the wordbank
gt; I intend to use would not be uniform. That's why I'd tried to weight the
gt; random process, but I'm at a loss for ideas on how to.
gt;
gt; Thank you very much for the code though. I apologise for not forgetting to
gt; mention that it was John's version that I'm adapting, but thank you for your
gt; help.
gt;
gt; Any ideas on how to weight the probabilities?
gt; Cheers, Neil
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; First, John Walkenbach has a very nice Hangman game at:
gt; gt; www.j-walk.com/ss/excel/files/general.htm
gt; gt; (look for Hangman)
gt; gt;
gt; gt; But have you thought about just building one giant array from all the words and
gt; gt; then randomly choosing a word from that giant array.
gt; gt;
gt; gt; It seems to me that this would be equivalent and easier to code.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme()
gt; gt;
gt; gt; Dim myArr() As String
gt; gt; Dim iCtr As Long
gt; gt; Dim iCol As Long
gt; gt; Dim iRow As Long
gt; gt; Dim FirstRow As Long
gt; gt; Dim FirstCol As Long
gt; gt; Dim LastRow As Long
gt; gt; Dim LastCol As Long
gt; gt; Dim wks As Worksheet
gt; gt; Dim myChosenValue As Long
gt; gt; Dim myChosenWord As String
gt; gt;
gt; gt; Set wks = Worksheets(quot;sheet1quot;)
gt; gt; With wks
gt; gt; FirstRow = 1
gt; gt; FirstCol = 1
gt; gt;
gt; gt; LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
gt; gt; iCtr = 0
gt; gt; For iCol = FirstCol To LastCol
gt; gt; For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
gt; gt; If .Cells(iRow, iCol).Value = quot;quot; Then
gt; gt; 'do nothing
gt; gt; Else
gt; gt; iCtr = iCtr 1
gt; gt; ReDim Preserve myArr(1 To iCtr)
gt; gt; myArr(iCtr) = .Cells(iRow, iCol).Value
gt; gt; End If
gt; gt; Next iRow
gt; gt; Next iCol
gt; gt; End With
gt; gt;
gt; gt; If iCtr gt; 0 Then
gt; gt; Randomize
gt; gt; myChosenValue _
gt; gt; = Int((UBound(myArr) - LBound(myArr) 1) * Rnd LBound(myArr))
gt; gt; myChosenWord = myArr(myChosenValue)
gt; gt; MsgBox myChosenWord
gt; gt; Else
gt; gt; MsgBox quot;no words!quot;
gt; gt; End If
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; Neil Goldwasser wrote:
gt; gt; gt;
gt; gt; gt; Hi! I am adapting a hangman game for my literacy students. The wordbank is
gt; gt; gt; stored in a different sheet to the game, arranged in columns by the length of
gt; gt; gt; the words. So for example, 5-letter words would be stored in column A,
gt; gt; gt; 6-letter ones in column B etc… The students can either choose how many
gt; gt; gt; letters there are in the word, or this can be randomly chosen. If so, a
gt; gt; gt; column will be randomly selected, and a word is then randomly selected from
gt; gt; gt; this column. Selecting the word is not a problem, but I am having difficulty
gt; gt; gt; randomly selecting a column, because though random, I want the probabilities
gt; gt; gt; of the columns being chosen to be relative to the number of entries in it,
gt; gt; gt; i.e. I want the random process for selecting a column to be weighted.
gt; gt; gt;
gt; gt; gt; I could have simply selected a column at random, with each column having an
gt; gt; gt; equal chance of being picked:
gt; gt; gt; =INT(([Number of columns]*RAND()) 1)
gt; gt; gt; However, this means that words appearing in a column with very few entries
gt; gt; gt; will occur far more often than words in a column ten times the size, for
gt; gt; gt; example.
gt; gt; gt;
gt; gt; gt; The probabilities for each column being picked need to be weighted, as in
gt; gt; gt; the following example:
gt; gt; gt; The percentage shows the percentage of the whole wordbank that appears in
gt; gt; gt; that column, the decimals represent the probability with which I’d like that
gt; gt; gt; column to be randomly chosen.
gt; gt; gt;
gt; gt; gt; Entries in Column A 1 10% 0.1
gt; gt; gt; Entries in Column B 2 20% 0.2
gt; gt; gt; Entries in Column C 3 30% 0.3
gt; gt; gt; Entries in Column D 4 40% 0.4
gt; gt; gt;
gt; gt; gt; TOTAL 10 100% 1
gt; gt; gt;
gt; gt; gt; So for example, I’d like column A to be picked at random 10% of the time,
gt; gt; gt; column B 20% etc…
gt; gt; gt;
gt; gt; gt; Does anybody know how to weight the RAND function in this way please? I’d be
gt; gt; gt; really grateful for any help.
gt; gt; gt; Many thanks in advance, Neil Goldwasser
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Firstly, my apologies for absent-mindedly forgetting to respond to this very
helpful post Dave. I posted the initial question during a particularly busy
period at work, and I'm afraid it got swallowed up with all the other things
I was working on - I'm very sorry!

For other people that may be browsing for similar solutions, there is also
another useful procedure code for forming one big array on the following post:
quot;Combine several columns of different length into one single columnquot;

Many thanks again for your help Dave, Neil
quot;Dave Petersonquot; wrote:

gt; Only the one I suggested--and that weights each word the same. But that seems
gt; equivalent to me.
gt;
gt; Neil Goldwasser wrote:
gt; gt;
gt; gt; Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
gt; gt; one that I have been adapting! (with his permission) Because I work in
gt; gt; Learnng Support, I needed to alter the wordbank a bit to suit the level of my
gt; gt; students, and this involved adapting the code to allow for 5-letter words, as
gt; gt; well as some particular spellings that are 13-letters long etc...
gt; gt;
gt; gt; John very kindly left the code open so I have been able to edit parts of it,
gt; gt; and it functions just fine, but I haven't been able to rejig the random
gt; gt; choosing of the column. In John's version, all the columns have 200 entries,
gt; gt; so each word has an exactly equal probability of coming up, but the wordbank
gt; gt; I intend to use would not be uniform. That's why I'd tried to weight the
gt; gt; random process, but I'm at a loss for ideas on how to.
gt; gt;
gt; gt; Thank you very much for the code though. I apologise for not forgetting to
gt; gt; mention that it was John's version that I'm adapting, but thank you for your
gt; gt; help.
gt; gt;
gt; gt; Any ideas on how to weight the probabilities?
gt; gt; Cheers, Neil
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; First, John Walkenbach has a very nice Hangman game at:
gt; gt; gt; www.j-walk.com/ss/excel/files/general.htm
gt; gt; gt; (look for Hangman)
gt; gt; gt;
gt; gt; gt; But have you thought about just building one giant array from all the words and
gt; gt; gt; then randomly choosing a word from that giant array.
gt; gt; gt;
gt; gt; gt; It seems to me that this would be equivalent and easier to code.
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testme()
gt; gt; gt;
gt; gt; gt; Dim myArr() As String
gt; gt; gt; Dim iCtr As Long
gt; gt; gt; Dim iCol As Long
gt; gt; gt; Dim iRow As Long
gt; gt; gt; Dim FirstRow As Long
gt; gt; gt; Dim FirstCol As Long
gt; gt; gt; Dim LastRow As Long
gt; gt; gt; Dim LastCol As Long
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; Dim myChosenValue As Long
gt; gt; gt; Dim myChosenWord As String
gt; gt; gt;
gt; gt; gt; Set wks = Worksheets(quot;sheet1quot;)
gt; gt; gt; With wks
gt; gt; gt; FirstRow = 1
gt; gt; gt; FirstCol = 1
gt; gt; gt;
gt; gt; gt; LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
gt; gt; gt; iCtr = 0
gt; gt; gt; For iCol = FirstCol To LastCol
gt; gt; gt; For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
gt; gt; gt; If .Cells(iRow, iCol).Value = quot;quot; Then
gt; gt; gt; 'do nothing
gt; gt; gt; Else
gt; gt; gt; iCtr = iCtr 1
gt; gt; gt; ReDim Preserve myArr(1 To iCtr)
gt; gt; gt; myArr(iCtr) = .Cells(iRow, iCol).Value
gt; gt; gt; End If
gt; gt; gt; Next iRow
gt; gt; gt; Next iCol
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; If iCtr gt; 0 Then
gt; gt; gt; Randomize
gt; gt; gt; myChosenValue _
gt; gt; gt; = Int((UBound(myArr) - LBound(myArr) 1) * Rnd LBound(myArr))
gt; gt; gt; myChosenWord = myArr(myChosenValue)
gt; gt; gt; MsgBox myChosenWord
gt; gt; gt; Else
gt; gt; gt; MsgBox quot;no words!quot;
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Neil Goldwasser wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hi! I am adapting a hangman game for my literacy students. The wordbank is
gt; gt; gt; gt; stored in a different sheet to the game, arranged in columns by the length of
gt; gt; gt; gt; the words. So for example, 5-letter words would be stored in column A,
gt; gt; gt; gt; 6-letter ones in column B etc… The students can either choose how many
gt; gt; gt; gt; letters there are in the word, or this can be randomly chosen. If so, a
gt; gt; gt; gt; column will be randomly selected, and a word is then randomly selected from
gt; gt; gt; gt; this column. Selecting the word is not a problem, but I am having difficulty
gt; gt; gt; gt; randomly selecting a column, because though random, I want the probabilities
gt; gt; gt; gt; of the columns being chosen to be relative to the number of entries in it,
gt; gt; gt; gt; i.e. I want the random process for selecting a column to be weighted.
gt; gt; gt; gt;
gt; gt; gt; gt; I could have simply selected a column at random, with each column having an
gt; gt; gt; gt; equal chance of being picked:
gt; gt; gt; gt; =INT(([Number of columns]*RAND()) 1)
gt; gt; gt; gt; However, this means that words appearing in a column with very few entries
gt; gt; gt; gt; will occur far more often than words in a column ten times the size, for
gt; gt; gt; gt; example.
gt; gt; gt; gt;
gt; gt; gt; gt; The probabilities for each column being picked need to be weighted, as in
gt; gt; gt; gt; the following example:
gt; gt; gt; gt; The percentage shows the percentage of the whole wordbank that appears in
gt; gt; gt; gt; that column, the decimals represent the probability with which I’d like that
gt; gt; gt; gt; column to be randomly chosen.
gt; gt; gt; gt;
gt; gt; gt; gt; Entries in Column A 1 10% 0.1
gt; gt; gt; gt; Entries in Column B 2 20% 0.2
gt; gt; gt; gt; Entries in Column C 3 30% 0.3
gt; gt; gt; gt; Entries in Column D 4 40% 0.4
gt; gt; gt; gt;
gt; gt; gt; gt; TOTAL 10 100% 1
gt; gt; gt; gt;
gt; gt; gt; gt; So for example, I’d like column A to be picked at random 10% of the time,
gt; gt; gt; gt; column B 20% etc…
gt; gt; gt; gt;
gt; gt; gt; gt; Does anybody know how to weight the RAND function in this way please? I’d be
gt; gt; gt; gt; really grateful for any help.
gt; gt; gt; gt; Many thanks in advance, Neil Goldwasser
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;