Progress28.ru

IT Новости
16 просмотров
Рейтинг статьи
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 vba присвоить переменной значение ячейки

Сообщения: 197
Благодарности: 51

Конфигурация компьютера
Материнская плата: Gigabyte P43-ES3G
HDD: 1Gb SATA-2 Hitachi Deskstar 7K1000.C; 500 Gb SATA-2 Seagate Barracuda 7200.11
Блок питания: ATX 450W
Монитор: BENQ G900AD 19′
Профиль | Отправить PM | Цитировать

На последней приведенной строке возникает 1004 ошибка «Application-defined or object-defined error». Пробовал разные вариации на тему, но получается та же ошибка.
Как реализовать банальнейшее присвоение значения ячейке.

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

Сообщения: 25778
Благодарности: 7508

На последней приведенной строке возникает 1004 ошибка «Application-defined or object-defined error». Пробовал разные вариации на тему, но получается та же ошибка. »

PhilB, недостаточно приведённых данных для ответа.

Как реализовать банальнейшее присвоение значения ячейке. »

Именно так, как у Вас написано. Но Вы хотите присвоить ячейке не значение, а формулу. Соответственно, Вы должны использовать не свойство «.Value», а свойства .Formula/.FormulaLocal или .FormulaR1C1/.FormulaR1C1Local.


Inserting a Worksheet Function into a Cell

To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook.

Это сообщение посчитали полезным следующие участники:

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

Сообщения: 197
Благодарности: 51

Конфигурация компьютера
Материнская плата: Gigabyte P43-ES3G
HDD: 1Gb SATA-2 Hitachi Deskstar 7K1000.C; 500 Gb SATA-2 Seagate Barracuda 7200.11
Блок питания: ATX 450W
Монитор: BENQ G900AD 19′

Сообщения: 25778
Благодарности: 7508

…недостаточно приведённых данных для ответа. »

Сообщения: 197
Благодарности: 51

Конфигурация компьютера
Материнская плата: Gigabyte P43-ES3G
HDD: 1Gb SATA-2 Hitachi Deskstar 7K1000.C; 500 Gb SATA-2 Seagate Barracuda 7200.11
Блок питания: ATX 450W
Монитор: BENQ G900AD 19′

Сообщения: 25778
Благодарности: 7508

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

Так не работает (а хотелось бы ):

«.FormulaR1C1()» предусматривает задание адреса в стиле «R1C1», наподобие «=Лист2!R[7]C[1]».

Вариант с .Formula тоже пробовал. Проблема та же. »

Это сообщение посчитали полезным следующие участники:

Сообщения: 197
Благодарности: 51

Конфигурация компьютера
Материнская плата: Gigabyte P43-ES3G
HDD: 1Gb SATA-2 Hitachi Deskstar 7K1000.C; 500 Gb SATA-2 Seagate Barracuda 7200.11
Блок питания: ATX 450W
Монитор: BENQ G900AD 19′

Сообщения: 25778
Благодарности: 7508

Это сообщение посчитали полезным следующие участники:

Сообщения: 1
Благодарности:

Сообщения: 25778
Благодарности: 7508

Переменные и константы в VBA

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

Например, константа Pi хранит значение 3,14159265… Число “Пи” не будет изменяться в ходе выполнения программы, но все же хранить такое значение удобнее как константу.

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

Типы данных

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

Тип данныхРазмерОписаниеДиапазон значений
Byte1 байтПоложительные целые числа; часто используется для двоичных данныхот 0 до 255
Boolean2 байтаМожет принимать значения либо True, либо FalseTrue или False
Integer2 байтаЦелые числа (нет дробной части)от -32 768 до +32 767
Long4 байтаБольшие целые числа (нет дробной части)от -2 147 483 648 до +2 147 483 647
Single4 байтаЧисло с плавающей точкой одинарной точностиот -3.4e38 до +3.4e38
Double8 байтЧисло с плавающей точкой двойной точностиот -1.8e308 до +1.8e308
Currency8 байтЧисло с плавающей точкой, с фиксированным количеством десятичных разрядовот -922 337 203 685 477.5808 до +922 337 203 685 477.5807
Date8 байтДата и время – данные типа Date представлены числом с плавающей точкой. Целая часть этого числа выражает дату, а дробная часть – времяот 1 Января 100 до 31 Декабря 9999
Object4 байтаСсылка на объектЛюбая ссылка на объект
StringизменяетсяНабор символов. Тип String может иметь фиксированную или изменяющуюся длину. Чаще используется с изменяющейся длинойФиксированной длины – приблизительно до 65 500 символов. Переменной длины – приблизительно до 2 миллиардов символов
VariantизменяетсяМожет содержать дату, число с плавающей точкой или строку символов. Этот тип используют в тех случаях, когда заранее не известно, какой именно тип данных будет введёнЧисло – Double, строка – String

