How to Implement Modbus TCP Protocol Using VBA with Excel

gclshortt

Member
Join Date
Dec 2014
Location
Ontario
Posts
3,217
Here is a post that I just finished. I thought that it would be worth sharing here.
Let me know what you think.
Thank you,
Garry
http://www.accautomation.ca

How to Implement Modbus TCP Protocol Using VBA with Excel

We will use Visual Basic for Applications (VBA) to communicate to a PLC using Modbus TCP protocol. Reading ten registers in the PLC and displaying a bar graph in Excel. Previously we have used VB6 to communicate Modbus TCP.

The following steps will be done:
1.Explain Modbus TCP protocol
2.Install OstroSoft Winsock Component
– Winsock API Calls for communication on network
3.Develop the Excel and VBA application
(Microsoft Excel 2010)
4.Communicate to the PLC and sample code
(Do-More Simulator)

Read the rest of the post...
How to Implement Modbus TCP Protocol Using VBA with Excel

Modbus-TCP-using-VBA-Excel-120-min.png
 
Hi,
Do you know what to change in the code to read the coils instead?
i tried to change the function code to 01, but it didn't work.

Thanks in advance.

/Daniel
 
Hi Daniel,
You are correct in just changing the function code to get the coils. However the Do-More Simulator and Do-More PLC do not support this function. Information can only be obtained through the MHR (Modbus Holding Registers) memory of the PLC using Modbus TCP. To get the bit status you must first transfer the bits to a MHR area in the PLC program.
Regards,
 
Thanks for a quick reply (y)

I'm using the excel document to connect to another modbus unit, a mitsubishi PLC. I just changed the IP-address and it worked like a charm.

But I would like to use it to check the modbus coils from the PLC.
Do you know how to change the VBA code to get it to work with coils?

Thanks in advance

/Daniel
 
Here is the original information:

If (wsTCP.State = 7) Then
MbusQuery = Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(6) + Chr(0) + Chr(3) + Chr(0) + Chr(0) + Chr(0) + Chr(20)
wsTCP.SendData MbusQuery ‘Send out the Modbus Information
‘ Read the information
‘0000: Transaction Identifier
‘0000: Protocol Identifier
‘0006: Message Length (6 bytes to follow)
’00: The Unit Identifier
’03: The Function Code (read MHR Read Holding Registers)
‘0000: The Data Address of the first register
‘0002: The number of registers requested

If we want to read the coils this information would change to the following:

If (wsTCP.State = 7) Then
MbusQuery = Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(6) + Chr(0) + Chr(1) + Chr(0) + Chr(13) + Chr(0) + Chr(25)
wsTCP.SendData MbusQuery ‘Send out the Modbus Information
‘ Read the information
‘0000: Transaction Identifier
‘0000: Protocol Identifier
‘0006: Message Length (6 bytes to follow)
’00: The Unit Identifier
’01: The Function Code (read Coil Status)
'0013: The Data Address of the first coil to read. ( 0013 hex = 19 , + 1 offset = coil #20 )
‘0025: The total number of coils requested. (25 hex = 37, inputs 20 to 56 )

Additional information can be obtained from the following url:
http://www.simplymodbus.ca/TCP.htm
The reading of the coil example is from the following url:
http://www.simplymodbus.ca/FC01.htm

Regards,
 
Modbus Excel Parsing

Hi,
I used your ACC Modbus TCP example above and have my plc modbus server communicating with excel and is functioning as it should.

The problem I am trying to solve involves implementing a carriage return in the VBA code so that data is logged with a time stamp.

My project involves 3 separate platform scales that run live in R registers. The scale data streams continuous through serial connection. At intervals during the batching process contacts will move a frame of the scale data to a second memory location located within the MHR registers. The data is then sent to the excel example you provided.

Once the data reaches the designated cell in excel I would like to then have that data copied to a second sheet or even an entirely different workbook where the data can be logged within a new cell every time the data is captured. In addition I would require a time stamp and a carriage return so that the next block of data will automatically be placed in the cell below the previous cell data.

Any and all help with coding this is greatly appreciated. I really don't have any experience with VBA.

Thanks,
 
Hi Gary,
Thank you for the quick reply. I specified a carriage return would be needed, however what I actually meant was a line feed, so that the courser moves below to the next cell down. I have tried the code below but not working.

Thanks again.
 
This would not move your cursor down to the next cell. Instead, it would add an additional line to the text within the same cell.

If you want to move the cursor to the next cell, you do would not change the cell's content but instead select the other cell.

See if this works for you:

Code:
ActiveCell.Offset(1).Select
 
Gary,
I want to thank you for posting your Modbus VBA example. After two months of expanding on the example and learning vba code, I was able to create full automation of our batch control record keeping from information streaming from my Domore PLC. From soup to nuts I am able to track incoming material usage, outgoing orders, batch weights, label printing, assign batch control numbers, date and time stamp and the list goes on.

Keep up the great work and thank you for sharing your wealth of programming knowledge and providing this free example.

Best regards,:p
 
How to set or reset coil or write Holding Register

Hello, it's possible to write a value into Holding Register or put True or False a Coil?
 

Similar Topics

Hi, We are doing the project on PLC-DCS interfacing. The main problem in this project is the protocol mismatch. We are using Messung's Xmp8-20 PLC...
Replies
1
Views
9,920
Guest
G
like using modscan we can use it as modbus master can we use it as slave????/ or is there any other sofware where we can send query through...
Replies
2
Views
6,742
Let's say I have an inverter fed three phase induction motor drive where in the braking phase (when the motor operates in a generator mode) the...
Replies
2
Views
123
I want to improve a semi custom product I am delivering to a customer with implementation of safety relays. The system is fairly simple with just...
Replies
5
Views
543
I'm struggling to implement the test case which is highlighted. What ends up happening is the servo starts when I turn the cycle on, but it...
Replies
3
Views
1,046
Back
Top Bottom