🎯 Расположение формы в окне Excel

Если при работе в Excel часто приходится обращаться к какой-либо форме, например с кнопками вызова макросов и/или открытия файлов, ее можно отобразить в немодальном окне. А для того, чтобы открытая форма не мешалась перед глазами, ее можно закрепить на постоянное место в окне рабочей книги. 📌

Наиболее подходящим местом для закрепления формы, по моему мнению, является верхний правый угол.

🛠 Как это работает?
🔹 При открытии книги, если активным является "Лист1", форма автоматически загружается и размещается в правом верхнем углу.
🔹 В открытой книге: при выборе листа "Лист1" - форма отображается, при выборе любого другого листа - форма закрывается.
🔹 При изменении размеров окна активной книги открытая форма будет перемещаться вслед за правым верхнем углом.

1️⃣ В стандартный модуль:

Sub UserFormPosition()
    With UserForm1
        'Задаем расположение формы (в правом верхнем углу)
        .Top = ActiveWindow.Top + 40
        .Left = ActiveWindow.Left + ActiveWindow.Width - .Width - 20
    End With
End Sub

🔸 Числовые коэффициенты отодвигают форму от краев окна. Сверху - чтобы не были закрыты кнопки управления окном приложения, а справа - просто так, для лучшего вида.

2️⃣ В модуль листа "Лист1":

'Загружаем форму при активации листа "Лист1"
Private Sub Worksheet_Activate()
    UserForm1.Show 0
    Call UserFormPosition
End Sub

'Выгружаем форму при деактивации листа "Лист1"
Private Sub Worksheet_Deactivate()
    Unload UserForm1
End Sub

3️⃣ В модуль книги:

'Загружаем форму при открытии книги, если нужно
Private Sub Workbook_Open()
    If ActiveSheet.Name = "Лист1" Then
        UserForm1.Show 0
        Call UserFormPosition
    End If
End Sub

'Перемещаем форму при изменении размеров окна Excel
Private Sub Workbook_WindowResize(ByVal Wn As Window)
    Call UserFormPosition
End Sub

💡 Примечания
🔹 При перемещении окна Книги в пределах экрана вручную, форма будет оставаться неподвижной. Чтобы вернуть ее в свой правый верхний угол, достаточно выбрать другой лист и вернуться на "Лист1".
🔹 Проверено в Excel 2016. Я работал с такой формой в Excel 2000, но там устанавливал расположение вручную, выбрав в свойствах формы: StartupPosition = 0 - Manual.

#VBA #ExcelVBA #Форма #Положение #Расположение

✨ Парсинг содержимого тегов с сайта в VBA Excel

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

Шаг 1: Подготовка среды
Перед началом работы нужно подключить необходимые библиотеки:
1. Откройте редактор VBA, нажав Alt + F11.
2. Перейдите в меню Tools → References.
3. Поставьте галочки напротив:
- Microsoft HTML Object Library (для работы с HTML).
- Microsoft XML, v6.0 (для отправки HTTP-запросов).
4. Нажмите OK.

Шаг 2: Код для парсинга
Ниже приведен готовый код, который отправляет запрос к сайту, извлекает содержимое тегов <h2> и записывает его в ячейки Excel:

Sub ParseTags()
    Dim http As Object
    Dim html As New HTMLDocument
    Dim tagElements As Object
    Dim tag As Object
    Dim url As String
    Dim i As Integer
    
    ' Укажите нужный URL страницы
    url = "https://internettovary.ru/avtomoyka-akkumulyatornaya/"
    
    ' Создаем объект для HTTP-запроса
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' Отправляем GET-запрос
    http.Open "GET", url, False
    http.send
    
    ' Проверяем успешность запроса
    If http.Status = 200 Then
        ' Загружаем HTML в объект HTMLDocument
        html.body.innerHTML = http.responseText
        
        ' Находим все теги <h2>
        Set tagElements = html.getElementsByTagName("h2")
        ' Выводим содержимое тегов <h2> в ячейки Excel
        i = 1
        For Each tag In tagElements
            Cells(i, 1).Value = tag.innerText
            i = i + 1
        Next tag
    Else
        MsgBox "Ошибка при получении страницы: " & http.Status
    End If
    
    ' Очистка
    Set http = Nothing
    Set html = Nothing
End Sub

🛠 Как это работает

1. Инициализация:
🔹 Объект http создается для отправки запроса.
🔹 Объект html используется для парсинга HTML-кода.
2. Запрос к странице:
🔹 Указываем URL сайта в переменной url.
🔹 Отправляем GET-запрос с помощью http.send.
3. Парсинг данных:
🔹 Если запрос успешен (код состояния 200), HTML-код загружается в html.body.innerHTML.
🔹 Метод getElementsByTagName("h2") находит все теги <h2> на странице.
4. Вывод в Excel:
🔹 Содержимое каждого тега <h2> (через innerText) записывается в столбец A, начиная с первой строки.
5. Обработка ошибок:
🔹 Если страница не загрузилась, выводится сообщение с кодом ошибки.

💡 Примечания
🔹 Если теги <h2> формируются JavaScript’ом, этот метод не сработает, так как XMLHTTP видит только исходный HTML.
🔹 При проблемах с отображением символов может потребоваться дополнительная перекодировка текста (у меня проблем с кодировкой URL-страницы из примера не было).
🔹 Чтобы спарсить содержимое других тегов, в выражении Set tagElements = html.getElementsByTagName("h2") замените тег "h2" на любой другой, например: "ul", "ol", "p", "a", "span".

Попробуйте этот код, чтобы быстро собрать заголовки <h2> с сайта прямо в Excel! 🚀

#VBA #ExcelVBA #Парсинг #Автоматизация

Пользовательские функции
Дата прописью в Excel
Денежная сумма прописью
Значение параметра функции по умолчанию
Курс валют
Синтаксис пользовательской функции
Функции для работы с буфером обмена
Функции для сортировки массива

#VBA #Excel #Функция #Пользовательская

📃 Список изменений на листе Excel

Допустим, у вас есть таблица или набор данных на листе Excel, которые нельзя редактировать без вашего ведома. 📋 Но как отследить изменения, внесенные случайно или намеренно посторонними? Или нужно вспомнить свои корректировки?

✏️ С помощью кода VBA можно организовать запись внесенных изменений на листе Excel в отдельный скрытый список.

📌 Что для этого нужно?
1️⃣ Создайте новый лист Excel с именем ярлыка - "СписокИзменений".
2️⃣ На листе "СписокИзменений" в ячейки A1, B1 и C1 впишите заголовки столбцов таблицы: Дата и время, Адрес ячейки и Новое значение.
3️⃣ Код процедуры, представленный ниже, разместите в модуле листа, на котором необходимо отслеживать изменения.

📆 Код для отслеживания изменений:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, UsedRange) Is Nothing Then
        Application.ScreenUpdating = False
            Dim n As Integer
            With Worksheets("СписокИзменений")
                'Отображаем лист для новой записи
                .Visible = True
                'Определяем номер первой пустой строки
                n = .Range("A1").CurrentRegion.Rows.Count + 1
                'Записываем дату и время редактирования
                .Cells(n, 1) = Now
                'Записываем адрес измененной ячейки
                .Cells(n, 2) = Target.Address
                'Записываем новое значение измененной ячейки
                .Cells(n, 3) = Target.Value
                'Скрываем лист со списком изменений
                .Visible = False
            End With
        Application.ScreenUpdating = True
    End If
End Sub

🛠 Как это работает:
🔹 Процедура Worksheet_Change отслеживает ручное изменение значений ячеек в любом месте листа, в модуле которого она размещена.
🔹 При обнаружении смены значения ячейки отображается скрытый лист "СписокИзменений", и в новой строке записываются: текущие дата и время, адрес измененной ячейки и ее новое значение.
🔹 После записи новой строки лист "СписокИзменений" скрывается.
🔹 Чтобы никто не заметил отображение скрытого листа "СписокИзменений", применено отключение обновлений экрана (Application.ScreenUpdating = False).

💡 Примечания:
🔸 При отслеживании изменения ячеек на всем листе, из процедуры Worksheet_Change можно убрать условие (If Not Intersect).
🔸 Если вам нужно отслеживать изменения ячеек в определенном диапазоне, а не на всем листе, замените в условии диапазон UsedRange на отслеживаемый диапазон.

#VBA #ExcelVBA #Список #Изменения #Отслеживание

🍰 Создание пользовательского меню

Создание пользовательских меню в Excel — это способ ускорить работу с большим количеством макросов. 🚀 Кнопки для часто используемых процедур будут всегда под рукой, без необходимости захода в редактор VBA.

В этом посте мы рассмотрим создание пользовательского меню для отдельной книги Excel с помощью объекта CommandBars. 🍧

Объект CommandBars позволяет добавлять меню и кнопки к интерфейсу Excel. Вот пример кода, который добавляет меню "Моё меню" с одним пунктом "Запуск макроса".

1️⃣ Процедуры, которые размещаются в стандартном модуле:

'Создание нового меню "Моё меню"
Sub AddCustomMenu()
    Dim cb As CommandBar
    Set cb = Application.CommandBars("Worksheet Menu Bar")
    
    Dim cbc As CommandBarControl
    ' Удаляем существующее меню, если оно есть
    On Error Resume Next
    cb.Controls("Моё меню").Delete
    On Error GoTo 0
    
    ' Добавляем новое меню
    Set cbc = cb.Controls.Add(Type:=msoControlPopup)
    cbc.Caption = "Моё меню"
    
    ' Добавляем пункт меню
    Dim cbb As CommandBarButton
    Set cbb = cbc.Controls.Add(Type:=msoControlButton)
    cbb.Caption = "Запуск макроса"
    cbb.FaceId = 263 'Иконка для пункта меню
    cbb.OnAction = "MyMacro"
End Sub

'Удаление существующего меню "Моё меню"
Sub DeleteCustomMenu()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Моё меню").Delete
    On Error GoTo 0
End Sub

'Макрос, привязанный к пункту меню "Запуск макроса"
Sub MyMacro()
    MsgBox "Кнопка работает, макрос запущен!"
End Sub

2️⃣ Процедуры, которые размещаются в модуле книги:

'Запускает создание меню "Моё меню" при открытии книги
Private Sub Workbook_Open()
    AddCustomMenu
End Sub

'Запускает удаление меню "Моё меню" при закрытии книги
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteCustomMenu
End Sub

💡 Особенности для разных версий Excel
🔸 В версиях до 2007 года пользовательское меню отображается в строке меню.
🔸 В Excel 2007 и новее пользовательское меню появляется на вкладке "Надстройки" (Add-Ins).

#VBA #ExcelVBA #Меню #Создание #CommandBars

Изображение к посту:
🔒 Защита кода VBA паролем

🔒 Защита кода VBA паролем

Стандартный способ защиты кода VBA включает использование встроенных средств Microsoft Office. Процесс прост и состоит из следующих шагов:

1️⃣ Откройте редактор VBA, нажав Alt+F11 или через вкладку "Разработчик" > "Visual Basic".
2️⃣ В меню выберите Tools > VBAProject Properties.
3️⃣ Перейдите на вкладку Protection, поставьте галочку "Lock project for viewing".
4️⃣ Введите пароль в поля "Password" и "Confirm password", нажмите 🆗.
5️⃣ Сохраните файл и перезапустите его. Теперь код будет выполняться, но для просмотра или редактирования потребуется пароль.

💡 Если забудете пароль — восстановить его невозможно! Запишите его и храните в безопасном месте.

⚠️ Ограничения стандартной защиты
🔹 Защита кода VBA встроенными средствами Microsoft Office не является полностью надежной и может быть взломана опытными пользователями. 🔑
🔹 Пароли VBA легко взламываются с помощью специализированных программ.
🔹 Стандартная защита не рекомендуется для критически важных или конфиденциальных данных, так как она не обеспечивает высокий уровень безопасности.

#VBA #ExcelVBA #Пароль #Защита #Проект #Установить

🔑 Снятие пароля с проекта VBA Excel

😕 Как было сказано в предыдущем посте, если забыть пароль, установленный для кода VBA Excel, восстановить его будет невозможно.

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

👌 Ознакомьтесь с пошаговой инструкцией, как можно снять пароль с проекта VBA Excel вручную, начиная с версии 2007 года.

⚔️ Пошаговая инструкция
1️⃣ Сделайте копию книги Excel, чтобы случайно не потерять ее.
2️⃣ Откройте файл .xlsm любым архиватором (7-Zip, WinRAR).
3️⃣ Откройте в архиве папку «xl», найдите файл «vbaProject.bin» и перетащите его с копированием в папку с книгой Excel (можно в любую папку).
4️⃣ Откройте файл «vbaProject.bin» программой «Notepad++», обычный блокнот не подойдет (об этом в первом примечании).
5️⃣ С помощью поиска (Ctrl+F) найдите фразу «DPB=», замените в ней букву «B» на любую другую, например на «z» и сохраните файл.
6️⃣ Перетащите файл «vbaProject.bin» обратно в архив, подтвердив копирование. Архив можно закрыть.
7️⃣ Откройте книгу Excel и на запрос о продолжении загрузки с невалидным ключом «DPz» ответьте «Да».
8️⃣ Откройте редактор VBA, трижды или четырежды нажав «OK» в сообщении о неизвестной ошибке.
9️⃣ Сохраните книгу Excel и закройте.
🔟 Откройте файл Excel, перейдите в редактор VBA, откройте окно «Tools» >> «VBAProject Properties», перейдите на вкладку «Protection» и убедитесь, что галочка у чек-бокса «Lock project for viewing» снята, поля «Password» и «Confirm password» — пустые.

