Изображение к посту "Курс валют"

💰 Курс валют

Проверил старую функцию на VBA Excel, возвращающую курс выбранной валюты в рублях. Оказалось, что она еще работает. 🚜

📌 Код функции

Function КурсВалют(Optional КодВалюты As String, Optional Дата As Date) As Double
    Dim myXml As Object, myDate As String
    Set myXml = CreateObject("msxml2.DOMDocument")
    If Not CBool(Len(КодВалюты)) Then КодВалюты = "USD"
    myDate = "?date_req=" & IIf(Дата, Дата, Date)
    myXml.async = 0: myXml.Load ("http://www.cbr.ru/scripts/XML_daily.asp" & myDate)
        With myXml.SelectSingleNode("*/Valute[CharCode='" & UCase(КодВалюты) & "']")
            КурсВалют = CDbl(.ChildNodes(4).Text) / Val(.ChildNodes(2).Text)
        End With
    Set myXml = Nothing
End Function

💡 Примечания
🔹 Параметры функции (КодВалюты и Дата) являются необязательными.
🔹 Если опустить параметр КодВалюты, будет применен код доллара.
🔹 Если опустить параметр Дата, будет применена текущая дата.
🔹 Если опустить оба параметра, будет возвращен курс доллара на текущую дату.

#VBA #ExcelVBA #КурсРубля #КурсДоллара #КурсЕвро #КурсЮаня

🖥 Вывод списка приложений

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

Код процедуры достаточно длинный 📋, и Телеграм не дает возможности опубликовать его в тексте поста ✖️. Но вы можете скопировать его с сайта «Время не ждёт» по этой ссылке.

#VBA #ExcelVBA #СписокПриложений #СписокПрограмм

📝 Запись значений диапазона в обычную переменную

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

📌 Пример кода

Sub Primer()
    Dim rng As Range, r&, c&, ar, i1&, i2&, txt$

    Set rng = Worksheets("Лист4").Range("A1:F40")

    With rng
        r = .Rows.Count
        c = .Columns.Count
        ar = .Value
    End With
    
    For i1 = 1 To r
        For i2 = 1 To c
            txt = txt & ar(i1, i2)
                If i2 = c Then
                    txt = txt & vbNewLine
                Else
                    txt = txt & "; "
                End If
        Next
    Next
       
    Debug.Print txt
End Sub

Отображение на экране будет построчным, как в исходном диапазоне. Элементы диапазона будут разделены друг от друга точкой с запятой и пробелом.

#VBA #ExcelVBA #Диапазон #Значение #Переменная #DebugPrint

📋 Выбор диапазона из таблицы

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

🛠 Функция для выбора диапазона

Function ВыборДиапазона(ИмяЛиста$, НачальнаяДата$, КонечнаяДата$) As Range
    Dim МассивДанных As Variant, i&, n1&, n2&, rws&, cls&
    
    'Отлавливаем всевозможные ошибки
    On Error GoTo ПриОшибке
 
    'Проверяем, чтобы начальная дата не превышала конечную
    If CDate(НачальнаяДата) > CDate(КонечнаяДата) Then
        MsgBox "Интервал дат задан неверно!"
        Exit Function
    End If
    
    With Worksheets(ИмяЛиста).Range("A1").CurrentRegion
        rws = .Rows.Count
        cls = .Columns.Count
        МассивДанных = .Value
    End With
    
    'Поиск начальной даты
    For i = 1 To rws
        If МассивДанных(i, 1) = НачальнаяДата Then
            n1 = i
            Exit For
        End If
    Next
    
    'Поиск конечной даты
    For i = rws To 1 Step -1
        If МассивДанных(i, 1) = КонечнаяДата Then
            n2 = i
            Exit For
        End If
    Next
    
    'Проверяем, что границы диапазона определены
    If n1 = 0 Or n2 = 0 Then
        MsgBox "Границы диапазона (или одна из границ) заданы неверно!"
        Exit Function
    End If
    
    Set ВыборДиапазона = Worksheets(ИмяЛиста).Range(Cells(n1, 1), Cells(n2, cls))
    Exit Function
    
ПриОшибке:
    MsgBox "Ошибка: " & Err.Description
End Function

💫 Пример выбора диапазона
Пример использования функции для выбора диапазона с использованием формы для ручного или автоматического заполнения интервала дат:

Private Sub CommandButton6_Click()
    Dim rng As Range
    
    'Отлавливаем ошибки
    On Error GoTo ПриОшибке
 
    Set rng = ВыборДиапазона("Лист4", TextBox1.Text, TextBox2.Text)
    
    If rng Is Nothing Then
        MsgBox "Диапазон не выбран!"
        Exit Sub
    End If
    
    'Строки кода для первоначального тестирования
    Dim r&, c&, ar, i1&, i2&, txt$
 
    With rng
        r = .Rows.Count
        c = .Columns.Count
        ar = .Value
    End With
    
    For i1 = 1 To r
        For i2 = 1 To c
            txt = txt & ar(i1, i2)
                If i2 = c Then
                    txt = txt & vbNewLine
                Else
                    txt = txt & "; "
                End If
        Next
    Next
    
    Debug.Print txt
    Exit Sub
    
ПриОшибке:
    MsgBox "Ошибка: " & Err.Description
End Sub

💡 Примечания
1️⃣ Формат дат, загружаемых в функцию, должен соответствовать формату дат в наборе данных.
2️⃣ Код VBA Excel для первоначального тестирования отображает на экране (в окне Immediate) содержимое ячеек выбранного диапазона. Отображаются значения построчно, как в исходном диапазоне, с разделением друг от друга точкой с запятой и пробелом.
3️⃣ Обратите внимание, что в окне Immediate умещается ограниченное количество знаков — если диапазон большой, все значения могут не поместиться.
4️⃣ После удачного тестирования, строки кода для первоначального тестирования заменяются на строки кода для обработки выбранного диапазона.

#VBA #ExcelVBA #Выбор #Диапазон #Таблица

Необязательные параметры в VBA Excel

Оказывается, для необязательных параметров процедур в VBA и VB есть определенные правила, сформулированные разработчиками:

1️⃣ Для каждого необязательного параметра в определении процедуры необходимо указать значение по умолчанию.
2️⃣ Значение по умолчанию для необязательного параметра должно быть константой или выражением константы.
3️⃣ В определении процедуры сначала указываются обязательные параметры, а затем — необязательные. Параметр, следующий за необязательным параметром, должен быть необязательным.

Пример указания необязательных параметров:

Sub Primer(Parametr1, Parametr2, Optional Parametr3, Optional Parametr4)
'    Код процедуры
End Sub

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

🔹 Указание значения по умолчанию в определении функции:

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

🔹 Указание значения по умолчанию с помощью функции IsMissing:

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

Функция IsMissing работает только с типом данных Variant.

#VBA #ExcelVBA #Optional #IsMissing #Параметры #Необязательные

Номер последней заполненной строки
Два способа найти номер последней заполненной строки любой таблицы на листе Excel. Для поиска последней строки будем отталкиваться от ее верхней левой ячейки: Cells(a, b). Соответственно, если верхняя левая ячейка A1, тогда Cells(1, 1).

1️⃣ Первый способ
Основная формула для поиска номера последней строки (PosStr) в любой таблице, не требующая соблюдения каких-либо условий:

PosStr = Cells(a, b).CurrentRegion.Cells(Cells(a, b).CurrentRegion.Cells.Count).Row

2️⃣ Второй способ
Дополнительная формула с условием, что в первом столбце таблицы нет пустых ячеек:

PosStr = Cells(a, b).End(xlDown).Row

#VBA #ExcelVBA #Номер #Строка #Таблица #Последняя

🔥 Вставка популярных символов в ячейки Excel

Быстрая вставка популярных символов в ячейки Excel (а также в Word, Блокнот) осуществляется с помощью левой клавиши Alt + [код символа]. ✨

Код символа набирается на цифровом блоке клавиатуры ⌨️ (в правой ее части). Применение сочетания клавиш удобнее, чем вызов Таблицы символов.

Мне чаще всего приходится использовать Alt-коды для ввода кавычек-ёлочек (кроме Word, конечно), знаков градуса и рубля:
Alt + [0171] → [«];
Alt + [0187] → [»];
Alt + [0176] → [°];
Правый Alt + [8] → [₽] (здесь 8 нажимается в строке над буквенным блоком клавиатуры).

Alt-коды других популярных символов в таблице ниже.

#VBA #Excel #Символ #Вставка #Alt #Код

Ещё больше Alt-кодов я опубликовал на своём сайте в статье Таблица символов и Alt-коды.

📋 Элемент управления ListView
ListView (вид списка, представление списка) — элемент управления, который позволяет отображать данные в виде значков, списка или таблицы внутри формы VBA Excel. Чаще всего ListView используется для отображения данных в виде таблицы - режим Отчет (lvwReport).

🎈 Добавление ListView на UserForm
1️⃣ Откройте редактор VBA (Alt + F11).
2️⃣ В редакторе VBA выберите Tools → References.
3️⃣ Найдите и активируйте библиотеку Microsoft Windows Common Controls, если она ещё не активирована.
4️⃣ Создайте новую форму (Insert → UserForm).
5️⃣ Если панели элементов управления не видно, откройте ее (View → Toolbox).
6️⃣ Щелкните правой кнопкой мыши на панели элементов управления и выберите «Additional Controls…» (Дополнительные элементы управления…).
7️⃣ Найдите «Microsoft ListView Control» и добавьте его на панель.
8️⃣ Перетащите элемент ListView с панели элементов на вашу UserForm.

📌 Пример заполнения ListView
Пример отображения списка сотрудников с их ФИО, должностью и отделом:

Private Sub UserForm_Initialize()
    ' Настройка формы
    With Me
        .Caption = "Список сотрудников"
        .Width = 300
        .Height = 160
    End With
 
    With Me.ListView1
        ' Настройка режима отображения
        .View = lvwReport
        .Gridlines = True
        .FullRowSelect = True
        .Left = Me.Left
        .Top = Me.Top
        .Width = Me.Width ' По ширине разворачиваем на всю форму
        .Height = Me.Height - 70 ' Внизу оставляем место для кнопок
        
        ' Добавляем колонки с заголовками и задаем ширину
        .ColumnHeaders.Add , , "ID", 30 ' Заголовок 1, но столбец 0
        .ColumnHeaders.Add , , "ФИО", 80 ' Заголовок 2, но столбец 1
        .ColumnHeaders.Add , , "Должность", 80 ' Заголовок 3, но столбец 2
        .ColumnHeaders.Add , , "Отдел", 105 ' Заголовок 4, но столбец 3
        
        ' Добавляем строки
        Dim itm As ListItem
        
        Set itm = .ListItems.Add(, , "1") ' Элемент 1 строки (0 столбец)
        itm.SubItems(1) = "Иванов И.И."   ' Подэлемент 1 строки (1 столбец)
        itm.SubItems(2) = "Менеджер"      ' Подэлемент 1 строки (2 столбец)
        itm.SubItems(3) = "Отдел продаж"  ' Подэлемент 1 строки (3 столбец)
        
        Set itm = .ListItems.Add(, , "2")
        itm.SubItems(1) = "Петров П.П."
        itm.SubItems(2) = "Аналитик"
        itm.SubItems(3) = "Финансовый отдел"
        
        Set itm = .ListItems.Add(, , "3")
        itm.SubItems(1) = "Сидоров С.С."
        itm.SubItems(2) = "Программист"
        itm.SubItems(3) = "ИТ-отдел"
    End With
End Sub

Вставьте код примера в модуль UserForm. Результат работы кода представлен на изображении ниже.

#VBA #Excel #ListView #Отчет #Report

Изображение к посту выше.

🔢 Сортировка данных в ListView

Чтобы сортировать данные в табличных отчетах ListView, добавьте следующий код VBA Excel в модуль формы:

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    With Me.ListView1
        .Sorted = True
        .SortKey = ColumnHeader.SubItemIndex  ' Сортировка по выбранному столбцу
        .SortOrder = IIf(.SortOrder = 1, 0, 1)  ' Переключение порядка сортировки
    End With
End Sub

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

💡 Небольшое пояснение для поста выше:
В таблицах ListView нумерация столбцов начинается с 0 (ColumnHeader.SubItemIndex), а нумерация заголовков столбцов - с 1 (ColumnHeader.Index).

#VBA #Excel #ListView #Сортировка #Sort #ColumnHeader

Доступ к данным в таблице ListView
Вывод данных из выделенной строки в таблице ListView с помощью кода VBA Excel на примере таблицы со скриншота выше.

Добавьте на форму с ListView элемент управления CommandButton1 (кнопка) и в модуль формы вставьте следующий код:

Private Sub CommandButton1_Click()
Dim sItem As ListItem
Set sItem = ListView1.selectedItem
    If Not sItem Is Nothing Then
        MsgBox "Выбрана строка:" & vbNewLine & _
            "ID = " & sItem.Text & vbNewLine & _
            "ФИО = " & sItem.ListSubItems(1).Text & vbNewLine & _
            "Должность = " & sItem.ListSubItems(2).Text & vbNewLine & _
            "Отдел = " & sItem.ListSubItems(3).Text
    Else
        MsgBox "Строка не выбрана!"
    End If
End Sub

Откройте форму с ListView, выберите строку и нажмите кнопку CommandButton1. Информационное окно MsgBox отобразит данные из выделенной строки.

#VBA #Excel #ListView #Данные #Вывод

📋 ListView в режиме lvwSmallIcon
ListView в режиме lvwSmallIcon - это более удачный вариант, чем ListView в режиме lvwIcon. По крайней мере, на моём программном обеспечении.

Пример использования ListView в режиме lvwSmallIcon
1️⃣ Подключите библиотеку Microsoft Windows Common Controls 6.0 (SP6), если она не подключена.
2️⃣ Добавьте на панель Toolbox элементы управления «Microsoft ListView Control» и «Microsoft ImageList Control».
3️⃣ Создайте новую форму и перетащите на неё элементы ListView, ImageList и CommandButton с панели элементов управления (Toolbox).
4️⃣ Создайте несколько иконок (в примере их 5 штук) и поместите их в папку с именем «Test» на диске «C».
5️⃣ В модуль формы вставьте следующий код:

Private Sub UserForm_Initialize()
    ' Настройка формы
    With Me
        .Caption = "Сообщество смурфов"
        .Width = 350
        .Height = 250
    End With
    
    ' Настройка кнопки
    With CommandButton1
        .Caption = "Выбрать"
        .Width = 60
        .Height = 24
        .Top = Me.Height - 70
        .Left = Me.Width / 3 + 20
        .Font.Size = 10
    End With
    
    ' Настройка ListView
    With ListView1
        .View = lvwSmallIcon
        .LabelEdit = lvwManual
        .Sorted = True
        .MultiSelect = False
        .HideSelection = False
        .FullRowSelect = False
        .Left = Me.Left
        .Top = Me.Top
        .Width = Me.Width ' По ширине разворачиваем на всю форму
        .Height = Me.Height - 80 ' Внизу оставляем место для кнопок
    End With
 
    With ImageList1.ListImages
        .Add , "PapaSmurf", LoadPicture("C:\Test\PapaSmurf.ico")
        .Add , "Smurfette", LoadPicture("C:\Test\Smurfette.ico")
        .Add , "BrainySmurf", LoadPicture("C:\Test\BrainySmurf.ico")
        .Add , "GutsySmurf", LoadPicture("C:\Test\GutsySmurf.ico")
        .Add , "ClumsySmurf", LoadPicture("C:\Test\ClumsySmurf.ico")
    End With
 
    ' Привязка ImageList к ListView
    Set ListView1.SmallIcons = ImageList1
 
    ' Добавление элементов с иконками
    With ListView1.ListItems
        .Add , , "Папа Смурф", , "PapaSmurf"
        .Add , , "Смурфетта", , "Smurfette"
        .Add , , "Смурф Знайка", , "BrainySmurf"
        .Add , , "Смурф Храбрец", , "GutsySmurf"
        .Add , , "Смурф Растяпа", , "ClumsySmurf"
    End With
End Sub

Запустите форму, нажав на треугольник (Run Sub/UserForm) или на клавишу F5. Результат работы кода на скриншоте ниже.

#VBA #Excel #ListView #Icon #Значок #Иконка

📋 ListView в режиме lvwSmallIcon (продолжение)

Для определения, какой элемент ListView1 выбран, добавьте в модуль формы процедуру для кнопки:

Private Sub CommandButton1_Click()
    MsgBox ListView1.selectedItem.Text
End Sub

Теперь при нажатии на кнопку «Выбрать», информационное окно будет выводить текст выбранного элемента.

#VBA #Excel #ListView #Icon #Значок #Иконка

☀️ Создание фигур в VBA Excel

Фигуры в VBA Excel создаются методом Shapes.AddShape:

Shapes.AddShape (Type, Left, Top, Width, Height)

Параметры метода AddShape:
🔸 Type - константа из коллекции MsoAutoShapeType, определяющая тип создаваемой фигуры.
🔸 Left - расстояние от левой границы фигуры до левой границы табличной части рабочего листа в пунктах. Тип данных — Single.
🔸 Top - расстояние от верхней границы фигуры до верхней границы табличной части рабочего листа в пунктах. Тип данных — Single.
🔸 Width - ширина фигуры по внешним границам в пунктах.
🔸 Height - высота фигуры по внешним границам в пунктах.

Пример создания фигур:

Sub Primer1()
    With ActiveSheet.Shapes
        'При создании фигуры без присвоения ее переменной скобки не нужны
        .AddShape msoShapeCube, 30, 40, 72, 72
        .AddShape msoShapeIsoscelesTriangle, 130, 40, 72, 72
        .AddShape msoShapeSun, 230, 40, 72, 72
        .AddShape msoShapeLightningBolt, 330, 40, 72, 72
        'Чтобы выбрать фигуру, параметры необходимо заключить в скобки
        .AddShape(msoShapeCloud, 430, 40, 72, 72).Select
    End With
End Sub

Результат работы кода на скриншоте ниже.

#VBA #Excel #Shape #AddShape #Фигура #Создать

Скриншот к посту: Создание фигур в VBA Excel

🔢 Метод Evaluate и квадратные скобки

❓ А вы знали, что действие квадратных скобок в VBA Excel аналогично действию метода Application.Evaluate?

💡 У метода Evaluate есть более короткий и удобный синоним — квадратные скобки. У квадратных скобок есть недостатки: в них не получится использовать переменные и вычислять математические выражения, переданные текстовой строкой.

🔬 Сравнение доступа к ячейке разными способами: традиционным, стандартным Evaluate и сокращенным (квадратные скобки).

1️⃣ Ссылка на ячейку в текущей книге

Debug.Print Sheets("Лист2").Range("A14")
Debug.Print Evaluate("Лист2!A14")
Debug.Print [Лист2!A14]

2️⃣ Ссылка на ячейку в другой книге

Debug.Print Workbooks("Книга2.xlsm").Sheets("Лист2").Range("A14")
Debug.Print Evaluate("'[Книга2.xlsm]Лист2'!A14")
Debug.Print ['[Книга2.xlsm]Лист2'!A14]

Доступ к ячейке через Evaluate не рекомендуется использовать в циклах, так как он медленнее, чем через Range.

Больше примеров с методом Evaluate и квадратными скобками на сайте Время не ждёт в статье: VBA Excel. Метод Application.Evaluate.

#VBA #Excel #Evaluate #Метод #Ссылка #Скобки