Visual Basic

В этом посте я покажу, как с помощью VBA сделать, то, для чего VBA вроде бы как изначально не предназначен – как получить значения нужных переменных из структуры JSON.

Этот пост сделан по мотивам просьбы моего первого и пока единственного подписчика, сделавшего комментарий на предыдущий пост «Макрос получения курсов доллара за период с сайта Банка России»

В чем отличие между сервисом ЦБ России и сайтом worldometers.info? В том, что ЦБ предлагает XML сервис для автоматической загрузки информации (см. http://www.cbr.ru/development/SXML/) – ее неудобно смотреть через веб браузер, но удобно получать с помощью паучьих алгоритмов, а worldometers.info предлагает информацию для людей, а не для пауков.

Поэтому создаваемому на VBA паучку придется постараться, чтобы понять разметку «для людей».

Для работы паука необходимо дополнительно подключить три библиотеки:

1. Microsoft XML parser (MSXML) – тот же, что использовался для получения курсов ЦБ с сайта Банка России.

2. Библиотеку для работы с объектной моделью HTML.

3. Библиотеку для использования возможностей JavaScript из VBA.

Поехали:

1) Запускаем паучка на сайт: https://www.worldometers.info/coronavirus/coronavirus-cases/

Получаем html с сайта:

В полученном html паучку нужно найти и распарсить данные о количестве зарегистрированных случаев из формата JSON. Эти данные представлены вторым аргументом в вызове функции Highcharts.chart(chartName, chartData), которая на сайте рисует график.

В результате выполнения нижепредставленного кода в переменной strJson должна оказаться структура с данными в JSON формате.

Теперь самое интересное – как распарсить эту JSON структуру? Чистый VBA это делать не умеет. Но с JSON прекрасно работает JavaScript. А в VBA есть инструмент для использования возможностей JavaScript для пользователей MS Excel.

Мы можем в VBA получить уже распарсенную JSON переменную:

Проблема в том, что с объектом objJSON ничего нельзя сделать в рамках VBA – у него нет ни свойств, ни методов. Поэтому создаем эти методы на языке JavaScript. Нам нужно вытащить даты (xAxis) и количество (series->data):

Вот что пишем в VBA редакторе:

Загоняем данные в привычные VBA массивы:

Ну и раскатываем эти массивы по рабочему листу:

Вот, что получилось в результате на листе рабочей книги:

По этим данным легко построить график, например, такой:

Если захотите получить готовый файл Excel с рабочим кодом – дайте знать в комментариях.

У меня на компьютере не установлен антивирус, а пароли хранятся в файле. Решил защитить шифрованием. Но бесплатных программ не нашёл. Думаю, уж лучше сделать свою. Хотел попробовать на C#, но среду разработки устанавливать - одна морока)) Если кто хочет этим заниматься, переходите по ссылке. А я - потом.

А пока - на Visual Basic:

Public Sub encrypt(ByVal Pass As String, ByVal InputFile As String, ByVal OutputFile As String, ByVal Crypt As Boolean)
Dim ByteIN As Byte() = IO.File.ReadAllBytes(InputFile), ByteOut(ByteIN.Length - 1) As Byte, PL As Integer = Pass.Length, bPass() As Byte = System.Text.ASCIIEncoding.ASCII.GetBytes(Pass), biPass(bPass.Length - 1) As Integer
bPass.CopyTo(biPass, 0)
If Crypt Then
For i As Integer = 0 To ByteIN.Length - 1
ByteOut(i) = (ByteIN(i) + biPass(i Mod PL)) Mod 256
Next
Else
For i As Integer = 0 To ByteIN.Length - 1
ByteOut(i) = (256 + ByteIN(i) - biPass(i Mod PL)) Mod 256
Next
End If
IO.File.WriteAllBytes(OutputFile, ByteOut)
End Sub
Function B(ByVal S As Char, ByVal Inp As Byte, ByVal Crypt As Boolean) As Byte
If Crypt Then
Return IIf(Inp + Asc(S) > 225, Inp + Asc(S) - 256, Inp + Asc(S))
Else
Return IIf(Inp - Asc(S) < 0, 256 + Inp - Asc(S), Inp - Asc(S))
End If
End Function

