How to Implement Modbus TCP Protocol Using VBA with Excel

I have 2 questions about the VBA code. First how do you change the address that you are looking at I believe what I am trying to pull starts on mod his address 65 and was wanting my first address to pull from that area.


Next is the address that you are pulling the decimal Modbus address or the hex. I am assuming that from the format it seems to be decimal but I was just wanting to make sure.
 
The following code will explain the read function in the VBA Code.

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,
 
Thank you for your reply.
However, I apologize I should have been more specific it is a variable I am trying to read not just a coil. From looking at the earlier messages is it just changing the chr(1) to a 3? And then the rest of the code applies the same?

Thank you again.
 
More Explanation Please

Gary,

Could you possibly explain this a bit more? I have read everything you have attached but am still confused about why there are more chr() functions than what is needed (12x chr() to 7 needed).

If easier, can you tell me what I would need to write if I wanted to write a 1 to modbus address 40001.

Thanks
 
Last edited:
Gary,

Thank you for the quick reply. I still am not quite understanding how to send and receive data.

It would help greatly, I think, if you could show me an example of what to write in a variable to send in order to change a modbus variable.
Lets say I wanted to change address MHR1 to 10. What would I need the chr() to be in the variable I am sending?

I looked up what the CHR function does here (https://docs.microsoft.com/en-us/of...erence/user-interface-help/character-set-0127) and that says chr(0) translates it to nothing?

Side note, the website you linked in the past post has been a godsend for me and what I have been trying to do. There is nothing out there even close to as helpful.
 
Error Message when trying to run on different computer

Hello,

Does anyone know what the error code "Error -2147221164: Class not registered" means? It gives me this error when trying to run the code by hitting the "Retrieve Data" button in the Excel worksheet. I have looked it up in OstroSofts error codes but there is nothing close to it. This has only happened when I tried to run the program on a different computer, it runs perfectly normal on mine. Any info would help.

Thanks in advance.
 
i am having the same problem as alex on a computer, with the class not registered.

other things it suggests is for office repair and such nothing has worked for me so far. do you know what this could be Garry?
 
The highest value of a byte of data is 256.


Ummm...


Cool beans, btw.


One query, just curious: why use OstroSoft? Isn't there already a socket interface in Windows available to eXcel? I don't remember what it is called [udpate: Winsock?], but I have used it before. Not that it matters.
 
Last edited:
i am having the same problem as alex on a computer, with the class not registered.

other things it suggests is for office repair and such nothing has worked for me so far. do you know what this could be Garry?


There should be a way to see where that exception is being thrown. Is there a Debug mode when running VBA in eXcel? If not, perhaps write an incrementing number, or a string like "Just before CreateObject(...)," to a logging cell at various points in the code. Then run it and that cell will give a pointer into the code to find which class is not registered.
 
I would try to install the Ostosoft Winsock component again.
http://ostrosoft.com/oswinsck.aspx
If this does not work, then you may have to register the component. This is a 32-bit component.
http://www.ctimls.com/Support/KB/How%20To/Register_dll.htm
I hope this helps you out.

Keep in mind that this post was written over 6 years ago. I would only use this method if you want to occasionally view data. A more simple method would be to use Node-RED with Modbus and SQLite database. Excel then can read the SQL database using ODBC.
https://accautomation.ca/series/node-red-iot-enabling-software/

Regards,
Garry
 

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