Очевидно, что пользуясь приведённой выше таблицей и правильно выбирая тип данных, можно использовать память более экономно (например, выбрать тип данных Integer вместо Long или Single вместо Double). Однако, используя более компактные типы данных, нужно внимательно следить за тем, чтобы в коде не было попыток уместить в них не соразмерно большие значения.

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

Примечание переводчика: Говоря о переменных в VBA, стоит упомянуть ещё один очень важный момент. Если мы объявляем переменную, но не присваиваем ей какое-либо значение, то она инициализируется значением по умолчанию:
• текстовые строки – инициализируются пустыми строками;
• числа – значением 0;
• переменные типа Boolean – False;
• даты – 30 декабря 1899.

Прежде чем использовать переменную или константу, её нужно объявить. Для этого в макрос добавляют вот такую простую строку кода:

Dim Имя_Переменной As Тип_Данных

В показанной выше строке кода Имя_Переменной – это имя переменной, которая будет использована в коде, а Тип_Данных – это один из типов данных из таблицы, приведённой чуть ранее в этой статье. Например:

Аналогично объявляются константы, но при объявлении констант обязательно сразу указывается их значение. Например, вот так:

Объявлять переменные в Excel не обязательно. По умолчанию все введённые, но не объявленные переменные в Excel будут иметь тип Variant и смогут принять как числовое, так и текстовое значение.

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

  1. Использование памяти и скорость вычислений. Если не объявлять переменную с указанием типа данных, то по умолчанию для неё будет установлен тип Variant. Этот тип данных использует больше памяти, чем другие типы данных.Казалось бы, несколько лишних байт на каждую переменную – не так уж много, но на практике в создаваемых программах могут быть тысячи переменных (особенно при работе с массивами). Поэтому излишняя память, используемая переменными типа Variant, по сравнению с переменными типа Integer или Single, может сложится в значительную сумму.К тому же, операции с переменными типа Variant выполняются гораздо медленнее, чем с переменными других типов, соответственно лишняя тысяча переменных типа Variant может значительно замедлить вычисления.
  2. Профилактика опечаток в именах переменных. Если все переменные объявляются, то можно использовать оператор VBA – Option Explicit (о нём расскажем далее), чтобы выявить все не объявленные переменные.Таким образом исключается появление в программе ошибки в результате не верно записанного имени переменной. Например, используя в коде переменную с именем sVAT_Rate, можно допустить опечатку и, присваивая значение этой переменной, записать: “VATRate = 0,175”. Ожидается, что с этого момента, переменная sVAT_Rate должна содержать значение 0,175 – но, конечно же, этого не происходит. Если же включен режим обязательного объявления всех используемых переменных, то компилятор VBA сразу же укажет на ошибку, так как не найдёт переменную VATRate среди объявленных.
  3. Выделение значений, не соответствующих объявленному типу переменной. Если объявить переменную определённого типа и попытаться присвоить ей данные другого типа, то появится ошибка, не исправив которую, можно получить сбой в работе программы.На первый взгляд, это может показаться хорошей причиной, чтобы не объявлять переменные, но на самом деле, чем раньше выяснится, что одна из переменных получила не те данные, которые должна была получить – тем лучше! Иначе, если программа продолжит работу, результаты могут оказаться неверными и неожиданными, а найти причину ошибок будет гораздо сложнее.Возможно также, что макрос будет “успешно” выполнен. В результате ошибка останется незамеченной и работа продолжится с неверными данными!

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

Option Explicit

Оператор Option Explicit заставляет объявлять все переменные, которые будут использованы в коде VBA, и при компиляции выделяет все не объявленные переменные как ошибки (прежде чем будет запущено выполнение кода). Применить этот оператор не сложно – просто запишите в самом верху файла VBA такую строку:

Если хотите всегда вставлять Option Explicit в начало каждого нового созданного модуля VBA, то это можно делать автоматически. Для этого необходимо включить параметр Require Variable Declaration в настройках редактора VBA.

Это делается так:

  • В меню редактора Visual Basic нажмите Tools >Options
  • В появившемся диалоговом окне откройте вкладку Editor
  • Отметьте галочкой параметр Require Variable Declaration и нажмите ОК

При включенном параметре строка Option Explicit будет автоматически вставляться в начало каждого нового созданного модуля.

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

