Moscow, Azovskaya 14
+7 (495) 310-97-15
Mo-Fr: 9.00 - 18.00 (Moscow time)
Order a call
Your name *
Enter phone *
Your Email *
Call me back
VBA Excel in automation as a way of working out a prototype


VBA EXCEL - Why might this be needed in the production of automation work?

1. If you have to make a complex neural network based on previous measurements in calculations
2. If the customer has absolutely no money, but something like SCADA is still necessary to create. Sometimes it fits.

Let's say you got a rather complicated mathematical task for working with a large amount of data, and the task for industrial purposes must be performed on the PLC. This task is easier to develop in C# or Visual Basic Application languages, as a result, the task of prototyping is born. Such problems can be, for example, problems of approximating data in a large non-linear array and finding the coefficients of a linear function under the condition of an increment to a straight line. There are also much simpler tasks that are also convenient to visualize in the MS EXCEL program using the VBA language, for example, reports. Writing programs in VBA will significantly reduce the costs associated with clarifying the terms of reference in the course of work.

There are also much simpler tasks that are also convenient to visualize in the MS EXCEL program using the VBA language, for example, reports. Writing programs in VBA will significantly reduce the costs associated with clarifying the terms of reference in the course of work.

Апроксимация кривых

However, questions immediately arise, how exactly and on what to develop a prototype of a code model, how to translate or convert algorithms executed in VBA into the Pascal language ST, in which PLCs of such brands as Siemens, Schneider Electric, Omron, Wago, Owen can be programmed. At the same time, tasks pop up with remote data acquisition from the PLC to EXCEL via the Modbus TCP protocol via the Internet.

In the task with which I am dealing (I do not disclose the goal), the Wago 750-8202 PLC was chosen with a certain set of I / O modules. The controller provides reception, processing and archiving of information within 30,000 variables in REAL format. Data is written to different structural data types with event logging.

The question arises: how to transfer such a volume of data to EXCEL via the Internet?

The answer is quite banal - we transmit in parts, by analogy with how the inscriptions on the station board change. The portion was read, the line changed, and so on ad infinitum, until the entire array of information is transmitted.

We broke a lot of firewood on setting up the router and modem to forward ports and create a stable connection with a dynamic IP. DDNS was used (accessing a remote host by URL), while the dynamic address of the Internet device may change. This does not affect the quality of communication and data reception. I used a Zyxel Kinetic router and a Megafon USB modem. Serious problems were encountered with MTS communication and it had to be abandoned. We have two ports open for permanent PLC operation: 2455 (Codesys) and 502 (Modbus TCP). When setting up the Wago PLC, you should use the instructions. As a function of communication with a remote PLC, a paid ActiveX applet was used, taken from the modbustools.coм


It works and is configured well, however, due to its triality, it exchanges data for no more than 60 minutes and after three months of use it stops starting. I did not try to clean the registry, but reinstalling Windows definitely helps.

Таблица переменных

This table created a visualization of 100 registers for reading and 10 for writing. The applet is the client part in the Modbus TCP exchange protocol (the initiator of sending packets). The PLC is the server side of the protocol, responding to remote client requests. The PLC has a buffer for remote exchange, which allows, when changing a line in the buffer, to change all sent data to the next ones in sequence. When EXCEL registers to receive data, it changes the value of the string in the buffer. Thus, without too much haste, we bypass all the values of the accumulated data.

(*Integral Array Buffer *)

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





And finally, converting data from the registers we received into convenient and understandable values for many in the REAL, INT, BOOL format

I use functions that I could dig up on the Internet.

ROW1 = MW100 ‘So we write INT

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

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

Z = CBool(MW106 And 1) ‘Так пишем BOOL

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

Function code for converting from two registers to 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

ST (Codesys) FOR & WHILE statements require special tricks. So, for example, to work with a large amount of data in arrays, you need to use functions. If this is not done, then the probability of issuing messages about overflowing the amount of RAM or about stopping the controller increases sharply. That is, for data processing by cyclic functions, it is important not to convert them into the category of temporarily accumulated data. In addition, you should only use cyclic statements for operations on working with arrays of data (justified use).


Be the first to comment

You comment add