Runtime error with the following code

[hurleypa2002]

Hello,

Have the following code that keeps giving me a runtime error code of (-2147417848(80010108)) or a method 'range' of object'_worksheet'failed message. I can surpress the errors with an exit sub routine but would really like to fix the issue......

On the worksheet I have a cell the communicates to a PLC through DDE:

Cell is a special ("AO3") =RSLINX|Auto_55!'LINX_BITS.Bit[0],L1,C1'
other cell is ("AM3") =IF(AO3=1,1,0)

Macro code for Sheet:

-----code---

then for sheet 1


Private Sub Worksheet_Calculate()
On Error GoTo Waittime:
'Goes to Waittime on hich-up from RS Linx

--------the next line is giving me the error ---------------
If Sheets("Sheet1").Range("AM3") = 0 Then
' PLC save bit AM3, Internal bit AM5

-----sometime it will error on this line instead----------
Sheets("Sheet1").Range("AM5") = 0
'Reset Bit to PLC save if PLC save bit low
If Sheets("Sheet1").Range("BJ2") = 1 Then
Sheets("Sheet1").Range("BJ2") = 0
RSI = Application.DDEInitiate(app:="RSLinx", topic:="Auto_55")
Data = "Linx_bits_From_Desktop.bit[1]"
Set rangetopoke = Worksheets("sheet1").Range("BJ2")
DDEPoke RSI, Data, rangetopoke
DDETerminate RSI
End If
End If
'


--- more code----


any help on how to rewrite the lines of code to avoid the error would get a "many thanks"

Pat

 

[RoryA]

I would start by adding the Value property in explicitly:

Private Sub Worksheet_Calculate()
On Error GoTo Waittime:
'Goes to Waittime on hich-up from RS Linx

--------the next line is giving me the error --------------- 
If Sheets("Sheet1").Range("AM3").Value = 0 Then
' PLC save bit AM3, Internal bit AM5

-----sometime it will error on this line instead----------
Sheets("Sheet1").Range("AM5").Value = 0
'Reset Bit to PLC save if PLC save bit low
If Sheets("Sheet1").Range("BJ2").Value = 1 Then
Sheets("Sheet1").Range("BJ2").Value = 0
RSI = Application.DDEInitiate(app:="RSLinx", topic:="Auto_55")
Data = "Linx_bits_From_Desktop.bit[1]"
Set rangetopoke = Worksheets("sheet1").Range("BJ2")
DDEPoke RSI, Data, rangetopoke
DDETerminate RSI
End If
End If
'

 

[hurleypa2002]

Rorya,

Seemed to help a great deal, but still happens. Is it because the special DDE link can cause a #REF! error? If so, is there any other coding to get around this?

Thanks.

Pat.

 

[RoryA]

Hard to say without seeing the error handler (which should presumably be dealing with this) but is this code in Sheet1?

 

[hurleypa2002]

Yes, sheet 1

I only have the error handler doing the following..currently disabled as I try and fix these faults...

Waittime:
Application.DisplayAlerts = False
'Disables pop-up messages from errors
'Application.Wait Now + TimeValue("00:0:05")
'Waits 5 seconds on RS Links communication error
'Application.DisplayAlerts = True
'Enables pop-up messages from errors

 

[RoryA]

In that case:

Private Sub Worksheet_Calculate()
On Error GoTo Waittime:
'Goes to Waittime on hich-up from RS Linx

--------the next line is giving me the error --------------- 
If Range("AM3").Value = 0 Then
' PLC save bit AM3, Internal bit AM5

-----sometime it will error on this line instead----------
Range("AM5").Value = 0
'Reset Bit to PLC save if PLC save bit low
If Range("BJ2").Value = 1 Then
Range("BJ2").Value = 0
RSI = Application.DDEInitiate(app:="RSLinx", topic:="Auto_55")
Data = "Linx_bits_From_Desktop.bit[1]"
Set rangetopoke = Range("BJ2")
DDEPoke RSI, Data, rangetopoke
DDETerminate RSI
End If
End If

What does your error handler do - just wait and then exit?

 

[hurleypa2002]

It blocks the error message from displaying.

 

[RoryA]

And then what?
Best to disable it while debugging.