Excel vba присвоить пустое значение

Обновлено: 06.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ 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 активного листа.

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

Значения null и пустые строки имеют специальные применения в API JavaScript для Excel. Они используются для представления пустых ячеек, отсутствия форматирования или значений по умолчанию. В этом разделе описано использование значения null и пустой строки при получении и настройке свойств.

В Excel диапазон представлен двумерным массивом, в котором первое измерение — это строки, а второе — столбцы. Чтобы задать значения, формат чисел или формулу только для определенных ячеек в диапазоне, укажите значения, формат чисел или формулу для этих ячеек в двумерном массиве, а для всех остальных ячеек в этом массиве укажите значение null .

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

null не является допустимым входным значением для одного свойства. Например, указанный ниже фрагмент кода не является допустимым, так как свойство values диапазона не должно иметь значение null .

Аналогично, указанный ниже фрагмент кода не является допустимым, так как null — недопустимое значение для свойства color .

Значения свойств null в ответе

Если в указанном диапазоне имеются другие значения, свойства форматирования, например size и color будут содержать значения null в ответе. Например, если вы получаете диапазон и загружаете его свойство format.font.color :

Если у всех ячеек в диапазоне один и тот же цвет шрифта, свойство range.format.font.color указывает этот цвет.
Если в диапазоне используется несколько цветов шрифтов, свойство range.format.font.color имеет значение null .

Пустое входное значение для свойства

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

Если вы укажете пустое значение для свойства values диапазона, содержимое диапазона будет очищено.
Если вы укажете пустое значение для свойства numberFormat , формат чисел будет "сброшен" до формата General .
Если вы укажете пустое значение для свойств formula и formulaLocale , значения формул будут очищены.

Значения пустых свойств в ответе

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

Подскажите, как вычислить посредством формулы, чтобы значение ячейки равнялось "пусто" (это те значения, которые имеют ячейки при открытии новой книги, а не ноль и не пустой текст (пустая строка) "")?

А что, "пусто" от "пустой строки" сильно отличается ?

это те значения, которые имеют ячейки при открытии новой книги, а не ноль и не пустой текст

Позвольте узнать, каким способом Вы определили, что ячейки вновь создаваемой книги какие-то "особо пустые"?

А что, "пусто" от "пустой строки" сильно отличается ?

Позвольте узнать, каким способом Вы определили, что ячейки вновь создаваемой книги какие-то "особо пустые"?

Определяется применением функции ЕПУСТО(): к девственной ячейке - получается значение ИСТИНА и к ячейке ="" - получается значение ЛОЖЬ.

позвольте, в ячейке ="" - записана формула, естесственно она неПУСТО.
Если в ячейке есть формула, то она уже никогда не будет "ЕПУСТО", какой бы результат не возвращала

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

41001804815208 - ЮMoney бывш.Яндекс-кошелек благодарности за удачные советы и решения можно отправлять прямо сюда)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете

Определяется применением функции ЕПУСТО(): к девственной ячейке - получается значение ИСТИНА и к ячейке ="" - получается значение ЛОЖЬ

А Вы проверять это пробовали?

Ввожу в ячейку формулу =ЕПУСТО(A4)
В ячейку А4 ввожу данные - формула выдаёт ЛОЖЬ
Удаляю данные (клавишей Delete) - формула выдаёт опять ИСТИНА.

Что, "девственность ячейки" восстановилась?

Вы несколько неправильно понимаете и вот почему.

Применением функции (в частности ЕПУСТО() ) к ячейке в качестве её аргумента используется именно значение ячейки, а не её содержание (формула).

Пример: в ячейке А1 запишем форумулу =""
Значение ячейки будет пустая строка (текст). Проверка - запишите в ячейке B1 формулу =ЕТЕКСТ(А1). Значение получится ИСТИНА.

мне показалось я правильно понял: "что такое написать в формуле, чтобы после ее работы ячейка была ЕПУСТО"?
Ответ: ничего такого написать нельзя, если там есть формула ЕПУСТО там никогда не будет.
Разве что очистить ручками (кнопкой Del) или макросом (ClearContents, кажется так пишется). И в том и другом случае вы формулу снесете с ячейки. 41001804815208 - ЮMoney бывш.Яндекс-кошелек благодарности за удачные советы и решения можно отправлять прямо сюда)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете

А Вы проверять это пробовали?

Что, "девственность ячейки" восстановилась?

Конечно, перед тем выкладывать пример проверяю его.

Вы совершенно правильно описали пример. Удаляя содержимое ячейки клавишей Delete, ячейка принимает значение ПУСТО. Я это понимаю именно так - "девственность ячейки" восстановилась. Т.е. значение не есть число или текст (частный случай текста пустая строка "").

Я же спрашивал как, например, формулой ЕСЛИ( лог_выражение; ячейка_принимает_значение_пусто ; [значение_если_ложь] ) определить значение ячейки как "ПУСТО".

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:

если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:

самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:

Dim oRange As Range

А на диапазон ячеек с A1 по D10 — так:

Dim oRange As Range

С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:

Set oRange1 = Worksheets("Лист1").Range("C1")

пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).

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

Dim oRange As Range

Set oRange = Worksheets("Лист1").Cells(1, 4)

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

Set oRange = Range(Cells(1, 1), Cells(5, 3))

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

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

Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:

Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.

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

sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))

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

If Selection.Areas.Count > 1 Then

Debug.Print "Диапазон с несмежными областями"

Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:

Dim oRange, oRange2 As Range

Set oRange = Range(Cells(2, 2), Cells(5, 3))

Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2

Debug.Print oRange2.Address 'Так оно и есть

Точно такие же особенности у свойств Row и Rows, Column и Columns.

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

Dim oRange As Range

Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.

Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом

Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
Worksheets("Лист1").Activate
ActiveCell.Dependents.Select

Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.

Читайте также: