Excel vba подсчет комментариев на рабочем листе


Содержание

Excel vba подсчет комментариев на рабочем листе

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

95-2000 Ячейка A1 комментируется Иванов С.Г.
XP-2003 Ячейка A1, автор примечания: Иванов С.Г.

Автором же примечания (при его создании) считается пользователь, который был «указан» в меню Сервис команда Параметры закладка Общие и поле Имя пользователя: И если Вас не это устраивает и Вы хотите видеть автором другого человека(пользователя/компанию), то используйте любой из двух нижеопубликованных макросов, разумеется указав свою ячейку, а также текст и автора примечания.

Вариант I.
Вариант II.

  • Ответ :
    Автофигуры для комментария [Microsoft Excel 97, 2000]
  • Ответ :

    Вариант I. Вариант II. Вариант III. Вариант IV. Вариант V. Примечание :

  • — Если рабочий лист защищён, то второй макрос работать не будет, если Вы не будете использовать этот [FAQ42]
  • Ответ :

    Вариант I. Комментарий :

  • Если текст примечания содержит более 255 символов, то Вы получите только первые 255 символов, для получения всего текста, можно использовать : Вариант II. Если необходимо получить текст комментария или информацию о наличии комментария непосредственно с помощью формул, то Вы можете использовать пользовательскую функцию : Пример вызова вышеопубликованной авторской функции : Если информация о наличии/отсутствии комментария Вас не интересует, то можно использовать такой вариант UDF :
  • Ответ :

    Для того, чтобы изменить текст примечания, можно сначала извлечь уже существующий текст [FAQ119], затем внести необходимые изменения (применив соответствующие функции), после чего, в зависимости от способа получения текста, использовать один из двух нижеприведённых вариантов ( если текст содержит более 255 символов, но при этом, Вы выбрали именно первый способ, то рекомендую посетить сей [FAQ117] )

    Вариант I.
    Вариант II.
    Однако, в некоторых случаях, например, при замене/удалении текста, который начинается с определённой позиции и содержит некоторое количество символов, или при замене всех (или только первого, второго и т.д.) встречающихся в тексте слов/выражений, можно обойтись без лишних действий и использовании переменной.

    Пример I , предположим, что в ячейке «A1» находится примечание, содержащее следующий текст «Отправка груза задерживается» в котором слово «задерживается» должно быть заменено на «произошла»

    Вариант I.
    Вариант II.
    Комментарий : Рекомендую использовать необязательные именованные аргументы Length и Overwrite в тех случаях, когда необходимо сохранить текст, который может находиться после заменяемого.

    Пример II , предположим, что в ячейке «A1» находится примечание, содержащее следующий текст «Отправка груза произошла. /Сусликов Б.С./» и нам необходимо избавиться от подписи, т.е. «/Сусликов Б.С./»

    Вариант I.
    Вариант II.
    Комментарий : Обратите внимание на то, в каждом варианте используются два принципиально разных подхода к удалению ненужного текста, и если первый способ требует от нас точного указания позиции, то второй способ позволяет удалить сразу все ненужные вхождения. Впрочем, при необходимости, можно и указать номер вхождения, который требуется удалить(заменить), т.е. воспользоваться необязательным четвёртым и пятым аргументом соответственно. Пример III , предположим, что в ячейке «A1» находится примечание, содержащее следующий текст «Отправка груза, типа, произошла. Ответственный, типа, Климов П.Ю.» и нам необходимо избавиться от слов-паразитов, в данном случае «типа»

    Вариант I.
    Вариант II.
    Примечание : Начиная с MS Excel 2000 стандартную функцию рабочего листа Application.Substitute можно заменить на VB(A) функцию Replace

    Пример IV , предположим, что в ячейке «A1» находится примечание, содержащее некий текст, который необходимо дополнить следующим текстом «Добавлено : » Т.е. новый текст необходимо вставить в самое начало примечания, но при этом, не удалить уже имеющейся текст.

    Вариант I.
    Вариант II.
    Пример V , предположим, что в ячейке «A1» находится примечание, содержащее некий текст, в конец которого (причём с новой строки) нам нужно добавить следующий текст «Исправлено » & текущая_дата_время

    Вариант I.
    Вариант II.

  • Ответ :
  • Ответ :
  • Ответ :

    Для того, чтобы удалить все примечания / комментарии в рабочем листе, причём без цикла, можно использовать следующие методы :
    Комментарий : Если рабочий лист защищён и хотя бы одна ячейка, также защищена, то Вы получите ошибку, если лист защищён, а ячейки нет, то ошибка не возникнет, но и примечания удалены не будут . Комментарий : Если рабочий лист защищён, то комментарии удалены не будут .

    Примечание : Резюмируя, можно изречь следующее, если лист защищён, а комментарии / примечания удалить необходимо, то можно удалять их в цикле (см. семейство Comments )
    Ответ :

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

    Нижеприведённый код необходимо разместить в модуле рабочего листа, где предполагается осуществить поиск примечаний. Если использование модуля листа нежелательно, то код можно разместить в стандартном модуле, обязательно заменив ключевое слово Me ссылкой на нужный рабочий лист.

    Вариант I. В этом варианте используется поиск частичного совпадения LookAt:=xlPart

    Вариант II. В этом варианте используется поиск полного совпадения LookAt:=xlWhole , однако, применение символа подстановки * позволяет добиться результата, при котором будет найдено примечание, содержащее, например : «Этот текст не должен содержать опечяток, но . «

    Комментарий : Символы подстановки имеет смысл применять для поиска текста согласно заданному шаблону.
    Ответ :

    Вариант I.
    Вариант II.
    Вариант III.

    Если выделенный диапазон относительно небольшой, то можно и просто «перебрать» все ячейки этого диапазона, т.е.
    Тоже самое, но без проверки. Примечание :

  • — Если рабочий лист защищён, то первый макрос работать не будет, если Вы не будете использовать этот [FAQ42]
  • — Если выделить всего одну ячейку, то первый макрос «переберёт» все ячейки рабочего листа, содержащие комментарии/примечания (если таковые конечно имеются)
  • — Не забывайте проверять действительно ли выделена ячейка/диапазон ячеек [FAQ104]
  • — Для корректной работы обоих макросов нужно, чтобы фокус ввода находился в ячейках рабочего листа (это актуально только для MS Excel 97)

    Комментарий : Если в выделенном диапазон будет много ячеек с комментариями/примечаниями, то на время вывода текста в ячейки, имеет смысл отключить обновление экрана [FAQ43]
    Ответ :

    Вариант I. Вариант II. Вариант III. Внимание :
    — Worksheets(1) и Столбцы «B» (2) и «C» (3) выбраны исключительно в качестве примеров

    Примечание :

  • — Если рабочий лист защищён, то второй макрос работать не будет, если Вы не будете использовать этот [FAQ42]
  • Ответ :

    Для того, чтобы создать «архив» примечаний активной рабочей книги, можно сохранить текст примечаний, а также адреса ячеек, которые их содержат, например, в текстовый файл. Для этого, достаточно воспользоваться макросом ExportComments , а чтобы восстановить сохранённые комментарии, можно использовать макрос ImportComments
    Примечание :

  • — Если в тексте комментариев будет встречаться символ возврата каретки Chr(13) (vbCr) или комбинация символов возврат каретки + перевод строки Chr(13) + Chr(10) (vbCrLf), то возникнет ошибка, т.к. эти символы являются признаком окончания строки для инструкции Line Input. Чтобы избежать такой ошибки, можно воспользоваться другим вариантом.
  • — Для импорта примечаний необходимо, чтобы рабочий лист не был защищён в отношении об’ектов, т.к. в противном случае возникнет ошибка 1004, которой, впрочем, можно избежать, если использовать данный совет.
  • — Для импорта примечаний также необходимо, чтобы рабочий лист, после экспорта не был переименован.
  • — В данном примере, примечание «восстанавливается» (по сути, просто создаётся заново) только в случае его отсутствия. Проще говоря, если текст комментария был, допустим, «Отгрузка 22/07/2006» и Вы создали архив примечаний, а затем, изменили текст на «Отмена». После чего, восстановили все примечания, то всё равно останется отмена. Впрочем, при желании, всё это можно изменить.
  • — Обладатели новых версий вместо расширения «.xls» могут указать своё, например, «.xlsm», «.xlsx» или определять расширение книги программно, т.е. с помощью VBA.
  • — Обладатели же XL97 вместо Replace должны использовать Application.Substitute или WorksheetFunction.Substitute
  • Ответ :

    Для того, чтобы скопировать формат нужного примечания нужно использовать метод PickUp , а для того, чтобы применить его, необходимо использовать метод Apply Предполагается, что :
    — в активном рабочем листе есть, как минимум, пять комментариев
    — скопировать необходимо формат первого
    — а вставить формат необходимо в пятый комментарий
    Ответ : Актуально для MS Excel 97, 2000, XP

    Для того, чтобы сразу после активации ячейки, которая содержит примечание, отобразить это примечание на экране, достаточно скопировать весь нижеприведённый код и разместить его в модуле ThisWorkbook(ЭтаКнига) : При желании можно не только отобразить примечание, но и управлять его месторасполажением на экране, например : Комментарий : В последних двух примерах уже используется событие рабочего листа, которое в отличии от событии книги должно располагаться в модуле того листа, где предполагается отображать примечания.
    Ответ :

    Для того, чтобы программно скрыть, оставить только индикатор (небольшой красный треугольник, расположенный в верхнем правом углу ячейки) или отобразить все примечания(комментарии), достаточно воспользоваться свойством DisplayCommentIndicator об’екта Application
    Актуально для MS Excel 95
    В этой версии, как, впрочем, и в последующих, для скрытия примечаний и отображения только индикаторов, можно использовать и такой вариант :
    Комментарий : Если тоже самое необходимо проделать без применения VBA, то в меню Сервис выберите команду Параметры , и в появившемся стандартном диалоговом окне выделите закладку Вид и в разделе Примечания установите «флажок» напротив нужного вида.
    Ответ :

    Для того, чтобы с помощью VBA изменить ширину и высоту всех примечаний(комментариев) определённого рабочего листа, можно использовать нижеопубликованный макрос, где размер увеличивается на 25% Внимание : Worksheets(1) — первый рабочий лист активной книги, выбран исключительно в качестве примера и может быть заменён на любой другой рабочий лист.
    Ответ :

    Для того, чтобы суммировать данные только тех ячеек, что содержат нужное примечание(комментарий) можно воспользоваться нижеопубликованным вариантом. Обратите внимание на то, что в качестве критерия суммирования, используется текст примечания ячейки «A1» первого рабочего листа текущей рабочей книги. Это условие, конечно же, не является обязательным и Вы можете указать другую ячейку или даже выбрать её, например, с помощью диалогового окна.
    Ответ :

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

    Ответ :

    Если Вам необходимо к каждой ячейке создать примечание, содержащее текст шапки столбца (подписи самой верхней строки) и строк (значение самого левого столбца) то можно воспользоваться любым из представленных вариантов. Не забывая при этом, что во втором варианте(без дополнительного цикла) комментарий будет содержать не более 255 символов, однако именно второй вариант более шустрый.

    Объект Comment (Excel) Comment object (Excel)

    Представляет Примечание ячейки. Represents a cell comment.


    Примечания Remarks

    Объект comment является элементом коллекции Comments. The Comment object is a member of the Comments collection.

    Пример Example

    Используйте свойство comment объекта Range для возврата объекта comment . Use the Comment property of the Range object to return a Comment object. В приведенном ниже примере текст комментария изменяется в ячейке «ячейка». The following example changes the text in the comment in cell E5.

    Используйте Комментарии (index), где index — это номер комментария, чтобы возвратить один комментарий из коллекции комментариев . Use Comments (index), where index is the comment number, to return a single comment from the Comments collection. В следующем примере показано, как скрыть комментарий два на листе один. The following example hides comment two on worksheet one.

    Используйте метод аддкоммент объекта Range , чтобы добавить комментарий к диапазону. Use the AddComment method of the Range object to add a comment to a range. В приведенном ниже примере показано, как добавить комментарий к ячейке «1» на листе. The following example adds a comment to cell E5 on worksheet one.

    Методы Methods

    Свойства Properties

    См. также See also

    Поддержка и обратная связь Support and feedback

    Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

    VBA Excel. Рабочий лист (обращение, переименование, скрытие)

    Обращение к рабочим листам Excel из кода VBA. Переименование листов, скрытие и отображение с помощью кода VBA Excel. Свойства Worksheets.Name и Worksheets.Visible.

    Обращение к рабочим листам

    Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.

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

    Илон Маск рекомендует:  Что такое код getftime

    Обращение к рабочему листу в коде VBA Excel:

    • УникИмяЛиста — уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
    • N — индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
    • Имя листа — имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.

    Количество листов в рабочей книге Excel определяется так:

    Переименование листов

    В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.

    С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:

    expression.Name

    где expression — переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.

    Допустим, у нас есть лист с уникальным именем (Name) — Лист1, индексом — 1 и именем Name — МойЛист, которое необходимо заменить на имя — Реестр.

    Скрытие и отображение листов

    Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:

    expression.Visible

    где expression — переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:

    • False — лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
    • xlVeryHidden — лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2020.
    • True — лист становится видимым.

    Аналоги присваиваемых значений:

      False = xlH >Примеры:

    Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье.

    VBA Excel: примеры программ. Макросы в Excel

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

    Что такое VBA

    Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

    Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

    Объекты, коллекции, свойства и методы

    Именно с этими понятиями нужно разобраться тем, кто собирается работать в среде VBA. Прежде всего необходимо понять, что такое объект. В Excel в этом качестве выступают лист, книга, ячейка и диапазон. Данные объекты обладают специальной иерархией, т.е. подчиняются друг другу.

    Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

    Что касается понятия «коллекция», то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

    Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

    Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в «Эксель» используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами (1,1) т.е. A1.

    Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

    Как начать

    Прежде всего требуется создать файл и сохранить его, присвоив имя и выбрав тип «Книга Excel с поддержкой макросов».

    Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

    • в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
    • выбирают команду Mudule;
    • сохраняют, нажав на иконку с изображением floppy disk;
    • пишут, скажем так, набросок кода.

    Он выглядит следующим образом:

    Обратите внимание, что строка «’Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

    Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее). Конечно, тем, кто знаком с азами Visual Basic, будет намного проще. Однако даже те, кто их не имеет, при желании смогут освоиться достаточно быстро.

    Макросы в Excel

    За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

    Пример 1

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

    • открывают вкладку «Вид»;
    • переходят на пиктограмму «Макросы»;
    • жмут на «Запись макроса»;
    • заполняют открывшуюся форму.

    Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

    Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.

    • вновь переходят на строку «Макросы»;
    • в списке выбирают «Макрос 1»;
    • нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

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

    Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

    Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

    Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.


    Пример 2

    Циклы VBA помогают создавать различные макросы в Excel.

    Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 – cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

    За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

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

    Do While x1 0 Then Cells(1, 1).Value = 1

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

    Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.

    В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить

    Одинаковые переменные для всех примеров

    Dim lLastRow As Long ‘а для lLastCol можно применить тип Integer, ‘т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long

    определяя таким способом нам надо знать что:
    1 — это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
    Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.

    Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) — то результат будет неверный(ну или не совсем такой, какой ожидали увидеть вы)
    Определение последнего столбца через свойство End

    lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    1 — это номер строки, последнюю заполненную ячейку в которой мы определяем.

    Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.

    Определение последнего столбца через SpecialCells

    Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку — Row либо столбец — Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
    Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).

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

    Способ 3:
    Определение последней строки через UsedRange

    lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1

    Определение последнего столбца через UsedRange

    lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1

    • ActiveSheet.UsedRange.Row — этой строкой мы определяем первую ячейку, с которой начинаются данные на листе. Важно понимать для чего это — если у вас первые строк 5 не заполнены ничем, то данная строка вернет 6(т.е. номер первой строки с данными). Если же все строки заполнены — то вернет 1.
    • ActiveSheet.UsedRange.Rows.Count — определяем кол-во строк, входящих в весь диапазон данных на листе.
      Т.е. получается: первая строка данных + кол-во строк с данными — 1. Зачем вычитать единицу? Попробуем посчитать вместе: первая строка: 3. Всего строк: 3. 3 + 3 = 6. Вроде все верно, чего тут непонятного? А теперь выделите на листе три ячейки, начиная с 3-ей. Все верно. Ведь у нас в 3-ей строке уже есть данные. Думаю, остальное уже понятно и без моих пояснений.
    • То же самое и с ActiveSheet.UsedRange.Column, только уже не для строк, а для столбцов.

    Обладает всеми недостатками предыдущего метода. . Однако, можно перед определением последней строки/столбца записать строку: With ActiveSheet.UsedRange: End With
    Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены.

    Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая — следующая за ней. Т.е. к результату необходимо прибавить 1.

    Способ 4:
    Определение последней строки и столбца, а так же адрес ячейки методом Find

    Dim rF As Range Dim lLastRow As Long, lLastCol As Long ‘ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение Set rF = ActiveSheet.UsedRange.Find(«*», , xlValues, xlWhole, , xlPrevious) If Not rF Is Nothing Then lLastRow = rF.Row ‘последняя заполненная строка lLastCol = rF.Column ‘последний заполненный столбец MsgBox rF.Address ‘показываем сообщение с адресом последней ячейки Else ‘если ничего не нашлось — значит лист пустой ‘и можно назначить в качестве последних первую строку и столбец lLastRow = 1 lLastCol = 1 End If

    Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул — только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=»»), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.

    Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.

    небольшой практический код , который поможет вам понять, как использовать полученную переменную:

    Sub Get_Last_Cell() Dim lLastRow As Long Dim lLastCol As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row MsgBox «Заполненные ячейки в столбце А: » & Range(«A1:A» & lLastRow).Address lLastCol = Cells.SpecialCells(xlLastCell).Column MsgBox «Заполненные ячейки в первой строке: » & Range(Cells(1, 1), Cells(1, lLastCol)).Address MsgBox «Адрес последней ячейки диапазона на листе: » & Cells.SpecialCells(xlLastCell).Address End Sub

    Илон Маск рекомендует:  Что такое код alter domain

    А такой код выделит диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:

    Sub Copy_To_Last_Cell() Range(«A1:C» & Cells(Rows.Count, 1).End(xlUp).Row).Select End Sub

    А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:

    Sub Copy_To_Last_Cell() Range(«B1»).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub

    Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение «», Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.

    Статья помогла? Поделись ссылкой с друзьями!

    Использование VBA для подсчета количества скрытых (видимых) рабочих листов слева от активного листа (Excel)?

    Я создаю анкету в Excel, и меня попросили включить индикатор типа «Вы на листе X из Y» на каждой странице.

    Количество видимых листов варьируется в зависимости от ответа на некоторые ранние вопросы (нерелевантные наборы вопросов скрыты и т. Д.), Поскольку числитель и знаменатель в этом утверждении являются переменными.

    У меня есть ярлык (Label1) на каждой странице, на котором я могу написать подпись, мне удалось подсчитать общее количество видимых листов и привязать запись подписи к событию активации листа:

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

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

    Кто-нибудь знает, как я мог бы сделать это с помощью VBA?

    Формула номера листа и страницы в Excel

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

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

    Рассмотрим варианты, которые позволят нам узнать номера листов и страниц в автоматическом режиме.
    Однако прежде всего поймем в чем именно разница между листом и страницей в Excel.
    Лист Excel — это таблица, состоящая из ячеек структурированная в виде данных в строках и столбцах, а страница — это представление листа при печати. Другими словами, в зависимости от настроек печати, один лист может содержать несколько страниц, поэтому задачу поиска номеров листов и страниц будем решать по отдельности.

    Формула номера листа в Excel

    Начиная с версии Excel 2013 добавлена стандартная функция ЛИСТ, которая возвращает номер листа по имеющейся ссылке.
    Однако в более ранних версиях Excel стандартных функций, к сожалению, нет, поэтому для этого случая мы воспользуемся возможностями VBA и напишем пользовательскую функцию, которая по имени листа будет возвращать его порядковый номер.

    Функция ЛИСТ (начиная с Excel 2013)

    Синтаксис и описание функции:

    ЛИСТ(значение)
    Возвращает номер указанного листа.

    • Значение(необязательный аргумент) — название листа или ссылка, для которой нужно узнать номер листа.

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

    При работе с функцией обратите внимание, что формулы =ЛИСТ(«Лист2») и =ЛИСТ(A1) (при этом ячейка A1, например, содержит текст «Лист2») могут вернуть разные результаты, так как в первом случае аргумент представлен в виде текста (поиск листа по текстовому названию), а во втором — в виде ссылки (поиск листа по ссылающейся ячейке).

    Номер листа на VBA

    Для создания пользовательской функции перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), далее создаем новый модуль и добавляем в него следующий код:

    Использование VBA для подсчета количества нескрываемых (видимых) рабочих листов слева от активного листа (Excel)?

    Я строй анкеты внутри первенствует, и попросил, чтобы включить «Вы на лист X из Y» типа индикатора на каждой странице.

    Количество видимых листов варьируется в зависимости от ответа на некоторые из ранних quesitons (нерелевантные наборы вопросов скрыты, и т.д.), в качестве такого как числителя и знаменателя в этом заявлении является переменными.

    У меня ярлык (Label1) на каждой странице, которую я могу написать заголовок, я сумел подсчитать общее число видимых листов и связать заголовок записи в случае активации листа:

    Это правильно пишет знаменатель в каждой метки на каждом листе.

    Для того чтобы найти числитель, хотя, мне нужно, чтобы иметь возможность найти количество нескрываемых листов слева от активного листа.

    Кто-нибудь знает, как я мог бы сделать это с помощью VBA?

    Макросы: пусть Excel работает за вас

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


    Как записать самый простой макрос?

    Для начала запишем самый легкий макрос — зададим в ячейке А1 формат вида 12 345:

    • Открываем новую книгу, в ячейке А1 набираем шестизначное число 123456. Сейчас оно выдается без разделителей разрядов. Запишем макрос, который ставит эти разделители.
    • Заходим на панели инструментов в закладку Вид*, находим кнопку Макросы, жмем Запись макроса. В появившемся окне задаем имя макроса и книгу, в которой хотим этот макрос сохранить.

    Важно

    Запустить макросы можно только из открытых книг, поэтому если вы планируете использовать записанные вами макросы довольно часто, стоит использовать специальную книгу макросов, которая автоматически открывается вместе с запуском сеанса Excel.

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

    • Выбираем Сохранить в… —Личная книга макросов и нажимаем Ок (рис. 1).

    Рис. 1. Запись макроса в личную книгу макросов

    • Записываем в макрос действия, которые хотим выполнить: вызываем контекстное меню Формат ячеек (можно воспользоваться комбинацией клавиш Сtrl+1) и задаем нужный нам формат числа: на закладке Число идем в блок (все форматы) и выбираем там формат вида # ##0.

    К сведению

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

    • На закладке Вид — Макросы выбираем пункт Остановить запись.

    Второй, более быстрый способ остановить запись макроса — нажать на появившийся в левом нижнем углу синий квадратик (рис. 2.).

    Мы рекомендуем

    Всегда обращайте внимание на этот квадратик: если он появился на панели, значит, сейчас идет запись всех ваших действий в макрос. Не забывайте вовремя останавливать запись макроса, чтобы потом его не пришлось переделывать.

    Проверяем, что макрос записан и работоспособен:

    • в ячейку А2 вбиваем любое шестизначное число;
    • запускаем макрос одним из двух способов: на закладке Вид — Макросы выбираем пункт Макросы или нажимаем комбинацию клавиш Alt+F8, находим в списке наш макрос и нажимаем кнопку Выполнить.

    Рис. 2. Форматирование числа и остановка записи макроса

    Итак, вы записали свой первый макрос! Примите поздравления. Теперь давайте познакомимся с личной книгой макросов и синтаксисом написания команд для макроса.

    Личная книга макросов

    По умолчанию Excel не отображает личную книгу макросов. Чтобы убедиться, что она открыта, выбираем на вкладке Вид кнопку Отобразить — в появившемся окне должна быть книга под именем PERSONAL.

    Мы убедились, что книга открыта, но отображать ее не будем, чтобы потом по ошибке не закрыть ее. По сути, в этой книге нас интересует так называемый Исходный текст — блок, в котором записываются макросы. Чтобы увидеть это окно, нажмите клавиши Alt+F11 или кликните правой кнопкой мыши на ярлыке любого листа Excel и выберите в контекстном меню Исходный текст. Откроется окно VBA-кодирования в Excel (рис. 3). Оно состоит из двух блоков:

    1. В левой части экрана окно Project – VBAProject — это проводник, в котором отображаются все открытые в данный момент книги Excel (даже если вы их не видите, как, например, книгу Personal). Работа с этим блоком аналогична работе в обычном проводнике — двойной клик по наименованию книги раскрывает ее содержимое. Нас интересует блок Modules — Module1. Кликаем левой кнопкой мыши дважды по этому объекту.

    2. В правой части экрана откроется блок записи и редактирования макросов. Здесь уже автоматически записался Макрос1. Рассмотрим на его примере основную канву макроса.

    Рис. 3. Окно VBA-кодирования в Excel

    Синтаксис макроса

    Макросы — это команды, написанные на языке VBA (Visual Basic for Applications). И синтаксис кода макроса не отличается от записи кода в Visual Basic.

    Любой макрос имеет следующий вид:

    ‘ комментарии к макросу — они нужны для вас, VBA не воспринимает такие строки как команды

    команды, написанные на языке VBA

    3 обязательных блока макроса:

    1. Начало макроса. Всегда начинается с команды Sub. Далее идет имя макроса — оно может быть на русском языке, но не должно содержать пробелы и специальные символы.

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

    2. Блок команд. В нашем примере он состоит из одной строки: Selection.NumberFormat = «#,##0»

    Каждая команда должна начинаться с новой строки. Если текст команды очень длинный и не помещается на экране, его можно разбить на несколько строк, заканчивая строку символом нижнего подчеркивания _ (далее в примере мы это увидим).

    3. Конец макроса. Всегда обозначается как End Sub.

    Есть и один необязательный блок — это комментарии, которые вы можете оставлять в любом месте внутри кода макроса, поставив перед началом комментариев знак апострофа ‘. Например, вы можете описать, что именно делает тот или иной макрос.

    Обратите внимание!

    Если вы хотите разместить комментарии в несколько строк, каждую новую строку надо начинать с апострофа.

    Теперь запишем более сложный макрос и научимся понимать текст его кода.

    Например, информационная система выдает отчет «Бюджет на месяц» без выделения групповых значений цветом или шрифтом.

    • выделить групповые строки полужирным шрифтом;
    • отформатировать на печать — расположить отчет по центру листа, задать масштаб 75 %, вывести в колонтитулы название отчета (рис. 4).

    Рис. 4. Изменения после написания макроса

    Запишем алгоритм форматирования отчета в макрос.

    Нажимаем кнопку записи макроса и выполняем следующие действия:

    • Даем макросу имя Форматирование_БДР, в блоке описания записываем, что будет делать этот макрос (например, Выделяет жирным курсивом итоги, форматирует на печать). Жмем Ок.
    • Выделяем столбцы А:С, ставим автофильтр — на закладке Данные находим кнопку Фильтр.
    • По столбцу КОД задаем условие не содержит точку: Текстовые фильтры — Не содержит и в поле текста ставим символ точки без пробелов (рис. 5).

    Рис. 5. Использование автофильтра по столбцу «КОД»

    • Выделяем отфильтрованный диапазон и задаем ему полужирный шрифт.
    • Снимаем автофильтр (повторное нажатие на закладке Данные кнопки Фильтр).
    • Заходим в меню форматирования на печать (Кнопка Файл/Office — Печать — Предварительный просмотр — Параметры страницы) и задаем там три параметра:

    1) на вкладке Страница задаем масштаб 75 %;

    2) на вкладке Поля отмечаем пункт Горизонтально в блоке Центрировать на странице;

    3) на вкладке Колонтитулы создаем верхний колонтитул с текстом Бюджет на январь.

    • Выходим из параметров страницы.
    • Заканчиваем запись макроса.
    • Нажимаем Alt+F11 и смотрим, что получилось (см. рис. 4).

    Код этого макроса уже гораздо длиннее и непонятнее, но легко читаем для знающих английский язык и азы программирования в VBA.

    Правила написания команд в VBA

    Любая команда макроса состоит из двух блоков, разделенных точкой:

    Объект.Действие с объектом или свойство объекта

    Объектами в Excel являются, например:

    • книга: WorkBook, ActiveWorkbook;
    • лист, листы: WorkSheet, ActiveSheet, Sheets;
    • ячейка: Cells(1,1) — в скобках указываются номера строки (Row) и столбца (Column) ячейки на листе;
    • диапазон ячеек (может быть и одна ячейка): Range(«А1:С5»), Range(«А1»);
    • строки (Rows);
    • столбцы (Columns);
    • выделение (Selection) — выделенный в данный момент диапазон (это может быть как несколько смежных ячеек, так и смежные строки или столбцы).

    Примеры действий с объектами:

    • ActiveWorkbook.Save — сохранить рабочую книгу (та, которая была активна в момент вызова пользователем макроса);
    • Sheets(«Лист3»).Name = «Отчет» — переименовать «Лист3» в «Отчет»;
    • Sheets(«Отчет»).Activate — перейти на лист с названием «Отчет»;
    • Range(«А1»).Copy — скопировать в буфер обмена данные из ячейки А1;
    • Rows(«13:13»).Delete Shift:=xlUp — удалить строку 13 со сдвигом вверх.


    Примеры свойств объектов:

    • ActiveCell.FormulaR1C1 = «БДР» — в выделенной (активной) ячейке записан текст «БДР»;
    • ActiveCell.Row *.*», _

    Задать критерий отбора «не содержит точку»

    Operator:=xlAnd

    Продолжение команды из предыдущей строки.

    Обратите внимание: предыдущая строка закончилась символом нижнего подчеркивания _, значит, текст команды не уместился на одну строку и окончание команды перенесено на следующую

    Range(«A1:C34»).Select

    Выделить (Select) объект Диапазон (Range) А1:С34.

    Обратите внимание: какой бы длины ни был ваш следующий отчет, для которого вы будете применять этот макрос, выделится всегда только диапазон до 34 строки!

    Как сделать этот диапазон любой длины — обсудим немного позже

    Selection.Font.Bold = True

    Для выделенного диапазона (Объект Selection) установить свойство «полужирный шрифт» (Font.Bold = True). Если нужно отменить какое-то свойство, пишем False

    Selection.AutoFilter

    Снять автофильтр (при записи макроса это было повторное нажатие кнопки Фильтр на закладке Данные)

    With ActiveSheet.PageSetup

    Начало процедуры With (используется, если далее перечисляются свойства или действия с одним и тем же объектом для компактности записи кода).

    Для объекта ActiveSheet (Текущий лист) применить следующие параметры свойства PageSetup (Параметры печати):

    .PrintTitleRows = «»

    Печатать на каждой странице сквозные строки — пусто (то есть данное свойство не задано пользователем)

    .PrintTitleColumns = «»

    Печатать на каждой странице сквозные столбцы — пусто

    End With

    Окончание процедуры With

    ActiveSheet.PageSetup.PrintArea = «»

    Заданная область печати — пусто (то есть пользователь не ограничил область печати, следовательно, на экран будет выведено все, что есть на листе)

    With ActiveSheet.PageSetup

    Начало процедуры With

    Для объекта Текущий лист применить следующие параметры печати (цветом выделены те, которые мы изменили по сравнению со стандартными):

    .LeftHeader = «»

    Левый верхний колонтитул — пусто

    .CenterHeader = «Бюджет на январь»

    Центральный верхний колонтитул — задан текст пользователем

    .RightHeader = «»

    Правый верхний колонтитул — пусто

    .LeftFooter = «»

    Левый нижний колонтитул — пусто

    .CenterFooter = «»

    Центральный нижний колонтитул — пусто

    .RightFooter = «»

    Правый нижний колонтитул — пусто

    .LeftMargin = Application.InchesToPoints(0.708661417322835)

    Размеры левого поля

    .RightMargin = Application.InchesToPoints(0.708661417322835)

    Размеры правого поля

    .TopMargin = Application.InchesToPoints(0.748031496062992)

    Размеры верхнего поля

    .BottomMargin = Application.InchesToPoints(0.748031496062992)

    Размеры нижнего поля

    .HeaderMargin = Application.InchesToPoints(0.31496062992126)

    Размеры верхнего колонтитула

    .FooterMargin = Application.InchesToPoints(0.31496062992126)

    Размеры нижнего колонтитула

    .PrintHeadings = False

    Не печатать заголовки строк и столбцов (False — пользователь не отметил этот пункт)

    .PrintGr >

    Не печатать сетку

    .PrintComments = xlPrintNoComments

    Не печатать примечания

    .PrintQuality = 600

    Качество печати — 600 точек на дюйм

    .CenterHorizontally = True

    Центрировать на странице горизонтально (True — пользователь отметил этот пункт)

    .CenterVertically = False

    Не центрировать по вертикали

    .Orientation = xlPortrait

    Ориентация страницы — книжная

    .Draft = False

    Пользователь не отметил пункт Черновая в блоке Печать

    .PaperSize = xlPaperA4


    Размер бумаги А4

    .FirstPageNumber = xlAutomatic

    Номер первой страницы — автоматически

    .Order = xlDownThenOver

    Последовательность вывода страниц: вниз, потом вправо (пункт в блоке Печать)

    .BlackAndWhite = False

    Пользователь не отметил пункт Черно-белая в блоке Печать

    .Zoom = 75

    .PrintErrors = xlPrintErrorsDisplayed

    Пункт в блоке ПечатьОшибки ячеекКак на экране

    .OddAndEvenPagesHeaderFooter = False

    Пользователь не задавал разные колонтитулы для четных и нечетных страниц (флажок в блоке Колонтитулы)

    .DifferentFirstPageHeaderFooter = False

    Пользователь не задавал отдельный колонтитул для первой страницы (флажок в блоке Колонтитулы)

    .ScaleWithDocHeaderFooter = True

    Флажок в блоке Колонтитулы – Изменять вместе с масштабом страницы отмечен пользователем

    .AlignMarginsHeaderFooter = True

    Флажок в блоке Колонтитулы – Выровнять относительно полей страницы отмечен пользователем

    .EvenPage.LeftHeader.Text = «»

    Текст колонтитулов для четных и первой страниц не задан

    .EvenPage.CenterHeader.Text = «»

    .EvenPage.RightHeader.Text = «»

    .EvenPage.LeftFooter.Text = «»

    .EvenPage.CenterFooter.Text = «»

    .EvenPage.RightFooter.Text = «»

    .FirstPage.LeftHeader.Text = «»

    .FirstPage.CenterHeader.Text = «»

    .FirstPage.RightHeader.Text = «»

    .FirstPage.LeftFooter.Text = «»

    .FirstPage.CenterFooter.Text = «»

    .FirstPage.RightFooter.Text = «»

    End With

    Окончание процедуры With

    End Sub

    Окончание кода макроса

    Редактирование макроса

    Как видите, код макроса легко читаем и понятен. Кроме того, нам не надо нагромождать его лишними строками: так как в параметрах печати мы меняли только три пункта, остальные строки можем удалить (они будут установлены по умолчанию). Удалим ненужные строки, поставим комментарии и в итоге получим вот такой элегантный код:

    ‘ Макрос выделяет жирным курсивом итоги, форматирует отчет на печать

    ‘ Выделяем столбцы и ставим фильтр по столбцу КОД

    ActiveSheet.Range(«$A$1:$C$34″).AutoFilter Field:=1, Criteria1:=»<>*.*», _

    ‘ Выделяем отфильтрованный диапазон полужирным шрифтом, снимаем фильтр

    ‘ Форматируем на печать: верхний колонтитул, центрирование по горизонтали, масштаб 75 %

    .CenterHeader = «Бюджет на январь»

    Ввод в код макроса функций и процедур

    Теперь усложним код макроса, задав ему два параметра:

    1. Название месяца отчета запрашиваем у пользователя.

    2. Последняя строка отчета БДР (в случае если она плавающая) рассчитывается в коде макроса.

    Функция InputBox

    Чтобы запросить у пользователя месяц отчета, воспользуемся функцией Inputbox, которая выводит диалоговое окно, в котором пользователь может самостоятельно задать значение переменной, используемой в коде. Синтаксис функции InputBox:

    Zapros = InputBox(«Текст запроса», , ,…)

    где Zapros — введенная вами переменная (имя придумываете вы сами), а в скобках через запятую перечисляются аргументы функции.

    Обратите внимание: первый аргумент является обязательным, то есть любое диалоговое окно должно сопровождаться пояснительным текстом, чего вы ждете от пользователя. Следующие аргументы обязательными не являются и их можно не указывать. То есть если вы хотите задать значение по умолчанию, но не хотите писать текст в шапке диалогового окна, разделите первый и третий аргументы двумя запятыми, как это будет сделано в нашем примере (см. далее).

    Для удобства присвойте полученное значение функции InputBox какой-нибудь введенной вами переменной, особенно если в коде макроса вы будете несколько раз использовать значение этой функции.

    Важно

    Имена вводимых переменных не должны совпадать с уже занятыми VBA словами под название объектов, свойств или функций!

    В нашем примере присвоим результат вызова функции InputBox переменной Mes.

    Например, нельзя завести свою переменную Range, Cells или Month — компилятор VBA предупредит вас, что делать этого нельзя, и не запустит макрос, пока вы не устраните ошибку (рис. 6).

    Рис. 6. Пример ошибки при заведении переменной

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

    Итак, приступим к редактированию кода макроса. Добавим в самое начало кода макроса строки:

    ‘ Запрашиваем у пользователя месяц отчета

    Mes = InputBox(«Введите название месяца отчета», , «Январь»)

    Теперь при запуске макроса будет выводиться диалоговое окно, в котором пользователь самостоятельно сможет указать, за какой месяц этот отчет (рис. 7).

    Рис. 7. Диалоговое окно для указания месяца

    С помощью функции InputBox можно запросить у пользователя и длину отчета. Но мы научим Excel высчитывать ее самостоятельно. Для этого нам потребуется процедура While.

    Процедура While

    Используется в коде, если надо сделать одинаковые действия несколько раз подряд до тех пор, пока выполняется какое-либо условие. Синтаксис процедуры While:

    Условие может состоять из одного выражения или содержать несколько, перечисленных через оператор AND (то есть оба условия должны быть выполнены) или OR (достаточно выполнения только одного из перечисленных условий). Также условие обязательно должно содержать переменную-счетчик (это может быть номер строки или столбца ячейки, значения которой вы проверяете).

    В список команд обязательно должна входить команда наращивания переменной-счетчика, иначе процедура зациклится (так как она постоянно будет сравнивать одно и то же значение) и макрос придется прерывать принудительно.

    Если макрос ушел в цикл, прервите его с помощью комбинации клавиш Ctrl+Break и либо прекратите макрос (кнопка End), либо зайдите в код макроса и исправьте ошибку (кнопка Debug). Чтобы макрос не уходил в цикл, рекомендуется включить в блок условий «защиту от дурака». Например, добавляем в условие проверку, чтобы значение счетчика не превышало определенной, заведомо достаточной для нас величины.


    Рассмотрим применение процедуры While для поиска конца отчета БДР.

    Как видно на рис. 4, последняя строка отчета имеет код «500». Напишем процедуру, которая будет проверять значения в ячейках столбца «А» и остановит свои действия, когда найдет ячейку с кодом «500».

    Обратите внимание!

    Excel иногда воспринимает числа как текст, поэтому включим два условия проверки значения ячейки и добавим «защиту от дурака» на случай, если в форме отчета случайно затрется код последней строки.

    Помним, что все текстовые значения надо брать в кавычки. Числа записываем без кавычек:

    ‘ Ищем последнюю строку отчета

    Dim Row_End As Integer ‘ Вводим переменную «счетчик номера строки»

    Row_End = 1 ‘ Присваиваем ей номер 1

    ‘ Начинаем процедуру поиска последней строки отчета по коду «500»

    Do While Cells(Row_End, 1).Value <> «500» And Cells(Row_End, 1).Value <> 500 And Row_End «500» And Cells(Row_End, 1).Value <> 500 And Row_End *.*», _

    ‘ Выделяем отфильтрованный диапазон полужирным шрифтом, снимаем фильтр

    ‘ Форматируем на печать: верхний колонтитул, центрирование по горизонтали, масштаб 75 %

    .CenterHeader = «Бюджет на » & Mes

    Теперь макрос стал более универсальным. Добавим в него еще одну «защиту от дурака».

    Если нам принципиально, правильно ли макрос нашел последнюю строку отчета, после окончания процедуры While (строка Loop) можно добавить блок проверки значения Row_End и запроса у пользователя подтверждения на продолжение макроса.

    Для этого изучим функцию MsgBox, процедуру IF и команду Exit Sub.

    Функция MsgBox

    MsgBox — еще один способ общения с пользователем: сообщения ему какой-то информации по ходу выполнения макроса или запрос у него подтверждения по дальнейшим действиям макроса путем нажатия на кнопки вида Yes, No, Ок, Cancel.

    Она имеет два вида записи:

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

    MsgBox «Текст сообщения», , ,…

    На экране отобразится диалоговое окно, и после нажатия пользователем кнопки Ок продолжится выполнение макроса (рис. 8).

    Рис. 8. Первый вид записи функции MsgBox

    Как и в случае с функцией InputBox, обязательным здесь является только первый аргумент — Текст сообщения. Остальные можно даже не указывать, сократив вид записи функции до вида:

    MsgBox «Текст сообщения»

    2. Нам важно, что ответил пользователь, мы записываем его ответ в значение какой-то переменной (рис. 9). В этом случае аргументы функции заключаются в скобки, а перед функцией обязательно должна стоять переменная, в которую мы записываем, на какую кнопку нажал пользователь:

    YesNo = MsgBox(«Текст сообщения», , ,…)

    Рис. 9. Второй вид записи функции MsgBox

    Варианты вида кнопок:

    • vbOKOnly — только кнопка ОК;
    • vbOKCanсel — кнопки ОК и Cancel;
    • vbYesNo — кнопки Yes и No;
    • vbYesNoCancel — кнопки Yes, No и Cancel.

    Соответственно в зависимости от нажатой кнопки значения функции MsgBox могут быть vbOK, vbCancel, vbYes или vbNo.

    Процедура If

    С помощью данной процедуры можно разбить команды кода на две ветки в зависимости от выполнения заданного условия. Синтаксис процедуры If:

    Обязательным к заполнению является только условие. Список команд в обоих блоках заполнять не обязательно. В зависимости от ваших задач вы можете заполнить оба блока или только один из них. Условие может состоять из одного выражения или содержать несколько выражений, перечисленных через оператор AND (то есть оба условия должны быть выполнены) или оператор OR (достаточно выполнения только одного из перечисленных условий).

    Команда Exit Sub

    Прекращает выполнение макроса. Обычно ее используют в ветках процедуры If при проверке допустимости дальнейшего исполнения макроса. Обратите внимание: ни одно действие после команды Exit Sub выполнено не будет. Поэтому если вы хотите сообщить пользователю о принудительном прекращении процедуры, команду MsgBox нужно расположить выше команды Exit Sub.

    Итак, объединим новые знания в код «защиты от дурака» и запишем его после строки Loop:

    ‘ Проверяем, дошла ли процедура While до «критичной» строки 100

    ‘ и спрашиваем подтверждение о продолжении выполнения макроса

    If Row_End = 100 Then

    YesNo = MsgBox(«Поиск последней строки отчета дошел до 100, продолжаем?», vbYesNo)

    If YesNo = vbNo Then ‘ Если нажата кнопка «No», то…

    MsgBox «Процедура прервана пользователем» ‘ 1. выдаем сообщение для пользователя,

    Exit Sub ‘ 2. останавливаем макрос

    В нашем коде прошло вложение одной процедуры If в другую:

    • сначала мы проверили, дошел ли счетчик Row_End до значения 100;
    • если дошел, мы спрашиваем у пользователя, продолжать ли нам выполнение макроса;
    • если пользователь ответил «нет», останавливаем макрос.

    В обеих процедурах If мы опустили второй блок (else): если Row_End не достиг значения 100 или пользователь ответил «да», макрос просто покинет процедуру If и продолжит выполнять команды, написанные ниже.

    Каждая процедура If должна заканчиваться командой End If. Если количество строк с командой If не будет соответствовать количеству строк с командой End If, компилятор VBA сообщит об ошибке и вам придется искать, где вы потеряли конец процедуры If.

    Поэтому рекомендуется сразу писать обе строки, а потом уже наполнять процедуру командами.

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

    Подведем итоги

    Мы с вами научились:

    • записывать макросы через команду Вид Макросы Запись макроса;
    • редактировать автоматически записанный макрос, удалять из него лишние команды;
    • унифицировать код макроса, вводя в него переменные, которые макрос запрашивает у пользователя или рассчитывает самостоятельно,

    а также изучили функции InputBox и MsgBox, процедуры While и If, команду Exit Sub.

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

    К. И. Панькова,
    руководитель ПЭО ХК «Домоцентр»

    Excel vba подсчет комментариев на рабочем листе

    Есть книга stock.xls, состоящая из нескольких листов. Я хочу с помощью макроса определить, на каком листе имеется текст, содержащийся в текущей ячейке другой открытой книги.
    Записал макрос (Ctrl-C, Ctrl-Tab, Ctrl-F, Ctrl-V, Enter), добавил конструкции For Each и With.
    Задумка такая: если текст находится на листе, активируется ячейка с найденным текстом и останов.
    Если текст не находится на листе, переход по ошибке на Next и поиск в следующем листе.

    Что получается. Если текст находится на первом листе книги stock.xls, то все ОК.
    Но если происходит переход к следующему листу, получаю

    Run-time error ’91’: Object variable or With block variable not set

    на команде Cells.Find
    Почему? Как исправить?
    Я уж и On Error в цикл запихал, и перед Cells.Find точку ставил — не помогает

    1. kiwi , 14.09.2003 12:05
    Что получается. Если текст находится на первом листе книги stock.xls, то все ОК.
    А если не находится, то Cells.Find возвращает значение Nothing. А ты его пытаешься Activate. Отсюда ошибка.
    2. Nosorog , 14.09.2003 20:19
    kiwi
    Спасибо, заработало.

    А можно сделать так, чтобы листы не мелькали на экране?
    То есть чтобы поиск шел без активации листа, а потом активировался лист с нужной ячейкой?

    3. Фдуч , 15.09.2003 03:40
    Nosorog
    А можно сделать так, чтобы листы не мелькали на экране?
    Application.ScreenUpdating = False
    4. Nosorog , 15.09.2003 12:40
    Фдуч
    Спасибо, работает.

    А все же интересно получается, что попытка применить Activate к Nothing — более серьезная ошибка, чем, например, деление на 0. То есть эту ошибку я не могу обработать по On Error .

    5. kiwi , 15.09.2003 19:35
    Nosorog
    А можно сделать так, чтобы листы не мелькали на экране?
    Можно их не активировать при поиске. Активировать только лист с найденным значением.

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

    Илон Маск рекомендует:  Циклы в Sass
    Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL