CX-Server CDMDDE Visual Basic Macro Errors

[elcharlie]

Hello All,

I am having some issues with Excel while communicating with the Omron PLC. The Excel file is getting the data from the PLC and the macro doing what I want to do, but I am getting the following error messages when opening the excel file.

I am attaching the pictures of the messages and the code where debug send me.

Thank you very much in advance

20274

20275

20276

20277

[offthelip]

if you get an error in J2 you will get that message, so i suspect that when you open the workbook J2 has an error in it, try this modification:


Private Sub Worksheet_Calculate()Static oldval As Variant
If VarType(Range("$J$2").Value) <> vbError Then


If Range("$J$2").Value <> oldval Then
oldval = Range("$J$2").Value
End If
End If


End Sub

[elcharlie]

Thnk you offthelip for your quick answer.

It almost works! :)


Private Sub Worksheet_Calculate()Static oldval As Variant If VarType(Range("$J$2").Value) <> vbError Then


If Range("$J$2").Value <> oldval Then
oldval = Range("$J$2").Value
Call Run
End If
End If

End Sub

I have added Call Run for calling the macro which is copying and pasting the data every time the value in J2 changes.

Now I open Excel without problem, and it does it right the first times but after one time it doesnt work again.

Any ideas??? almost there! I really appreciate it

Thank you very much.

Regards

[offthelip]

and it does it right the first times but after one time it doesnt work again.

what do you mean by this, a bit more detail is needed for us to help you

[elcharlie]

Sorry,

What I mean is. After opening the excel file from an external source I change the value in J2, When this happens code call Run macro.

The Run macro copy and paste the values just one time.

It is supposed that every time I change the value in J2 it should copy and paste the values in the first row in the next available row. But it only do it once.

Thank you very much and sorry for my poor english :(

:)

[elcharlie]

Hello offthelip

I am sorry, I do not know the reason it didn't work before, but now it's working.

Thank you very much for your help I really appreciate it :)

[offthelip]

looking further at your "Run" module one thing to be careful of is that you are modfying cells on the worksheet that you have got the worksheet calculate event trigger, this can cause problems and certainly will slow excel down even if it doesn't go into a loop.
Can I suggtest that you turn events off before you copy the cells across and turn them on again afterwards, which you can do by modify the subroutine as below


Private Sub Worksheet_Calculate()Static oldval As Variant
If VarType(Range("$J$2").Value) <> vbError Then


If Range("$J$2").Value <> oldval Then
oldval = Range("$J$2").Value
Application.EnableEvents = False
Call Run
Application.EnableEvents = True
End If
End If


End Sub