Evolcom.ru

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

Как очистить содержимое поля со списком с кодом VBA в Excel?

Как работать с Excel через VBA или объектная модель Excel

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

Структура объектов

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

Самым важным объектом, вершиной нашей структуры, является сам Excel — объект Application. Какие объекты включены в Excel? Правильно, книги (файлы Excel), поэтому объект Application содержит коллекцию книг — объект Workbooks. Из чего состоят книги — рабочие листы и диаграммы, соответственно — в объекте Workbooks мы видим Worksheets, а в объекте Charts — Charts. Объект Sheets состоит из объектов Rows, Columns, Cells и Rows, Columns and Ranges.

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

Объектная модель Excel

Объекты и коллекции объектов

Коллекции объектов относятся к числу тех, которые мы перечислили выше. Коллекция объектов — это группа связанных объектов (или классов). Примером может служить коллекция Workbook, которая содержит список всех активных книг (объекты Workbook). А коллекция Worksheet s состоит из листов книги (объекты Worksheet), и так далее. Как правило, все коллекции заканчиваются буквой S.

Следующий код показывает, как ссылаться на определенные элементы коллекции в VBA.

Можно указать порядковый номер (начиная с 1) элемента в коллекции. Мы обращаемся к 3-й странице книги с помощью Worksheets(3).

Мы также можем указать название листа в кавычках Worksheets("Название листа").

Аналогичным образом можно обратиться к конкретной книге Workbook("Названием книги").

Чтобы сослаться на объект Excel (лист или ячейку), необходимо указать полный путь к объекту на основе модели объекта.

Полная ссылка для ячейки A1, например, будет выглядеть так:

На самом деле полный путь писать не обязательно. Application — можно практически всегда не указывать. Workbooks("Название книги") — можно не указывать, если необходимо обратиться к ячейке из активной книги, а Worksheets("Название листа") можно не писать в случае если код должен выполнять действия на активном листе. Т.е. в можно упростить до:

Но всегда помните, что это будет ячейка активного листа.

Свойства объектов

Все объекты имеют свойства и методы. Например, объект Range (диапазон ячеек) имеет свойство Value, которое содержит значение.

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

В следующем коде свойству Value присваивается значение 12345.

Проверьте, чтобы эти функции работали в вашем файле.

Кстати, есть свойства объектов, которые доступны только для чтения. Т.е. вы сможете только получить значения этих свойств, но не сможете присвоить им другие значения. Например, свойство Range("A1").Address которое содержит адрес ячейки. При попытке записать новое значение в такое свойство будет возникать ошибка.
Или же свойство Worksheets.Count — вы можете только получить количество листов, но не можете изменить количество листов, задав новое свойство Count. Для этого существуют методы объектов.

Методы объектов

Методы выполняют некоторые действия над объектом, в отличие от свойств, которые просто хранят значения. Например, Worksheets. Новые чистые листы добавляются в книгу с помощью метода Add. Второй пример — метод Clear, который позволяет удалить содержимое ячеек. Данные и форматы будут очищены из ячеек с A1 по C10. Для этого будет выполнен следующий код.

Читайте так же:
Как предотвратить ввод специальных символов в Excel?

Существует аналогичная функция, которая не удаляет форматирование ячеек, а только очищает значения

Методы могут иметь дополнительные аргументы. Например, метод AddComment имеет обязательный аргумент Text, в который нужно передать текст комментария к ячейке.

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

Приемы и лайфхаки

Автоматическое заполнение кода по начальным символам

Часто, особенно в начале, вы не будете помнить точное название всех объектов, но вы будете помнить первые несколько символов. В редакторе кода вы можете использовать его для отображения списка имен и автоматической регистрации объекта по его первым символам. Для этого используйте сочетание клавиш Ctrl+J, и вы увидите список предложенных вариантов.

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

Когда вы пишете свои программы, редактор кода VBE запрашивает список свойств и методов объекта. Чтобы отобразить этот список, просто введите объект, например Worksheets, и поставьте точку, после чего отобразится список свойств и методов. Выберите нужный с помощью мыши или стрелок. Вы также можете начать вводить название свойства, и редактор кода выберет нужное. Когда вы сделаете свой выбор, нажмите клавишу Tab.

Явно указывайте тип переменных

