Oc-windows.ru

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

Запись значения в ячейку excel vba

Чтение и запись значения ячейки в VBA

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

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

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

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

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

  • Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

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

Способы переноса данных в Excel из Visual Basic

Описание

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

Дополнительные сведения

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

  • Перенос ячейки данных по ячейке
  • Передача данных в массиве в диапазон ячеек
  • Передача данных из набора записей ADO в диапазон ячеек с помощью метода Копифромрекордсет
  • Создание QueryTable на листе Excel, который содержит результат запроса в источнике данных ODBC или OLEDB
  • Перенесите данные в буфер обмена, а затем вставьте содержимое буфера обмена в лист Excel.

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

  • Перенос данных в текстовый файл с разделителями — табуляцией или запятыми, который Excel может выполнить в дальнейшем, разбить на ячейки листа
  • Передача данных на лист с помощью ADO
  • Передача данных в Excel с помощью динамического обмена данными (DDE)

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

Note (Примечание ) При использовании Microsoft Office Excel 2007 вы можете использовать новый формат файла книги Excel 2007 (XLSX) при сохранении книг. Для этого откройте следующую строку кода в следующих примерах кода:

Читать еще:  Название диаграммы в excel

Замените этот код следующей строкой кода:

Кроме того, база данных «Борей» по умолчанию не включена в Office 2007. Тем не менее, вы можете скачать базу данных Northwind из Microsoft Office Online.

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

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

Передача данных по ячейкам может быть приемлемым способом, если объем данных мал. У вас есть гибкие возможности для размещения данных в любом месте книги и для условного форматирования ячеек во время выполнения. Однако этот подход не рекомендуется, если для передачи в книгу Excel требуется большой объем данных. Каждый объект диапазона, полученный во время выполнения, вызывает запрос интерфейса таким образом, что передача данных таким способом может замедлиться. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение на 64 КБ для запросов интерфейса. Если вы достигли предельного значения 64 КБ на запросах интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или могут возникать ошибки, указывающие на нехватку памяти.

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

Дополнительные примеры кода для автоматизации Excel приведены в статье Автоматизация Microsoft Excel в Visual Basic.

Использование автоматизации для переноса массива данных в диапазон листа

Массив данных можно переносить в диапазон нескольких ячеек одновременно:

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

Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, а другой для объекта Range, возвращаемого методом resize). С другой стороны, при передаче ячейки данных по ячейке потребуются запросы 300 интерфейсов к объектам Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.

Использование автоматизации для переноса набора записей ADO в диапазон листа

В Excel 2000 появился метод Копифромрекордсет, позволяющий перенести набор записей ADO (или DAO) в диапазон на листе. В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и перенести содержимое таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет.

Note (Примечание ) Если вы используете версию Office 2007 для базы данных Northwind, необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Excel 97 также предоставляет метод Копифромрекордсет, но его можно использовать только с набором записей DAO. Копифромрекордсет с Excel 97 не поддерживает ADO.

Для получения дополнительных сведений об использовании ADO и методе Копифромрекордсет обратитесь к следующей статье базы знаний Майкрософт:

246335 как перенести данные из набора записей ADO в Excel с помощью автоматизации

Использование автоматизации для создания QueryTable на листе

Объект QueryTable представляет таблицу, созданную на основе данных, возвращенных из внешнего источника данных. При автоматизации Microsoft Excel можно создать QueryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC, а также строку SQL. Excel предполагает ответственность за создание набора записей и вставку его на лист в указанном расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом Копифромрекордсет:

  • Excel обрабатывает создание набора записей и его расположение на листе.
  • Запрос можно сохранить вместе с QueryTable, чтобы его можно было обновить позже, чтобы получить обновленный набор записей.
  • Когда на лист добавляется новый QueryTable, вы можете указать, что данные, уже существующие в ячейках листа, будут смещены в соответствии с новыми данными (Дополнительные сведения см. в свойстве Рефрешстиле).

В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003, чтобы создать новый QueryTable на листе Excel с помощью данных из учебной базы данных Northwind:

Использование буфера обмена

Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки. В приведенном ниже коде показано, как Visual Basic может использовать объект Clipboard для передачи данных в Excel:

Создание текстового файла с разделителями, который Excel может проанализировать по строкам и столбцам

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

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

Примечание Если вы используете версию Office 2007 для базы данных Northwind, необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Если текстовый файл имеет расширение. Расширение CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделяются запятыми. Аналогично, если файл имеет расширение. TXT, Excel автоматически проанализирует файл с помощью разделителей табуляции.

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

Читать еще:  Где находится маркер заполнения в excel

Передача данных на лист с помощью ADO

С помощью поставщика OLE DB для Microsoft Jet можно добавлять записи в таблицу в существующей книге Excel. «Таблица» в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки содержат записи. В следующей процедуре показано, как создать книгу с пустой таблицей с именем MyTable.

Excel 97, Excel 2000 и Excel 2003

Создайте новую книгу в Excel.

Добавьте следующие заголовки в ячейки a1: B1 из Лист1:

A1: имя B1: LastName

Форматирование ячейки B1 в соответствии с выравниванием по правому краю.

В меню Вставка выберите пункт имена, а затем выберите команду определить. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:Book1.xls и закройте Excel.

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

Excel 2007

В Excel 2007 создайте новую книгу.

Добавьте следующие заголовки в ячейки a1: B1 из Лист1:

A1: имя B1: LastName

Форматирование ячейки B1 в соответствии с выравниванием по правому краю.

На ленте щелкните вкладку формулы , а затем выберите команду задать имя. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:Book1.xlsx, а затем закройте Excel.

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

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

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

Метод обновления данных на листе Excel с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложений в Access после установки пакета обновления 2 (SP2) для Office 2003 или после установки обновления для Access 2002, включенного в микропрограммы Статья базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic для приложений в среде приложений из других приложений Office, таких как Word, Excel и Outlook.

Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт: 904953 невозможно изменить, добавить или удалить данные в таблицах, связанных с книгой Excel в Office Access 2003 или в Access 2002

Дополнительную информацию об использовании ADO для доступа к книге Excel можно узнать, как запрашивать и обновлять данные Excel с помощью ADO из ASP.

Использование DDE для передачи данных в Excel

DDE является альтернативой автоматизации для связи с Excel и передачи данных; Однако с появлением автоматизации и COM-интерфейс DDE больше не является предпочтительным методом связи с другими приложениями и его следует использовать только в том случае, если вы не можете использовать другие решения.

Для передачи данных в Excel с помощью DDE можно использовать метод Линкпоке для обмена данными с определенным диапазоном ячеек или с помощью метода Линкексекуте для отправки команд, которые будут выполняться приложением Excel.

В приведенном ниже примере кода показано, как установить сеанс DDE с Excel, чтобы можно было выполнять ввод данных в ячейки листа и выполнять команды. С помощью этого примера можно успешно установить сеанс DDE в Линктопик Excel | Мибук. xls книга с именем Мибук. xls должна быть уже открыта в запущенном экземпляре Excel.

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

Note (Примечание ) В этом примере Текст1 представляет элемент управления «текстовое поле» в форме Visual Basic:

При использовании Линкпоке с Excel необходимо указать диапазон в нотации Row-Column (R1C1) для Линкитем. При Покинг данных к нескольким ячейкам можно использовать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки.

При использовании Линкексекуте для выполнения команды в Excel необходимо дать команду в виде синтаксиса в формате макросов Excel (XLM). Документация по XLM не входит в состав Excel версий 97 и более поздних версий. DDE не является рекомендуемым решением для связи с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет вам дополнительный доступ к новым функциям, которые предлагает Excel.

Несколько советов по работе с VBA в Excel


Добрый день!

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

Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

Visual Basic

Опции

Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

Так же рекомендуется прописать:

В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

Ещё одним важным оператором является ON ERROR. Привожу варианты:

Возможности языка

Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):

Ускорение работы макросов

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

Читать еще:  Как объединить ячейки в excel 2020

По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

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

Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

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

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

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

Загрузка книги и события

При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

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

Защита

Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

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

Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:

Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

Заключение

VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

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

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

Ссылка на основную публикацию
Adblock
detector