How to Implement Modbus TCP Protocol using VBA with Excel

[@jaycodes8790]

Thank you so much sir for sharing such a wonderful thing for free. It's because of guys like you we are learning technologies for free. 👍🙏.

[@ACCautomation]

Thank you for the comment, Jay Shah. You made my day! Garry

[@amaritja4045]

Hello I'am Mr. Amarit from Thailand great your video, thaks

[@ACCautomation]

Thank you for the comment. Regards, Garry

[@chandararanget7879]

hello Sir Im from Cambodia great video

[@ACCautomation]

Thanks for the comment Chandarara. Garry

[@eugenioargaiz1604]

Hello, from Spain, great your video, thaks

[@ACCautomation]

+Eugenio Argaíz Thank you

[@aussiegruber86]

Is this similar for the click plc

[@ACCautomation]

Hi aussieguber86, This is similar for the ethernet click PLC. The register address 40000 would be equivalent to the DS area in the click. To see the Modbus addresses, use the address picker and select show Modbus addresses. Another option would be to use AdvancedHMI. accautomation.ca/click-plc-advancedhmi-communication/ This free vb.net program has the Modbus drivers already written. You would then use VB to write to the excel file or store the information in a database file. Regards, Garry

[@MrPereira99]

Great video, thnks! I have one question, the adress of the first register(excel cell) is a hexa number? Any other input data is in hexa too?

[@ACCautomation]

Hi joao pereira, Here are the cell numbers in the Excel sheet: IP Address = B4 MHR 1 to 10 values located at B10 to B19 These are just pointers to the cell. The values that are returned are hexadecimal from the PLC. (16 Bit Binary Word) 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. The full explanation can be found on our website for this video: accautomation.ca/how-to-implement-modbus-tcp-protocol-using-vba-with-excel/ I hope this helps you out. Regards, Garry

[@MrPereira99]

