Tag Archives: modbus tcp

Suppanel Android HMI to Click PLC

Suppanel is an Android app that is used to create a human machine interface (HMI). This HMI panel can be created on your computer, tablet or phone using the appropriate operating system or software. You can use this software to create panels that you can control or modify values in your automated system. These panels can be shared with other Suppanel users.

We will be creating a Suppanel Panel to monitor and control a Solo Process Temperature Controller via the Click programmable logic controller (PLC). The Click will be communicating to the Solo with serial RS485 using Modbus RTU protocol. The Suppanel HMI will be communicating to the Click PLC using Ethernet Modbus TCP protocol.

Watch on YouTube : Suppanel Android HMI to Click PLC

Let’s get started. Continue Reading!

BRX PLC AdvancedHMI Communication – Modbus TCP

Advanced HMI is a powerful, adaptable HMI/SCADA (Supervisory Control and Data Acquisition) development package that takes advantage of Visual Studio. There is no coding required and you can simply drag and drop items onto the page. The best thing is that the software is free. We will look at using AdvancedHMI with the BRX Series PLC.

Our sample BRX PLC program will display a digital panel meter and a gauge of a value in the PLC. Stop and start momentary pushbuttons on the HMI will allow this number to increase or stop. An indication will turn green when the number is increasing and red when it has stopped. The AdvancedHMI package will communicate Modbus TCP over Ethernet to the BRX PLC. We will be able to monitor our process via the AdvancedHMI window. Lets get started. Watch on YouTube…
Continue Reading!

Modbus TCP Data Logging to Database

Using Visual Basic 2015 we will log three holding registers from the PLC along with time and date into a Microsoft Access Database. We will log every minute into the database with the information that we collect from the PLC via Modbus TCP (Ethernet). All code will be done and shown so you can implement this in your application with different parameters. The information collected in the database can then be distributed or analyzed in the future.ModbusTCP MSAccess 010-min

Visual Basic 2015 will be used with the EasyModbusTCP client/server library for .net. We will communicate to an Automation Direct – Do-More PLC. Using the free simulation software of the PLC Designer Software, we will retrieve three values of the Modbus Holding Registers using Modbus TCP. Once we have this information out of the programmable logic controller it will be placed in a Microsoft Access (2010) Database. This will be done by using a SQL command to insert the data.

Automation Direct has a powerful simulator with their Do-More PLC. The PLC software is available from Automation Direct as a free download.
http://support.automationdirect.com/products/domore.html

ModbusTCP MSAccess 040-min

We will start with the PLC program. The first  three holding registers will be used. That will be MHR1, MHR2 and MHR3 in the Do-More PLC. This will correspond to register numbers 40001, 40002 and 40003. The following is a table with all of the references for Modbus communications to the Do-More.

Coil/Register Numbers Data Addresses Type Do-More PLC Table Name
00001-09999 0000 to 270E Read-Write MC1 to MC1023 Discrete Output Coils
10001-19999 0000 to 270E Read-Only MI1 to MI1023 Discrete Input Contacts
30001-39999 0000 to 270E Read-Only MIR1 to MIR2047 Analog Input Registers
40001-49999 0000 to 270E Read-Write MHR1 to MHR2047 Analog Output Holding Registers

Note: The Do More PLC uses the Modbus area to communicate. This is because having direct access to the digital I/O can be dangerous when connected via Ethernet to the internet. Data must move in and out of this area via the PLC program.

Here is the PLC program that sets the three registers.
ModbusTCP MSAccess 050-min

The next thing to do is set up our Microsoft Access 2010 Database. Our database will be named ACC_Database and it will be located at the following location: “C:\AccLog”. We will have one table called ACC_Log. In the table we will have five fields; LogDate, LogTime, Register1, Register2, Register3.

ModbusTCP MSAccess 090-min
ModbusTCP MSAccess 100-min

Visual Basic 2015 is free and is part of Visual Studio 2015. It can be downloaded at the following location:
https://www.visualstudio.com/en-us/products/visual-studio-community-vs

ModbusTCP MSAccess 020-min

EasyModbusTCP is free software. It will be referenced in our visual basic program.  It can be downloaded from the following location:
https://sourceforge.net/projects/easymodbustcp/
After downloading you must extract the files.

ModbusTCP MSAccess 030-min

EasyModbusTCP is a Modbus TCP, Modbus UDP client/server library for .NET and Client library for JAVA. .NET (.dll) Client/Server also supports Modbus RTU. It supports the following function codes: – Read Coils (FC1)
– Read Discrete Inputs (FC2)
– Read Holding Registers (FC3)
– Read Input Registers (FC4)
– Write Single Coil (FC5)
– Write Single Register (FC6)
– Write Multiple Coils (FC15)
– Write Multiple Registers (FC16)
– Read/Write Multiple Registers (FC23)

Now we will call up visual studio and create our project. The first thing that we must do is reference our EasyModbus.dll file.
ModbusTCP MSAccess 110-min
ModbusTCP MSAccess 120-min

Our program will log the three registers based upon a timer function. When the time expires we will set the interval to 1 minute and call a subroutine (Button1). This subroutine will communicate to the PLC via Modbus TCP and get our three registers. It will then insert the data from the registers along with the time and date into an access database.

Here is what our visual basic form will look like:
ModbusTCP MSAccess 060-min

Here is the visual basic code for our project. We use the Imports command for the EasyModbusTCP namespacing.

Imports EasyModbus 'Import the EasyModbus 

Here is the part of the subroutine that will communicate to the PLC via the EasyModbusTCP, You will notice that we have used the ‘Try’ command on the ModbusClient.Connect() statement. This way we can track if we are communicating or not to the PLC.

Public Class Form1
  Private Sub Button1_Click() Handles Button1.Click
 'This subroutine will communicate using the EasyModbusTCP to the PLC
 'This will execute when the user clicks the button or at specific intervals from the Timer1
 Dim ComError = 0 'Set communication error flag to 0
 'Specify the IP Address and Port Number that we are connecting
 Dim ModbusClient As EasyModbus.ModbusClient = New EasyModbus.ModbusClient(TextBox1.Text, 502)
 Try
 ModbusClient.Connect() 'Connect to the PLC
 Catch ex As Exception 'What to do when an error occurs
 Label10.ForeColor = Color.Red
 Label10.Text = "Communication Error!"
 ComError = 1 'Set communication error flag to 1
 End Try
 If ComError = 0 Then 'Do the following when communication is OK
 Label10.ForeColor = Color.Black
 Label10.Text = "Logging..."
 Dim Registers As Integer() 'Where to store the information
 Registers = ModbusClient.ReadHoldingRegisters(0, 3) 'Read three registers starting at the first one
 Label1.Text = Registers(0) 'Value of MHR1
 Label2.Text = Registers(1) 'Value of MHR2
 Label3.Text = Registers(2) 'Value of MHR3
 ModbusClient.Disconnect() 'Disconnect from the PLC

Here is the code to open the database connection and insert the data into our ACC_Database file. We use the ‘Try’ command to ensure that if errors occur it will not stop our program. The connection string ( Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AccLog\ACC_Database.accdb) may be different then yours. In order to get your connection string, please review the following from a previous post and search for connection string.

'Log values into an Access Database
 'We will use a SQL instruction to insert a record into the table
 Try
 Dim SQL As String 'SQL Command String
 Dim objCmd As New OleDb.OleDbCommand 'Command
 'Connection String to the Access Database
 Dim Con = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AccLog\ACC_Database.accdb")
 'SQL Statement - All values must be set for the table
 SQL = "INSERT INTO ACC_Log VALUES ('" & Now.ToString("yyyy/MM/dd") & "', '" & Now.ToString("hh:mm:ss") & "', '" & Registers(0) & "', '" & Registers(1) & "', '" & Registers(2) & "')"
 Con.Open() 'Open the database connection
 objCmd = New OleDb.OleDbCommand(SQL, Con) 'Set the command
 objCmd.ExecuteNonQuery() 'Execute the SQL command
 Con.Close() 'Close the database connection
 Catch ex As Exception 'What to do when an error occurs
 Label10.ForeColor = Color.Red
 Label10.Text = "Database Error!"
 End Try
 End If
 End Sub

This timer is originally set for 100ms. When the program starts, it will log the first time and then set the timer interval to 1 minute. (60000ms)

 Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
   Label9.Text = Now 'Display time and date
 Timer1.Interval = 60000 'Set interval for 1 minute
 Timer1.Enabled = True
 Call Sub() Button1_Click() 'Call the routine to get the PLC information and store in a database
 End Sub

This will handle the LinkLabel on the form.

 Private Sub LinkLabel1_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
   ' Specify that the link was visited.
 Me.LinkLabel1.LinkVisited = True
 ' Navigate to a URL.
 System.Diagnostics.Process.Start("https://www.accautomation.ca")
 End Sub
 End Class

ModbusTCP MSAccess 070-min

Running the program:
The status will show ‘Initializing..’ so that the imports can be loading in the program. This will happen each time the software starts.
ModbusTCP MSAccess 083-min

Logging will be displayed in the status to indicate that everything is working correctly.
ModbusTCP MSAccess 080-min

Error messages will show in the status when an error has occurred. We have programmed two errors:
Communication Error! – Information is not being received from the PLC. This could be due to the wrong IP address or communications links have been broken.
Database Error! – Information cannot be written into the database, the database is not present, etc.
ModbusTCP MSAccess 085-min

Here is what our database looks like after collecting a few samples.
ModbusTCP MSAccess 105-min

As you can see, collecting information from the PLC via ModbusTCP and inserting it into an Access database can be easily done. The next steps would be to ensure that the data is always collected. What happens when the communication to the PLC is cut? Robust logging is required. This is when we create a buffer in the PLC of the logged values. Please refer to our EBook ‘Robust Data Logging for Free’ .

Download the sample database, visual basic and PLC code for this project here.

Watch on YouTube : Modbus TCP Data Logging to Database
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.




Building a PLC Program That You Can Be Proud Of – Part 5

The Game of Simon
Learning all about bit manipulation and sequencers

Simon is a memory game introduced in 1978. It has four coloured buttons, each producing a particular tone when it is pressed or activated by the device. A round in the game consists of the device lighting up one or more buttons in a random order, after which the player must reproduce that order by pressing the buttons. As the game progresses, the number of buttons to be pressed increases. If the wrong button is hit the current game is over. Our game will have a high level score and a current level score.

We will be using AdvancedHMI to communicate Modbus TCP to the Automation Direct Do-More Designer Software Simulator.

Watch on YouTube : The Game of Simon Play (PLC / HMI)
Here is the end result of our program.

Note: The programs are provided free of charge and are an excellent way to learn PLC / HMI programming.

Here is a quick review of the programming series so far. If you are new to the site, we recommend reviewing the other parts in the series first. In part 1 we looked at writing PLC programs to control a traffic light using discrete bits and then using timed sequencing using indirect addressing. Part 2 used indirect addressing for inputs as well as output to control the sequence of pneumatic (air) cylinders in the program. Part 3 and 4 we returned to the traffic light application and expand our program significantly. We looked at the sequence of operation using Input, output and mask tables.

Simon Game HMI 130-min

The first thing that we will do is look at the HMI programming. Please refer to the following post for information on setting up and using AdvancedHMI software.
Create a PLC with HMI Training and Learning Environment Free

The following table is the Modbus TCP memory map to the Do-More PLC:

Coil/Register Numbers Data Addresses Type Do-More PLC Table Name
00001-09999 0000 to 270E Read-Write MC1 to MC1023 Discrete Output Coils
10001-19999 0000 to 270E Read-Only MI1 to MI1023 Discrete Input Contacts
30001-39999 0000 to 270E Read-Only MIR1 to MIR2047 Analog Input Registers
40001-49999 0000 to 270E Read-Write MHR1 to MHR2047 Analog Output Holding Registers

Simon Game HMI 100-min
Add the ModbusTCPCom control and set the IP Address. Also set the PollRateOverride to 50 so the response to our PLC is quicker.

The following map will apply to our game:
(Communication between the HMI and PLC)

40002 – MHR2 – Register – Game Sounds
40001 – MHR1 – Register – Current Game Level
40003 – MHR3 – Register – Highest Game Level
00005 – MC5 – Bit – Start/Reset Game
00001 – MC1 – Bit – Green Button Input
10001 – MI1 – Bit – Green Button Set
00002 – MC1 – Bit – Red Button Input
10002 – MI1 – Bit – Red Button Set
00003 – MC1 – Bit – Yellow Button Input
10003 – MI1 – Bit – Yellow Button Set
00004 – MC1 – Bit – Blue Button Input
10004 – MI1 – Bit – Blue Button Set

Simon Game HMI 110-min
The DataSubsciber is used to read information from the PLC and manipulate the data in the visual basic code.  We will use this to determine what sounds to play.

The following is the code for the button when hit to play the sound and the DataSubscriber1 to play the sound when the playback from the PLC is required. (Console.Beep (Frequncy (Hz), Duration (msec)))

Private Sub PilotLight1_Click(sender As Object, e As EventArgs) Handles PilotLight1.Click ‘ Green Light
     Console.Beep(415, 420)
End Sub

Private Sub PilotLight2_Click(sender As Object, e As EventArgs) Handles PilotLight2.Click ‘ Red Light
     Console.Beep(310, 420)
End Sub

Private Sub PilotLight3_Click(sender As Object, e As EventArgs) Handles PilotLight3.Click ‘ Yellow Light
     Console.Beep(252, 420)
End Sub

Private Sub PilotLight4_Click(sender As Object, e As EventArgs) Handles PilotLight4.Click ‘ Blue Light
     Console.Beep(209, 420)
End Sub

Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged
If DataSubscriber1.Value = “1” Then
     Console.Beep(415, 420) ‘ Green
ElseIf DataSubscriber1.Value = “2” Then
     Console.Beep(310, 420) ‘ Red
ElseIf DataSubscriber1.Value = “4” Then
     Console.Beep(252, 420) ‘ Yellow
ElseIf DataSubscriber1.Value = “8” Then
     Console.Beep(209, 420) ‘ Blue
ElseIf DataSubscriber1.Value = “10” Then
     Console.Beep(120, 1500) ‘ Losing Sound
ElseIf DataSubscriber1.Value = “20” Then
     For x = 1 To 8
          Console.Beep(600, 90) ‘ Winning Sound
          Threading.Thread.Sleep(20)
     Next ‘x
End If
End Sub

Simon Game HMI 120-min

We now have our HMI interface complete and can move onto the PLC programming.

Random sequence generator – MHR4
The first four bits of MHR4 will be used to generate the random sequence for each step of the pattern.
The first scan is to set (1) a bit in MHR0. This will also ensure that the rest of the bits in the word are reset (0).
The second rung will shift the bits left in the output word MHR0. This will happen once per scan of the PLC. When bit 04 turns on then bit 00 will then be turned on again. This way we will always have one of the first four bits turned on in the output word. (00, 01, 02 or 03) MHR0 is logically AND with Hex value 000F and the result is placed in MHR4.

Simon Game PLC 100-min

Start the Game
The game will be started n the leading edge of the reset button. (MC5)
All of the registers and pointers are reset to start the game.
1     is moved into the current level – MHR1
100 is moved into the Play Sequence Pointer – V1
100 is moved into the Input Sequence Pointer – V2
100 is moved into the Current Level Pointer – V0
The random sequence MHR5 is moved indirectly into the Current Level Pointer V0 and this pointer is then incremented by 1.
An initialization bit is then set.

Simon Game PLC 110-min

The initialization bit starts a timer for 600msec in order to give time for the HMI to respond. Once the time expires the Initialization bit is reset and the game start bit is set. (Y0)
Y0 – Game Started is used to determine if play is to continue.

Simon Game PLC 120-min

Play the Sequence

Simon Game PLC 130-min

Set the outputs to play the sequence.
This will also set the sound to play for each of the colours selected. (MHR2)

Simon Game PLC 140-min

Reset the sound during the playing of the sequence.

Simon Game PLC 150-min

Read the inputs from the HMI. (MC1 – MC4)
Set the bits in V501 so we can compare the word.

Simon Game PLC 160-min

If the wrong button is hit, then play a sound and stop the game.

Simon Game PLC 170-min

If the correct color is selected, increment to the next random colour in the sequence.

Simon Game PLC 180-min

If the correct colour sequence has been entered, then the level has been completed.
Start a delay to ensure that the HMI has finished playing all of the sounds.

Simon Game PLC 190-min

When the level is entered correctly and the time delay has expired, we will reset the play and input pointers. The level increases by 1 and  another random number is added to the sequence and the current level pointer is incremented by 1.
MHR1 – Level – Increases
V1 – 100
V2 – 100
V0 – New random number gets indirecly addressed and the pointer is incremented by 1

Simon Game PLC 200-min

Setting the Highest Level achieved
If the current level is greater than the highest level, the current level is moved into the highest level.

Simon Game PLC 210-min

If no key is hit for 45 seconds after the sequence is played a sound will be played and the game start bit will be reset.

Simon Game PLC 220-min

If the game start bit is off for more than 500ms, the sound will be reset.

Simon Game PLC 230-min

Download the PLC program and the Bin directory for the AdvancedHMI screen.

Watch on YouTube : Building a PLC Program that You can be Proud Of – Part 5 – Game of Simon

Part 6 will look at a sequencer controlling seven cylinders that can be taught. The cylinders can be operator programmed from the AdvancedHMI screen.

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.




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)

 

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
Modbus TCP using VBA Excel 001-min

2. Run downloaded file from Windows Explorer or command-line
Modbus TCP using VBA Excel 002-min
Hit OK
Modbus TCP using VBA Excel 003-min
I use the default directories where the program will be installed. Click the button to install.
Modbus TCP using VBA Excel 004-min
Leave the program group to the default so I know what the program is after installation. Click continue.
Modbus TCP using VBA Excel 005-min
Click OK
The OstroSoft Winsock Component is now installed.

Start Microsoft Excel.

Modbus TCP using VBA Excel 010-min
Select ‘Developer’ along the top tabs.
Modbus TCP using VBA Excel 020-min

If the Developer tab is not present then we must turn on the developer tab.
Select File | Options Modbus TCP using VBA Excel 021-minSelect ‘Customize Ribbon’Modbus TCP using VBA Excel 022-minCheck the ‘Developer’ under Main Tabs. Modbus TCP using VBA Excel 023-min

Under the Developer menu. Select ‘Visual Basic’
Modbus TCP using VBA Excel 030-minThe Visual Basic Editor window will now be displayed. Modbus TCP using VBA Excel 040-min
From the menu – Tools | References
We can now add the OstroSoft Winsock Component to our application.
Select OK
Modbus TCP using VBA Excel 050-minSelect Sheet1(Sheet1). Modbus TCP using VBA Excel 060-min

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

Modbus TCP using VBA Excel 070-min

 

Communication to the PLC

Start the Do-More Designer software.
Under the Project Browser select ‘System Configuration’
Modbus TCP using VBA Excel 125-min
Make note of the IP address. If you are running the simulator then this is automatically filled in.
Modbus TCP using VBA Excel 126-min
Ensure that the Enable Modbus/TCP Server is checked. Also make sure that the TCP Port Number is 502.
Modbus TCP using VBA Excel 127-min

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)
Modbus TCP using VBA Excel 150-min

This is repeated with different internal clock bit flags up to MHR10.

Running the program will produce the following:Modbus TCP using VBA Excel 120-min

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.