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.