Whats The Maximum Nr Of Worksheet_change Events?

[knarfo]

hello,
Whats the maximum nr of Worksheet_Change events per second/millisecond?
I have a program which put data in Excel cells.
I try to catch them all with Worksheet_Change. like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d3:e40")) Is Nothing Then
Open "c:\log.txt" For Append As #1
Write #1, Date & "," & Time & " , " & Target.Row & " , " & Target.Column
Close #1
End If
End Sub

It happens often that the source program writes 5 values in the Range d3:e40
and Worksheet_change it misses 1 of the 5 values
thanks
frank

 

[SkipVought]

Hi,

I really do not understand what you are doing. You KNOW that each row that you write to is a change. What ADDED VALUE is the worksheet_change event? Why not add the Date/Time stamp as you write?

An EVENT used is to TRAP something that would be otherwise MISSED. If I cause an event in code, I do not need to trap the event. In fact, many times EnableEvents will be DISABLED during the execution of code because it gets in the way and can cause recursive processing.

I guess I do not understand what you are trying to do.

Skip,

_[red][/red]

 

[CBasicAsslember]

I have a program which put data in Excel cells.

Are you writing to a range of cells rather then writing one cell at a time?

 

[stevexff]

Whats the maximum nr of Worksheet_Change events per second/millisecond?

How fast is your box? What else is running on it? How long is a piece of string?


Also, continually opening and closing the log file on every event is going to be uber-slow. Consider doing this some other way.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[knarfo]

thank you very much for replying.
(I noticed that the code above works fine. I doesnt miss any updates and the logfile is written everytime there is an update.)

I'l try to be more clear.

1. A program writes values in the range d3:e40 really fast
2. It can be 1 cell or upto 38 at the (nearly) the same time
3. Depending on the cell row and collumn, Excel needs to call sub_a or sub_b

The problem occurs that when I use the follow up code(2 subs). This requires some computer resources. That means that the second update or later can be missed by the worksheet change event. The example:
(To keep it really simple):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d3:e40")) Is Nothing Then
a = Target.Cells.Row
b = Target.Cells.Column
If b = 4 and cells(a,b) > 0 Then Call sub_a(a)
If b = 5 and cells(a,b) > 0 Then Call sub_b(a)
End Sub

Sub sub_a(a)
End Sub
Sub sub_b(a)
End Sub

(So i took out the log file)
It looks like that when Sub_a or Sub_b is called and there is a new update received that it is sometimes missed by the worksheet_change event.
How can I prevent that?
Let's say d3,e7,d15,e30,d37 are given a value of 1 at nearly the same time.
So The Worksheet_change event is called 5 times and the corresponding subs are called.
Why does it sometimes happen that for example the 3rd update (d15) is missed by Excel?
Is it possible to run sub_a and sub_b in a seperate thread?

thanks in advance
frank

 

[SkipVought]

I would not use the worksheet_change event, since your program ought to know what cells got updated when.

Skip,

_[red][/red]

 

[knarfo]

Well,

The program (a financial charting package) calls a dll which writes values to cells in Excel. Excel detects cell change and passes orders to another program to shoot orders to the stock exchange.
frank

 

[CBasicAsslember]

Well, now it makes sense. When you are running several programs the operating system allocates processor time to each program. IOW, only one program is actually running at any one time. It just looks like they are all running at the same time. If the chart program manages to write 2 values in it's allocated time it would be the same as writing a range of values. Your code will only capture one of the changes unless you check to see how many cells changed. You need something like:

Target.Cells.Count

You can then loop through all the changes.

 

[SkipVought]

Why did you not say so initially???

for each r in target
  'here's what to do for each cell that was changed
next

Skip,

_[red][/red]

 

[knarfo]

I was focusing on the worksheet_change event
Target.Cells.Count is never more then 1 because the DLL
writes the cells in Excel at nearly exactly the same time. It's really fast which is an advantage.

So the first time the worksheet_change event is triggered and
Sub sub_a(a) is called then 1 millisecond later the second worksheet_change event is triggered. But the computer is still busy processing the Sub sub_a(a) and misses the second update.
The sub_a(a) has some code in it I cannot leave out or improve because from there info is sent to the Stock Exchange software. But is also has a logfile part.

What's the best way to make a logfile?
(opening and closing the log file on every event is going to be uber-slow)


frank