Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 4th, 2005, 06:01 PM
Authorized User
 
Join Date: Feb 2005
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excecuting repeated code every one second

Hi all,

I have found some code on the web which runs a routine every 1 second (using an API call: winmm.dll).
THis code runs great when a user isnt typing in a cell in Excel. When a user does, an error occurs.

Does anyone know a solution to this problem, or has a better way to let a routine change a cell (in a certain interval), while the user is typing in another cell?

thanks in advance
max
 
Old March 5th, 2005, 04:36 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Max

I´m doing by this way
This does not make error while typing in a cell.
It waits until typing is finished

Sub StartAgain()
Start = Now + TimeSerial(0, 0, 1)
Application.OnTime Start, "StartAgain"
'*** Doing something
Debug.Print Time
End Sub

Sub StopStart()
On Error Resume Next
Application.OnTime Start, "StartAgain", , False
End Sub

-vemaju

 
Old March 5th, 2005, 06:11 AM
Authorized User
 
Join Date: Feb 2005
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you for your reply. Vemaju, I know this property of VBA.

What I am trying to do is making a timer which counts down while a user is editing cells (this timer has to run continuously). The .OnTime statement, as you said, updates when a user is done with entering a value, which is not what i want

Are there any other suggestions?

thanks in advance
max
 
Old March 6th, 2005, 08:38 AM
Authorized User
 
Join Date: Jun 2003
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I use SetTimer and KillTimer API functions with a callback to my timer procedure. I haven't seen it raise an error before so you may want to use that instead

eg:

Code:
Public Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long

Private mTimerId As Long

Public Sub beginTimer()
  Dim timerInterval As Long
  
  timerInterval = 1000 'milliseconds
  
  mTimerId = SetTimer(0, 0, timerInterval, AddressOf yourTimerProcedure)
End Sub

Public Sub endTimer()
  KillTimer 0, mTimerId
End Sub

Public Sub yourTimerProcedure(hWnd As Long, message As Long, idTimer As Long, dwTime As Long)
  'your code here, eg:
  Static counter As Long
  counter = counter + 1
  Debug.Print counter
End Sub
 
Old March 6th, 2005, 11:56 AM
Authorized User
 
Join Date: Feb 2005
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

gcianfanelli,

I used your code and adjusted it. See below:

Public Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long

Private mTimerId As Long

Public Sub beginTimer()
  Dim timerInterval As Long

  timerInterval = 1000 'milliseconds

  mTimerId = SetTimer(0, 0, timerInterval, AddressOf yourTimerProcedure)
End Sub

Public Sub endTimer()
  KillTimer 0, mTimerId
End Sub

Public Sub yourTimerProcedure(hWnd As Long, message As Long, idTimer As Long, dwTime As Long)
  'your code here, eg:
sheet1.range("A1").value = Sheet1.Range("A1").Value + 1
' Static counter As Long
' counter = counter + 1
' Debug.Print counter
End Sub
'
Try running this code. If you aren't typing in a cell, the code works fine.
However, if you select a cell while the code is running,
Excel crashes.

I want to be able to change cell content whilst the counter keeps running. Any way
to do that?

thanks

-Max
 
Old March 6th, 2005, 01:41 PM
Authorized User
 
Join Date: Jun 2003
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe it can't be done this way, although you could cheat and write the text to a floating text frame which is set to be the same size etc as cell A1. This still would, however, mean that the currently edited cell's contents would be "committed", and would make data entry quite difficult

Do you really have to update a specific cell every second? If it is for information only, couldn't you use a non-modal floating userform instead? Or even a custom toolbar control..
 
Old March 6th, 2005, 06:34 PM
Authorized User
 
Join Date: Feb 2005
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i put a label on the worksheet, and i can type in cells now. However, I think this is not a problem with code
but i think its Excel that somehow disables screenrefreshing when you type in a cell.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Dataset repeated values Narts C# 1 November 23rd, 2007 10:31 AM
matches without repeated items pongup BOOK: Beginning Regular Expressions 0 April 7th, 2007 09:05 PM
An error that has been repeated many times over Giuda BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 1 October 31st, 2006 06:05 PM
How can I stop repeated words elfstoneUK BOOK: Beginning Regular Expressions 1 September 8th, 2006 03:04 AM
How to delete repeated records in a query ramk_1978 SQL Language 11 March 15th, 2005 10:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.