XLM (Excel Macro) vs. VBA for data logging application

Hello. For a PLC data logging application at a candy factory, a customer requested me to setup an add-in for MS Excel called "MX Sheet" which is relatively easy to setup to get data from a Mitsubishi PLC through any supported communication link, but it is very limited with regards to logging capabilities. I have to automate the logging by means of Excel macros. I have never programmed in VBA, but decades ago I did a lot of development with the legacy XLM (ExceL Macro) language, which I find very powerful. Since the PC will be dedicated for this logging task, my application will not have dialogues and the data will be dumped in MS Excel files that the customer will retrieve through a shared directory.
Here's the question: web searches on the topic recommend using VBA as opposed to XLM. Does anybody have some advice in this regard? I am almost done, have solved the most important problems and find XML very suitable for what I need. But I may change my mind and force myself to finally learn VBA if I can get some compelling reason from the forum. Some of the search results are hilarious in their (mostly correct I guess) implication that those of us who use XLM older kind of guys, but have not found the arguments compelling enough. A PLC person may be able to provide better arguments.

Thanks for reading.

Alfredo this is not a VBA/XLM answer related, I use data loggin thru XLReporter from Sytech.com, you need and OPC server. I have used it for more than 10 years.
Plan B: Go to AdvancedHMI (vba.net related), look for the forum. This one is free.
Archie is the man, write a PM to him.
Sorry Advancedhmi is designed for Allen Bradley PLCs. My mistake.

Thanks Widelto. Will look into XLReporter for the next project.

If anything goes wrong, and you used MS Office as part of your solution, you will be mocked and ridiculed, and perhaps discussed in parliament.


https://www.bbc.com/news/technology-54423988
"Excel was always meant for people mucking around with a bunch of data for their small company to see what it looked like," commented Prof Jon Crowcroft from the University of Cambridge.


"And then when you need to do something more serious, you build something bespoke that works - there's dozens of other things you could do."

Thanks AustralIan. Hilarious

I have used VBA in excel many times, mainly to create reports from an SQL database, it's dead easy to use, you can format your sheet like a report. I know there are better tools out there, but the last place I worked it was difficult to get reporting systems past IT, this was the ideal solution as I could protect the sheet in code and the only thing the staff could do was generate reports, it was available via the intranet. we eventually went to web reports but it worked well for a number of years.

Thanks for your advise parky. Over 140 views to this post and no comments on my use of legacy XML. Will give a thought to your comments on VBA, but I am very advanced so maybe for the next project. Thanks to all of you.

If you are not too comfortable with VBA, there are plenty of resources on the internet, if you want to do a particular function there will be plenty of examples.

As I said, I am very comfortable with the predecessor of VBA, which is the old eXel Macro Language (XML). The articles I have found about XML are very unflattering, but it works for me.

I also was fascinated with the original excel macro sheets and used them to automate data entry and validation, but found VBA much more powerful and flexible. It has been easy to combine with RSLinx (not the lite version) and Allen Bradley PLC's to log data and load large amounts of data into the PLC. As others point out, there are many more elegant solutions out there, but if you've developed a library of functions over the years it's hard to beat.

AlfredoQuintero said:

As I said, I am very comfortable with the predecessor of VBA, which is the old eXel Macro Language (XML). The articles I have found about XML are very unflattering, but it works for me.

Click to expand...

If you are comfortable with the old Excel macro language, you should remember Excel's macro recorder.
You can still use the macro recorder to see how Excel does things (in a VBA paradigm) and then trim out the flowery excesses that Excel includes in its recordings.

kwade said:

If you are comfortable with the old Excel macro language, you should remember Excel's macro recorder.
You can still use the macro recorder to see how Excel does things (in a VBA paradigm) and then trim out the flowery excesses that Excel includes in its recordings.

Click to expand...

Hello kwade: Yes, I do remember the macro recorder. And yes, normally a recorded macro requires some brush-up. Thanks a lot for your input.

FluxCap said:

I also was fascinated with the original excel macro sheets and used them to automate data entry and validation, but found VBA much more powerful and flexible. It has been easy to combine with RSLinx (not the lite version) and Allen Bradley PLC's to log data and load large amounts of data into the PLC. As others point out, there are many more elegant solutions out there, but if you've developed a library of functions over the years it's hard to beat.

Click to expand...

FluxCap, hello. Thanks very much for your comments. I do have a curiosity itch for VBA and want to try it out if I need to develop something that requires dialog for the human interface. But what I am doing now runs on a DIN-rail mount PC with no keyboard, mouse or monitor. This application will be running and checking the communication with the PLC and creating MS Excel reports that are saved in a shared folder when some conditions are met, which the customer will copy through their local area network.

For a moment I though that I was not going to be able to do it, because in this case I am using Mitsubishi, not Rockwell, and instead of RSLinx I am using an add-in for Excel called MX-Sheet from Mitsubishi Electric. I found that if I used a macro that runs automatically when the worksheet is opened, the macro itself does not let the MX-Sheet add-in to start, so the PLC communication would not start. But the XML did not disappoint, and the ON.TIME function came to the rescue, allowing the macro to be halted for 10 seconds so the communication add-in can start. Having jumped this hurdle, I think I have no problem with the rest, in fact I am almost done. I share the info below thinking it might be of interest.

Hi AlfredoQuintero
Mitsubishi has Modbus communication protocol on most of their PLCs.
It was mentioned before that AdvancedHMI. It has Modbus TCP (Ethernet) and RTU (Serial) communication drivers.
https://www.advancedhmi.com/
You would then easily store the information in a database.
Excel would then read the information.

Another way to do this is to use Node-RED.
https://nodered.org/
I am currently doing a series on data collection using Modbus TCP and RTU using node-red. This will store information in a SQL database and then show how to read the logged data using a spreadsheet.
https://accautomation.ca/series/node-red-iot-enabling-software/

Regards,

widelto said:

Alfredo this is not a VBA/XLM answer related, I use data loggin thru XLReporter from Sytech.com, you need and OPC server. I have used it for more than 10 years.
Plan B: Go to AdvancedHMI (vba.net related), look for the forum. This one is free.
Archie is the man, write a PM to him.
Sorry Advancedhmi is designed for Allen Bradley PLCs. My mistake.

Click to expand...

Has XLReporter been around that long? I've been working with it a few months and it feels like a beta product with the number of bugs i've found and hacky ways i've had to do things.