Макросы и программы VBA | Просмотров: 12914 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 03.06.2017 | Комментарии (1)
В рамках практического изучения ООП написал набор классов, реализующих построение древовидной структуры любого уровня сложности для VBA. Как вы знаете, сам VBA кроме массивов и коллекций (объект Collection) в готовом виде ничего более не имеет. Пределом мечтаний на данный момент является внешний компонент Dictionary из библиотеки Microsoft Scripting Runtime.
В виду этакой скудности приходится городить конструкты типа Dictionary с элементами в виде других Dictionary, либо изобретать свои классы. Чем я и занялся. Теперь, если вам потребуется выстроить дерево, то вы можете воспользоваться моим готовым решением.
Макросы и программы VBA | Просмотров: 10431 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 07.05.2017 | Комментарии (2)
В статье рассказывается, как преодолеть противоречие между видом таблицы, оптимизированной для ввода данных, и форматом таблицы, который удобен для хранения и извлечения данных.
VBA для начинающих — Первые шаги — Новый курс VBA (2)
Макросы и программы VBA | Просмотров: 9069 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 13.09.2015 | Комментарии (18)
Если вы программируете на VBA/VBS, то рано или поздно вынуждены будете познакомиться с объектом Dictionary. Если в двух словах, то Dictionary — это продвинутый массив.
Макросы и программы VBA | Просмотров: 97281 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 20.07.2015 | Комментарии (65)
Поискав по рунету материал на тему обработки ошибок в VBA, не увидал на первых двух страницах результатов поиска чего-то, что мне понравилось. Может плохо смотрел, но решил написать на эту тему свою статью.
Макросы и программы VBA | Просмотров: 66847 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 18.05.2015 | Комментарии (4)
Поговорим про то, как в VBA обращаться к листам книги Excel.
Макросы и программы VBA | Просмотров: 15728 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 27.04.2015 | Комментарии (10)
Продолжаем разбираться с интереснейшим объектом Range
Макросы и программы VBA | Просмотров: 37790 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 06.04.2015 | Комментарии (3)
Небольшой, но весьма полезный макрос для автоматизации создания оглавления с гиперссылками для книг Excel, которые содержат большое количество листов. Те, кто пробовал работать с такими книгами, прекрасно знают, как напрягает и бесит долгая возня с мышкой для перехода из одного листа в другой. На помощь приходит замечательная способность Excel создавать гиперссылки на ячейки, расположенные на других листах.
Макросы и программы VBA | Просмотров: 5340 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 30.01.2015 | Комментарии (1)
Макросы и программы VBA | Просмотров: 23105 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 30.01.2015 | Комментарии (0)
Всё про массивы в VBA: от самого начала до весьма тонких нюансов.
Макросы и программы VBA | Просмотров: 174065 | Author: Батьянов Денис | Добавил: dsb75 | Дата: 06.01.2015 | Комментарии (10)
Программирование макросов на VBA в Excel
Источник: perfect-excel.ru
Запуск и создание макросов в Excel для чайников
VBA коды для простых программ макросов необходимых при работе с таблицами на каждый день.
Создание и выполнение макросов
Макрос для копирования листа в Excel c любым количеством копий.
Практический пример с описанием и исходным кодом макроса для одновременного копирования любого количества рабочих листов.
Макрос для выделения и удаления пустых столбцов в Excel.
Выделение, удаление, скрытие и добавление пустых столбцов в таблицу по условию пользователя с помощью макросов. Исходные коды предоставляться с описанием и примерами в картинках.
Макрос для выделения и удаления пустых строк в Excel.
Примеры макросов с исходными кодами для удаления, выделения, добавления и скрытия пустых строк в исходной таблице данных.
Макрос для выделения ячеек со снятой защитой на листе Excel.
Исходный код с примером и описанием для VBA-макроса выделения ячеек у которых, снятая защита от ввода значений данных и редактирования.
Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием.
Исходный VBA-код макроса для поиска выпадающих списков на рабочем листе. Исходный код VBA-макроса для поиска ячеек с условным форматированием.
Макрос для выделения ячеек Excel по условию больше меньше.
2 исходных кода макросов для выделения ячеек по условию больше равно или меньше равно относительно их числовых значений.
Макрос для выборочного выделения ячеек на листе Excel.
Исходный код макроса для автоматического выделения отдельных ячеек по заданному условию в критериях поиска значений. Как быстро выделять несмежные диапазоны макросом?
Макрос для добавления строк с заданной высотой в таблицу Excel.
Пошаговое руководство по написанию кода макроса VBA для умной вставки пустых строк между ячейками таблицы. Как автоматически вставить строки через одну ячейку?
Как сделать границы ячеек макросом в таблице Excel.
Как автоматически рисовать границы в таблицах с помощью макроса? Автоматизированное форматирование границ ячеек по разным цветам, стилям и толщине линий используя макрос.
Макрос для объединения повторяющихся ячеек в таблице Excel.
Практический пример и пошаговый разбор исходного VBA-кода макроса для автоматического объединения повторяющихся одинаковых ячеек в строках таблицы.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Карта сайта
- Скачать примеры
Источник: exceltable.com
Авторизация в Excel на VBA
Что же представляет из себя авторизация в Excel? Это форма, то есть окно, с запросом ввода логина и пароля, при успешном вводе которых, пользователю будут открываться различные листы документа, в зависимости от группы доступа пользователя. Точно также, помимо открытия листов, можно будет выставить ограничения на действия в Excel: запрет форматирования ячеек, удаление строк, столбцов, использование фильтров, объектов, сценариев и так далее. Но обо всем по порядку.
Авторизация в Excel: основной алгоритм работы
Для начала, необходимо разработать алгоритм, по которому мы будем работать, и выглядеть он будет примерно так:
Итак, поехали!
Авторизация в Excel: макет документа
Наш рабочий документ будет состоять из четырех листов:
- Лист с приветствием — единственный лист, который будет отображаться всем пользователям до авторизации
- Лист с дашбордом (визуализированным отчетом) — графики, диаграммы/гистограммы — изначально со свойством VeryHidden*
- Лист с данными — источник расчетов для дашборда — изначально со свойством VeryHidden
- Служебный/технический лист — для хранения логинов, паролей и служебной и вспомогательной информации — также, со свойством VeryHidden
*VeryHidden— свойство листа, при котором сам лист скрыт и включить его отображение можно только через использование режима разработчика. Для выбора данного свойства, необходимо в Excel зайти в меню «Разработчик» — «Visual Basic». Далее, необходимо выбрать нужный нам лист и в его свойствах («Properties») найти свойство Visible и установить в «2 — xlSheetVeryHidden».
После этого лист станет «очень скрытым». Отменить свойство VeryHidden можно точно так же через режим разработчика, либо используя необходимый нам макрос, о котором я напишу чуть дальше.
Авторизация в Excel: группы доступа
После создания макета документа, нам необходимо разработать несколько групп доступа и распределить и ограничить для них права. Предположим, что таких групп будет три:
- Администраторы (Admin): доступны все листы , в том числе служебный, а также отсутствуют какие-либо ограничения.
- Руководители и ответственные за данные (Head): доступны 2 рабочих листа + стоит пароль на изменение структуры книги.
- Рядовые сотрудники компании (Worker): доступен только 1 рабочий лист с дашбордом + стоит пароль на изменение структуры книги.
Авторизация в Excel: разработка макета формы
С этим пунктом не должно возникнуть никаких проблем. Элементов на форме авторизации должно быть не так уж и много:
- Поле ввода для логина
- Поле ввода для пароля
- Кнопка «Авторизация» (проверка логина и пароля)
- Различные подписи на форме
- По желанию: кнопка закрытия формы, кнопка восстановления пароля, логотипы, справка и все, что душе угодно
Если быстро набросать элементы, которые мы перечислили, должно получиться что-то вроде этого:
После добавления элементов, поменял их стандартные названия:
«UserForm1» переименовал в «Authorization».
«TextBox1» переименовал в «TextBox_Login».
«TextBox2» переименовал в «TextBox_Pass».
Также, в свойствах «TextBox_Pass», мы находим свойство «PasswordChar» и вводим любой символ, который мы хотим видеть вместо вводимых символов пароля — чаще всего используется символ «*».
Ничего лишнего, пока что все просто. Переходим далее.
Подготовка служебного листа
В моем примере, служебный лист будет содержать список логинов, паролей, а также соответствующую логину группу доступа. Чтобы немного «усложнить» потенциальный взлом (хотя надо понимать, что взломать Excel продвинутому пользователю не составит никакого труда), пароли на листе мы будем хранить в захешированном виде. Подробнее о хешировании можно прочитать на Википедии, но в вкратце — это преобразование данных в строку фиксированной длины, состоящей из цифр и букв с помощью определенных математических алгоритмов. О том, как мы будем хешировать пароли в Excel, я расскажу далее.
Итак, для начала, содержимое служебного листа будет выглядеть вот так:
Авторизация в Excel: особенности и написание программного кода на VBA
Данные пункты тесно взаимосвязаны между собой, поэтому я их объединил в один большой. Здесь мы будем писать программный код для всего нашего файла: как для формы, так и для некоторых событий.
Для начала, мы напишем код, который будет отображать различные листы в зависимости от роли пользователя после авторизации. Макрос user_group мы делаем приватным и вписывать его будем не в отдельный модуль, а в нашу готовую форму Authorization. Аргументом для макроса является переменная X, которая будет содержать название группы доступа в виде текстовой строки String:
Private Sub user_group(ByVal X As String) Dim Sht As Worksheet ‘создаем переменную типа Рабочий Лист ActiveWorkbook.Unprotect «112» ‘Снимаем защиту структуры книги паролем For Each Sht In ThisWorkbook.Sheets ‘перебираем все листы книги If (X = «Admin») Then Sht.Visible = -xlSheetVisible ‘если роль Admin — каждый лист будет видимый If (X = «Head») And (Sht.Name <> «Settings») Then Sht.Visible = -xlSheetVisible ‘если роль Head — видны все, кроме Settings If (X = «Worker») And (Sht.Name = «Dashboard») Then Sht.Visible = -xlSheetVisible ‘если роль Worker — виден только Dashboard Next Sht ‘если роль либо Head, либо Worker — ставим защиту на структуру книги с паролем «112» If (X = «Head») Or (X = «Worker») Then ActiveWorkbook.Protect Password:=»112″, Structure:=True, Windows:=False End Sub
Хочу обратить внимание на то, что у нашего файла будет стоять «защита структуры книги» с паролем «112», т.е. пользователь не сможет создавать, удалять и переименовывать листы нашего документа. И, чтобы изменить видимость листов, необходимо сначала снять эту защиту программно, а затем, в зависимости от роли пользователя, поставить обратно, что мы и делаем в нашем коде.
Далее, код для кнопки «Авторизация». При нажатии на данную кнопку, запускается несколько проверок:
- Проверка полей логина и пароля заполнение: если одно из них не заполнено — выводится уведомление, выполнение макроса прекращается.
- Проверка переменной Check: данная переменная хранит значение «некорректных» попыток входа. Ее необходимо объявить в основном модуле книги: Public check As Integer. Если данная переменная больше 3х — доступ к авторизации в текущей сессии блокируется.
- Поиск и проверка логина на наличие на листе «Settings». Если логин найден — введенный пароль хешируется и сравнивается с хранимым хешем на соответствующей строке логина:
- Если все корректно — запускается макрос User_Group, который открывает доступ к листам в зависимости от группы доступа, соответствующей логину.
- Если данные некорректные — пользователь предупреждается об этом, а также наращивается счетчик некорректных попыток авторизации.
Private Sub CommandButton1_Click() If (TextBox_Login = «») Or (TextBox_Pass = «») Then ‘предупреждение на заполнение полей логина/пароля MsgBox «Не введен логин или пароль!», vbInformation + vbOKOnly, «Внимание!» Exit Sub End If If (check > 3) Then ‘проверка на количество паролей, введенных некорректно MsgBox «Вы ввели неверный пароль больше трех раз. Доступ к файлу заблокирован!», vbCritical + vbOKOnly, «Внимание» Exit Sub End If LastRow = Sheets(«Settings»).Cells(Rows.Count, 1).End(xlUp).Row ‘проверка списка логинов For i = 2 To LastRow If TextBox_Login = Sheets(«Settings»).Cells(i, 1) Then ‘если логин найден If Sheets(«Settings»).Cells(i, 2) = GetHash(TextBox_Pass.Value) Then ‘пароль хешируется и сравнивается с хранимым хешем user_group Sheets(«Settings»).Cells(i, 3).Value ‘и если все ок — запускается макрос разграничения групп доступа Unload Authorization Exit Sub Else ‘в противном случае — уведомление о неправильном пароле MsgBox «Неверный пароль», vbCritical + vbOKOnly, «Внимание!» check = check + 1 ‘также наращивается счетчик неправильных паролей Exit Sub End If End If Next i ‘в оставшемся случае — уведомления о несуществующем логине. MsgBox «Пользователя с данным логином не существует.», vbInformation + vbOKOnly, «Внимание!» End Sub
В макросе CommandButton1_Click используется функция GetHash (строка 15), которая преобразует входящие данные в хеш. Данная функция взята с сайта ExcelVba.ru, за что выражаю им огромную благодарность. Код необходимо вставить в главный модуль книги:
Function GetHash(ByVal txt$) As String Dim oUTF8, oMD5, abyt, i, hi, chHi$, chLo$ Set oUTF8 = CreateObject(«System.Text.UTF8Encoding») Set oMD5 = CreateObject(«System.Security.Cryptography.MD5CryptoServiceProvider») abyt = oMD5.ComputeHash_2(oUTF8.GetBytes_4(txt$)) For i = 1 To LenB(abyt) k = AscB(MidB(abyt, i, 1)) lo = k Mod 16: hi = (k — lo) / 16 If hi > 9 Then chHi = Chr(Asc(«a») + hi — 10) Else chHi = Chr(Asc(«0») + hi) If lo > 9 Then chLo = Chr(Asc(«a») + lo — 10) Else chLo = Chr(Asc(«0») + lo) GetHash = GetHash chLo Next Set oUTF8 = Nothing: Set oMD5 = Nothing End Function
Основной код написан, теперь переходим к более мелким.
Макрос, который запускает форму авторизации (можно установить на кнопку на главном листе «Main»):
Sub Authorization_Start() Authorization.Show End Sub
Также, макрос можно использовать при событии «Open» — при открытии книги:
Private Sub Workbook_Open() Authorization_Start End Sub
Стоит отметить, что у большинства пользователей запуск макросов по умолчанию отключен, именно поэтому на главном листе Main необходимо сделать что-то вроде «памятки» со справочной информацией о том, как активировать работу макросов.
Макрос для закрытия книги.
При закрытии книги, нам нужно снять защиту со структуры книги, затем скрыть все листы (кроме листа «Main»), а затем снова вернуть защиту структуры книги с паролем «112» (можно установить на какую-либо кнопку):
Sub close_book() Dim Sht As Worksheet ActiveWorkbook.Unprotect «112» For Each Sht In ThisWorkbook.Sheets If Sht.Name <> «Main» Then Sht.Visible = xlSheetVeryHidden Next Sht ActiveWorkbook.Protect Password:=»112″, Structure:=True, Windows:=False End Sub
Да, данный макрос можно использовать при событии «BeforeClose» — перед закрытием книги. Однако, может возникнуть несколько проблем:
-
Если пользователь сохраняется вручную до закрытия документа, закрывает документ, а затем при автоматическом запросе о сохранении нажимает «не сохранять». В таком случае, при новом запуске документа, скрытые листы будут видны всем. Да, вероятность такого события крайне мала, но она все-таки есть.
Private Sub Workbook_BeforeClose(Cancel As Boolean) close_book End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False close_book ThisWorkbook.Save End Sub
Поэтому, чтобы избежать такой ситуации, я хочу предложить более радикальный, менее удобный вариант, который, однако, сведет вышеперечисленные потенциальные проблемы к минимуму: помимо выполнения макроса close_book перед каждым закрытием книги с помощью события «BeforeClose» (как на первом проблемном варианте), мы будем выполнять его и перед каждым ручным сохранением файла с помощью события BeforeSave:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) close_book End Sub
Осталось только сделать памятку по включению макросов на главном листе документа, а также выполнить важный шаг, без которого все наши проделанные действия не имеют смысла — поставить пароль на проект VBA: Меню «Разработчик» — «Visual Basic» — «Tools» — «VBAProject — Project Properties» — Вкладка «Protection» — поставить галочку «Lock project for viewing» и ввести пароль и нажать «Ок».
Также, по желанию, мы можем сделать отдельную форму для администраторов, для добавления новых пользователей на лист «Settings», либо форму для восстановления пароля по какому-либо ключевому слову, но в данном примере такие функции мы реализовывать не будем.
Источник: shtem.ru