×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Loop until key press

Loop until key press

(OP)
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.

RE: Loop until key press

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?

RE: Loop until key press

>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.

RE: Loop until key press

>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.

RE: Loop until key press

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.

CODE -->

#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

RE: Loop until key press

(OP)
@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

CODE

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.

RE: Loop until key press

I'm not sure what you plan to do, but basing on the code I posted:
- Application.Onkey 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,
- GetAsyncKeyState tracks what key(s) are pressed "!": if SHIFT+"1", requires definitions Const VK_1 = &H31 '1 key and Const VK_SHIFT = &H10 'SHIFT key, test with GetAsyncKeyState(VK_1) And GetAsyncKeyState(VK_SHIFT)
- Call ActivateMyTimer(0.25) activates timer with interval as in argument, in seconds
- Timer_CallBackFunction is called by timer as long as it is active, it is passed to timer in SetTimer procedure. You can put here all the navigation you need.
Rnd(Now()) is for illustration only, you can use other seed, generate whole numbers using Application.WorksheetFunction.RandBetween 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.
Onkey method works for current excel instance, so you need testing if a procedure linked to key can be fired.

combo

RE: Loop until key press

>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 ...

RE: Loop until key press

Quote:

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.)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!