PDA

View Full Version : CX-Server CDMDDE Visual Basic Macro Errors



elcharlie
09-07-2017, 12:31 AM
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
09-07-2017, 04:11 AM
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
09-07-2017, 05:04 AM
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
09-07-2017, 05:29 AM
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
09-07-2017, 06:00 AM
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
09-07-2017, 06:39 AM
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
09-07-2017, 09:42 AM
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