💡 Примечания
🔹 По пункту 3️⃣: не помню, почему я писал, что обычный блокнот не подойдет, — блокнот Windows 11 подходит. Возможно, в предыдущих версиях блокнота были проблемы с кодировкой.
🔹 По пункту 8️⃣: раньше у меня сообщение о неизвестной ошибке появлялось три раза, но после обновления ОС до Windows 11 и переустановки офисных программ (Office LTSC, определяется, как версия 2016) сообщение о неизвестной ошибке стало появляться четыре раза.
🔹 После вышеуказанных обновлений проявился и другой странный эффект, который раньше не наблюдался: после открытия файла .xlsm архиватором, он и другие Excel-файлы перестают открываться программой Excel (правда, не всегда). Все восстанавливается после перезагрузки компьютера.

#VBA #ExcelVBA #Пароль #Защита #Проект #Снять

🔒 Как запретить закрытие файла Excel?

Иногда бывает нужно защитить файл Excel от случайного закрытия, особенно если пользователь не должен его закрывать до завершения определённых действий. Это можно реализовать с помощью обработки события Workbook_BeforeClose.

Для того, чтобы всё-таки закрыть книгу, не прибегая к редактированию кода VBA, предусмотрен ввод пароля.

✅ Пример кода VBA, запрещающего закрывать файл Excel без пароля:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim pass As String
    pass = InputBox("Введите пароль для закрытия файла:")
        If pass <> "123456" Then
            MsgBox "Неверный пароль. Закрытие отменено.", vbCritical
            Cancel = True
        End If
End Sub

🔐 Теперь закрыть файл можно только с правильным паролем.

#VBA #ExcelVBA #Пароль #Закрыть #Книга #Файл

🔏 Дополнение к посту: «Снятие пароля с проекта VBA Excel»

Недавно встретил следующую рекомендацию по редактированию строки «DPB=...»:

5. Найди строку:
DPB=... (иногда DPX или похожее)
6. Замени её на:
DPB="

