How I used classes in VBA and even fell in love with this business

The idea of ​​the self-existence of information wanders in the circles of mostly techies who overslept university philosophy somewhere in the back as a humanitarian subject they do not need and creates an incredible confusion that really makes it difficult to understand what an object is and why it is needed. If we take information that necessarily reflects the objects of the real world, then the information object only abstracts reality, and, accordingly, the value of processing it is real, and self-existence only produces barren objects, from which nothing real follows. The code, in fact, is the maximum abstraction, and the development of the PL is constantly moving towards bringing it closer to natural language, to physical concepts.

So, an object in OOP is useful precisely because it reduces the level of abstraction of the code, brings it closer to reality, and simplifies the structuring and processing of information. You can drive information into a three-dimensional array, where to present. that the first dimension reflects, for example, a specific machine, the second – the main components, and the third – spare parts. But in the code, such an array will be represented as a set of abstract symbols, and the difficulties in its perception for leather bags begin already from the third order. The person to whom I threw such an array in the code almost went crazy trying to understand how and why the fourth dimension of the array is recalculated. To his credit, I myself didn’t understand after a month what I meant when I put exactly 5, not 6 or 4, in the bTotal (x, y, z, 5) array, and for what data were reserved empty elements, and whether they should be used at all.

An array in VBA still has a drawback – I cannot make part of the elements of one type, and part of another. I can make it of a general type, when the smart machine determines the type itself, and thereby give it to the fool user who will change the cell type and then whine that nothing works for him.

And the alternative is an object – a sort of mini-array with a different data type, where I can bind the properties and methods of the object to real objects of objective reality by naming and reduce the level of abstraction of the code for perception.

For example, I have invoices. Invoice – it has some properties. There is a client to whom it goes, there is an address, a date, a carrier. In the warehouse, the invoice is completed – into pallets, the pallet has properties – weight and dimensions, type of pallet, boxes are in the pallets, they also have properties – the number of materials, the number of sets, dimensions, in the boxes – individual materials with their own properties – number, weight, dimensions , hazard class and sets of materials with properties such as quantity of materials, quantity of packaging, etc.

You can pack all this into an array, or you can create a VBA object of a custom class for each real object and link them into a collection of objects with an internal hierarchy, nesting objects and collections of objects inside the object, almost literally repeating the physical process in the code – an invoice is taken, materials for the consignment note is selected in sets, placed in boxes, boxes and individual materials are placed in pallets. If suddenly some material has some properties, then they are easily added without recalculating the indices – the property is simply added. That is, we bring the code closer to natural language and to objective reality, bringing it to an acceptable level of abstraction for us. At the same time, as a bonus, I get a search by key, and I don’t go through the same arrays thousands of times to find the desired element (the built-in function secretly sorts them, which does not improve the speed, but for plates less than a hundred thousand lines this is not very important).

Create a class for the Waybill invoice by defining the following properties

Public Address As String 'адрес
Public WGT As Double 'общий вес
Public pal As Long ' количество паллет
Public Whole As Integer 'количество целых
Public Part As Integer ' количество сборных
Public WblNbr As String ' номер накладной
Public StrQty As Long 'количество строк в накладной
Public IDHQty 'количество материалов в накладной
Public Company As String 'Наименование компании
Public Receiver As String 'Получатель
Public Order As String 'Заказ
Public Timeslot As String 'Время погрузки
Public Status As String 'статус
Public OutDate As String 'дата отгрузки
Public Client As String 'Клиент
Public PLTs As New Collection 'коллекция паллет

It doesn’t look as short as just defining Dim Waybill(17, count) and remembering or commenting somewhere that the first index 1 means the invoice number and 2 means the recipient, for example. Plus, we have a variety of data types in one place, and these types do not disappear from us anywhere – one element is lowercase, the other is numeric, and everything is under control.

Creating a collection

Set wbls= New Collection

fill it with elements of the Waybill class

For i=2 to count

wbls.Add Item:=New Waybill, Key: =CStr(Cells(i,1))

Next i

Inside the loop, we fill the collection element with something, for example, we assign some properties to it, which we have in the table. For example,

akey=CStr(Cells(i, WB))

wbls.Add Item:=New MWaybill, Key:=akey
wbls(akey).Receiver = Cells(i, rec)
wbls(akey).WblNbr = CStr(Cells(i, WB))
wbls(akey).Timeslot = CStr(Cells(i, ts))
wbls(akey).OutDate = CStr(Cells(i, dt))

Thus, in the code, we see that faceless indices turn into quite understandable and somehow human images that carry information specifically for a person – I don’t need to check that we have encrypted in the first / second and so on index of the array, at the same time, to get recipient by the invoice number from the array, no need to rustle with brute force or search. Each invoice corresponds to one class object with its own properties

But we have one more plate – with a description of the pallets, which can be correlated with the first plate only by the invoice number. Since there can be many of them in each invoice (tens and even hundreds), we create a nested collection PLTs of the Pallets class for each object, in which we collect information on pallets:

Public WgtPal As Double'вес паллета
Public pType As String 'тип паллета
Public pColor As Integer 'цвет заливки
Public HUnbr As String 'Номер паллета
Public IDHInside As New Collection 'коллекция материалов внутри паллета
Public IDHCountinPal As Long 'количество материалов на паллете

similarly, a collection of materials is created inside the pallet IDHInside.

In practice, accessing this collection looks somewhat cumbersome:

wbls(key1).PLTs(key2).IDHInside(key3).QtyBox = Cells(i, 3)
wbls(key1).PLTs(key2).IDHInside(key3).QtyPcs= Cells(i, 4)

But it more than makes up for it in structure. Now in the array (collection) you can immediately refer to the number of boxes with this material on the pallet and to the amount of loose powder on the same pallet by the invoice number, pallet number and material number. That is, as a result, the code is clear, reads well from scratch without special confusing descriptions, it is easy to add elements to it at any level, we get an array structured in a certain way, including hierarchically, and equipped with keys.

And if we need to generate and print a packing list for an invoice, then we find the invoice by the key and transfer each of the pallets to the desired form for printing by sorting through the collection of pallets through

For Each y In wbls(key1).PLTs
For Each z In y.IDHInside
count = count + 1
Cells(count, 1) = wbls(key1).WblNbr'номер накладной
Cells(count, 2) = y.HUnbr'номер паллета
Cells(count, 3) = z.IDH0'материал
Cells(count, 4) = z.QtyPcs'количество расыпухи
Cells(count, 5) = z.QtyBox' количество целых упаковок
Cells(count, 6) = y.pType' тип паллета

Next y

Next z

And if the form of the packaging suddenly changes, the data source is added, some property of the invoice is added that needs to be displayed (for example, the details of the recipient or the physical dimensions, or the type of transport for each pallet), then it is quite easy to add without changing the algorithm and code structure. In short, modeling real-world physical objects through custom VBA objects helps make code easier to work with.

Eat. of course, limitations – for example, I have never tried collections of this kind for ranges of more than 30 thousand lines, and therefore I don’t know how it will behave on a half-million tablet, and whether the whole office will collapse like Turkish shacks during an earthquake, but I suspect that the question of resources will definitely arise. In some cases, it is unnecessary to fence this garden – it will take more time to write the collection itself. than just do a few loops through the array. However, it makes sense that such code is easier to develop, and in principle any data types can be attached to an object, including very exotic objects.

item 4. Is this relevant now, when small software companies have waved the pen to us, and VBA seems to be developed by no one?

Oddly enough, CBO and the departure of many foreign companies provoked a temporary boom in the VBA application market. Many companies have begun to replace the standard functionality of more non-functional programs with crutches in Excel, which is still working. Of course, far from all the necessary and useful functions of ERP systems have been replaced by imports, for example, and due to the fact that corporations are already working very hard to finalize their software products, people began to fuss en masse and look for developers of crutches that could support crumbling processes. In principle, even before CBO, a lot of lotions were written in VBA due to the inflexibility of the 1C box, the high cost and confusion of its refinement with a file and various self-made craftsmen. I would say with confidence that more than one generation of VBA developers has fed the 1C product. And after February 2022, dumbfounded by the new realities, people were just as eager to close the holes that were formed using the methods available to them.

For many years, well-established processes fell down – for example, one company lost its license for the xml conversion and transfer tool (there were more functions, but for some reason they could not replace the xml conversion and adding data to it) – they closed it with a VBA lotion. The decision was planned as temporary until the end of the year – but in January I was already completing an update in the form, apparently, the clumsy corporate processes did not solve it, or the lotion simply costs them less.

I believe in the latter quite well – Russian companies that rushed to “import substitution” give such prices for development that it is easier to hire a person who will press the macro button on a broken office at home than to buy their product.

Another factor that I would like to note is that over the past year, the complexity of tasks for VBA has also increased quite noticeably. If earlier the bulk of the tasks were a block of fairly simple analytical tasks or the formation of printed forms, then in 2022 they began to ask such things that draw on a very full-fledged development of individual applications with blackjack and hookers. For example, simulate on VBA WMS. Or automatically collect information from sites, analyze it and upload it back through the form, or build a product of complex logic on the border with AI to form a package of some kind of regulatory documents. That is, something that even an experienced person can cope with with great difficulty, not to mention the average user, has gone.

The second moment that richly fed VBAs in the Russian Federation over the past year was the high threshold for entering macro languages ​​of import-substituting MS Office packages. Although it should be, in principle, the other way around – the more they replace imports, the less they get on VBA. But nevertheless, having discovered that import-substituting packages use a poorly documented object base, with little-known PLs (classic – LUA in MyOffice), with a poor IDE and a NOT developed environment (to the point of asking how to process some kind of picture on sheet, you have to ask the developers of the package directly), many companies choose to conduct a number of processes specifically on MS Office until they are forcibly chopped off. In this case, developing a macro in VBA is orders of magnitude cheaper, easier than in JS, and cheaper than manual monkey labor. If for the simplest macro on LUA you still need to find some kind of craftsman who will also roll out a price tag, then there are already quite a lot of craftsmen on VBA, and they also have more opportunities than on new, unknown products. And therefore, until small software is cut off for everyone hands opportunity to work in their products, will cry. prick, but eat this cactus. Because it is much more edible than the brick that is offered in return.

Just the other day, specialists from the import-substituting MS Office company, to the question – “since your macro language pulls the API, then where can I get a full description of this API and where to run if something doesn’t work”, mumbling obscurely, threw off a link to the site where the article was telling how to programmatically colorize a cell, and the question about the presence of a macro recorder was cheerfully answered “no”. From this, one can quite confidently pretend that in those cases in which macro crutches are required in office applications, they cling, cling and will cling to Microsoft, because there is nothing more to cling to.

In short, while the patient is more alive than dead. It may not be possible to pump out, but this will not happen soon, and therefore, even after a year of “decisive refusal” by the outgoing and then returning Microsoft, the article is unlikely to have lost its relevance.