Шифрует просто замечательно. Побитно. Я один раз файл pic.png зашифровал в pic2.jpg и потом расшифровал в pic3.bmp. Конвертирует довольно хорошо. А ещё может сделать из exzeshnik.exe exzeshnik.txt и наоборот.

Кстати, после шифрования надо убить входной файл командой Kill(<файл>). В приведённом коде это не предусмотрено, это надо сделать после вызова процедуры encrypt() c необходимыми параметрами. Объясняю, что за параметры:

Pass - код шифрования;

InputFile - входной файл (который будет зашифрован);

OutputFile - выходной файл (путь и имя того файла, который получится, чтобы сохранить его);

Crypt - шифровать или расшифровывать. При значении True InputFile будет зашифрован и результат будет сохранён в OutputFile, а при значении False зашифрованный InputFile будет расшифрован и результат будет сохранён в OutputFile.

Итак, вызываем encrypt() с объяснёнными выше параметрами, убиваем входной файл - и всё. У меня получился такой шифратор:

Кто заинтересовался - посмотрите видос:

Даже не ожидал, что у Visual Basic 2010 есть такие возможности. Там есть метод, который запускает команду Windows для выключения компьютера. Метод такой:

Для выключения: Shell("Shutdown -s -f -t 00")

Для перезагрузки: Shell("Shutdown -r -f -t 00")

А вот для выхода из системы код немного другой. После Public Class Form1 код должен быть такой:

Private Declare Function ExitWindowsEx Lib "user32" (ByVal uFlags As Long, ByVal dwReserved As Long) As Long

Без него нельзя. Но для собственно выхода нужен другой код (этот тоже оставьте). Его писать в том месте, где надо выйти. Вот такой:

ExitWindowsEx(0, 0)

Выглядеть будет так:

Это работает!!!!!! А вот видео:

(Офисной оптимизации пост, точнее, об одном из инструментов этой самой оптимизации)

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

Так вот, многие даже и не подозревают, сколько всего может на нём держаться, - не только российская мировая финансовая система, но и, например, целый каскад программного обеспечения в какой-нибудь организации. Сам я работал айтишником в одной организации и ввиду специфики работы всей компании, довелось разрабатывать ПО (программное обеспечение) на Excel (да, на этих страничках с табличками, где, как я думал ещё в школьные годы, проводятся всякие узкоспециализированные записи, ну кто будет оформлять документ в какой-то неудобной таблице), профи в области Excel себя не считаю, ибо есть куча белых пятен вроде финансовых функций и надстроек в которых всегда найдутся люди, которые разбираются лучше меня. Но это так, отступление.

Сами по себе формулы - очень мощный инструмент, на умении их использовать вывезло столько оптимизаторов, не владеющих программированием, что, думаю, нет еще офиса, где какой-нибудь местный Кулибин в обеденный перерыв не замутил еще какую-нибудь узкоспециализированную считалку для отдела. Но, работая с большими массивами возникает ряд проблем в использовании формул: относительность (ты получаешь не строку информации в базе данных, а динамичный результат вычисления) и оптимизация. Да, оптимизация на нескольких тысячах строк с десятком колонок и, допустим, парой связанных таблиц, это беда. Такая связка на i3 4гб оперативы просто будет повергать бедный офисный комп в ужас, заставляя его терять сознание при каждом пересчете и вылетать, если ты нарушил священный ритуал пятиминутного сохранения (знал я одного мужика, у него была такая формульная портянка, что сохранял базу он лишь два раза в день, перед обедом и уходя домой, ибо на сохранение уходило минут 20, ненавидели мы его все, ибо, уходя в отпуск, он оставлял это чудище кому-то из нас). Специфика работы была такова, что интернета у нас не было, а стороннее ПО нельзя - пользуйтесь чем дали. Окей, но автоматика же нужна, без неё никуда, потому пришлось использовать то, что есть и открывается это:

Разработчикам показалось мало создать мощный инструмент формул, они решили впаять в и так могущественное ПО целый язык программирования (точнее его диалект) VBA (Visual Basic for Application), возможность подключать модули с других языков, использовать API (хоть и работает это ИМХО через жопу) и встроенную среду разработки (а это означает, что вообще ничего не надо качать, если у Вас есть офис, значит все что нужно, чтобы стать мамкиным программистом уже есть). Но что нам это даёт? Огромные (ну это как посмотреть) возможности для разработки ПО, преимущественно узкоспециализированного, без использования чего-либо кроме Excel; базы данных? О чём речь, Excel - это и есть БД (то ещё извращение, но для утонченных можно связать с Access или Sql), возможность проводить расчёты (циклы, тонкие переборы, фильтрация) над большими массивами информации в кустарных условиях, использовать встроенные библиотеки для работы с другими приложениями (самое важное - MS Word), возможность наконец применить Visual Basic, который ты учил лет 20 назад, а он нигде так и не пригодился, ну и самое главное, научиться основам программирования, если ты что-то шаришь, но твои лучшие успехи - верстка шаблонов сайтов на HTML, CSS с вкраплениями PHP. Также это нам даёт возможность кодить на ведре (прям совсем ведре-ведре). VB хоть и относится к ООП, но де-факто работа в нём редко сводится к пользовательским классам, всяким тонкостям и т.д., в основном он ощущается как скриптовый язык, работа приходит к чему:

накидать разных элементов управления (это если у нас пользовательская форма, если просто код, то вообще просто запихать всё в модуль) и привязать к ним обработчики событий. Вкурил в циклы, условные конструкции, чутка простого синтаксиса и на рабочий телефон уже звонят из Майкрософт и предлагают тебе перейти к ним (Нет). Но не буду вдаваться в разработку ведь не об этом пост, что из этого можно получить на практике? Рабочие программы с базами данных и пользовательским интерфейсом (чтобы упростить и ограничить взаимодействие пользователя с данными) или скрипты для обработки целой тонны упорядоченной (а может и нет, слава условным конструкциям) информации. Например (набросал за минуту, палками не бить):

Немного возни и у нас есть пользовательская форма со списком сотрудников и полями, которые надо заполнить, на выходе по нажатию кнопки мы получаем готовую вордовскую справку. Для этого нужен лист с данными сотрудников, лист список справок и шаблон MS Word. Готово, вы бог офисной оптимизации. И так можно многое: отчёты, справки, товарные чеки, письма, документы разные, приказы целые, любые действия с информацией.

Сразу предупрежу всех, кто уже нацелился писать "на кой хрен ты раскопал этот старый кусок говна на заре 30го десятилетия 21го века". Пост чисто информативный, это не гайд, не самоучитель, может кому интересно, на прорыв в IT сфере ни разу не претендует, это раз, есть в нашей стране места, где развитие этой сферы отстаёт как раз на эти 20 лет, это два, ну и просто, может кому понадобится, может кто-то захочет на работе чему-то подучиться.
Какие минусы? Оптимизация всё равно сосёт бибу (но не такую, как формулы), безопасность тоже, чисто теоретически можно использовать криптографическое шифрование БД и расшифрование в ходе работы, но я не проверял, можно ли легко вскрыть защиту самого VBA проекта, да и оптимизация пососёт ещё большую бибу (да и вообще, кому это надо, ребят, это же Excel), ну а стандартную Excel защиту листов можно вскрыть обычным архивом и блокнотом. Также, недостатком я считаю ряд ограничений среды, по типу того, что без API не работает прокрутка колёсиком мыши, стабильность - excel иногда любит чудить. Совместимость - отдельные танцы с бубном для x64 и x32 (но это если используете сторонние API, модули). Ну и объяснять людям, как разрешить запуск макросов :D.
Спасибо, если дочитал этот длинный (и наверное скучный) пост до конца, если вдруг кому стало интересно могу написать ещё много чего, например, как написать сапёр на Excel, как сделать различные простенькие, но очень нужные офисные программки, как научиться этому (но.. зачем?), как использовать макрорекордер и много чего ещё, связанного с Excel. А ведь это всё ещё цветочки, есть люди, которые целые стратегические пошаговые игры в ячейках писали на том же VBA.

P.S. Если ты профи, знаешь больше и лучше меня, и видишь, что я в чём-то неправ - поправь, буду рад.