Looping VBA code in Excel

Hello to all,

A contractor has written a horsepower calculation using Excel 2000 VBA. My responsibility is to DDE data in and out of the spreadsheet to a PLC5 via RSLinx. I have been sucsessful creating a pushbutton that does just this.

My problem is I want automate the pushbutton code on a routine basis without operator interaction. My experience with VB tells me to use a timer to call the pushbutton code, but Excel VBA dosn't appear to have a timer control. I tried to add the control to the existing controls list, but it doesn't appear.

When I try to create my own timer and loop the pushbutton code, it consumes all the application resourses and appears to lock up. When I step though each line of code it works, but as soon as I run the code I get the same lock-up.

Does any one know how I can get the timer control for Excel or create a timer that won't consume the resourses?

Thanks in advance

Bill

Base princible in Windows world is that everything happend with Events as button_clik, Data_has_changed etc.

If we do program who is running and executes Loop, it takes recources from OS. Usually when we don't do enything, we can see from Windows Task Manager Performance CPU Usage is 0 to 1 %.

Is it possible to do it by VisualBasic where You can use all ActiveX controls as timers, or if ypu are intrested of data_canges Events etc.
I don't know is it possible give events from RSLinx and wat is the connection OPC or DDE with RSLinx. Do you like collect data to database for research purpoces, then you can insert them to database and later you can use database for excample with Excell.
Excell and VisualBasic are trademark of MicroSoft.

investigate DDE

You need to investigate and learn about 3 different modes of
DDE!
Your code is obviously set-up for manual DDE mode and you need to click the button to get the data, right?
Well set it up for AUTO mode and it will continue reading all the time. To minimize the communications you can tailor make the
RSLinx polling, slow it down.
To learn more about DDE read some Microsoft examples, AB examples
don't go into enough detail.

Thanks for your replys,

I am admitedly a little green on the DDE. It is a little difficult to get DDE info from AB.

I did set up RSLinx in the poll mode. I assumed it will poll the PLC automatically, but I don't see any traffic until I request or send data in the Excel App.

I know you can setup a topic and paste a link in to Excel and data will appear in a cell, but that seemed to be read only. Can I write the same way?

Hi,

There is some tips on the net about excel timer

See, if the link below is of any help to you

http://www.mrexcel.com/board/viewtopic.php?topic=39470&forum=2&6

Best Regards

Chavak

Hi there,

If you set CommandButton_Click()
text1.linkmode = 1 this should autopoll with rslinx.

Again, Many thanks to your replies.

garryt1

"If you set CommandButton_Click()
text1.linkmode = 1 this should autopoll with rslinx."

Could not find linkmode property in Excel VBA. I did find it Visual Basic.

chavak,

The link did have some good info. I was able to make the timer work. Pretty advanced from my experience.

Thanks again

Bill

Hi there,

Have you tried to put a seperate button commandButton2_Click() in the sheet to terminate DDETerminate command.
Another alternative would be to use a bit from the plc to enable autopoll which you could set via a plc timer.

Sub Word_Read()

'open dde link: testsol=DDE Topic

RSIchan = DDEInitiate("RSLinx", "testsol")

'get data and store in data variable

data = DDERequest(RSIchan, "N7:30")

'Paste data into selected range

Range("[RSLINXXL.XLS]DDE_Sheet!C7").Value = data

'close dde link

DDETerminate (RSIchan)