Visual Basic Application EXCEL for automation

Experience using EXCEL VBA for modeling automation, there is a rather complex mathematical problem working with arrays, which are much easier to develop in C# or Visual Basic Application languages. These tasks may include, for example, the task of data fitting in a large non-linear array and finding the coefficients of the linear function, subject to the direct increments. There are also much more simple tasks, which are also conveniently visualize TO MS EXCEL program using VBA language, for example reports.


However, immediately raises questions, how and what to develop a prototype of the Model Code, how to translate or convert the algorithms performed on the VBA, in paskaleobrazny language ST, which can be programmed PLC brands such as Siemens, Schneider Electric, Omron, Wago, Owen. At the same time come up problems with the remote retrieval of data from the PLC in EXCEL for Modbus TCP protocol via the Internet.

In that task, with which I was dealing with (the goal is not disclosed), PLC Wago 750-8202 has been chosen with a certain set of input-output modules. The controller provides the reception, processing and archiving of information within the 30000 variable in REAL format. Data is recorded in different structural types of data latching for time events.

The question arises: how such a volume of data transferred in EXCEL on the internet?

The answer is quite trivial – transfers part, by analogy with how changing the inscriptions on the station display. A portion of the read, the line was replaced and so on ad infinitum, until the entire array will be transferred information.

Mangled a lot of firewood for configuring the router and modem port mapping and build sustainable connections with dynamic IP. It was used DDNS (appeal to the remote site by URL), while a dynamic address Internet devices can vary. The quality of communication and data acquisition is not reflected. Used router Zyxel Kinetic and USB modem megaphone. With MTS communications have met serious problems and it was abandoned. We are open to the PLC two ports continuous operation: 2455 (Codesys) and 502 (Modbus TCP). When configuring PLC Wago necessary to use the instruction.

As the communication functions of the remote PLC surcharge ActiveX applet was used, it is taken on site


It works well and is configured, however, because of its triality communicates no more than 60 minutes, and after three months of use ceases to run. Registry Cleaner not tried, but definitely helps to reinstall Windows.


This table is created visualization 100 registers for reading and 10 for writing. An applet is a client part of the protocol exchange to Modbus TCP (initiator packets sending). PLC is the server part of the protocol, in response to a remote client requests. In the PLC to create a buffer for the remote exchange, which allows for a change in the buffer line change all data sent to the following sequence. EXCEL registers when receiving data, it changes the value of the row in the buffer. Thus, without undue haste, we go around all the values of the accumulated data.

(* Buffer integrated array *)

IF CMB.ROW=0 OR CMB.ROW=10001 THEN CMB.ROW:=1; END_IF;(*%MW12388*)





Finally, the conversion of data from the registers received by us in a comfortable and clear for many values in REAL format, INT, BOOL

By using features that could dig up on the Internet.

ROW1 = MW100 ‘So write INT

x = TwoSIntToFloat (Val (MW102), Val (MW103)) ‘So write REAL

Y = TwoSIntToFloat (Val (MW104), Val (MW105)) ‘So write REAL

Z = CBool (MW106 And 1) ‘So write BOOL

dy = TwoSIntToFloat (Val (MW108), Val (MW109)) ‘So write REAL

Function code to convert from the two registers in REAL format:

Private Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” (Destination As Any, Source As Any, ByVal Length As Long)

‘Binary and single float conversion

‘A.Gamble 2006

‘Note – This code is based upon the ieee standards.

‘Some applications may require the word order flipping

Function TwoSIntToFloat(Wd1 As Integer, Wd2 As Integer) As Single

Dim bytArray(3) As Byte

‘Example use x = TwoSIntToFloat(16384,-15635)

‘Copy the memory area of both words to the memory area of our declared array of 4 bytes

‘We have to do this because all 4 bytes are stored consecutively in memory

CopyMemory bytArray(0), Wd1, 2

CopyMemory bytArray(2), Wd2, 2

‘Copy the memory area of the 4 byte array to the memory area of our float

CopyMemory TwoSIntToFloat, bytArray(0), 4

End Function

With operators FOR & WHILE in the language ST (Codesys) should apply special techniques. Thus, for example, to work with large data arrays must be used in the function. If this is not done, it dramatically increases the probability of issuing reports on the amount of RAM is full, or to stop the controller. That is, for the cyclic functions of the data is important not to translate them into the category of temporarily stored data. In addition, the use of cyclic operators should only be for working with data sets of operations (justified application).

#VBA, #VBAEXCEL, #automation, #workwithdata, #C#, #VisualBasicApplication, #PLC, #MSEXCEL, #EXCEL, #ST, #ModbusTCP

Be the first to comment

You comment add

Back to the list