Data logging with a DDE link and VBA

[Elceller in distress]

I have a created a spreadsheet that uses a DDE link to show values from plant
equipment. The information is displayed on four cells in sheet1. I have put
together a macro that copy’s the information from the four cells in sheet1
and pastes the information on sheet 2. This routine runs every second and
moves to the next cell. . It saves the information every seven hours and
starts over. In other words I know what the equipment was doing every second
during a 7 hour shift. The program seems to work fine when I test it in the
office, without the link and without letting it run 7 hours. When I leave it
running on the machine the program crashes, I have not seen the error that
comes up.

Will a DDE link cause any type of error that can stop the program from
running?
Is there a way to program around DDE errors?

This is the only program running on this computer can windows cause it to
crash in any way?

 

[Thomas Lutz]

A DDE Link should not cause any problems in an Excel spreadsheet as
long as the links are implemented correctly. It is more likely that
the problem is in whatever code is running every second. You may want
to throw in an error handler to record all the errors so that you can
track down what is going wrong.
A better way to do things might be to use the Excel SetLinkOnData
method to monitor the DDE data and trigger the macro that you are
currently triggering with a timer. The SetLinkOnData method lets you
configure a VBA subroutine to run automatically whenever data from a
DDE link changes. Using this approach, you would never miss a single
data value. With a timer taking a snapshot of the data every second,
you could easily miss data as well as record redundant data.

 

[Elceller in distress]

Thanks for your help Thomas,

I am new to error handles,
In every sub I am guessing I need to add:

"On Error GoTo ErrorHandler"?

Then also add a sub called "sub Errorhandler()"?

What code would I use to store the error?

Sub errorhandler()
???
end sub

For reference purposes, I have attached a portion of my program. I just
took a piece of it so it will not work.










sub controller()

If time = TimeSerial(19, 0, 0) Then
ExportandSave
insertsheet
FormatSheet
ResetRange
Setnewtime
setoldtime
test

Else

If OldTime < NewTime Then
ZeroScan = False
setoldtime

Rng1 = Rng1 + 1
Rng2 = Rng2 + 1
getrange

Sheets("Sheet1").Range("A2:g2").Copy


Sheets(Sheetname).Range(InsertRange).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(Sheetname).Range(DTETMEInsertRange).Offset(0,
7).Value = Date
Sheets(Sheetname).Range(DTETMEInsertRange).Offset(0,
8).Value = time


ElseIf NewTime = 0 Then
OldTime = 0

If ZeroScan = False Then

Rng1 = Rng1 + 1
Rng2 = Rng2 + 1
getrange

Sheets("Sheet1").Range("A2:g2").Copy



Sheets(Sheetname).Range(InsertRange).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(Sheetname).Range(DTETMEInsertRange).Offset(0,
7).Value = Date
Sheets(Sheetname).Range(DTETMEInsertRange).Offset(0,
8).Value = time



ZeroScan = True

Else
Setnewtime
End If

Else
ZeroScan = False
Setnewtime
End If
End If

Loop

End Sub

 

[gimme_this_gimme_that]

Insert a DoEvents statement into your loop so you don't tie down the
operating system.

 

[Elceller in distress]

Thanks, what about error handling

 

[Elceller in distress]

I think I figured out the problem, the laptop that I was using was set up to
cut off the hard drive after 45 minutes. It seems to work fine now. Thanks
for your help.