[RESOLVED] Worksheet_Calculate not working when grabbing data from OPC server

[phuz]

Hi all...new to the forum.

I am converting an Excel program that used DDE to communicate to a DDE server (RSLinx) to OPC and I have the communication working.

In the DDE version, I used the Worksheet_Calculate() function to look for data changes and I would trigger different functions when certain cells would change from 0 to 1. The cells had a direct reference to the DDE server by using =DDEServer|TopicName!Address while the cell values in the new OPC version all get filled through VBA and I think this is why the Worksheet_Calculate function is not responding to the changes.

Is there another method I can use to look for data changes so that I can execute my functions?

[vbfbryce]

I believe the Worksheet_Calculate should still work for you. I have programs that get updated via VBA and the calculate event fires appropriately. Can you show us where you have the code tied to the Calculate event?

[phuz]

The VBA code is on Sheet1, which is where the values are changing.

[vbfbryce]

So just like this:

[koolsid]

Welcome to the forums phuz

while the cell values in the new OPC version all get filled through VBA and I think this is why the Worksheet_Calculate function is not responding to the changes.

In such a scenario, you may have to use the `Worksheet_Change()` event in lieu of `Worksheet_Calculate()`. I will be able to confirm that after seeing your code.

Can you show us the code that you are using?

[phuz]

I switched to the Worksheet_Change(ByVal Target As Range) event and it sort of works, but not the way I want.

It seems to be breaking out of the current function every time a value changes.

Private Sub Worksheet_Change(ByVal Target As Range)
   'When Value in "C4" or "D4" changes..
    Dim target_save, target_load As Range
    Dim r_List_Save, r_List_Load, r_Batch_Save As Range
    
    Set target_save = Range("C4")
    Set target_load = Range("D4")
    Set r_List_Save = Range("F4")
    Set r_List_Load = Range("G4")
    Set r_Batch_Save = Range("K4")
    Set handshake_init = Range("M4")
    
    
    'If Recipe Load bit is set from PLC, call Recipe Load
    If Not Intersect(target_load, Range("D4")) Is Nothing Then
        If CInt(target_load.Value) = 1 Then
            Call ThisWorkbook.Recipe_Load
        End If
    End If
    'If List Load bit is set from PLC, call List Load
    If Not Intersect(r_List_Load, Range("G4")) Is Nothing Then
        If CInt(r_List_Load.Value) = 1 Then
            Call ThisWorkbook.List_Load
        End If
    End If
    'If Recipe Save bit is set from PLC, call Recipe Save
    If Not Intersect(target_save, Range("C4")) Is Nothing Then
        If CInt(target_save.Value) = 1 Then
            Call Recipe_Save
            Call ThisWorkbook.List_Save
            Call ThisWorkbook.List_Sort
        End If
    End If
    'If List Save bit is set from PLC, call List Save
    If Not Intersect(r_List_Save, Range("F4")) Is Nothing Then
        If CInt(r_List_Save.Value) = 1 Then
            Call ThisWorkbook.List_Save
        End If
    End If
    'If Batch Save bit is set from PLC, call Batch Save
    If Not Intersect(r_Batch_Save, Range("K4")) Is Nothing Then
        If CInt(r_Batch_Save.Value) = 1 Then
            Call Batch_Save
        End If
    End If
    'If VBA stops executing, re-start PLC handshaking when button is pressed on HMI
    If Not Intersect(handshake_init, Range("M4")) Is Nothing Then
        If CInt(handshake_init.Value) = 1 Then
            'Call PLC_Handshake
            'DDEPoke RSIchan, "B22:30/3,L1", Range("[RSLINXXL.XLS]Template!E4")
        End If
    End If

End Sub

