Parus16.ru

Парус №16
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

VBA в Excel

VBA в Excel

Язык VBA позволяет писать макросы в Excel. Как это делать рассматривается ниже.

Application

Application — это объект, занимающий вершину иерархии объектов в Excel. Application – это и есть Excel. Вот примеры использования Application:

окно нормальных размеров

окно максимальных размеров

Workbooks

Workbooks — это коллекция рабочих книг, которые открыты. Каждая рабочая книга представлена объектом Workbook. Узнать сколько рабочих книг сейчас в коллекции можно так:

Обатиться к нужной рабочей книге в коллекции Workbooks можно обратиться по его номеру или по имени:

здесь мы активизировали второй элемент коллекции Workbooks.

Другие примеры мы увидим ниже.

Workbook

Workbook — это рабочая книга.

Сохранить рабочую книгу:

И где же в этом примере рабочая книга Workbook? Здесь: Workbooks.Item(1), это выражение возвращает первый элемент коллекции Workbooks, а это и есть Workbook.

Закрыть рабочую книгу:

Sheets

Sheets — это все листы рабочей книги. Листы рабочей книги представлены двумя типами: рабочие листы — это обычные листы Excel и второй тип — это листы диаграмм. Те и другие и составляют коллекцию Sheets.

Charts

Charts — это только диаграммы рабочей книги.

Chart

Chart — это объект, представляющий одину диаграмму. Если диаграмма содержится в обычном рабочем листе, то она объектом Chart не является.

Worksheets

Worksheets — это только рабочие листы рабочей книги.

Добавим рабочий лист в коллекцию Worksheets:

Worksheet

Worksheet — это один лист рабочей книги Excel.

Все ячейки рабочего листа Worksheet:

Range. Работа с ячейками в Excel

Range — это одна ячейка листа или несколько ячеек. Этот объект рассмотрим подробнее. Установим кнопку на рабочий лист Excel. Откройте панель инструментов «Элементы управления» (правой кнопкой по свободному полю панели инструментов и выбираем из выпавшего меню). В этой панели выбираем кнопку и устанавливаем её на свободное место рабочего листа, в его правой части. Правой кнопкой мыши по нашей кнопке — > Свойства. Установите значение свойства Name — CommandButton, а свойства Caption — Range Test.

Закройте окно свойств кнопки, и кликаем по нашей кнопке правой кнопкой мыши, из выпавшего меню выбираем «Исходный текст». Открывается окно редактора VBA, оно называется Microsoft Visual Basic. В нём уже есть заготовка обработчика нажатия на кнопку CommandButton:

Кстати, помотрите на эту заготовку, ключевое слово Sub говорит, что это процедура, а слово Private указывает, что эта процедура видна лишь в данном модуле.

Всё у нас готово для начала изучения работы с ячейками рабочего листа Excel.

Как задать активную ячейку?

Сделаем активной ячейку A2:A2:

Далее сохраняем, далее Debug — > Compile VBA Project. Если нет ошибок, то запускаем нашу программу: Run — > Run Sub/UserForm.

Java

Выделим любую другую ячейу и нажимём на кнопку Range Test. Вновь выделится ячейка «А2». Это значит, что обработчик нажатия на кнопку работает исправно.

Как выделить ряд ячеек?

Выделим интервал ячеек «A2:D2». Вносим в нашу заготовку макроса новый код:

Далее сохраняем, далее Debug — > Compile VBA Project. Переходим на рабочий лист Excel и жмем на кнопку Range Test.

Java

Как установить значение в ячейку?

Внесем число 123 в ячейку «A2:A2» для этого изменим код макроса так:

Читайте так же:
Можно ли вконтакте скрыть семейное положение

Далее сохраняем, далее Debug — > Compile VBA Project. Переходим на рабочий лист Excel и жмем на кнопку Range Test.

Open Notes

Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.

Служебные команды для ускорения скорости выполнения макроса:

‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение появления предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение появления предупреждения об обновлении связей данных Application.AskToUpdateLinks = False ‘Очистка буфера обмена Application.CutCopyMode = False

Проверка имени пользователя, запустившего макрос:

Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:

If Application.UserName = "Имя_автора_документа" Then .

Поиск последней строки таблицы:

Set myWSheet = ThisWorkbook.Sheets("Имя_листа") With myWSheet ‘Определение индекса последней строки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки последней строке столбца A lastARow = .Range("A" & lastRow).Value End With

Замена формулы на значение:

Добавление нового листа с именем после всех существующих:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "Имя_листа"

Как узнать последний день предыдущего месяца:

LastMonthDay = DateAdd("d", -1, DateSerial(Year(dtDate), Month(dtDate), 1))

Определение оставшихся дней месяца:

dToEndOfMonth = DateDiff("d", dFrom, DateAdd("d", -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))

Номер текущего дня в неделе (воскресенье — первый день):

DayOfWeek = DatePart("w", dToday)

Создание нового файла из текущего:

pathNewBook = "C:Temp" nameNewBook = "Имя_нового_файла.xls" Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True

Сохранить текущий файл в формате CSV

Чтобы при сохранении файла в формате CSV, вместо запятых в качестве разделителя использовалась точка с запятой, следует использовать подобный код:

ActiveWorkbook.SaveAs FileName:="Name.csv", FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True

Копирование данных из одного файла в другой:

wbPath = "C:Temp" wbName = "Имя_файла_откуда_копируем.xls" Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets("Лист 1").Range("A1:С10").Copy Sheet("Лист_в_текущем_файле").Range("A2").PasteSpecial xlPasteValues

Чтобы открыть файл только для чтения, следует использовать:

Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)

Предотвращение ошибки при неудачном поиске значения в таблице:

Set DateRowObj = WB.Sheets("Имя_листа").Range("A:A")._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox "Данные не найдены." Else DateRow = DateRowObj.Row ‘Номер строки с искомым значением End If

Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):

wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") — 1)

Проверка существования файла:

fPath = "C:Temp" fName = "Файл.txt" If Dir(fPath & fName) = "" Then MsgBox "Файл не найден:" & Chr(13) & fPath & fName Exit Sub End If

Кнопка, скрывающая/разворачивающая часть таблицы:

Private Sub tbVid_Click() Application.ScreenUpdating = False If tbVid Then tbVid.Caption = "Скрыть" ActiveSheet.Rows("2:29").Hidden = False Else tbVid.Caption = "Развернуть" ActiveSheet.Rows("2:29").Hidden = True End If End Sub

Обновление сводной таблицы:

currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables("СводнаяТаблица1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & "[" & _ currWBName & "]Лист1!R1C1:R10C5")

Обращение к элементам Frame:

Замена #ДЕЛ/0! в диапазоне:

Selection.Replace What:="#DIV/0!", Replacement:="", LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False

Количество строк в отфильтрованной таблице:

Быстро убрать лишние пробелы в диапазоне:

Программно снять защиту с листа:

Работа с диапазоном

Умножить диапазон на число:

ThisWorkbook.Sheets(1).Range("A1:A10") = _ ThisWorkbook.Sheets(1).Evaluate("A1:A10" & "*80")

Добавить ко всем значениям диапазона строку:

Сортировка выбранного столбца в сводной таблице

Col = Selection.Column ‘Номер выбранного столбца ColMax = ActiveSheet.PivotTables("СводнаяТаблица").PivotColumnAxis. _ PivotLines.Count If Col — 1 <= ColMax And Col 1 Then ActiveSheet.PivotTables("СводнаяТаблица").PivotFields("Label").AutoSort _ xlDescending, " ", ActiveSheet.PivotTables("СводнаяТаблица"). _ PivotColumnAxis.PivotLines(Col — 1), 1 End If

Читайте так же:
Можно ли потолстеть от роллтона

Счетчик времени выполнения процедуры

‘Счётчик, ставится в начале процедуры StartUpdDate = Now ‘Сообщение, выводится в конце процедуры MsgBox "Данные обновлены за " & Fix(1440 * (Now – StartUpdDate)) & " мин. " & 86400 * (Now – StartUpdDate) Mod 60 & " сек."

Функция транслитерации с русского на английский

Поиск файлов в папке

Dim strDirPath, strMaskSearch, strFileName as String strDirPath = "C:/test/" ‘Папка поиска strMaskSearch = "*.xls*" ‘Маска поиска ‘Получаем первый файл соответствующий шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> "" ‘До тех пор пока файлы "не закончатся" MsgBox strFileName strFileName = Dir ‘Следующий файл Loop

VBA Last Row

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

Я просто укажу один пример, который повторяет важность динамического кода.

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

И посмотрите на код, приведенный ниже:

Код:

Здесь этот код печатает сумму зарплат для всех сотрудников (ячейка B2: B11 ) в ячейке D2 . Смотрите изображение ниже:

Теперь, что, если я добавлю несколько ячеек в эти данные и снова выполню этот код?

Логически говоря, приведенный выше код не будет суммировать все 14 строк из столбца B. Причина того же — диапазон, который мы обновили в WorksheetFunction (то есть B2: B11). По этой причине динамический код, который может принимать во внимание последнюю заполненную строку, делает нас более важными.

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

Как найти последнюю использованную строку в столбце, используя VBA?

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

Вы можете скачать этот шаблон Excel последней строки VBA здесь — Шаблон Excel последней строки VBA

Пример # 1 — Использование метода Range.End ()

Что ж, этот метод такой же, как использование стрелки Ctrl + Down в Excel для перехода к последней непустой строке. Аналогичным образом выполните следующие шаги для создания кода в VBA, чтобы добраться до последней непустой строки столбца в Excel.

Шаг 1: Определите переменную, которая может принимать значение для последней непустой строки столбца Excel.

Код:

Здесь переменная Last_Row определена как LONG просто для того, чтобы она могла принимать любое количество аргументов.

Шаг 2: Используйте определенную переменную для хранения значения последней непустой строки.

Код:

Шаг 3: Введите код, начинающийся с CELLS (Rows.Count перед Last_Row = .

Код:

Шаг 4: Упомяните 1 после запятой в вышеприведенном коде. Числовое значение 1 является синонимом первого столбца в таблице Excel.

Код:

Этот код позволяет VBA узнать общее количество (пустых + непустых) строк, присутствующих в первом столбце таблицы Excel. Это означает, что этот код позволяет системе перейти к последней ячейке Excel.

Читайте так же:
Госуслуги личный кабинет клиента физического лица

А что, если вы находитесь в последней ячейке Excel и хотите перейти к последней непустой строке? Вы будете использовать Ctrl + Стрелка вверх, верно?

Эту же логику мы будем использовать в следующей строке кода.

Шаг 5: Используйте комбинацию клавиш End и xlUp, чтобы перейти к последней непустой строке в Excel.

Код:

Это приведет вас к последней непустой строке в Excel. Тем не менее, вы хотели номер строки для того же.

Шаг 6: Используйте ROW, чтобы получить номер строки последней непустой строки.

Код:

Шаг 7: Показать значение Last_Row, которое содержит номер последней непустой строки, используя MsgBox.

Код:

Шаг 8: Запустите код, используя кнопку Run или нажав F5, и просмотрите результат.

Выход:

Шаг 9: Теперь давайте удалим одну строку и посмотрим, даст ли код точный результат или нет. Это поможет нам проверить динамичность нашего кода.

Пример № 2 — Использование Range и SpecialCells

Мы также можем использовать свойства Range и SepcialCells VBA, чтобы получить последнюю непустую строку таблицы Excel.

Выполните следующие шаги, чтобы получить последнюю непустую строку в Excel, используя код VBA:

Шаг 1: Определите переменную снова как Long.

Код:

Шаг 2: Начните сохранять значение в переменной Last_Row с помощью оператора присваивания.

Код:

Шаг 3: Начните вводить диапазон («A: A») .

Код:

Шаг 4: Используйте функцию SpecialCells, чтобы узнать последнюю непустую ячейку.

Код:

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

Шаг 5: Теперь используйте ROW, чтобы получить последний ряд из вашего листа Excel.

Код:

Это вернет вам последнюю непустую строку из вашего Excel.

Шаг 6: Теперь присвойте MsgBox это значение Last_Row, чтобы мы могли видеть последний непустой номер строки в окне сообщения.

Код:

Шаг 7: Запустите код, нажав F5 или кнопку Run, расположенную в верхней части левого угла.

Выход:

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

Шаг 8: Если мы удалим строку и сможем запустить эту формулу. Давай посмотрим что происходит.

Вы можете видеть, что система все еще дала счетчик строк как 14. Несмотря на то, что я удалил строку, а фактическое количество строк равно 13, система точно не зафиксировала количество строк. Чтобы система зафиксировала фактическое количество строк, вам нужно сохранить лист и снова запустить код.

Вы можете видеть фактическое количество строк, показанное на этом снимке экрана.

Пример # 3 — Использование Range.Find ()

Выполните следующие шаги, чтобы получить последнюю непустую строку в Excel, используя код VBA:

Шаг 1: Определите переменную как долго.

Код:

Шаг 2: Теперь используйте следующий код, чтобы увидеть последнюю непустую строку.

Код:

Здесь функция FIND ищет первую непустую ячейку. Звездочка (*) — это подстановочный оператор, который помогает выяснить то же самое.

Начиная с ячейки A1, система возвращается к последней ячейке листа и выполняет поиск в обратном направлении (xlPrevious). Он перемещается справа налево (xlByRows) и зацикливается на одном и том же листе по всем строкам на похожих строках, пока не найдет непустую строку (см. .ROW в конце кода).

Читайте так же:
Можно ли снять деньги через киви кошелек

Шаг 3: Используйте MsgBox, чтобы сохранить значение последней непустой строки и увидеть его как всплывающее окно.

Код:

Шаг 4: Запустите код и просмотрите вывод в виде всплывающего окна, содержащего последний непустой номер строки.

Выход:

То, что нужно запомнить

  • End (Example1) может использоваться для определения первой пустой ячейки / строки или последней непустой ячейки / строки в данном столбце с использованием кода VBA.
  • Конец работает на одном столбце большую часть времени. Если у вас есть данные в диапазонах, было бы трудно решить, какой столбец следует использовать для поиска последней непустой строки.
  • Find (Example3) работает во всем диапазоне от начальной точки и находит последнюю непустую ячейку / строку в данном столбце, используя код VBA. Это также может быть использовано для определения последнего непустого столбца.

Рекомендуемые статьи

Это руководство к VBA Last Row. Здесь мы обсудим, как найти последнюю использованную строку в данном столбце вместе с некоторыми практическими примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше —

Не удается запустить макрос. макрос может быть недоступен в этой книге

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

В частности, у меня есть две таблицы и несколько сабвуферов VBA в этих таблицах. В одном из проектов VBA (скажем, workbook1.xlsm) у меня есть следующий код:

Но я получил следующую ошибку. Макросы на обоих листах включены. В обеих таблицах сабвуферы находятся в Модуле1.

Не удается запустить макрос «Workbook.xlsm! PrepareTheTables». Макрос может быть недоступен в> этой книге или все макросы могут быть отключены.

Я также попробовал Application.Run «Python solution macro.xlsm!Module1.PreparetheTables» но не сработало.

ОТВЕТЫ

Ответ 1

Если у вас есть пробел в названии книги, вы должны использовать одинарные кавычки (‘) вокруг имени файла. Я также удалил полную остановку.

Ответ 2

В Microsoft KB попробуйте разрешить программный доступ к проекту Visual Basic:

  1. Нажмите кнопку Microsoft Office, а затем нажмите Параметры Excel.
  2. Нажмите Траст-центр.
  3. Нажмите Настройки центра управления безопасностью.
  4. Нажмите Настройки макроса.
  5. Нажмите, чтобы установить флажок Доверительный доступ к объектной модели проекта VBA.
  6. Нажмите кнопку ОК, чтобы закрыть диалоговое окно «Параметры Excel».
  7. Возможно, вам придется закрыть и снова открыть Excel.
Ответ 3

Имел ту же проблему, и я «Скомпилировал проект VBA», который выявил ошибку. После коррекции и компиляции макросы работали.

Ответ 4

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

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

Если вы попытаетесь запустить из кода, нажав зеленую кнопку воспроизведения, вы также увидите ту же ошибку.

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

Ответ 5

У меня также была проблема с этой ошибкой, оказалось, что имя файла вызывает проблему.

Читайте так же:
Можно ли установить операционную систему на флешку

Я называл это следующим образом: Application.Run «и strPath и strFName и»!! UPC.PrintaFew «

Переменная strFName содержала в себе апостроф, который, конечно же, перепутал вещи. Мне потребовалось несколько часов, чтобы понять это. Но как только апостроф был удален из имени файла, он сработал.

Ответ 6

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

Ответ 7

В моем случае эта ошибка возникла, когда имя Sub было идентично имени модуля.

Ответ 8

Удалите макрос имени и снова создайте его. Я сделал это, и макрос работал.

Ответ 9

Наиболее вероятной причиной этой ошибки является то, что функция безопасности в Excel VBA, которая не позволяет запускать код VBA. Пользователь должен явно предоставить разрешение на запуск макросов excel вместе с книгой. Этот может или не может требовать программный доступ к проекту Visual Basic.

Для меня эта ошибка была решена: —

Включить редактирование и включение содержимого

Включить кнопку содержимого

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

Ответ 10

У меня была такая же проблема, как и у OP, и это было вызвано тем, что объявление опций было написано с ошибкой:

в дополнительном модуле вместо правильного;

Ответ 11

Сохранить как .xlsm(Excel Macro-Enabled Workbook).

Ответ 12

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

Ответ 13

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

Вот что я закончил: Application.Run( «‘» и WbName и «‘» и «! ModuleName.MacroName» )

Ответ 14

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

Ответ 15

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

Решение

Как мне найти решение?

Щелкните правой кнопкой мыши по флажку, а затем назначьте макрос вручную. Затем я проверил это, он работал отлично. Затем я заметил, что excel назначает макрос с использованием кодовых имен листов, а не фактических имен, которые мы установили внизу.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector