Keeping data, not loosing it due to source reset

[Coonrapper]

I'm looking at an error status bit in a live running PLC program, and using Rslinx to transfer the bit number to a worksheet cell in Excel.
The bit is always zero until an error occurs, then a number higher than zero and equivelent to the specific error is written to Excel. I need to keep this number, because after the error is reset in the PLC program it returns the bit to zero.
Is there a way to move this number (>0) so its not still attached to the source.

I have a PC running Excel and Rslinx and communicating in real time with the PLC.

I hope I made myself clear.

thanks from an Excel rookie.

Peter O,Neill

 

[tactps]

Welcome
You could try this as an event handler:

Let's assume it's in cell b2.

Press Alt-F11.
In the left-pane, double-click the sheet that you want this to work in.
In the right pane, copy and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 And Target.Value > 0 Then [c2] = [b2].Value
End Sub

Is this what you want.

 

[Coonrapper]

Thanks for the quick reply, I will check this out at work tomorrow.

 

[HalfAce]

If your Rslinx program transfers the PLC output bit as a static (for the moment) value, then tactps' solution should work fine (unless you want to preserve all the errors without overwriting them) and that can be done like so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 And Target.Value > 0 Then [IV2].End(xlToLeft)(1, 2).Value = [b2].Value
End Sub

If however, you're getting your PLC outputs from formulas, (I do this from within excel), then you'll need to use a Worksheet_Calculate event instead of the Worksheet_Change.

Hope it helps,
Dan

 

[Coonrapper]

Thanks Dan,
this works perfectly, I appreciate the help.

Peter

 

[Coonrapper]

Hi Dan,
I've run into a problem with the macro, It writes into the cell to the right with the number >0 only after you click another cell or push enter.
Because the cell b2 is active, it never writes to the cell at the right without that keyboard stroke, is there anyway to make that happen without my input .

thanks, Peter.

p.s. i have the following formula in cell b2 =RSLINX|data!'S2:6,L1,C1' the location of the data I'm monitoring.

 

[HalfAce]

So... am I correct in thinking you're not pushing your updates into excel from RSLINX, but actually pulling the data into excel from the other application by way of your formula? (That's the way it looks to me. - and it's the way I do it from a gehcs app.)

If I'm right, then you should be using a Worksheet_Calculation event instead of the Worksheet_Change. This gets a little bit more involved.
You'll want some way upon opening the workbook to record the initial output bit somewhere in the sheet to be used as a static value. Then with each calculation performed in the sheet, you'll want to compare the current output bit to that static value. If it's not equal to it, then record B2's value (the new output bit value) in the next cell to the right, and then copy the new output bit to the static value cell so that's what gets compared against the next time. (Sounds kind confusing I guess...)

Give this a try in a blank workbook first and I think you'll see what I mean.
In B2 I simply put the formula =A1+A2
I am going to use A3 as the "static value" cell.

In the worksheet module (replacing the worksheet change code you have in there now) I used this:

Private Sub Worksheet_Calculate()
If [B2] <> [A3] Then
    [IV2].End(xlToLeft)(1, 2).Value = [B2].Value
    [A3].Value = [B2].Value
End If
End Sub

Play with the values in A1 or A2 and I think you'll get the idea of what I'm thinking.

Now, if this is what you want, you can get your initial "static value" when the workbook opens by using something like this in the ThisWorkbook module.

Private Sub Workbook_Open()
Sheets("Sheet1").[A3].Value = Sheets("Sheet1").[B2].Value
End Sub

Does this help any?
Dan

 

[Coonrapper]

I copied the worksheet code and the workbook code, and the workbook code does what you said and writes B2 to A3 when I first open it, but when I change B2 so that its not equal to A3, nothing gets written in the cell to the right of B2.
I'm not sure whats wrong.

 

[HalfAce]

You don't want to be changing B2, you want to be changing A1 or A2, so that B2 changes by itself through calculation. It is the act of B2 changing by calculation that sets off the code.

Dan

 

[Coonrapper]

You don't want to be changing B2, you want to be changing A1 or A2, so that B2 changes by itself through calculation. It is the act of B2 changing by calculation that sets off the code.

Dan

Dan
everything works with both macros running and the formula in B2, and I put a number in A1 or A2.
But when I substitue =A1+A2 for my formula =RSLINX|data!'S2:6,L1,C1' and run the file it writes to B2 but not A3 which has #N/A in it, and Debug points to IF [B2]<>[A3] THEN
with runtime error '13' type mismatch.
I was wondering is it possible the worksheet marco executes before the workbook macro.
I went to the PLC and forced bit S2:6 to 2 and B2 updates to 2, but A3 never changes.
What do you think?