Excel vba присвоить пустое значение
Иногда вы работаете с типом значения, который не имеет определенного значения в определенных обстоятельствах. Например, поле в базе данных может отличаться от присвоенного значения, которое является осмысленным и не имеет присвоенного значения. Типы значений могут быть расширены для получения их нормальных значений или значения NULL. Такое расширение называется типом, допускающим значение NULL.
Переменная ridesBusToWork может содержать значение True , значение False или вообще не иметь значения. Его начальное значение по умолчанию — вообще не имеет значения, что в данном случае может означать, что данные еще не были получены для этого пользователя. В отличие от этого, False это может означать, что информация получена и пользователь не передается на работу.
Можно объявить переменные и свойства с типами значений, допускающими значение null, и можно объявить массив с элементами типа значения, допускающего значение null. Можно объявлять процедуры с типами значений, допускающими значение null, в качестве параметров, а также возвращать тип значения, допускающий значение null, из Function процедуры.
Нельзя создать тип, допускающий значение null, для ссылочного типа, такого как массив, String или класс. Базовый тип должен быть типом значения. Дополнительные сведения см. в разделе типы значений и ссылочные типы.
Использование переменной типа, допускающей значение null
Наиболее важными членами типа, допускающего значение null, являются HasValue Свойства и Value . Для переменной типа значения, допускающего значение null, HasValue сообщает, содержит ли переменная определенное значение. Если HasValue параметр имеет True значение, то можно считать значения из Value . Обратите внимание, что HasValue Свойства и Value являются ReadOnly свойствами.
Значения по умолчанию
При объявлении переменной с типом значения, допускающим значение null, ее HasValue свойство имеет значение False по умолчанию. Это означает, что по умолчанию переменная не имеет определенного значения, а не значения по умолчанию базового типа значения. В следующем примере переменная numberOfChildren изначально не имеет определенного значения, хотя значение по умолчанию для Integer этого типа равно 0.
Значение NULL полезно для указания неопределенного или неизвестного значения. Если numberOfChildren было объявлено как Integer , то не будет значения, которое может указывать на то, что информация в настоящее время недоступна.
Сохранение значений
Вы сохраняете значение в переменной или свойстве типа значения, допускающего значение null, обычным способом. В следующем примере значение присваивается переменной numberOfChildren , объявленной в предыдущем примере.
Если переменная или свойство типа значения, допускающего значение null, содержит определенное значение, можно вернуть его первоначальное состояние, не имеющее присвоенного значения. Для этого нужно задать для Nothing переменной или свойства значение, как показано в следующем примере.
Хотя можно присвоить Nothing переменной тип значения, допускающего значение null, его Nothing нельзя проверить с помощью знака равенства. При сравнении, в котором используется знак равенства, someVar = Nothing всегда вычисляется значение Nothing . Можно проверить свойство переменной HasValue для False или проверить с помощью Is оператора или IsNot .
Получение значений
Чтобы получить значение переменной типа, допускающего значение null, сначала следует проверить его HasValue свойство, чтобы убедиться, что оно имеет значение. если вы попытаетесь считать значение HasValue False , то Visual Basic создает InvalidOperationException исключение. В следующем примере показан рекомендуемый способ чтения переменной numberOfChildren из предыдущих примеров.
Сравнение типов, допускающих значение null
Если в логических выражениях используются допускающие значения NULL Boolean переменные, результатом может быть True , False или Nothing . Ниже приведена таблица истинности для And и Or . Поскольку b1 у и b2 теперь есть три возможных значения, можно вычислить девять комбинаций.
B1 | ячейк | B1 и B2 | B1 или B2 |
---|---|---|---|
Nothing | Nothing | Nothing | Nothing |
Nothing | True | Nothing | True |
Nothing | False | False | Nothing |
True | Nothing | Nothing | True |
True | True | True | True |
True | False | False | True |
False | Nothing | False | Nothing |
False | True | False | True |
False | False | False | False |
Если значение логической переменной или выражения равно Nothing , то оно не равно ни true false . Рассмотрим следующий пример.
В этом примере b1 And b2 принимает значение Nothing . В результате Else предложение выполняется в каждой If инструкции, и выходные данные выглядят следующим образом:
Expression is not true
Expression is not false
AndAlso и OrElse , использующие сокращенную оценку, должны оценивать свои вторые операнды при первом вычислении до Nothing .
Распространение
Если один или оба операнда арифметических операций, операции сравнения, сдвига или типа имеют тип значения, допускающий значение null, результат операции также является типом значения, допускающим значение null. Если оба операнда имеют значения, которые не Nothing являются, операция выполняется с базовыми значениями операндов, как если бы ни был тип значения, допускающий значение null. В следующем примере переменные compare1 и sum1 неявно типизированы. Если навести на них указатель мыши, вы увидите, что компилятор определит типы значений, допускающие значение null, для обоих типов.
Если один или оба операнда имеют значение Nothing , результатом будет Nothing .
Использование типов, допускающих значение null, с данными
База данных является одним из наиболее важных мест для использования типов значений, допускающих значение null. Не все объекты базы данных в настоящее время поддерживают типы значений, допускающие значения NULL, но адаптеры таблиц, созданные конструктором, выполняют. См. раздел Поддержка TableAdapter для типов, допускающих значение NULL.
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
Для справки: выражение Range("1:1048576") описывает диапазон с 1 по 1048576 строку, где число 1048576 – это номер последней строки на рабочем листе Excel 2016.
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Для справки: порядковый номер ячейки в диапазоне считается построчно слева направо с перемещением к следующей строке сверху вниз.
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
Свойство | Описание |
---|---|
Address | Возвращает адрес ячейки (диапазона). |
Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
Name | Возвращает или задает имя ячейки (диапазона). |
NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Метод Application.InputBox предназначен в VBA Excel для вывода диалогового окна с более расширенными возможностями, чем диалоговое окно, отображаемое функцией InputBox. Главным преимуществом метода Application.InputBox является возможность автоматической записи в поле ввода диапазона ячеек (в том числе одной ячейки ) путем его выделения на рабочем листе книги Excel и возвращения различных данных, связанных с ним, а также проверка соответствия возвращаемого значения заданному типу данных.
Синтаксис метода
Application.InputBox ( Prompt , Title , Default , Left , Top , HelpFile , HelpContextID , Type )
Обязательным параметром метода Application.InputBox является Prompt, если значения остальных параметров явно не указаны, используются их значения по умолчанию.
Обратите внимание на то, что
- оператор InputBox вызывает функцию InputBox, а
- оператор Application.InputBox вызывает метод InputBox.
Чтобы не было путаницы, метод InputBox пишут как метод Application.InputBox, в том числе и в справке разработчика.
Параметры метода
* Параметры Left и Top учитываются при отображении диалогового окна методом Application.InputBox в Excel 2003, а в последующих версиях Excel 2007-2016 уже не работают.
**При первом запуске горизонтальное выравнивание устанавливается по центру, при последующих — форма отобразиться в том месте, где ее последний раз закрыли.
***При первом запуске вертикальное расположение приблизительно равно 1/3 высоты экрана, при последующих — форма отобразиться в том месте, где ее последний раз закрыли.
**** Если будут указаны параметры HelpFile и HelpContextID, в диалоговом окне появится кнопка справки.
Возвращаемые значения
Диалоговое окно, созданное методом Application.InputBox, возвращает значение типа Variant и проверяет соответствие возвращаемого значения типу данных, заданному параметром Type. Напомню, что тип значений Variant является универсальным контейнером для значений других типов, а в нашем случае для возвращаемых в зависимости от значения параметра Type.
Аргументы параметра Type и соответствующие им типы возвращаемых значений:
Примеры
В отличие от других встроенных диалоговых окон VBA Excel, Application.InputBox при запуске процедуры непосредственно из редактора, открывается прямо в редакторе, и, чтобы выбрать диапазон ячеек на рабочем листе, нужно по вкладке браузера перейти в книгу Excel. Поэтому для тестирования диалога Application.InputBox удобнее создать кнопку, перетащив ее на вкладке «Разработчик» из «Элементов управления формы» (не из «Элементов ActiveX») и в окошке «Назначить макрос объекту» выбрать имя тестируемой процедуры. Чтобы можно было выбрать процедуру сразу при создании кнопки, она должна быть уже вставлена в стандартный программный модуль. Можно назначить процедуру кнопке позже, кликнув по ней правой кнопкой мыши и выбрав в контекстном меню «Назначить макрос…».
Пример 1 — параметры по умолчанию
Тестируем метод Application.InputBox с необязательными параметрами по умолчанию. Аргумент параметра Type по умолчанию равен 2.
Значения 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 , значения формул будут очищены.
Значения пустых свойств в ответе
Для операций чтения пустое значение свойства в ответе (то есть две кавычки подряд без других знаков между '' ) указывает, что ячейка не содержит данных или значения. В первом примере ниже первая и последняя ячейки в диапазоне не содержат данных. Во втором примере две первые ячейки в диапазоне не содержат формул.
Читайте также: