На собеседовании:

- Как у вас с Экселем?

- Я его ненавижу!

- О, да вы VBA-разработчик!

Я не могу открывать здесь Америк, ибо не технарь, но раз тут был чей-то текст, как написать "Hello world" в Word'e, и всем понравился, то думаю, что нелишним будет и мой небольшой опыт по теме, слабо раскрытой в целом в литературе, и в учебных курсах дающейся обычно факультативно.

п1. Как обычный пользователь пишет макросы в эксельке

Периодически и даже регулярно мне приходится читать чужой код на VBA. Как русскоязычных соотечественников, так и их зарубежных коллег. И создается впечатление, что макрописание, несмотря на большую распространенность, достаточно неплохо разработанный макрорекордер (по сравнению с импортозамещающими аналогами - так просто идеально разработанный), в русский народ не пошло, или почти не пошло. Подавляющее большинство кода для Экселя - это так или иначе связанные между собой и кое-как зацикленные куски, записанные макрорекордером и обработанные напильником.

Ну, например, очень редко для обращения к ячейке употребляется метод Cells с индексами номера строки и колонки, по большей части используют уродцев типа Cells("A"&(i+1)) , а переход к следующей ячейке делают через Offset (и. что интересно, код работает - умудряются не запутываться в сдвиге А относительно Б, С относительно Б и так далее, могу только предположить, сколько такую связку из офсетов отлаживают). Массивов в их классическом виде средний российский макрописец не признает в принципе, и я догадываюсь, что он просто не знает, что есть такая сущность, и все переборы и вычисления проводит. обращаясь напрямую к ячейкам (хотя сансэй Уокенбах в каждой книжке повторяет, что массивы работают на порядок быстрей).

Даже на специализированных форумах типа "Планеты Эксель" взрослые дяди, написавшие не по одной тысяче строк кода, по старинке отвергают мертвую теорию и продолжают обрабатывать массивы данных через прямое обращения к ячейкам и диапазонам мимо массивов и коллекций объектов.

В зарубежье же немного, на мой взгляд по-другому, там очень резкое разделение на профи. заставших еще табличный редактор Cells и сами разрабатывавшие эти чертовы эксели (реально, как-то на замудреный вопрос по Outlook'у ответил русский чувак из Кремниевой долины, который этот же самый Namespace и разрабатывал в составе команды таких же гиков- вопрос его как-то профессионально задел) и ламеров, которые в основном тыкают в макрорекордер и теории не ведают, как Адам и Ева не ведали добра и зла до поедания злополучного яблочка. Но тут все понятно, а вот в случае наших родных макрописцев, то похоже. что все-таки русская привычка читать инструкцию в последнюю очередь стойко берет верх над здравым смыслом.

Но тем не менее - мне достаточно редко приходилось видеть массивы в чужом VBAшном коде, как правило, код при этом был не нашего происхождения, и ни разу не приходилось видеть на наших родных просторах классов и объектов пользовательского класса хотя бы для эксельки. Сразу оговорюсь - для Outlook'а и Powerpoint видел, но обычно такие вещи пишутся не "средним" пользователем - в эти макросы достаточно высокий порог входа.

п.2 Как я дошел до пользовательских объектов

Путь к пользовательским объектам и классам объектов был далеко не прямой. Дело в том, что макросы я писать начал где-то в году 2004-2005, и в памяти еще свежи (относительно) были школьные годы(самое начало 90-х), когда про ООП ни в доступной школьнику литературе, ни из преподавателей никто не заикался. И мое знакомство с VBA началось с того, что я сначала просто правил на работе глючные макросы, написанные кем-то до меня (благополучно ушедшим в закат) и адаптировал их к новым реалиям. Они написаны были приблизительно так, как описано было в п.1., и мне просто некогда было подвергать ревизии их общую концепцию, а надо было дописывать что-то или менять какие-то параметры, адреса ячеек, алгоритмы перебора и пр.

А потом был перерыв в работе с VBA (просто не было задач) ,прошло еще лет 10, и мне пришлось самостоятельно что-то делать. И делал это я сам так, как мне подсказывал собственный опыт, приобретенный в 90-х - то есть, через массивы. Например, алгоритм сортировки пузырьком для массива на бейсике мне в школе вдолбили чисто на уровне фотографической памяти в виде готового кода. А когда я попробовал сортирнуть это для одного мужика через обращение к ячейкам, то я поймал себя на том, что блин, задумался и засомневался в правильности своих знаний (!). Короче, как научился я когда-то давно, когда об экселе ведали только продавцы умных табличек и IBM286 AT\XT, работать с массивами, так и продолжал - загонял диапазон в массив, работал с ним, и образно заполнял.

Получалось проще чем с ячейками, негромоздко, и, главное - массив в своей форме так или иначе отражал некоторое содержание данных - если я называл массив Result1, то я понимал, что я обрабатываю первичный результат, если обзывал Total - то значит, массив обрабатывает общие данные. Точно так же можно было наименовать диапазон, но первый же опыт показал, что в диапазоне контроль за типом данных эфемерен - стоит только одному дураку поменять тип ячейки, и все летит к черту.

Что же до коллекций объектов и классов, то сансэй (в смысле Уокенбах) в 2010 году очень бегло и кратко прошелся по этой теме, не создав впечатления. что эта вещь нужна, важна и удобна. А те русскоязычные пособия, которые писались для ВУЗов и где проблема была разложена глубже, писались на задачах чисто расчетных и технических, и для моих задач ни фига не подходили.

Первый случай, когда встала реальная необходимость написать класс и создать коллекцию объектов - это была классическая задача динамических контролов на юзерформе. И я ее написал по образцу, найденному в интернетах и снова надолго забыл об этом. Потому что обычно хватало массивов - двумерных, трехмерных, я тут не видел особых проблем.

Но потом оказалось, что оказывается, нужная эта штука - объекты пользовательских классов.

п.3 Объект реального мира и зачем он в эксельке

Как-то на Хабре вычитал, что объект-де в коде надо создавать просто так, потому что надо. Что объект в программировании не должен отражать объекты реального мира и каких-то отраженных в данных реальных свойств реальных объектов. Объект есть - а он ничего реального не отражает, просто набор данных.

И тут меня накрыло обломками старых‑престарых дискуссий махистов с марксистами. Потому что еще в 1911 году материалисты не сомневались, что в голове нет и не может быть ничего того. что не отражало бы объективной реальности, что не соответcвовало бы реальному миру. А больше всех не сомневался Ульянов‑Ленин с его работой «Материализм и эпириокритицизм», и правильно делал. Заодно вспомнилось, что как Винер, так и Витгенштейн считали информацию некоторым самосущным предметом, даже без носителя и связи с материальной объективной реальностью, за что кибернетику и окрестили буржуазной лженаукой. Но это не значит, что были прекращены работы по теории информации, ее организации и переработке — в то время, когда кибернетику в СССР громили, кибернетикой называлось только достаточно узкое теоретическое направление западной мысли, которое считало информацию существующей отдельно от материи с целым рядом вытекающих из этого выводов — как то о самостоятельном мышлении роботов. Расширенное понимание как общая теория информации термин «кибернетика» получил позже.

Мысль о самосущности информации бродит в кругах в основном технарей, проспавших ВУЗовскую философию где-то на задних партах как ненужный им гуманитарный предмет и создает невероятную путаницу, реально мешающую понять, что такое объект и зачем он нужен. Если мы берем информацию обязательно отражающей объекты реального мира - то тогда информационный объект лишь абстрагирует реальность, а соответственно, и ценность обработки ее реальна, а самосущность лишь плодит бесплодные объекты, из которых не следует ничего реального. Код, по сути, есть максимальная абстракция, и развитие ЯП постоянно идет на приближение его к естественному языку, к физическим понятиям.

Так вот, объект в ООП полезен именно тем, что снижает уровень абстрактности кода, приближает его к реальности, упрощает структурирование и обработку информации. Можно загнать информацию в трехмерный массив, где представить, что первая размерность отражает, например, конкретную машину, вторая — основные узлы, а третья — запчасти. Но в коде такой массив представится набором абстрактных символов, и трудности в его восприятии у кожаных мешков начинаются уже с третьего порядка. Человек, которому я подкинул такой массив в коде, чуть с ума не сошел, пытаясь понять, как и почему пересчитывается четвертая размерность массива. К чести его, я и сам не понял уже через месяц, что же такое я имел в виду, когда в массиве bTotal(x,y,z,5) поставил именно 5, а не 6 или 4, и под какие данные были зарезервированы пустые элементы, и должны ли они вообще использоваться.

У массива в VBA еще есть недостаток - я не могу сделать часть элементов одного типа, а часть другого. Я могу сделать его общего типа, когда умная машина сама определяет тип, и тем самым отдать на откуп дураку-пользователю, который поменяет тип ячейки и потом будет ныть, что у него ничего не работает.

И альтернативой является объект - этакий мини-массивчик с разным типом данных, где я могу неймингом привязать свойства и методы объекта к реальным объектам объективной реальности и снизить уровень абстрактности кода для восприятия.

Например, у меня есть накладные. Накладная — у нее есть некоторые свойства. Есть клиент, которому она идет, есть адрес, дата, перевозчик. На складе накладную комплектуют — в паллеты, у паллеты есть свойства — весогабариты, тип паллета, в паллетах лежат короба, у них тоже свойства — количество материалов, количество комплектов, габариты, в коробах‑ отдельные материалы со своими свойствами — номером, весом, габаритами, классом опасности и комплекты материалов с свойствами типа количества материалов, количество упаковки и пр.

Можно это все упаковать в массив, а можно на каждый реальный объект создать по объекту VBA пользовательского класса и связать их в коллекцию объектов с внутренней иерархией, вкладывая объекты и коллекции объектов внутрь объекта, в коде почти буквально повторяя физический процесс - берется накладная, материалы по накладной подбираются в комплекты, кладутся в короба, короба и отдельные материалы кладутся в паллеты. Если вдруг у какого-то материала появятся какие-то свойства, то они легко добавляются без пересчета индексов - просто дописывается свойство. То есть, мы приближаем код к естественному языку и к объективной реальности, выводя его на приемлемый для нас уровень абстракции. Заодно как бонус я получаю поиск по ключу, и не перебираю тысячи раз одни и те же массивы, чтобы найти нужный элемент (их тайно перебирает встроенная функция, от чего скорость не улучшается, но для табличек менее сотни тысяч строк это не очень принципиально).

Создаем класс для накладной Waybill, определяя следующие свойства

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 'коллекция паллет

Выглядит не так коротко, чем просто определить Dim Waybill(17, count) и где-то запомнить или прокомментировать, что первый индекс 1 означает номер накладной, а 2 - получателя, например. Плюс мы имеем самые разные типы данных в одном месте, и эти типы никуда от нас не деваются - один элемент строчный, другой числовой, и все под контролем.

Создаем коллекцию

Set wbls= New Collection

заполняем ее элементами класса Waybill

For i=2 to count

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

Next i

Внутри цикла заполняем элемент коллекции чем-то, например, присваиваем ему свойства какие-то, какие есть у нас в табличке. Например,

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))

Таким образом в коде мы видим, что безликие индексы превращаются во вполне понятные и кое-как человеческие образы, несущие информацию именно для человека - мне не надо сверяться, что у нас в первом\втором и так далее индексе массива нашифровано, заодно, чтобы достать получателя по номеру накладной из массива, не надо шуршать перебором или поиском. Каждой накладной у нас соответствует один объект класса со своими свойствами

Но у нас есть есть еще одна табличка - с описанием паллет, которая с первой табличкой соотнести можно только по номеру накладной. Так как в каждой накладной их там может быть много (десятки и даже сотни), мы создаем для каждого объекта вложенную коллекцию PLTs класса 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 'количество материалов на паллете

аналогично создается коллекция материалов внутри паллета IDHInside.

На практике обращение к этой коллекции выглядит несколько громоздко:

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

Но это с лихвой компенсируется структурированностью. Теперь в массиве (коллекции) можно по номеру накладной, номеру паллета и номеру материала сразу обратиться к количеству коробок с этим материалом на паллете и к количеству рассыпухи на том же паллете. То есть, в результате код нагляден, неплохо читается с нуля без особых замороченных описаний, в него легко добавлять элементы на любом уровне, мы получаем массив, определенным образом структурированный, в том числе и иерархически, и снабженный ключами.

И если нам надо сформировать и распечатать упаковочный лист на накладную, то мы находим накладную по ключу и переносим в нужную форму для печати каждый из паллет перебором коллекции паллет через

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

И если вдруг изменится форма упаковочного, источник данных, добавится какое-то свойство накладной, которое надо выводить (например. реквизиты получателя или физические габариты, или тип транспорта для каждой паллеты), то достаточно легко добавить, не меняя алгоритм и структуру кода. Одним словом, моделирование физических объектов реального мира через пользовательские объекты VBA помогает упростить работу с кодом.

Есть. конечно, ограничения - например, я никогда не пробовал коллекции такого рода для диапазонов более 30 тысяч строк, а потому не знаю, как это будет вести себя на полумиллионной табличке, и не сложится ли весь офис, как турецкие хибары во время землетрясения, но подозреваю, что вопрос о ресурсах обязательно встанет. В некоторых случаях этот огород городить излишне - написать само заполнение коллекций займет больше времени. чем просто сделать проход нескольких циклов по массиву. Однако смысл есть в том, что такой код проще развивать, и к объекту можно цеплять в принципе любые типы данных, в том числе и очень экзотические объекты.

п.4. Актуально ли сие сейчас, когда мелкософты помахали нам ручкой, а VBA вроде как развивать никто не собирается?

Как ни странно, но СВО и уход многих зарубежных компаний спровоцировал временный бум на рынке приложений VBA. Многие компании начали заменять стандартный функционал нефункциональных более программ на костыли в пока еще работающем Экселе. Импортозаместили, разумеется далеко не все нужные и полезные функции систем ERP, например, и в силу того, что корпорации и без этого очень туго дорабатывают свои программные продукты, народ начал массово суетиться и искать разработчиков костылей, которыми можно было бы подпереть рассыпающиеся процессы. В принципе, и до СВО очень много примочек на VBA писалось благодаря негибкости 1Сной коробки, дороговизне и замороченности ее доработки напильником и различными самопальными мастерами. Я бы с уверенностью сказал, что не одно поколение VBA-разработчиков накормил продукт 1С. А после февраля 2022 ошарашенные новыми реалиями люди прямо таки рвались закрывать образовавшиеся дыры доступными им способами.

Многие годами налаженные процессы посыпались - например, у одной компании отвалилась лицензия на инструмент конвертации и передачи xml (там функций было больше, но почему-то конвертацию xml и добавление в него данных заместить не смогли) — закрыли примочкой на VBA. Решение планировалось как временное до конца года — но я в январе уже дописывал обновление по форме, видимо, неповоротливые корпоративные процессы его так и не решили, или примочка им просто дешевле обходится.

В последнее я неплохо верю — российские компании, которые кинулись «импортозамещать», выдают такие цены на разработку, что проще нанять человека, который будет нажимать кнопку макроса на ломаном офисе у себя дома, чем купить их продукт.

Еще один фактор, который хотел бы отметить - за последний год достаточно заметно повысилась и сложность задач для VBA. Если ранее основная масса задач составляла блок достаточно простых аналитических задач или формирования печатных форм, то в 2022 начали спрашивать такое, что тянет на очень полноценную разработку отдельных приложений с блэкджеком и шлюхами. Например, имитировать на VBA WMS. Или автоматически собирать информацию с сайтов, анализировать ее и закидывать обратно через форму, или нафигачить продукт сложной логики на границе с ИИ по формированию пакета каких-то регламентных документов. То есть, пошло то, с чем даже опытный человек справится с огромным трудом, не говоря уже об обычном пользователе.

Вторым моментом, который обильно накормил VBAшников в РФ за последний год стал высокий порог входа в языки макросов импортозамещающих MS Office пакетов. Хотя должно быть в принципе, наоборот — чем больше импортозамещают, тем меньше получают на VBA. Но тем не менее — обнаружив, что импортозамещающие пакеты используют плохо документированную объектную базу, с малоизвестными ЯП (классика — LUA в МойОфис), с убогим IDE и НИКАК не развитой средой (вплоть до того, что спрашивать, как обработать какую‑нибудь картинку на листе приходится спрашивать напрямую разработчиков пакета), многие компании выбирают ряд процессов вести именно на MS Office до тех пор, пока принудительно не отрубят. В таком случае разработка макроса на VBA на порядки дешевле, проще, чем на JS и дешевле, чем ручной обезьяний труд. Если для простейшего макроса на LUA надо еще найти какого‑то умельца, который еще и ценник выкатит, то на VBA умельцев уже достаточно много, и возможностей у них тоже побольше, чем на новых никому не известных продуктах. И поэтому пока мелкософты всем не отрубят руки возможность работать в своих продуктах, будут плакать, колоться, но жрать этот кактус. Потому что он гораздо съедобней того кирпича, который предлагают взамен.

Буквально на днях специалисты из импортозамещающей MS Office компании на вопрос - "раз ваш язык макросов дергает API, то где взять полное описание этого API и куда бежать, если что-то не сработает", промычав маловразумительно, скинули ссылку на сайт, где была статья рассказывающая, как программно расскрасить ячейку, а на вопрос о наличии макрорекордера бодро ответили "нет". Из этого можно вполне уверенно делать вид, что в тех случаях, в которых потребуются макрокостыли в офисных приложениях, цепляются, цеплялись и будут цепляться за майкрософт, потому что более цепляться не за что.

Одним словом, пока пациент скорее жив, чем мертв. Откачать, может, и не получится, но это произойдет не скоро, а потому даже после года "решительного отказа" уходящего и потом возвращающегося Майкрософта актуальность статья вряд ли потеряла.