Решил проверить. Пробовал и так, как в рекомендации (DPB="), и с двумя двойными кавычками (DPB=""), и без кавычек (DPB=).

Результат во всех случаях был один и тот же: ✖️ полностью удален файл vbaProject.bin. ✖️

🚫 Вариант оказался не рабочий.

#VBA #ExcelVBA #Пароль #Защита #Проект #Снять

🧩 Что именно хранится в vbaProject.bin?
✅ Коды всех макросов.
✅ Модули, классы, формы.
✅ Ссылки на библиотеки.
✅ Параметры проекта: структура, защита, различные настройки.

⚠️ Без копии файла с проектом, при таких экспериментах, можно всё потерять.

Зарезервировано для дополнительного меню

#Резерв

Изображение к посту:
🎯 Валидация ввода данных в поля формы VBA Excel

🎯 Валидация ввода данных в поля формы VBA Excel

Когда пользователь заполняет форму в Excel, важно убедиться, что он вводит корректные значения. В примере ниже рассмотрены простые способы валидации введенных данных.

Пользовательская форма содержит:

1️⃣ TextBox1 — ввод имени (обязательное поле, мин. 3 символа)
2️⃣ TextBox2 — ввод возраста (только число)
3️⃣ TextBox3 — ввод Email (обязательное поле, с проверкой формата)
4️⃣ CommandButton1 — кнопка "Отправить"

Введенные данные записываются на новую строку листа "Анкеты".

📌 Пример:

Private Sub CommandButton1_Click()
    'Проверка имени на пустую строку
    If Trim(TextBox1.Value) = "" Then
        MsgBox "Пожалуйста, введите имя.", vbExclamation
        TextBox1.SetFocus
        Exit Sub
    End If
    'Проверка имени на количество символов
    If Len(TextBox1.Value) < 3 Then
        MsgBox "Имя должно содержать не менее 3 символов.", vbInformation
        TextBox1.SetFocus
        Exit Sub
    End If

    'Проверка возраста на число
    If Not IsNumeric(TextBox2.Value) Then
        MsgBox "Возраст должен быть числом.", vbExclamation
        TextBox2.SetFocus
        Exit Sub
    End If

    'Проверка Email на пустую строку
    If Trim(TextBox3.Value) = "" Then
        MsgBox "Пожалуйста, введите email.", vbExclamation
        TextBox3.SetFocus
        Exit Sub
    End If
    'Проверка Email по шаблону
    If Not IsValidEmail(TextBox3.Value) Then
        MsgBox "Неверный формат email.", vbCritical
        TextBox3.SetFocus
        Exit Sub
    End If

    'Отправка данных в таблицу
    Dim ws As Worksheet
    Dim nextRow As Long

    Set ws = ThisWorkbook.Sheets("Анкеты")
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = TextBox1.Value 'Имя
    ws.Cells(nextRow, 2).Value = TextBox2.Value 'Возраст
    ws.Cells(nextRow, 3).Value = TextBox3.Value 'Email

    MsgBox "Данные успешно отправлены!", vbInformation
    Unload Me
End Sub

'Функция для проверки Email с помощью простого паттерна
Private Function IsValidEmail(email As String) As Boolean
    IsValidEmail = email Like "*@*.*" And InStr(email, " ") = 0
End Function

💡 Примечания:
🔹 Возраст можно ограничить интервалом, например: от 10 до 110.
🔹 Вместо простенького шаблона "*@*.*" для проверки Email можно использовать регулярное выражение.

#VBA #ExcelVBA #Валидация #Ввод #Форма

📠 Программное создание модуля и процедуры

Программное создание нового модуля и вставка в него новой процедуры из кода VBA Excel.

📌 Пример кода

Sub AddModuleAndSub()

'Создаем новый модуль
Dim newModule As Object
Set newModule = ThisWorkbook.VBProject.VBComponents.Add(1)

'Записываем новую процедуру
Dim n As Integer
With newModule.CodeModule
    n = .CountOfLines
    .InsertLines n + 1, "Sub NewSub()"
    .InsertLines n + 2, "Dim s As String"
    .InsertLines n + 3, "    s = ""Это новый модуль и новая процедура!"""
    .InsertLines n + 4, "    MsgBox s"
    .InsertLines n + 5, "End Sub"
End With

MsgBox "Новый модуль создан, новая процедура записана!"
End Sub

После записи новой процедуры новый модуль автоматически получит фокус.

#VBA #ExcelVBA #Модуль #Процедура #Создание #Программно

🔐 Генератор паролей на VBA Excel

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

📌 Код генератора паролей

Sub InsertRandomPassword()
    Dim Chars As String
    Dim Password As String
    Dim i As Integer
    Dim Length As Integer

    Length = 16 'Длина пароля, можно изменить
    
    'Набор знаков для генерации пароля
    Chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()-_=+"
    
    'Инициализация генератора случайных чисел
    Randomize

    'Генерация пароля
    For i = 1 To Length
        Password = Password & Mid(Chars, Int(Rnd() * Len(Chars)) + 1, 1)
    Next i
    
    'Записываем пароль в активную ячейку
    ActiveCell.Value = Password
End Sub

💡 Примечания
1️⃣ Если вы хотите, чтобы в случайных паролях чаще встречались символы, удвойте их количество в переменной Chars. Точно также можно увеличить количество цифр.
2️⃣ По желанию, можно удалить из переменной Chars похожие знаки: [O][0] и [l][I][1].

#VBA #ExcelVBA #Генератор #Пароль #Создать

Изображение к посту «Значение параметра функции по умолчанию»

📈 Значение параметра функции по умолчанию

С помощью оператора Optional можно не только объявить параметр функции как необязательный, но и присвоить ему значение по умолчанию.

📌 Пример 1

Function SquareNumbers(Optional d As Double = 1.2) As Double
    SquareNumbers = d * d
End Function

💡 Примечания
🔹 В этой функции, в отличии от функции ниже, можно указать для параметра d тип данных Double (и другие типы данных при необходимости).
🔹 Некоторые версии VBA не поддерживают значения по умолчанию для параметров с Optional, тогда нужно использовать другой подход, как во втором примере.

📌 Пример 2

Function SquareNumbers(Optional d)
    If IsMissing(d) Then d = 1.2
    SquareNumbers = d * d
End Function

💡 Примечание
🔹 Функция IsMissing возвращает True, если опциональный (необязательный) параметр типа Variant не передан при вызове. Поэтому во втором примере оставлено значение типа данных параметра d по умолчанию (Variant).

#VBA #ExcelVBA #Optional #Функция #Параметр #Значение