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:
- Explain Modbus TCP protocol
- Install OstroSoft Winsock Component
– Winsock API Calls for communication on network - Develop the Excel and VBA application
(Microsoft Excel 2010) - Communicate to the PLC and sample code
(Do-More Simulator)
The Modbus TCP/IP or Modbus TCP is a Protocol that is used for communications over TCP/IP networks. This is done on port 502. Modbus TCP does not require a checksum calculation as lower layers already provide checksum protection. You can think of this as a letter being sent and Ethernet TCP/IP acts like an envelope for the Modbus Commands. I will not go into the details of the communication protocol but here are some links to references:
Introduction to Modbus TCP/IP
Simply Modbus – Modbus TCP
OstroSoft Winsock Component
OSWINSCK.dll serves as a wrapper for the Winsock API and helps programmers to abstract from the complexity of API calls and focus on application functionality. Works with programming and scripting languages supporting COM.
You will need to download and install the OstroSoft Winsock Component on your computer.
For use with .NET, Visual Basic 4 or 5, Visual C++, ASP, VBA, VBScript, JavaScript or any other language, supporting COM:
1. Download oswinsck.exe
2. Run downloaded file from Windows Explorer or command-line
Hit OK
I use the default directories where the program will be installed. Click the button to install.
Leave the program group to the default so I know what the program is after installation. Click continue.
Click OK
The OstroSoft Winsock Component is now installed.
Start Microsoft Excel.
Select ‘Developer’ along the top tabs.
If the Developer tab is not present then we must turn on the developer tab.
Select File | Options 
Select ‘Customize Ribbon’
Check the ‘Developer’ under Main Tabs. 
Under the Developer menu. Select ‘Visual Basic’
The Visual Basic Editor window will now be displayed. 
From the menu – Tools | References
We can now add the OstroSoft Winsock Component to our application.
Select OK
Select Sheet1(Sheet1). 
Now put the visual basic code in the Sheet1(Sheet1)
Here is the code:
‘This example uses OstroSoft Winsock Component
‘http://www.ostrosoft.com/oswinsck.asp
Option Explicit
Dim bytesTotal As Long
Dim sPage As String
Dim MbusQuery
Dim returnInfo
Dim wsTCPgdb
Dim WithEvents wsTCP As OSWINSCK.Winsock
Dim SetObject
Dim RetrieveData
Private Sub CommandButton1_Click() ‘ Retrieve Data
On Error GoTo ErrHandler
Dim sServer As String
Dim nPort As Long
Dim StartTime
DoEvents
nPort = 502 ‘ See configuration in Do-More Designer
‘ Set the IP address of the PLC
sServer = Sheets(“Sheet1”).Range(“B4″) ‘”192.168.1.3”
RetrieveData = 1
CommandButton1.BackColor = “&H0000FF00” ‘ Set colour to Green
‘Check to see if the object has been created. If not set wsTCP.
If SetObject = “” Then
Set wsTCP = CreateObject(“OSWINSCK.Winsock”)
wsTCP.Protocol = sckTCPProtocol
SetObject = 1
End If
‘ Check the state of the TCP connection
‘0 sckClosed connection closed
‘1 sckOpen open
‘2 sckListening listening for incoming connections
‘3 sckConnectionPending connection pending
‘4 sckResolvingHost resolving remote host name
‘5 sckHostResolved remote host name successfully resolved
‘6 sckConnecting connecting to remote host
‘7 sckConnected connected to remote host
‘8 sckClosing Connection Is closing
‘9 sckError error occured
‘ If TCP is not connected, try to connect again.
If wsTCP.State <> 7 Then
If (wsTCP.State <> sckClosed) Then
wsTCP.CloseWinsock
End If
‘ Open the connection
wsTCP.Connect sServer, nPort
StartTime = Timer ‘ Use the timer to determine if a connection cannot be made
Do While ((Timer < StartTime + 2) And (wsTCP.State <> 7))
DoEvents
Loop
If (wsTCP.State = 7) Then
Else
Exit Sub
End If
End If
‘ If we are connected then request the 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 to write
‘ Write the information
‘0000: Transaction Identifier
‘0000: Protocol Identifier
‘0009: Message Length (6 bytes to follow)
’01: The Unit Identifier
’16: The Function Code (read Analog Output Holding Registers)
‘0000: The Data Address of the first register
‘0001: The number of registers to write
’02: The number of data bytes to follow
‘0030 The number to put into the register
‘ Note: Addresses are offset by 1
‘ Example: MHR1 = Address 0000
‘ Example: MHR30 = Address 0029
End If
Exit Sub
ErrHandler:
MsgBox “Error ” & Err.Number & “: ” & Err.Description
End Sub
Private Sub CommandButton2_Click() ‘ Stop the communication
RetrieveData = 0
CommandButton1.BackColor = “&H8000000F” ‘ Set the default colour
End Sub
Private Sub wsTCP_OnDataArrival(ByVal bytesTotal As Long)
Dim sBuffer
Dim i
Dim MbusByteArray(500)
Dim h As Integer
Dim txtSource
wsTCP.GetData sBuffer
txtSource = txtSource & sBuffer
Dim j As Byte
returnInfo = “”
For i = 1 To bytesTotal
wsTCP.GetData j, vbByte
MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
returnInfo = returnInfo & Asc(Mid(sBuffer, i, 2))
Next
txtSource = returnInfo
txtSource = Val(Str((MbusByteArray(10) * 256) + MbusByteArray(11)))
Sheets(“Sheet1”).Range(“B10”) = Val(Str((MbusByteArray(10) * 256) + MbusByteArray(11)))
Sheets(“Sheet1”).Range(“B11”) = Val(Str((MbusByteArray(12) * 256) + MbusByteArray(13)))
Sheets(“Sheet1”).Range(“B12”) = Val(Str((MbusByteArray(14) * 256) + MbusByteArray(15)))
Sheets(“Sheet1”).Range(“B13”) = Val(Str((MbusByteArray(16) * 256) + MbusByteArray(17)))
Sheets(“Sheet1”).Range(“B14”) = Val(Str((MbusByteArray(18) * 256) + MbusByteArray(19)))
Sheets(“Sheet1”).Range(“B15”) = Val(Str((MbusByteArray(20) * 256) + MbusByteArray(21)))
Sheets(“Sheet1”).Range(“B16”) = Val(Str((MbusByteArray(22) * 256) + MbusByteArray(23)))
Sheets(“Sheet1”).Range(“B17”) = Val(Str((MbusByteArray(24) * 256) + MbusByteArray(25)))
Sheets(“Sheet1”).Range(“B18”) = Val(Str((MbusByteArray(26) * 256) + MbusByteArray(27)))
Sheets(“Sheet1”).Range(“B19”) = Val(Str((MbusByteArray(28) * 256) + MbusByteArray(29)))
DoEvents
‘ Determine if we retrieve the data again.
If RetrieveData = 1 Then
Call CommandButton1_Click
End If
End Sub
Private Sub wsTCP_OnError(ByVal Number As Integer, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, CancelDisplay As Boolean)
MsgBox Number & “: ” & Description
End Sub
Private Sub wsTCP_OnStatusChanged(ByVal Status As String)
Debug.Print Status
End Sub
Note: The program utilizes the CHR and STR functions to convert the data from binary to ASCII and back.
The highest value of a byte of data is 256. This is why we have to multiply the highest significant byte with 256
Interface:
Go back to Sheet1 and we can now put on the worksheet what we would like to see.
Note the following:
IP Address = B4
MHR 1 to 10 values located at B10 to B19
‘Stop Data’ – CommandButton2
‘Retrieve Data’ – CommandButton1
Communication to the PLC
Start the Do-More Designer software.
Under the Project Browser select ‘System Configuration’
Make note of the IP address. If you are running the simulator then this is automatically filled in.
Ensure that the Enable Modbus/TCP Server is checked. Also make sure that the TCP Port Number is 502.
The sample PLC program will write values in the range from 0 to 4000. These values will be put in MHR 1 to MHR 10.
Here is the first couple of rungs of the PLC program. It will use clock bit flags to increment the MHR 1 channel. When it gets to the value above 4000, a move instruction will put a 0 back into MHR 1.
If input X0 turns on then the value in XW0 will be moved into MHR1 and the previous clock bit will not be in effect. Values will be between 0 and 4096. (12 bit resolution)
This is repeated with different internal clock bit flags up to MHR10.
Running the program will produce the following:
As you can see the Modbus TCP protocol is easy to implement with visual basic for applications.
Download the PLC program and Excel file.
Additional Information:
Excel – Conditional Movement of Data
Watch on YouTube : How to Implement Modbus TCP Protocol using VBA with Excel
If you have any questions or need further information please contact me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.
To get this free article, subscribe to my free email newsletter.
Use the information to inform other people how numbering systems work. Sign up now.
The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.
Read OR Write Value 1234
Return Value 1087
Error Chr(n) + “Chr(not > 63)”
I am not sure of the question that you are asking. What Modbus device are you connected to? (What are the settings of that device?)
What function code/s are you using?
Regards,
Garry
Thank you Garry for your exausting explanation.
But would like to know hot read 32bit registers .
Thank you again.
Mean… how to read 32bit registers
Hi Luca,
In the Modbus protocol we specify the address and the number of registers to read. 32 bit addresses will be twice as long as 16. We would just specify the starting address and twice the amount of registers to read. Take the result and join them together.
‘ 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 to read
Regards,
Garry
Great tutorial!
Reading the addresses with the Excel file works perfect. I do however get the error “10053: Software caused connection abort.” after some time when i click the Stop Data button.
Does the oswin socket have a timer that closes the connection when it is not used for some time? (meaning I should close the handler when i stop the data acc.)
Hi Christian,
You are correct. The oswinsck must be closed. I did forget to include this in the sample program.
I would enter another timer that gets activated when the connection is closed.
If (wsTCP.State <> sckClosed) Then
wsTCP.CloseWinsock
End If
Thanks Christian,
Garry
Garry,
I am getting “Compile error: Variable not defined”. In the code, “” is highlighted withing the line:
returninfo = “”
I am aslwo using Windows 7, Excel 2010.
Any thoughts?
Thank you,
Justin
Hi Justin,
The Dim statement was for “returnInfo”. Please verify the spelling in the line.
Regards,
Garry
Here are those few lines (copy and pasted):
Dim j As Byte
returninfo = “”
For i = 1 To bytesTotal
wsTCP.GetData j, vbByte
MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
returninfo = returninfo & Asc(Mid(sBuffer, i, 2))
Hi Justin,
Here are the exact same lines copied and pasted below. Note the spelling of returnInfo not returninfo. If you keep the returninfo spelling then go back and change the following line of code:
Dim returnInfo change to Dim returninfor
returnInfo = “”
For i = 1 To bytesTotal
wsTCP.GetData j, vbByte
MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
returnInfo = returnInfo & Asc(Mid(sBuffer, i, 2))
Next
txtSource = returnInfo
Hi Sir
ACC Modbus TCP Connect Excel VBA with LS Industrial Systems (XGB_XBC) PLC
We tested communication.
It shows the following result values
PLC value 1 ~ 128 -> Excel 1 ~ 128 The normal value is displayed
PLC value 129 ~ 254 -> Excel 63 Only certain values are displayed
PLC value 255 ~ 384 -> Excel 255 ~ 384 The normal value is displayed
PLC values 385 to 510 -> Excel 319 Only certain values are displayed
PLC value 511 to 640 -> Excel 511 to 640 The normal value is displayed
PLC values 641 to 766 -> Excel 575 Only certain values are displayed
Tell me what’s wrong with this pattern in Excel
Thank you
수정 제안하기
Hi Ji
Since information is being received in Excel, then all communication is working correctly. I would look at the address registers in the PLC to determine the register type (Binary, Hex, ASCII, Decimal, etc).
See if there is a relationship between the register type and the information being returned in Excel.
Regards,
Garry
Hi Garry
Please reply to purchase method and purchase cost by E-MAIL
Regards,
JI
Hi Ji,
I provide this information free of charge to you. There is nothing to purchase. The advertising that you see on the website and videos help to pay for the upkeep of the site.
This is my way of giving back to our automation field.
Regards,
Garry
Hi Garry
If you know why the error value appears, please let me know.
Thank you,
ji
Hi Ji,
What error messages are you getting?
You can send me a screen shot of the errors?
Thanks
Garry
garryshortt@accautomation.ca
Can we use same VBA script for modbus RTU (serial) device over which is connected to TCPIP ethernet converter ?
If yes then where we will put device ID, baud rate, parity, stop bit in your excel file and VBA script ?
Hi Santosh,
Yes you can use VBA script to communicate through the serial port Modbus RTU. Here is a link where you will find additional information.
https://pps2.com/smf/index.php?topic=25.0
You would specify the serial communications when opening the serial port. The device ID would be similar to the Modbus TCP example above.
Hope this helps you out.
Regards,
Garry
Hi There,
I really like this spread sheet you made but I cannot get it to work. I am trying to read 400001-400008 on a productivity 2000 plc. I have those locations setup as 32 bit integers but I could change that. Any differences between the two PLCs that could cause this not to work?
Hi Jordan,
Once the Mod Start and Mod End are set up in the Productivity Suite software. These addresses should be able to read.
Are you able to program through the Ethernet port of the controller? This will prove that the cable is correct.
Modbus Server Settings can be seen from the productivity suite software. ( File | Project Properties ) Under the tab Modbus Server Settings you will see a series of options. I usually keep this as the default.
If everything else fails you can try another Modbus Client like AdvancedHMI. This will prove the communications to the PLC.
https://sourceforge.net/projects/advancedhmi/
Here is a post on installing this software:
https://accautomation.ca/create-a-plc-with-hmi-training-and-learning-environment-free/
Regards,
Garry
Hi I’m trying to read a single value from my Schneider power meter 5320; this has a Modbus over IP connection through Ethernet. The meters address is 255 with an IP address of 192.168.0.201.
I apply the following parameters (below), but the only thing that happens is MHR 3 to MHR 5 flash up with values for less than a second..
IP Address 192.168.0.201 Unit Ident. 254
Transaction Ident. 0000 Function Code 03
Protocol Ident. 0000 Address First Reg. 43060
Message Length 0006 Number of Reg. 2
Any ideas?
Hi Adam,
I have not programmed this unit that you are trying to communicate. The address that you specify is not one that is listed in the Modbus addresses of the unit.
https://www.schneider-electric.ca/en/faqs/FA234017/
You will see an excel file with the Modbus addresses. (PM5100_PM5300_ModbusRegisterList.xls)
I would try one of the other addresses. If that fails then I would look for another device to try your communications to the unit. This will try to eliminate where the problem resides.
Hope this helps you out.
Regards,
Garry
Hi,
Im trying to read from the Excel to the PLC, now i can read from the PLC to the Excel, what i need to change in the code?
Thank you.
Hi Mary,
You would need to change the function code from 3 to 16 and set the appropriate registers.
‘ Write the information
‘0000: Transaction Identifier
‘0000: Protocol Identifier
‘0009: Message Length (6 bytes to follow)
’01: The Unit Identifier
’16: The Function Code (write Analog Output Holding Registers)
‘0000: The Data Address of the first register
‘0001: The number of registers to write
’02: The number of data bytes to follow
‘0030 The number to put into the register
Regards,
Garry
Hi,
I previously had program running with much success for over two years on a windows 7 machine, however I recently upgraded to windows 10 pro and latest version of excel with VBA 7.1 on the same machine and I can not get automation to run. I receive error 429: active x component can’t create object. I tried to register manually regsvr32 oswinsck.dll and receive error oswinsck.dll may not be compatible with x86 or x64 version of regsvr32. Any ideas on how to resolve this problem. Thank you.
Hi Jim,
Did you install the 64 bit or 32 bit version of Excel? Use the 32-bit Office. Switching from 64-bit Office will require an uninstall and reinstall. You will then need to reinstall oswinsck.
Let me know how you make out.
Regards,
Garry
My Lord, I’ve spent countless hours on trying to resolve the problem and it’s now working. Who would have thought to run the 32 bit version on a 64 machine.
Thank you! Happy Holidays!!
Hi Jim,
Thanks for the update. I’m glad you got it working on the new machine.
Happy Holidays to you and your family.
Garry