Excel DDE Server

[JaronSanthosh]

Dear All,
I am using a DDE server application provided by PLC supplier and if I copy code values getting from PLC to Excel work sheet using the code from dde server application.

But the code pasted in a single cell auto updates the variable in the same cell. is there any possibility or VBA code to change the pasted code(ie, formula to the next cell whenever the variable changes)

(ex: now I pasted code in C1 cell if value changes in plc C1 updates from 10,11,12,13,14,15...
Is it possible to make c1=10, c2=11, c3=12, c4=13,....)

any help will be highly appreciated. thanks.

 

[John_w]

Welcome to MrExcel forums.

You could use this Worksheet_Calculate event handler. Every time the DDE formula value in C1 changes, the value is copied to the next cell in column C (C2, C3, etc.). Put the code in the worksheet module of the worksheet containing the DDE formula.

Private Sub Worksheet_Calculate()
    Dim nextRow As Long
    nextRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
    Cells(nextRow, "C").Value = Range("C1").Value
End Sub