Вы должны явно указать тип локальных переменных, если хотите воспользоваться предыдущим советом. Например, если вы хотите создать переменную MySheet, в которой вы хотите хранить текущий лист. Свойства и методы таких переменных не будут отображаться. Однако стоит добавить строку кода и явно указать тип переменной:

И рельсы снова заработают.

Быстрый просмотр справки

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

Чтобы быстро просмотреть информацию об интересующем вас объекте, свойстве или методе, просто установите курсор на интересующий вас элемент и нажмите клавишу F1. В окне браузера появится официальная справка по этому элементу.

Поиск по объектной модели

Также вначале пути у вас постоянно будут возникать вопрос "А как называется свойство, которое отвечает за. ". Иногда мы и не заем есть ли такое свойство/метод вообще. Хороший вариант изучения — просмотр объектной модели. Переходите в редактор кода VBA и нажимайте клавишу F2. Откроется окно с поиском по объектной модели. Тут вы найдете все имеющиеся свойства, методы, события и прочие элементы в модели. Просматривайте их, как правило в названии элемента заложена его суть и вы найдете то что искали. А в процессе поиска будете запоминать и другие элементы, которые будут попадаться на глаза.

Рассмотрим пример. Допустим, нам нужно защитить лист, но мы не знаем точно, как называется свойство или метод.

Поиск по объектной модели

  1. Откройте модель объекта с помощью клавиши F2.
  2. Поскольку мы хотим защитить электронную таблицу, логично предположить, что это метод в объекте электронной таблицы. Давайте введем его в строку поиска и нажмем клавишу просмотра.
  3. В результатах поиска мы выбираем наш объект Worksheet и просматриваем его элементы.
  4. Мы находим метод Protect.
  5. Вы также можете увидеть описание всех аргументов данного метода
  6. Если что-то непонятно, вы можете, конечно, нажать F1 и открыть справку по данному методу с подробным описанием каждого аргумента.

На этом мы заканчиваем эту статью, а в следующей мы более подробно поговорим о самом распространенном объекте Excel — объекте Range (Диапазон).

Элемент управления Список

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

Читайте так же:
Как повернуть круговую диаграмму в Excel?

Список может состоять из одного или нескольких столбцов, однако при выборе элемента списка в связанное с ним поле таблицы передается только одно значение, например в приведенных на рис. 9.14 списках это "КодТипа" (CategoryID) и "КодСотрудника" (EmployeeID), которые в списках даже не отображаются. Внешний вид списка и его содержимое определяются его свойствами, которые можно задать вручную, а можно воспользоваться мастером при размещении списка в форме, который, запросив нужную информацию, определит большую часть свойств автоматически. Создание списка с помощью мастера уже демонстрировалось в первой части книги. Мастер списков очень похож на Мастера, с помощью которого создаётся элемент Поле со списком(см. разд. "Создание и удаление элементов управления" гл. 5). Для того чтобы список правильно работал, нужно корректно определить его важнейшие свойства — это первые восемь свойств на рис. 9.15. Соответственно, если он работает неправильно, именно эти свойства нужно проверять в первую очередь.

Рис. 9.14. Элементы управления списком

Рисунок 9.15. Перечислите свойства элемента управления

  • Имя (Name) — название данного элемента управления. Нужно дать ему осмысленное название, т. к. оно используется во всех ссылках на этот элемент управления в выражениях, программах и макросах.
  • Данные (Control Source) — название поля в таблице, которое будет содержать выбранное в списке значение.
  • Следующие два свойства: Тип источника строк (Row Source Type) и Источник строк — (Row Source) определяют источник данных для элементов списка. В приведенном примере в качестве источника данных используется таблица "Типы" (Categories), поэтому в свойстве Тип источника строк (Row Source Type) выбрано значение Таблица или запрос (Table/Query), а в качестве значения свойства Источник строк (Row Source) указана инструкция SQL, которая выбирает нужные данные из таблицы (поля "КодТипа" и "Катерогия"): SELECT Типы.КодТипа, Типы.Категория FROM Типы; для английской версии Access: SELECT Categories.CategorylD, Categories.CategoryName FROM Categories;
  • Чтобы элементы в данном списке были отсортированы в алфавитном порядке по полю "Категория", в конец инструкции SQL нужно дабавить предложение: ORDER BY Типы.Категория (ORDER BY Categories.CategoryName).

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

  • Свойство Число столбцов (Column Count) имеет значение 2, т. е. в списке будет два столбца (выбираются два поля), однако свойство Ширина столбцов (Column Width) определяет ширину первого столбца, равной 0, т. е. он не будет отображаться на экране.
  • Несмотря на то, что первый столбец является скрытым, его тоже можно присоединить к полю таблицы, как и любой другой столбец списка, чтобы в выбранном элементе списка значение именно из этого столбца (в нашем примере — это значение поля "КодТипа") записывалось в базовую таблицу формы. Чаще всего именно первый слобец списка требуется присоединить к полю источника данных. Номер присоединенного столбца указывается в свойстве Присоединенный столбец (Bound Column).
  • Свойство Заглавия столбцов (Column Heads) в нашем примере имеет значение Нет (No), поэтому они не будут отображаться в списке.

В дополнение к вышеперечисленным, существует еще три типа источников строк для элемента управления ListBox:

    Список значений. Если количество строк в списке невелико и постоянно, можно все значения задать прямо в окне свойств элемента. В этом случае в свойстве Тип источника строк выбирается значение Список значений (Value List), а значения элементов списка перечисляются через точку с запятой без пробелов в свойстве Источник строк. Например:

Если элементы списка содержат пробелы, они должны быть заключены в инвертированные запятые. Чтобы отобразить все дни недели в одном столбце списка, установите свойство Число столбцов равным 1, а свойство Ширина столбца — ненулевому значению, например 2,5 см (2,5 дюйма).

Вы можете вывести два столбца значений, установив в свойстве Source строк в перечислении значений пару значений, а в свойстве width столбцов — пару значений; например, 2, 5см; Зсм (2, 5см; 3см).

Читайте так же:
Как переместить десятичную точку влево в Excel?

Рисунок 9.16. Использование функции в качестве источника строк

Я хотел бы поговорить о третьем типе источника строк списка. Есть несколько аргументов, которые должна принимать эта функция:

Funkcja Имя_функции(fid Как Control, id Как Variant, _

Row как переменная, col как переменная, code как переменная) как переменная.

Данная функция вызывается Access каждый раз, когда требуется заполнение списка элементами. Например, это происходит автоматически, когда форма, содержащая список, открывается в первый раз. Вызвать обновление содержимого списка в какой-то другой момент можно программно, с помощью метода обновления содержимого элемента управления Require. Каждый раз при обновлении содержимого списка эта функция вызывается неоднократно: с различным "кодом действия".

Мы объясним, что она делает с аргументами и что возвращает.

  • code — "код действия", определяет, какое действие должна выполнить функция при данном вызове. Это могут быть следующие действия: инициализация списка, его открытие, получение количества строк, количества столбцов, ширины столбцов, самих элементов списка и завершение работы. Всем этим действиям соответствуют зарезервированные константы VBA.
  • fid — ссылка на элемент управления Список или Поле со списком, который заполняется элементами, с помощью данной функции.
  • id — уникальный идентификатор заполняемого элемента управления. Этот аргумент полезен, когда такая функция используется для заполнения нескольких элементов управления, чтобы их различить.
  • row — текущий номер строки для заполняемого элемента списка (начиная с 0).
  • col — текущий номер столбца для заполняемого элемента списка (начиная с 0).
  • Возвращаемое значение и его тип зависят от текущего "кода действия", переданного в функцию. Например, для кода acLBGetValue (получить данные) это должно быть строковое значение элемента списка в заданной строке row и столбце col.

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

О создании функции, выполняющей роль источника данных для списка или поля со списком, можно получить подробную информацию в справочной системе Access 2002, в разд. "Справка по Microsoft Access, Программирование на Visual Basic, Справочник по Visual Basic для Microsoft Access, Свойства, Q-R, RowSourceType" (Microsoft Access Help, Programming in Visual Basic, Microsoft Access Visual Basic Reference, Properties, Q-R, RowSourceType Property). Пример использования такой функции можно найти также на компакт-диске, являющемся дополнительным приложением к данной книге, который продается отдельно.

Свойство Bound Column, определяющее столбец в списке, значение которого становится значением элемента управления при выборе, может иметь значение 0. В этом случае значением поля списка будет индекс (номер) выбранного элемента в списке.

Еще одной особенностью элемента управления Список является возможность одновременного выбора нескольких элементов из списка. Вы можете выбрать несколько элементов в списке одновременно, чего не позволяет сделать элемент управления Combo Box. Эта возможность часто нужна, когда вы задаете критерии для выбора элементов. Например, вы можете использовать базу данных объектов недвижимости для поиска интересующих вас квартир, а также интересующих вас квартир в определенных районах города. Конечно, вы можете выбрать один район в списке, чтобы поочередно отобрать все квартиры в данном районе, затем другой и т.д. Однако в данном случае удобнее использовать список, в котором выбираются сразу все интересующие районы, а затем формируется соответствующий запрос к базе данных.

Вы можете определить, как пользователь выбирает значения из списка, используя свойство Multi Select. Доступ к нему можно получить на вкладке Другие в окне Свойства. Обычно это свойство имеет значение None. В этом случае разрешается выбирать только один элемент из списка. Но это свойство может быть установлено в значения Simple и Extended. В первом случае множественный выбор можно сделать, просто щелкая по элементам, а во втором случае список работает так, как работают почти все списки в Windows. Вы можете использовать клавиши для выбора смежных и несмежных элементов. Массивы используются в программах VBA для хранения и извлечения результатов выбора.

Читайте так же:
Как переместить группу плюс / минус вверх в Excel?

Объект Range (Excel)

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

Хотите расширить возможности Office на разных платформах? Новая модель дополнений Office уже здесь. Дополнения Office занимают меньше места, чем дополнения и VSTO-решения, и их можно создавать, используя практически любые технологии веб-программирования, такие как HTML5, JavaScript, CSS3 и XML.

Примечания

Элемент по умолчанию объекта Range направляет вызовы без параметров на свойство Value, а вызовы с параметрами — на элемент Item. Таким образом, someRange = someOtherRange соответствует someRange.Value = someOtherRange.Value , someRange(1) соответствует someRange.Item(1) и someRange(1,1) соответствует someRange.Item(1,1) .

Следующие свойства и методы для возвращения объекта Range описаны в разделе Примеры:

  • Свойства Диапазон и ячейки в объекте Электронная таблица
  • Свойства Диапазон и ячейки в объекте Диапазон
  • Свойства Строки и столбцы в объекте Электронная таблица
  • Свойства Строки и столбцы в объекте Диапазон
  • Свойство смещения для объекта Диапазон
  • Метод объединения для объекта Приложение

Пример

Чтобы вернуть объект Range, представляющий одну ячейку или диапазон ячеек, используйте синтаксис Range ( arg ), где arg представляет диапазон. В следующем примере значение ячейки A1 помещается в ячейку A5.

В следующем примере область A1:H8 заполняется случайными числами путем задания формулы для каждой ячейки в области. Когда свойство Range используется без классификатора объекта (объект слева от точки), свойство Range возвращает диапазон активного листа. Если активное окно не является рабочим листом, метод завершается с ошибкой.

Прежде чем использовать свойство Range без явного классификатора объекта, активируйте рабочий лист, используя метод Activate объекта Worksheet.

Как показано ниже, содержимое диапазона Criteria очищено.

Если для адреса диапазона используется текстовый аргумент, то адрес должен быть указан в нотации стиля A1 (нотацию стиля R1C1 использовать нельзя).

Чтобы получить диапазон, содержащий все отдельные ячейки листа, используйте свойство Cells листа. Вы можете ссылаться на отдельные ячейки, используя синтаксис Item(row, column), где row — индекс строки, а column — индекс столбца. Свойство Item можно не указывать, так как вызов направляется на него с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной рабочей книги ячейке A1 присвоено значение 24, а ячейке B1 — значение 42.

Следующий пример устанавливает формулу для ячейки A2.

Хотя также можно использовать Range("A1") , чтобы вернуть значение ячейки A1, иногда свойство Cells может быть удобнее, так как позволяет использовать переменную для строки или столбца. В следующем примере создаются заголовки столбцов и строк на листе Sheet1. Обратите внимание, что после активации листа можно использовать свойство Cells без явного объявления листа (оно возвращает ячейку на активном листе).

