Loop until key press 1

[kwbMitel]

I'm creating a random number generator for D&D Gaming purposes for character creation.

I've had issues with RND before so I thought I would introduce a human interface where it will just continue to generate numbers until a key is pressed.

So it is continually generating and displaying the output, key is pressed, one final output, and stop.

Ideally the key press will be the space bar but I'm not picky.

Thoughts?

**********************************************
Any intelligent fool can make things bigger and more complex… It takes a touch of genius – and a lot of courage to move in the opposite direction.

 

[Deniall]

I don't remember the specifics, but I have read somewhere that Excel's VBA function RND() does have some weaknesses, even in later versions of Excel. However (according to whatever it was I read) the spreadsheet environment's RAND() function was much improved starting with Excel-2010. Can you work things to use RAND() rather than RND(), assuming you are using >=2010?

 

[strongm]

>I've had issues with RND before so I thought I would introduce a human interface where it will just continue to generate numbers until a key is pressed.

Keen to understand how you think this will improve random number generation.

 

[strongm]

>VBA function RND() does have some weaknesses

It uses a power of 2 modulo linear congruent generator> Weaknesses are basically a relatively short period (iterations before it starts again …) and the fact that low bits have a shorter period than the high bits. Indeed, the lowest bit never changes …

But I'd suggest that for D&D character generation it is more than sufficient.

 

[combo]

Mixing a code I have for years for API timer with found for key state reader, the code below works in excel. You need to activate space bar procedure first. Next active cell is populated with given frequency by random numbers when space bar is pressed. The timer code may require conditional compiling too to be more secure.

#If VBA7 Then
    'declare virtual key event listener
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" _
            (ByVal vKey As Long) As Integer
#Else
    'declare virtual key event listener
    Private Declare Function GetAsyncKeyState Lib "user32" _
            (ByVal vKey As Long) As Integer
#End If

Declare Function SetTimer _
    Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerfunc As Long) _
As Long

Declare Function KillTimer _
    Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long) _
As Long

Public TimerID As Long            'Turn on and off with this ID
Public TimerActive As Boolean     'Is the timer active
Public Const VK_SPACE = &H20      'SPACEBAR key

' manage activation of SPACEBAR key functionality
Sub InitializeSpaceBarTrigger()
Application.OnKey " ", "RndNumberInActiveCell"
End Sub

Sub DeactivateSpaceBarTrigger()
Application.OnKey " ", ""
End Sub

' manage TIMER - to generate ticks with desired frequency
Public Sub ActivateMyTimer(ByVal sec As Long)
sec = sec * 1000
If TimerActive Then Call DeActivateMyTimer

On Error Resume Next
TimerID = SetTimer(0, 0, sec, AddressOf Timer_CallBackFunction)
TimerActive = True
End Sub

Public Sub DeActivateMyTimer()
    KillTimer 0, TimerID
End Sub

Sub RndNumberInActiveCell()
Call ActivateMyTimer(0.25)
End Sub

Public Sub Timer_CallBackFunction(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, _
    ByVal Systime As Long)
Dim aCell As Range
On Error GoTo errh
Set aCell = ActiveCell
If GetAsyncKeyState(VK_SPACE) Then
    aCell = Rnd(Now())
Else
    Call DeActivateMyTimer
End If

errh:
If Not TimerActive Then Call DeActivateMyTimer
End Sub

combo

 

[kwbMitel]

@Strongm

I see the improvement from the fact that although it is possible for the RND function to repeat sequences, it is unlikely that a human can repeat where to stop a sequence. I am generating numbers within a very narrow band so I am unconcerned with the actual numbers as I am bound to have significant repetition regardless. There is also the human psyche to consider in creating the illusion of control

@Combo, I am self taught in programming so I have many weaknesses. Is there a way to dumb that down?

What I want to achieve

Define keypressvalue as "!"
Do While keypressvalue = "!"
  For x = 1 to 6
     Generate Number and update Cell with value
     Pause for time to see digits
     Re-position to next cell
  Next X
getkeypressvalue
loop

**********************************************
Any intelligent fool can make things bigger and more complex… It takes a touch of genius – and a lot of courage to move in the opposite direction.

 

[combo]

I'm not sure what you plan to do, but basing on the code I posted:
- [tt]Application.Onkey[/tt] defines key (first argument) and procedure (second argument) that will be fired when key is pressed. Empty procedure name cancels hotkey. You have to run both of them somehow to start and stop hotkey,
- [tt]GetAsyncKeyState[/tt] tracks what key(s) are pressed "!": if SHIFT+"1", requires definitions [tt]Const VK_1 = &H31 '1 key[/tt] and [tt]Const VK_SHIFT = &H10 'SHIFT key[/tt], test with [tt]GetAsyncKeyState(VK_1) And GetAsyncKeyState(VK_SHIFT)[/tt]
- [tt]Call ActivateMyTimer(0.25)[/tt] activates timer with interval as in argument, in seconds
- [tt]Timer_CallBackFunction[/tt] is called by timer as long as it is active, it is passed to timer in [tt]SetTimer[/tt] procedure. You can put here all the navigation you need.
[tt]Rnd(Now())[/tt] is for illustration only, you can use other seed, generate whole numbers using [tt]Application.WorksheetFunction.RandBetween[/tt] or whatever you need. It is a place to code actions.
Note that wrongly handled API functions can crash host application, so you need to be careful and save your work frequently.
[tt]Onkey[/tt] method works for current excel instance, so you need testing if a procedure linked to key can be fired.

combo

 

[strongm]

>There is also the human psyche to consider in creating the illusion of control



That's the only bit that makes sense to me here. Getting a human to stop a sequence of numbers from VBA's PRNG versus simply generating a single number by the PRNG makes no difference to the randomness of the result. Unless, of course there is something broken about the way you are generating the random numbers ...

 

[Deniall]

There is also the human psyche to consider in creating the illusion of control

If you are happy merely with the illusion, you might be able to simplify your keystroke trapping by still using RND() or RAND().[ ] (I have no experience whatsoever with keystroke trapping.)