[koolsid]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
    
    Application.EnableEvents = False
    
    'When Value in "C4" or "D4" changes..
    Dim target_save  As Range, target_load As Range
    Dim r_List_Save  As Range, r_List_Load  As Range, r_Batch_Save As Range
    Dim handshake_init As Range
    
    Set target_save = Range("C4")
    Set target_load = Range("D4")
    Set r_List_Save = Range("F4")
    Set r_List_Load = Range("G4")
    Set r_Batch_Save = Range("K4")
    Set handshake_init = Range("M4")
    
    
    'If Recipe Load bit is set from PLC, call Recipe Load
    If Not Intersect(Target, target_load) Is Nothing Then
        If CInt(target_load.Value) = 1 Then
            Call ThisWorkbook.Recipe_Load
        End If
    End If
    
    'If List Load bit is set from PLC, call List Load
    If Not Intersect(Target, r_List_Load) Is Nothing Then
        If CInt(r_List_Load.Value) = 1 Then
            Call ThisWorkbook.List_Load
        End If
    End If
    'If Recipe Save bit is set from PLC, call Recipe Save
    If Not Intersect(Target, target_save) Is Nothing Then
        If CInt(target_save.Value) = 1 Then
            Call Recipe_Save
            Call ThisWorkbook.List_Save
            Call ThisWorkbook.List_Sort
        End If
    End If
    'If List Save bit is set from PLC, call List Save
    If Not Intersect(Target, r_List_Save) Is Nothing Then
        If CInt(r_List_Save.Value) = 1 Then
            Call ThisWorkbook.List_Save
        End If
    End If
    'If Batch Save bit is set from PLC, call Batch Save
    If Not Intersect(Target, r_Batch_Save) Is Nothing Then
        If CInt(r_Batch_Save.Value) = 1 Then
            Call Batch_Save
        End If
    End If
    'If VBA stops executing, re-start PLC handshaking when button is pressed on HMI
    If Not Intersect(Target, handshake_init) Is Nothing Then
        If CInt(handshake_init.Value) = 1 Then
            'Call PLC_Handshake
            'DDEPoke RSIchan, "B22:30/3,L1", Range("[RSLINXXL.XLS]Template!E4")
        End If
    End If
    
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

[phuz]

Thanks. I ended up going a different route. I have a subroutine that runs every 2 seconds and it updates all the values that I am polling and I just added some code in that routine which looks at the trigger cells to call another subroutine and then reset the cell to 0. So far it is working well and I'm progressing with my application. Be assured that I'll be back here when I hit the next hiccup.
Thanks.

[koolsid]

Didn't the code that I gave above work?

[phuz]

Didn't the code that I gave above work?

No, it still broke out of the other routine as soon as a value changed.

[koolsid]

Which routine or line did it break on?

[phuz]

I have a routine which updates cell values from an OPC data source. When it gets to the line where it updates the cell, it jumps out of that routine and over to the Worksheet_Change routine, and it does this continually.

[koolsid]

Did you use the code as I gave you? I mean did you switch off

Application.EnableEvents = False

and reset them as I did?

[phuz]

Did you use the code as I gave you? I mean did you switch off

Application.EnableEvents = False

and reset them as I did?

Sure did!! It's OK. My method is working rather well. I do have a question regarding OPC connection, though. When I create the initial connection, I am polling 300+ integers. I can watch my OPC Data source connection tab and I see it pulling about 30 integers per second on the initial connection. After that, I am refreshing those same 300+ integers every 2 seconds and they load immediately. It is only the first connection that takes awhile. Is this normal or is there a way to speed that up? It isn't really a big deal for this particular use, but I am curious.

Thanks.

[koolsid]

That is normal AFAIK

[phuz]

Works for me. Thanks!

[koolsid]

phuz, Hope your query is resolved?

If it is then you might want to help us by marking the thread as resolved. This would let other experts know that your query is solved and they can skip the post if they wish to. This would help them concentrate on UNRESOLVED threads.

If you have JavaScript enabled you can do that by selecting the Mark Thread Resolved item from the Thread Tools menu. Otherwise please insert [Resolved] at the start of the Subject and select the green check mark from the post icons. You may also visit the FAQ to see the snapshots on how it works.

If someone has been particularly helpful you also have the ability to affect their forum reputation by rating their post. More information about rating can be found here.

[phuz]

Yup...I know how it all works.
Thanks.