Главная » Макросы VBA Excel

Макросы VBA Excel — Страница 2

Надстройка: выпадающий список с поиском (комбо)
Скриншот формы ввода (выпадающий список с поиском)

Надстройка для облегчения ввода значений в ячейку Excel

Автор: nerv
Last Update: 27/03/2012

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

А, может, иметь дело с одними теми же, но не структурированными данными?

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

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

Макрос перекодировки (изменения кодировки) текста и файлов

Функции ChangeFileCharset и ChangeTextCharset предназначены для изменения кодировки символов в текстовых файлах и строках.

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

ВНИМАНИЕ: Функции чтения и сохранения текста в файл в заданной кодировке

Список доступных на вашем компьютере кодировок можно найти в реестре Windows в ветке
HKEY_CLASSES_ROOT\MIME\Database\Charset

Среди доступных кодировок есть koi8-r, ascii, utf-7, utf-8, Windows-1250, Windows-1251, Windows-1252, и т.д. и т.п.

Определить исходную и конечную кодировку можно, воспользовавшись онлайн-декодером:
http://www.artlebedev.ru/tools/decoder/advanced/
(после преобразования снизу будет написано, из какой кодировки в какую переведён текст)

Sub ПримерИспользования_ChangeTextCharset()
 
    ИсходнаяСтрока = "бНОПНЯ"
    ' вызываем функцию ChangeTextCharset с указанием кодировок
    ' (меняем кодировку с KOI8-R на Windows-1251)
    ПерекодированнаяСтрока = ChangeTextCharset(ИсходнаяСтрока, "Windows-1251", "KOI8-R")
 
    MsgBox "Результат перекодировки: """ & ПерекодированнаяСтрока & """", _
           vbInformation, "Исходная строка: """ & ИсходнаяСтрока & """"
 
End Sub

Загрузка списка прокси-серверов с проверкой на доступность

Макрос загружает список прокси серверов с сайта free-proxy-list.info
При поиске адресов прокси серверов на странице сайта используются регулярные выражения.

Каждый найденный Proxy Server проверяется на доступность (для последующего использования в других макросах)
Функция проверки доступности прокси-сервера есть в этой статье: http://excelvba.ru/code/ProxyServer

Sub ПримерПроверкиСпискаПроксиСерверовНаДоступность()
    Dim ProxyServers As Collection
    Set ProxyServers = ProxyServersList    ' запрашиваем список прокси серверов
    Debug.Print "Найдено прокси-серверов: " & ProxyServers.Count
 
    ' перебираем все прокси-серверы, проверяя их на доступность
    ' (время отклика менее 2 секунд)
    For Each ProxyServer In ProxyServers
        If CheckProxyServer(ProxyServer) Then
            Debug.Print "Прокси сервер с адресом " & ProxyServer & " доступен!"
        Else
            Debug.Print "Прокси сервер с адресом " & ProxyServer & " недоступен!"
        End If
    Next
End Sub

Код функции ProxyServersList:

Макрос (функция) для получения курса валют с сайта ЦБ РФ

Данный код (пользовательская функция) позволяет получить данные о курсе валюты с сайта Центробанка.

Данную функцию можно использовать и в виде формулы на листе Excel (см. пример во вложении)

Function GetRate(ByVal CurrencyName As String, ByVal RateDate As Date) As Double
...

Sub ПримерИспользованияФункции_GetRate()
    MsgBox "Сегодня курс доллара к рублю составил " & GetRate("USD", Now), vbInformation
    MsgBox "А вчера курс евро к рублю был равен " & GetRate("EUR", Now - 1), vbInformation
End Sub

Поддерживается получение курсов рубля по отношению к наиболее распространённым валютам мира.

Если вы желаете вывести информацию по всем валютам - используйте макрос ВывестиСегодняшниеКурсыВсехВалют

Визуализация работы макроса при помощи прогресс-бара
Прогресс-бар на VBA

Данный прогресс-бар позволяет отображать ход выполнения любого макроса.

Для использования этого индикатора перетащите из файла-примера в свой файл модуль класса ProgressIndicator и форму F_Progress

Использовать прогресс бар сравнительно просто - достаточно добавить в макрос несколько строк кода:

Макрос для снятия защиты листа или книги Excel

Макрос предназначен для программного подбора и снятия защиты с листа Excel.

Подобранный пароль не совпадает с установленным - но, тем не менее, защита снимается.

 

Sub Unlock_Excel_Worksheet()
    t = Timer
    If UnlockSheet(ActiveSheet) Then
        MsgBox "Защита снята. Потребовалось времени: " & Format(Timer - t, "0.0 сек.")
    Else
        MsgBox "Не удалось снять защиту листа", vbCritical
    End If
End Sub

Функция перевода ФИО в дательный падеж

Пользовательская функция (UDF) для перевода ФИО (фамилии, имя, отчества) в дательный падеж.

 

Новые версии функций (изменения от 2019 года) доступны в надстройке FIO + Propis

 

Эту функцию можно использовать как в коде программы, так и как формулу в ячейках листа Excel
(см. пример в прикреплённом файле)

PS: Функцию написал не я - нашел на просторах интернета несколько примеров кода, и переработал код для уменьшения количества ошибок при склонении.
Тестировал склонение на списке разнообразных ФИО (см. первый столбец в прикреплённом файле), и заведомо корректных результатах склонения (третий столбец)

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

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

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

Пример использования:

Sub ПереводФИОвДательныйПадеж()
    ' если фамилия, имя и отчество - в одной переменной (или ячейке)
    FIO$ = "Сидоров Иван Скотиныч"
    ДательныйПадеж$ = DativeCase(FIO$)
    Debug.Print ДательныйПадеж$    ' результат: Сидорову Ивану Скотинычу

    ' если фамилия, имя и отчество - в разных переменных (или ячейках)
    Кому$ = DativeCase("Андреева", "Ольга", "Федоровна")
    Debug.Print Кому$    ' результат: Андреевой Ольге Федоровне
End Sub

Код функции DativeCase (новая версия, от 29 января 2013 года):

Функции для работы с текстовыми файлами

Данные функции предназначены для работы с текстовыми файлами из VBA Excel.

Используя эти функции, вы при помощи одной строки кода сможете записать текст из переменной в файл, или наоборот, загрузить содержимое текстового файла в переменную.
Подразумевается, что текстовые файлы имеют формат ANSI (он же ASCII, он же windows-1251)

ВНИМАНИЕ: См. также функции чтения и сохранения текста в файл в заданной кодировке

Чтение текстового файла в переменную:

Function ReadTXTfile(ByVal filename As String) As String
    Set fso = CreateObject("scripting.filesystemobject")
    Set ts = fso.OpenTextFile(filename, 1, True): ReadTXTfile = ts.ReadAll: ts.Close
    Set ts = Nothing: Set fso = Nothing
End Function
Запись в текстовый файл из переменной: 
Function SaveTXTfile(ByVal filename As String, ByVal txt As String) As Boolean
    On Error Resume Next: Err.Clear
    Set fso = CreateObject("scripting.filesystemobject")
    Set ts = fso.CreateTextFile(filename, True)
    ts.Write txt: ts.Close
    SaveTXTfile = Err = 0
    Set ts = Nothing: Set fso = Nothing
End Function
Добавление в текстовый файл из переменной: 
Function AddIntoTXTfile(ByVal filename As String, ByVal txt As String) As Boolean
    On Error Resume Next: Err.Clear
    Set fso = CreateObject("scripting.filesystemobject")
    Set ts = fso.OpenTextFile(filename, 8, True): ts.Write txt: ts.Close
    Set ts = Nothing: Set fso = Nothing
    AddIntoTXTfile = Err = 0
End Function

Отправка почты макросом VBA без использования почтовых программ

Если вам нужно рассылать письма из Excel,
воспользуйтесь готовым решением в виде надстройки FillDocuments

Функция перевода ФИО в родительный падеж

Пользовательская функция (UDF) для перевода ФИО (фамилии, имя, отчества) в родительный падеж.