Несмотря на возможность использования строковых функций Visual Basic для изменения ссылок на стиль A1, использование нотации Cells(1, 1) более предпочтительно (и проще).

Используйте syntax_expression_.Cells, если выражение возвращает объект Range, чтобы получить диапазон с тем же адресом, состоящий из отдельных ячеек. В таком диапазоне отдельные ячейки открываются с помощью синтаксиса Item(row, column) относительно левого верхнего угла первого диапазона в диапазоне. Свойство Item можно не указывать, поскольку оно вызывается с использованием позиции объекта Range по умолчанию. В следующем примере ячейки C5 и D5 на первом листе активной книги содержат формулу в ячейках C5 и D5.

Чтобы вернуть объект Range, используйте синтаксис Range ( cell1, cell2 ), где cell1 и cell2 — объекты Range, указывающие начальную и конечную ячейки. Ячейки A1-J10 в следующем примере имеют тип линии границы Margins.

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

Читайте так же:
Как очистить ограниченные значения в ячейках в Excel?

Если вам нужен диапазон, содержащий все строки рабочего листа, используйте свойство Rows рабочего листа. На отдельные строки можно ссылаться, используя синтаксис Item(row), где row — индекс строки. Свойство Item можно не указывать, поскольку вызов направляется на него с помощью элемента по умолчанию объекта Range.

Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала необходимо преобразовать их в отдельные ячейки с помощью свойства Cells.

В приведенном ниже примере строки 4 и 10 удаляются из первого листа активной бухгалтерской книги.

Чтобы получить диапазон, содержащий все столбцы листа, используйте свойство Columns листа. Вы можете ссылаться на отдельные колонки, используя синтаксис Item(string) [sic], где string — индекс колонки в виде номера колонки или адреса в формате A1. Свойство Item можно не указывать, так как вызов направляется на него с помощью элемента по умолчанию объекта Range.

Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала необходимо преобразовать их в отдельные ячейки с помощью свойства Cells.

Колонки B, C, E и J первого листа активной книги были удалены.

Используйте синтаксис_выражения_. Чтобы получить диапазон, включающий строки первой области диапазона, используйте выражение, возвращающее объект Range. Item(row) — это синтаксис для ссылки на отдельные строки, где row — это относительный индекс строки от верхнего края области диапазона. Свойство Item можно пропустить, поскольку вызов направляется на него с использованием элемента по умолчанию объекта Range.

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

В примере ниже интервалы C8:D8 и C6:D6 удалены с первого листа активной книги.

Используйте syntax_expression_.Columns, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из столбцов первого диапазона. Вы можете ссылаться на отдельные столбцы, используя синтаксис Item(string) [sic], где string — относительный индекс столбца от левого края первого диапазона в диапазоне, заданный в виде числа или адреса столбца в формате A1. Свойство Item можно не указывать, так как оно ссылается на элемент по умолчанию объекта Range.

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

В следующем примере диапазоны L2:L10, G2:G10, F2:F10 и D2:D10 удалены с первого листа активной рабочей книги.

Диапазон со смещением относительно другого диапазона можно вернуть с помощью Offset (row, column), где row и column — смещения строк и столбцов. Следующий пример выбирает ячейки на три строки вниз и один столбец вправо от ячейки в левом верхнем углу текущего выделения. Перед выделением ячеек необходимо открыть активный лист.

Примените синтаксис Union ( range1, range2, . ) для возврата диапазонов из нескольких областей, то есть диапазонов, состоящих из двух или более соседних блоков ячеек. Выбрав диапазон из A1:B2 и C3:D4, мы создаем объект, определяемый объединением этих диапазонов.

Если в выборке есть несколько областей, свойство Areas может быть полезным. Объекты RangeObjects создаются из выделения с несколькими областями, а затем возвращаются в виде коллекции. Подсчет областей в возвращаемой коллекции гарантирует, что выборка содержит более одной области, как показано в следующем примере.

Чтобы создать список уникальных значений для столбца A, используется метод AdvancedFilter объекта Range.

Методы

Свойства

См. также

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

Что вы думаете об этой статье или о Office VBA? Рекомендации по другим способам получения поддержки и обратной связи см. в статье Поддержка и обратная связь Office VBA.

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