inefficient worksheet change by value

[creed12]

Hi

looking for some guidance on the following any help much appreciated.

I have spreadsheet (sheet1) reading live data from instruments approx 1-2 times a second, the data is placed into cells A1-A230.

One piece of data, in cell A2 is digital should it value change from 0 to 1 then i wish to use it as a trigger to set off a macro.

I tried to use worksheet Private Sub Worksheet_Change(ByVal target As Range) within the sheets1's code

Private Sub Worksheet_Change(ByVal target As Range)

If Target.Address = "$B$15" Then

If target <> old_value Then
'a change has occured in cell C7 so do your processing....
Call test
old_value = ActiveSheet.Range("$b$15")
Else
End If

endif

End Sub


with target set to "A2", the code runs ok, but whilst debugging i noticed
everytime any cell changes the Private Sub Worksheet_Change(ByVal target As Range) routine is called and then jumps out as a result of the if statements, but just calling the routine when operating normally as the instrument data is streaming in this code is continiously running and affecting excels ability to keep up with further supplementary calculations.

is there an alternative to "Private Sub Worksheet_Change" that only ever assesses one cell (A2)? or a way i can code this more effieciently



many thanks

 

[SkipVought]

hi,

only ever assesses one cell ([highlight]A2[/highlight])?

If Target.Address = "[highlight]$B$15[/highlight]" Then

[highlight]???[/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[creed12]

sorry reference cell A2 was as an example, so its not a case of the incorrect reference.

any other other ideas?

 

[SkipVought]

Your example ought to conform with your question, else it raises doubts about your veracity. You refer to A2, B15 and C7, yet B15 is the only reference in your code???

Your problem is old_value! Where is that variable declared? As posted, the scope of old_value is limited to this procedure and that's as long as it 'lives!' It contains NOTHING!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B15")) Is Nothing Then
    
        If Target.Value <> old_value Then
            'a change has occured in cell C7 so do your processing....
            Call test
            old_value = Target.Value
        End If
    
    End If

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[creed12]

skip

my apologies, perhaps if i start again

using the code below,
add a break point on the line
If Not Intersect(Target, Range("B15")) Is Nothing Then

then alter any cell other than a1 or b15,you see then the
Private Sub Worksheet_Change(ByVal Target As Range)

is called for every change, and although is immediatly stepped out of when reaching the if statement, it means for every change to any cell VB needs to react, which in my case if your updating 250 data points twice a second from instruements, means that it leaves excel very little time to carry out any other functions, i got a lot of functions going on processing the raw data, which in turn leads to more cells being altered and then more abortive runs of Private Sub Worksheet_Change(ByVal Target As Range).

So what i'm thinking is that i need a routine which only flags / reacts to a chnage of a single cell, and does not monitor all cells.

any ideas?

cheers in advance





if you past this code into any module
'------------------
Public temp
Public old_value
Sub test()

Worksheets("sheet1").Range("a1") = temp

temp = temp + 1

End Sub
'---------------

then this into sheet1's module

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B15")) Is Nothing Then
If Target.Value <> old_value Then
'a change has occured in cell C7 so do your processing....
Call test
old_value = Target.Value
End If
End If
End Sub
'------------------------------------------

 

[SkipVought]

then alter any cell other than a1 or b15,you see then the
Private Sub Worksheet_Change(ByVal Target As Range)

Actually, changing any cell on the sheet fires this procedure.

Actually, only a change to B15 executes the Test procedure. A change to A1 is irrelevant to whether Test runs or not.

I do not understand your prolem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B15")) Is Nothing Then
    [!]Application.EnableEvents = False[/!]
    If Target.Value <> old_value Then
      Call test
      old_value = Target.Value
    End If
    [!]Application.EnableEvents = True[/!]
  End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[creed12]

thanks guys, but its not the execution of the code thats the problem, its the fact that inherantly everytime any cell chnanges the proceedure is run, so with live data streaming in, the procedure is constantly being called for the other cells which are changing and therefore taking too much of the processors time.

 

[PHV]

So, Excel isn't the good tool to catch live data streaming ...

 

[Gavona]

Surely the event occurs whether or not you reference and make use of it. So every time there is a change in the sheet (while events are enabled) there is an overhead of this test:
If Not Intersect(Target, Range("B15")) Is Nothing Then

Does that really cause the issues?

If so then how about not using events but using the OnTime method to periodically run some code that checks for a change in your target cell.


Gavin

 

[N1GHTEYES]

It looks like Gavin's solution works, but it would be asynchronous with your input. I think there is a way to do what you want as an event procedure, but you'll need another sheet to your workbook.

Can you add another sheet, and on the new sheet include a cell containing a formula which triggers a change in the new sheet that causes the new sheet to recalculate when your criterion is met in your input data (this could be a simple If statement). Then write a worksheet_calculate() procedure for your new sheet which runs your macro. The advantage of doing it this way is that the procedure only triggers when your criterion is met.

I think the problem with your current method is that everytime you add data to the worksheet, each changed cell causes the worksheet change event, so if you are changing 200+ cells, it is running 200+ times every time you update. On the other hand the above process only runs once when your criterion is met.

Does that help?

Tony

 

[Gavona]

Good call Tony!

Gavin

 

[creed12]

cheers Tony . Gavin

I have foillowed a similar logic to that Gavin suggest works fine.

I've tried the suggestion you made Tony, as you say it cuts out the effect of the other cells the only downside is that as the cell in sheet one chnanges the associated cell in sheet 2 although not changing value it recalculats and so i've found the proceedure calls anyway.

Thanks everybody

 

[N1GHTEYES]

If you are happy with your solution, then that's what matters. Thanks for letting us know the outcome.

Though I'm not sure exactly what you're saying. Did the "new sheet" method work for you or not?

I tested it myself and found that it triggered when, and only when, the criterion was met. Changes anywhere on the original sheet except the target area did not trigger the procedure on the new sheet. Changes to the target area, which did not change whether it met the criterion, also did not trigger the procedure. The only way to trigger the procedure was to make a change to the original sheet data which changed whether it met the criterion or not.

Tony