Vba макросы excel: Макросы для Excel с исходными кодами и описанием функций
Содержание
Макросы Excel — отладка кода
Вы узнали, что макрос хранится в виде кода VBA в Excel. Вы также узнали, что можете напрямую написать код для создания макроса в редакторе VBA. Однако, как и в случае с любым кодом, даже код макроса может иметь дефекты, и макрос может работать не так, как вы ожидали.
Это требует изучения кода, чтобы найти дефекты и исправить их. Термин, который используется для этого действия в разработке программного обеспечения, является отладкой.
Редактор VBA позволяет приостановить выполнение кода и выполнить любую необходимую задачу отладки. Ниже приведены некоторые из задач отладки, которые вы можете выполнить.
- Пошаговое выполнение кода
- Использование точек останова
- Резервное копирование или движение вперед в коде
- Не переступая через каждую строку кода
- Запрашивать что-либо во время пошагового выполнения кода
- Остановка казни
Это лишь некоторые из задач, которые вы можете выполнять в среде отладки VBA.
Пошаговое выполнение кода
Первое, что вам нужно сделать для отладки, это пройти по коду при его выполнении. Если у вас есть представление о том, какая часть кода, вероятно, создает дефект, вы можете перейти к этой строке кода. В противном случае вы можете выполнить код построчно, выполнить резервное копирование или продвинуться вперед в коде.
Вы можете перейти к коду из диалогового окна «Макрос» в рабочей книге или из самого редактора VBA.
Войдя в код из рабочей книги
Чтобы войти в код из рабочей книги, выполните следующие действия:
- Нажмите вкладку VIEW на ленте.
- Нажмите Макросы.
- Выберите View Macros из выпадающего списка.
Откроется диалоговое окно «Макрос».
- Нажмите на имя макроса.
- Нажмите кнопку Шаг в.
Откроется редактор VBA и в окне кода появится код макроса. Первая строка в коде макроса будет выделена желтым цветом.
Шаг в код от редактора VBA
Чтобы войти в код из редактора VBA, выполните следующие действия:
- Нажмите вкладку РАЗРАБОТЧИК на ленте.
- Нажмите Visual Basic. Откроется редактор VBA.
- Нажмите на модуль, который содержит код макроса.
Код макроса появится в окне кода.
Перейдите на вкладку «Отладка» на ленте.
Выберите Step into из выпадающего списка.
Первая строка в коде макроса будет выделена. Код находится в режиме отладки, и параметры в раскрывающемся списке «Отладка» станут активными.
Резервное копирование или движение вперед в коде
Вы можете двигаться вперед или назад в коде, выбрав Step Over или Step Out.
Не переступая через каждую строку кода
Вы можете избежать пошагового прохождения каждого строкового кода, если вы идентифицируете потенциальную часть кода, которую необходимо обсудить, выбрав Run to Cursor.
Использование точек останова
Кроме того, вы можете установить точки останова в определенных строках кода и выполнить код, наблюдая результаты в каждой точке останова. Вы можете переключать точку останова и очищать все точки останова, если и когда это необходимо.
Использование часов
Вы можете добавить часы во время отладки, чтобы оценить выражение и остановить выполнение, когда переменная достигает определенного значения. Это означает, что вы настраиваете выражение наблюдения, которое будет отслеживаться до тех пор, пока оно не станет истинным, а затем макрос остановится и оставит вас в режиме прерывания. VBA предоставляет вам несколько типов часов на выбор, чтобы выполнить то, что вы ищете.
Остановка казни
Во время отладки, в любой момент, если вы нашли подсказку о том, что идет не так, вы можете остановить выполнение, чтобы расшифровать его дальше.
Если вы опытный разработчик, терминология отладки вам знакома, а опции отладки в редакторе VBA сделают вашу жизнь проще. Даже в противном случае освоение этого навыка не займет много времени, если вы изучите VBA и поймете код.
Макросы VBA Excel для замены формул на значения
planacademy
MS Excel,VBA
В данной статье представлены разные варианты макросов для быстрой замены формул на значения в документах MS Excel
Заменить формулу на значение можно следующим образом: выделяем нужную ячейку, нажимаем F2, F9, Enter и в текущей ячейке формула заменится на значение. Если таких ячеек много, то это превратится в долгий и рутинный процесс. На помощь придут следующие макросы
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl):
Sub Formulas_To_Values_Selection() 'преобразование формул в значения в выделенном диапазоне Dim smallrng As Range For Each smallrng In Selection.Areas smallrng.Value = smallrng.Value Next smallrng End Sub
Макрос для превращения всех формул в значения на текущем листе:
Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
Макрос для превращения всех формул в книге и на всех листах:
Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
End Sub
Данный макрос меняет все формулы на значения во всех файлах на всех листах в указанной папке
Sub УдалитьВсеФормулыВПапке() Dim fd As FileDialog Dim iPath As String Dim iFileName As String Dim iSheet As Worksheet Set fd = Application.FileDialog(msoFileDialogFolderPicker) ChDir "C:\" With fd .ButtonName = "Выбрать" If .Show = -1 Then iPath = .SelectedItems(1) & Application.PathSeparator Else Exit Sub End If End With Set fd = Nothing if MsgBox("Во всех документах Excel в папке " & iPath & " на всех листах формулы будут заменены на значения!" & Chr(13) & "Вы уверены ???", vbOKCancel + vbExclamation, "Подтверждение") = vbCancel Then Exit Sub If MsgBox("Вы отдаёте себе отчёт, что формулы во всех файлах будут удалены?", vbOKCancel + vbExclamation, "Подтверждение") = vbCancel Then Exit Sub If MsgBox("Во всех документах Excel в папке " & iPath & " на всех листах формулы будут заменены на значения!" & Chr(13) & "Вы уверены ???", vbOKCancel + vbExclamation, "Подтверждение") = vbCancel Then Exit Sub With Application .ScreenUpdating = False .Calculation = xlCalculationManual '.EnableEvents = False iFileName = Dir(iPath & "*.xls") Do While iFileName$ <> "" With Workbooks.Open(Filename:=iPath & iFileName, UpdateLinks:=0) For Each iSheet In .Sheets With iSheet.UsedRange .Value = .Value End With Next .Close saveChanges:=True End With iFileName$ = Dir Loop .EnableEvents = True .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With MsgBox "Во всех документах Excel в папке " & iPath & " на всех листах формулы были заменены на значения!", 64, "Конец" End Sub
Код нужных макросов можно скопировать в новый модуль вашего файла. Нажимаем Alt+F11 чтобы попасть в Visual Basic, далее Insert – Module чтобы попасть в Visual Basic, далее Insert – Module. Запускать их потом можно через вкладку Разработчик – Макросы (Developer – Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся.
Внимание! Действия выполненные макросом невозможно отменить – применяйте их с осторожностью.
Логические операторы в макросах Excel VBA
Логические операторы в VBA позволяют принимать решения при соблюдении определенных условий.
Они позволяют вам проверить, равно ли что-то этому ИЛИ этому; это и то; Не это; и комбинации всех трех. При использовании в сочетании с операторами IF они позволяют вам делать более сложные выборы в ваших макросах и VBA.
Логические операторы
Оператор | Значение |
---|---|
И | A < B AND B < 10 — проверяет, выполняются ли оба условия. |
ИЛИ | A < B OR B < 10 — Проверяет, верно ли одно из условий. |
НЕ | Not A < B — это меняет проверку, что означает, что это будет оцениваться как истинное, если A больше, чем B, и НЕ, если оно меньше, чем B. |
Хор | Используется редко. Щелкните здесь для получения дополнительной информации об этом. |
Их легче понять, когда вы видите их в приведенных ниже примерах.
Примеры логических операторов
В этих примерах предполагается, что вы знакомы с оператором IF в VBA/макросах.
И
Проверьте, все ли условия оцениваются как истинные.
Проверить, если A меньше B И B меньше 10:
Если A < B и B < 10, то MsgBox "Привет" Конец, если
Важная часть кода: A < B And B < 10
Здесь не нужны круглые скобки, и вы можете иметь столько условий, сколько вам нужно.
Вот полный пример:
Это говорит о том, что если A меньше B И B меньше 10, появится окно сообщения «Привет».
Это оценивается как True , поскольку A меньше B (A установлено на 1, а B на 5), а B меньше 10.
Или
Проверьте, оценивается ли ЛЮБОЕ из условий как истинное.
Проверить, если A меньше B ИЛИ B меньше 10.
Если A < B или B < 10, то MsgBox "Привет" Конец, если
Важная часть кода: A < B или B < 10
Здесь не нужны круглые скобки, и вы можете иметь столько условий, сколько вам нужно.
Вот полный пример:
На этот раз я установил B равным 50, но оператор IF по-прежнему оценивается как True, потому что A, который равен 1, меньше, чем B. Таким образом, появится окно сообщения и скажи привет".
При использовании или только 1 условие должно быть истинным, чтобы утверждение было оценено как истинное.
Не
Не буквально обращает любое условие, которому оно предшествует.
Здесь давайте проверим, меньше ли A, чем B, НО поставим перед ним , а не .
Если не А < В, то MsgBox "Привет" Конец, если
Важная часть кода: Не A < B
Вот полный пример:
Обычно это оценивается как True, потому что A меньше, чем B, но поскольку перед проверкой стоит Not , это оценивается как False .
Если это сбивает с толку, просто мысленно уберите НЕ и выполните сравнение в уме, в этом случае вы получите Истинно, затем просто верните обратное, когда НЕ присутствует.
В этом примере окно сообщения не появится.
Более сложные примеры
Давайте немного поднимем тему.
Если в вашем операторе IF есть более сложные проверки, вам нужно будет использовать круглые скобки.
Пример 1
Проверить, если A меньше B И B больше 10 ИЛИ меньше 40.
Sub logical_examples_1() А = 100 В = 30 Если А < В И (В > 10 Или В < 40), то MsgBox "Привет" Конец, если Конец сабвуфера
Это оценивается как False ; однако без круглых скобок после и это будет равно True .
Скобки заставляют сначала оценивать часть проверки или просто вместе. В этом примере оценивается часть перед И, а затем все в круглых скобках оценивается вместе; после этого часть слева от И сравнивается с частью справа.
Это немного сбивает с толку; вам действительно нужно скачать образец файла для этого урока и поиграть с ним, пока вы не поймете его; это лучший способ изучить эту концепцию.
Пример 2 (Не)
Давайте воспользуемся тем же примером, за исключением того, что A теперь равно 10, и мы поместим Not после And .
Sub logical_examples_2() А = 10 В = 30 Если A < B И Нет (B > 10 или B < 40), то MsgBox "Привет" Конец, если Конец сабвуфера
Если бы не было Не после И в этом примере, это было бы оценено как Истина и появилось окно сообщения.
Однако команда Not меняет результат на противоположный. В этом случае Not отменяет результат проверки в скобках.
Просмотрите каждое сравнение, оцените его, а затем сравните их с помощью логических операторов. Это лучший способ понять, как все это работает. Это может показаться запутанным, но это поможет, если вы загрузите файл примера, прикрепленный к этому руководству, и поэкспериментируете с этим примером.
Примечания
Логические операторы And , Or и Not чрезвычайно полезны в VBA и макросах. Вы должны научиться их использовать, если хотите создавать более полезные макросы в Excel.
Когда дело доходит до скобок, люди склонны злоупотреблять ими; однако я говорю, что вы должны использовать их до такой степени, чтобы вам все стало ясно и понятно! Если вам нужно много скобок, добавьте их; если вы действительно хорошо разбираетесь в порядке старшинства, который определяет, какие части условия будут оцениваться первыми, используйте меньше круглых скобок.
Чтобы научиться использовать эти операторы и круглые скобки, загрузите файл примера, прикрепленный к этому руководству, и поэкспериментируйте с приведенными там примерами; сравните их с этим руководством, отредактируйте и посмотрите, что получится. Если вы сделаете это, вы запомните эти понятия в кратчайшие сроки.
Эффективные способы использования макросов VBA в отчетах и анализе
Обновлено 7 июня 2023 г.
Что такое приложения Visual Basic (макросы VBA)
VBA — это язык программирования, разработанный Microsoft для пакетов Microsoft Office, таких как Word, Аксесс, Эксель и другие. Он используется для настройки приложений в соответствии с потребностями бизнеса. Это мощный и удобный инструмент для повторного выполнения операции и помогает анализировать данные. VBA используется для доступа к функциям приложений и управления ими в других приложениях. Макросы VBA используются для эффективного и действенного выполнения финансовой отчетности и анализа.
VBA в Excel создает, форматирует и распечатывает отчет, используя графические представления, такие как диаграммы. Отчеты легко генерируются, и это просто с помощью макросов VBA. Отчеты формируются с использованием различных опций в соответствии с потребностями руководства.
Использование макросов VBA в Excel для создания отчетов
Чтобы использовать VBA в Excel, убедитесь, что вкладка «Разработчик» находится на вкладке «Меню».
Если опция «Разработчик» недоступна на вкладке «Меню», перейдите к кнопке «Офис» à «Параметры Excel» à Щелкните вкладку «Показать разработчик». Установите флажок и нажмите «ОК».
После включения этой опции вы можете начать использовать Visual Basic. На вкладке «Разработчик» выберите параметр Visual Basic
. Выберите «Меню вставки» à «Пользовательская форма». вы можете ввести заголовок для ярлыка.
Выберите значок текстового поля на панели инструментов и оставьте его рядом с полем метки. Затем щелкните правой кнопкой мыши текстовое поле и во всплывающем меню «Свойства» введите имя текстового поля в свойстве «Имя».
Повторите шаги, чтобы добавить столько меток и текстовых полей, которые вы хотите ввести в область пользовательской формы.
Теперь выберите параметр «Кнопка управления» на панели инструментов и поместите его в форму. Щелкните правой кнопкой мыши и во всплывающем окне свойств введите нужную команду в свойстве подписей.
После ввода всех данных пользовательского интерфейса необходимо ввести код для создания отчета. Чтобы дважды открыть окно кода, нажмите кнопку Command в форме.
Перед вставкой кода вы должны установить ссылку, которая поможет вам получить доступ к базе данных VB. Чтобы выбрать ссылки, выберите Меню инструментов à Ссылки
Выберите тип библиотеки, которую вы хотите использовать, в диалоговом окне. На этом рисунке установлен флажок Microsoft ActiveX Data Objects 2.6 Library.
В коде Window вводит код, используемый для запуска команды. Код размещается между «Private Sub CommandButton1_Click()» и End Sub.
Вы также можете добавить функции в окно кода для создания отчета.
После ввода кода необходимо создать макросы для данных. Перейдите на вкладку «Разработчик» à «Макросы», чтобы использовать меню «Макросы».
Введите имя макроса для отчета и нажмите кнопку «Создать».
Введите код формы между Sub и End Sub option
После выполнения всех этих шагов все готово для запуска отчета.
Чтобы запустить отчет, выполните следующие действия:
Щелкните меню «Макросы» на вкладке «Разработчик»
Выберите имя макроса из списка и нажмите кнопку «Выполнить».
Введите необходимые данные для создания отчета.
Наконец, отчет создан в соответствии с вашими потребностями.
Понимание переменных, условий и циклов в VBA
Сначала давайте посмотрим, что означают все три термина в VBA
Переменная — это небольшая часть системной памяти, которая используется для хранения данных
Условие 9006 8 – Это логическая проверка, которую компьютер выполняет для проверки чего-либо
Цикл — это набор инструкций, которым нужно следовать определенное количество раз
Теперь мы увидим, как использовать все эти три в VBA
Создание переменных в макросах VBA
Если вы хотите использовать переменную в макросах VBA, вам сначала нужно их создать. DIM — это оператор, который используется для создания любой переменной.
Ниже приведены несколько примеров созданных переменных.
- Dim someNumber As Integer — это означает, что вы будете использовать только некоторую числовую переменную для хранения целочисленных значений. Это используется в VBA как someNumber = 7, что означает, что он сохраняет 7 в переменной someNumber 9.0316
- Dim myList(1 To 10) As String — эта строка означает, что вы хотите использовать список значений как строку (текст). И размер списка ограничен 10. Это используется как myList(5) = 792, что означает, что он устанавливает значение 5-го элемента в массиве myList равным 792.
- Dim otherNumber As Double
- Dim AnotherList() As Variant
- Затемнить текст как строку
- Dim myCells As Range
- Dim myChart As Chart
- Dim aCondition As Boolean
Использование условий в макросах VBA
Все в компьютерах связано с условиями принятия решений и тестирования. Есть много утверждений для тестовых условий. Некоторые из них поясняются ниже для вашего понимания. Вот пример для проверки условия покупки
Если наша покупка < 600 или наша покупка > 6000, то
«специальные инструкции для обработки слишком большого или слишком малого количества покупок
конец, если
Оператор Else
Оператор Else используется, когда условие If не выполняется.
Если ourPurchase < 600 или ourPurchase > 6000, то
'специальные инструкции по обработке слишком большого или слишком малого количества покупок
Else
'Запишите покупки и двигайтесь дальше
end if
900 58 Использование циклов в макросах VBA
Существует несколько способов написания циклов в VBA. Здесь мы упомянули самый простой тип Loop для вашего понимания
For Loop
Цикл For используется для повторения набора инструкций заданное количество раз.
For storeNumber = от 20 до 50
'позвонить в магазин
'спросить цифру продаж
'сделать что-нибудь, если нужно
'повесить трубку
Next storeNumber
9033 6 Для каждой петли
Этот тип петли используется для запуска одних и тех же инструкций для каждого элемента в списке.
Для каждой ячейки в диапазоне («B1:B10»)
cell.value = cell.value + 2
Следующая ячейка
Ярлыки
Есть несколько важных сочетаний клавиш, которые следует знать при использовании VBA в Excel
- ALT + F11 — для просмотра редактора VBA, также известного как VBE
- ALT + F8 — для отображения всех макросов
- ALT+Q: Чтобы закрыть редактор VBA и вернуться в Excel
- F5: Для запуска макроса
- F2: Показать обозреватель объектов
- F7: Редактор кода дисплея
- CTRL+G: Открытие непосредственного окна
- F1: Показать справку
Analysis ToolPak
Analysis ToolPak — это надстройка, которая является встроенной программой по умолчанию в Microsoft Excel. Это инструмент анализа данных, который помогает в финансовом, статистическом и инженерном анализе данных. Вы всегда можете сэкономить свое время и энергию, используя Analysis ToolPak.
Вам просто нужно предоставить данные для анализа, и Analysis ToolPak использует соответствующую функцию макроса и отображает результат в выходной таблице. Они также иногда создают графики вместе с отчетами.
Чтобы загрузить пакет инструментов анализа, выполните следующие простые шаги.
Перейдите на вкладку «Зеленый файл» и нажмите «Параметры».
В диалоговом окне установите флажок «Пакет анализа» и нажмите «ОК».
Если параметр «Пакет анализа» недоступен, щелкните параметр «Обзор». Установите флажок напротив параметра «Пакет анализа» и нажмите «ОК», если он доступен.
Теперь на вкладке «Данные» вы можете нажать «Анализ данных»
Наконец, вы можете выбрать вариант, который необходимо выполнить в Excel. На приведенном ниже рисунке гистограмма выбрана для создания гистограммы в Excel для заданных данных.
Всего в Analysis ToolPak доступно 19 вариантов анализа данных. Они перечислены ниже
- Anova: Single Factor
- Anova: двухфакторный анализ с репликацией
- Anova: двухфакторный без репликации
- Корреляция
- Ковариация
- Описательная статистика
- Экспоненциальное сглаживание
- F-тест с двумя образцами для дисперсии
- Анализ Фурье
- Гистограмма
- Скользящее среднее
- Генерация случайных чисел
- Ранг и проценты
- Регрессия
- Отбор проб
- t-тест: два образца в паре для среднего
- t-критерий: две выборки при условии равенства дисперсий
- t-критерий: две выборки в предположении неравных дисперсий
- Z-тест: две выборки для среднего
Некоторые из этих методов анализа данных подробно объясняются ниже со снимками экрана того же
Описательная статистика
После установки пакета инструментов анализа открывается диалоговое окно «Анализ данных» со списком вариантов. Выберите описательную статистику. После выбора этой опции вы получите диалоговое окно, как показано на рисунке ниже. отображаться. Ниже приведен вывод описательной статистики
Одним из основных недостатков использования параметра описательной статистики является то, что он отображает результат в виде значений, а не в виде формул. Таким образом, если в исходные данные будут внесены какие-либо изменения, они не будут отражены в выходных данных. Вам нужно повторить весь метод с самого начала.
Создание гистограммы
Гистограмма представляет собой набор значений в определенных диапазонах. Диапазоны известны как ячейки в гистограмме. В том же диалоговом окне «Анализ данных» выберите параметр «Гистограмма». Вы получите диалоговое окно, как показано на рисунке ниже
Вам необходимо ввести такие детали, как диапазон исходных данных, диапазон обозначений бинов и место, где должен отображаться результат. Результат покажет вам распределение данных вместе с параметрами, как показано на рисунке ниже.
Диалоговое окно гистограммы также позволяет вам создать диаграмму вместе с выходными данными. Это даст вам легкое понимание.
Скользящие средние
Этот параметр поможет вам определить тенденции в последовательных данных. Исходные данные в этом случае основаны на периодах, а среднее значение всегда рассчитывается за определенное количество периодов. Ниже приведено диалоговое окно Moving Averages 9.0003
В этом диалоговом окне можно выбрать диапазон исходных данных, шаг интервала и диапазон вывода. Интервальный шаг является основным фактором скользящих средних, так как вывод основан на этом. Это также дает вам возможность создать диаграмму в выходных данных, как показано ниже. Генерация номера
Это один из параметров в диалоговом окне Анализ данных. После выбора вы получите диалоговое окно Генерация случайных чисел ниже.
Даже в Excel есть эта опция, но опция генерации случайных чисел в Analysis ToolPak гораздо более продвинутая, чем в Excel.