Каждая объявленная переменная или константа имеет свою ограниченную область действия, то есть ограниченную часть программы, в которой эта переменная существует. Область действия зависит от того, где было сделано объявление переменной или константы. Возьмём, к примеру, переменную sVAT_Rate, которая используется в функции Total_Cost. В следующей таблице рассмотрены два варианта области действия переменной sVAT_Rate, объявленной в двух различных позициях в модуле:

Если переменная sVAT_Rate объявлена в самом начале модуля, то областью действия этой переменной будет весь модуль (т.е. переменная sVAT_Rate будет распознаваться всеми процедурами в этом модуле).

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

Однако, если будет вызвана какая-то функция, расположенная в другом модуле, то для неё переменная sVAT_Rate будет не известна.

Если переменная sVAT_Rate объявлена в начале функции Total_Cost, то её область действия будет ограничена только этой функцией (т.е. в пределах функции Total_Cost, можно будет использовать переменную sVAT_Rate, а за её пределами – нет).

При попытке использовать sVAT_Rate в другой процедуре, компилятор VBA сообщит об ошибке, так как эта переменная не была объявлена за пределами функции Total_Cost (при условии, что использован оператор Option Explicit).

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

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

Для объявления констант также можно использовать ключевые слова Public и Private, но не вместо ключевого слова Const, а вместе с ним.

В следующих примерах показано использование ключевых слов Public и Private в применении к переменным и к константам.

Excel vba присвоить переменной значение ячейки

На этом шаге мы рассмотрим реализацию данного действия .

Для ввода в табличную базу данных на листе База расстояния между НП и их названий, запишите макрос РасстоянияВвести (рисунок 1).

Рис.1. Подпрограмма РасстоянияВвести

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

Присвоение ячейкам листа База значений, введенных в диапазон ячеек D2:G2 на рабочем листе Города , производится с помощью кода VBA . Последовательность выполнения подпрограммы следующая:

  • трем переменным X, Y и Z присваиваются значения, предварительно введенные в ячейки D2 , E2 и G2 активного рабочего листа. Так как макрос РасстоянияВвести будет запускаться на выполнение кнопкой Расстояние ввести , находящейся на рабочем листе Города , то необходимости указания имени этого рабочего листа в тексте подпрограммы нет. Макрос довольно простой, и особой необходимости вводить в подпрограмму переменные не было. Это сделано с позиции читаемости.
  • переменной Row присваивается значение определяемое с помощью функции CountA , которая проводит подсчет непустых ячеек в столбце В (столбец может быть любой от А до D ) на листе База и увеличенное на единицу. Значение переменной Row указывает на номер первой пустой строки, с которой будет осуществляться ввод данных;
  • в табличную базу данных на листе База необходимо ввести две строки, по четыре записи в каждой: НП отправления (столбец А ), НП прибытия (столбец В ), соединенное название пункта отправления и прибытия, разделенные символом пробел (столбец С ) и расстояние между НП (столбец D ). Эти значения последовательно присваиваются четырем ячейкам слева направо сначала первой, а затем второй строки. Следовательно, адреса ячеек изменяются в следующей последовательности: первая строка — Cells(Row, 1), Cells(Row, 2), Cells(Row, 3), Cells(Row, 4) и вторая строка — Cells(Row + 1, 1), Cells(Row + 1, 2), Cells(Row + 1, 3) и Cells(Row + 1, 4) .

Рассмотрим более подробно строку кода VBA для ввода данных в ячейку во второй заполняемой строке столбца С рабочего листа База :

Прокомментируем приведенную формулу.

  • Sheets(» База «) — ранее, при выделении или активизации ячейки, рабочий лист не указывался. В этой строке кода VBA присвоение значения ячейке листа База задается без его активизации и с другого листа. По иерархии объект Sheets (Рабочий лист) находится выше, чем объект Cells (Ячейка, Диапазон) . Если не указать достаточный путь, в частности, рабочий лист, то ввод данных будет производиться в ячейки на активном рабочем листе.
  • Cells(Row + 1, 3) — координаты ячейки: Row+1 (строка); 3 (столбец С ).
  • Value — ячейке будет присвоено значение.
  • = — операция присваивания.
  • X & » » & Y — значение ячейки D2 (или значение, возвращаемое формулой, находящейся в этой ячейке) объединить через пробел со значением ячейки Е2 (или значение возвращаемое формулой). Знак & — операция объединения.

На следующем шаге мы рассмотрим реализацию проверки наличия повторяющихся записей .

Читать еще:  Как убрать красные линии в word
Ссылка на основную публикацию
Adblock
detector
×
×