@@ACCautomation I saw that code already. I want to know if the input data i fill, in first register(excel cell), that is sent to PLC is hexa. The code is working with the do-more simulator, but i am testing with a PLC i have with no sucess :(

[@ACCautomation]

@@MrPereira99 Yes, the information from the PLC registers is in hex. The Do-More Simulator and Do-More PLC (Like the BRX) should be the same code. It would just be the IP address change. I would also check your firewall on your computer to ensure that you will allow communication to port 502 (Modbus TCP default). Regards, Garry

[@MrPereira99]

​@@ACCautomation If i want to change the first adress of the first register i have to change in the vba code, more precisely in the mbusQuery, correct? in the excel cells is not enough. I think use chr() to transform in ASCII but i didnt understand how i write the message i want to send this way, can you explain? (Sorry for the late response, i was busing doing other things :( )

[@ACCautomation]

@@MrPereira99 If you want to write data to the Modbus device you will need to change the code. The website post for this video will include the code to write information. accautomation.ca/how-to-implement-modbus-tcp-protocol-using-vba-with-excel/ ‘ 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 Regards, Garry

[@javiervidoza2316]

Good morning. Where can I get the excel file? Thank you

[@ACCautomation]

Hi Javier, The website will have detailed steps of the program as well as the download file. accautomation.ca/how-to-implement-modbus-tcp-protocol-using-vba-with-excel/ Here is a link to the download file: www.dropbox.com/s/o32a1wwmu997yzz/ACC%20Modbus%20TCP%20Excel%20VBA.zip?dl=0 Regards, Garry

[@KristienPraest]

I keep getting "Error -2147221164: Class not registered" when I press the 'Retrieve Data' button. Already repaired Excel, error persists. The VBA code is not modified since downloading from your dropbox. I'm running Windows 11, MS Excel 2021

[@ACCautomation]

Hi Kristien, Can you register the OSWINSCK.dll file from the command line? Here is a link to explain how to do this. myaps.reckon.com/knowledgebase/article/KA-01428/en-us#:~:text=To%20register%20DLL%20or%20OCX,e.g.&text=2.,regsvr32%20with%20the%20%2Fu%20switch. -To register a 32-bit dll or ocx Windows System file in a 64-bit version of Windows (11, 10, 8.1, 7, or Vista), Type cd \Windows\SysWOW64 [then press Enter] Type regSvr32 oswinsck.dll [then press Enter] Please let me know if this works for you. Regards, Garry

[@KristienPraest]

@@ACCautomation Copied the DLL file from the programs files folder into the SysWOW64 folder. The OCX file was already there. Ran the following two commands.. C:\Windows\System32>C:\Windows\SysWOW64 egsvr32 oswinsck.dll C:\Windows\System32>C:\Windows\SysWOW64 egsvr32 oswinsck.ocx Both registered successfully, but still getting the same error in Excel

[@ingenieriaeyp1431]

One Question Can I use this to Query a Modbus data fom '040001 register? How? Best regards

[@ACCautomation]

You should be able to query the Modbus data from 40001. The website will show you step by step. accautomation.ca/how-to-implement-modbus-tcp-protocol-using-vba-with-excel/ Regards, Garry

[@michaelnielsen7974]

Hi, how do i change Modbus addresses to another component using Modbus TCP / IP. It is for a Genset controller DEIF AGC4. for example input register 04 address 501 Generator voltage L1-L2 (INT16)

[@ACCautomation]

Hi Michael Nielsen, I have not used this controller. The manual appears to say that you can use software to change the Modbus address. deif-cdn-umbraco.azureedge.net/media/purl03z4/option-h2-and-h9-modbus-communication-agc-4-4189341180-uk.pdf?rnd=132572580230370000&v=4 I would contact the manufacturer. Regards, Garry

[@michaelnielsen7974]

@@ACCautomation I have the all the adresses to the controller. I want to know how to change your excel to these ardesses.

[@ACCautomation]

@@michaelnielsen7974 All of the documentation for this post is located on our website. accautomation.ca/how-to-implement-modbus-tcp-protocol-using-vba-with-excel/ 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) ‘ 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 ‘0020: The number of bytes to read I hope this helps you out. Regards, Garry

[@TheAlansal]

Fantastic video... Thanks... But what if i needed each cell to be between the values (-65536 and 65536)?

[@ACCautomation]

Hi TheAlansal, www.mrexcel.com/forum/excel-questions/767831-signed-hex-numbers.html The above link may help you out. It will be a matter of converting the hex number into a signed number. Regards, Garry

[@TheEguato]

Hi garry!I have a problem with this code. The code works perfectly for Integer data, but I need to work with floating type data. when I try to implement float data in the excel book, the program shows me an error. I already see the info in your webpage, but I can't find a solution for this. Can you help me?

[@ACCautomation]

Hi Ramses MG, Once you have retrieved the information, then you can convert to floating-point. Here is post that explains the process. davestow.com/handy/excel-hex-to-floating-point.htm What I would do is use a program like AdvancedHMI to get the data. This program runs on visual studio, vb.net and is free. You would then communicate directly to the excel file for data. accautomation.ca/create-a-plc-with-hmi-training-and-learning-environment-free/ AdvancedHMI has the drivers are already written for floating-point and long information. Regards, Garry

[@johnichan]

Stuck with Error 429: ActiveX component can't create object.. any hope?

[@ACCautomation]

Hi Johnson, www.pcreview.co.uk/threads/mswinsck-ocx-run-time-error-429-activex-component-cant-create-ob.3880005/ The above link may help you with this error. I am not sure what windows version you are using, but this error has to do with the Ostrosoft Winsock Component. I would also try to reinstall the component. Regards, Garry

[@queerprodigy]

I try use your code but i have trouble. I line: Set wsTCP = CreateObject("OSWINSCK.Winsock") I have: Error 429: ActiveX component can't create object I use Windows 10 64bit Can you help?

[@ACCautomation]

+Silar Silar I have not tried this on Windows 10. This was originally ran on Windows 7, 64 bit. Run-time error '429': ActiveX component can't create object This error occurs when the Component Object Model (COM) cannot create the requested Automation object, and the Automation object is therefore unavailable to Visual Basic. This error does not occur on all computers. Try the following before the Set line: DIM wsTCP As OSWINSCK.Winsock Set wsTCP = CreateObject("OSWINSCK.Winsock") Let me know if this works, Thank you, Garry

[@jackodin8388]

This doesn't work. I've the same erro 429. I'm on Win10 with Access 2013. Maybe i should try on another PC ? Or you found a solution ? If I find a solution I'll tell you. Thanks. Jack.

[@ACCautomation]

Hi Jack, Can you try to register the OSWINSCK.dll file from the command line. Here is a link to explain how to do this. www.ctimls.com/Support/KB/How%20To/Register_dll.htm -To register a 32-bit dll or ocx Windows System file in a 64-bit version of Windows (10, 8.1, 7, or Vista), Type cd \Windows\SysWOW64 [then press Enter] Type regSvr32 oswinsck.dll [then press Enter] Please let me know if this works for you. Thanks Jack, Garry

[@jackodin8388]

I tried your solution but the same problem happened. Should I tried to run it on Windows7 Virtual Machine ? Thanks for your help.

[@ACCautomation]

Hi Jack, This was originally done on a windows 7 64bit machine. Yes, please try it on a windows 7 or a virtual machine. Let me know how you make out. Thanks Jack

[@venkateshchennuri8992]

Sir, i want to read floating point type holding registers from PLC to Excel using VBA. Please provide me sample code

[@ACCautomation]

Information is exchanged in Modbus via Hex values. To change the hexadecimal to floating-point use the following information. www.simplymodbus.ca/ieeefloats.xls I hope this helps you out. Regards, Garry

[@venkateshchennuri8992]

@@ACCautomation thank you very much. Could u please provide vba script for converting hexadecimal to IEE floating point sir.

[@ACCautomation]

@@venkateshchennuri8992 The following will demonstrate using VBA in excel. davestow.com/handy/excel-hex-to-floating-point.htm Regards, Garry

[@samsam7051]

Thank you