Excel залить ячейку программно

Обновлено: 07.07.2024

Сам код, где делаю заливку

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

Форматирование ячеек Excel
Добрый день! Подскажите, как программно форматировать данные в ячейках Excel? Интересует заливка.

Использование get_Range для выбора диапазона ячеек в Excel
Добрый день всем! Использую get_Range("a" + 7, "i" + 95); тут понятно все, выбирается диапазон.

Можно ли получить формат ячеек листа Excel через ADO
Собственно, возможно ли получить формат ячеек листа Excel через ADO - с данными все понятно, а.

Может ссылка есть у кого по этой теме, буду очень благодарен, если поделитесь)
З. Ы. Вообще по возможности лучше использовать генератор отчетов вместо Excel. Если это в finally все написать, то приложение будет сразу закрываться, пробовал такой вариант, но после него все равно процесс остаётся висеть, не могу понять саму логику, как работает это. Мое приложение создаёт процесс excel, он уничтожается только при неаварийном закрытии моего приложения. Если убивать сам процесс, то слетят все документы, это тоже не выход и заставлять пользователя отдельной кнопкой закрывать excel, как то не очень гуманно) Возможно как то подловить процесс закрытия именно созданного моим приложением процесса excel, и на событие типа closing, повешать уничтожение этого процесса. В excel какая то своя логика, прям 1с напоминает. Вообще я просто после выгрузки открывал файл для просмотра:

А дальше если пользователь его закрывал, то процесс исчезал из списка автоматом.

Спасибо за подсказку по решению задачи с процессом. По мастеру отчетов, вы имеете в виду report viewer, раньше как то работал с ним, в принципе неплохое решение. Но хочу excel этот добить до конца, логику не понимаю именно этого приложения, хочу залить ее зеброй))) может у вас есть наброски какие?

Добавлено через 1 минуту
Буду очень благодарен.

Решение

Вообще я имел ввиду скорее Stimulsoft/crystal/fast reports.
хочу залить ее зеброй))) может у вас есть наброски какие? Честно говоря не совсем понимаю, что вы имеете ввиду под зеброй, точнее как эта зебра должна выглядеть. При открытии в excel таблиц XML есть своеобразная зебра правда она построчная, т. е. четные строки темно-синие, а нечетные просто синие (цвет можно поменять при желании). Как вариант можете в Excel попробовать запустить в вкладка разработчика -> запись макроса затем реализуем, то что хотим в Excel останавливаем запись и смотрим получившиеся строки, может это даст вам наводку. Если не хотим делать программно можно просто создать шаблон и тупо его копировать, а затем заполнять. Спасибо большое за наводку, думаю перейду на шаблон, ну и попробую с crystal reports, вроде самые простые на сколько помню с ними работал последний раз. ЕвгенийКор, выбросите Interop на помойку, это полная хрень.
Работайте с OpenXML или проще с надстройкой ClosedXML. Спасибо. У вас случаем нет хорошей ссылочки, как с этим работать, желательно на русском языке, буду очень благодарен.

Interop ни разу не подводил.

Вообще по возможности лучше использовать генератор отчетов вместо Excel.

Ну, это спорное утверждение.

В экселе создавать шаблоны отчетов удобнее, чем в любом конструкторе любого генератора.
В экселе можно и очень удобно писать макросы
Для экселя не нужно никаких дополнительных установок на ПК клиентов - как, правило, там стоит уже офис.
Шаблоны экселя прекрасно хранятся в БД и отчеты формируеются через COM-технологию, в т.ч. на стороне клиента
В экселе есть гибкая и универсальная возможность напрямую обмениваться данными с SQL-сервером.

Минус экселя - отсутствие кроссплатформенности. Но это - единственный минус, пожалуй

Добавлено через 8 минут
Еще из бесспорных преимуществ.

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

Добавлено через 34 минуты
Вот, еще вспомнил

В шаблоне можно предусмотреть и формочку для ввода исходных данных для отчета (параметров).

Тогда в приложении вообще ничего не нужно - просто открывается эксель - а он уже сам все делает: получает параметры от юзера, выбирает данные из SQL-сервера (обычно - ХП), и строит отчет - красота !

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Workbooks ( "Книга2.xlsm" ) . Sheets ( "Лист2" ) . Cells ( 5 , 3 ) Workbooks ( "Книга2.xlsm" ) . Sheets ( "Лист2" ) . Cells ( 5 , "C" )

Удобнее обращаться к ячейке через свойство рабочего листа 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 является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Заливка ячейки цветом в VBA Excel

Пример кода 1:

Range ( "C12:D17" ) . Cells ( 4 ) . Interior . Color = 568569

Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.

Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

Cells ( 2 , 1 ) . Interior . Color = 16777215 + ( - 12207890 )

Проверено в Excel 2016.

Пример кода 2:

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

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Предопределенная константа	Наименование цвета
vbBlack	Черный
vbBlue	Голубой
vbCyan	Бирюзовый
vbGreen	Зеленый
vbMagenta	Пурпурный
vbRed	Красный
vbWhite	Белый
vbYellow	Желтый
xlNone	Нет заливки

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

Пример кода 3:

Цветовая модель RGB

Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

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

Пример кода 4:

Очистка ячейки (диапазона) от заливки

Для очистки ячейки (диапазона) от заливки используется константа xlNone :

Свойство .Interior.ColorIndex объекта Range

Пример кода 5:

Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

Пример кода 6:

Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

Готовую стандартную палитру из 56 цветов можете посмотреть здесь.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

59 комментариев для “VBA Excel. Цвет ячейки (заливка, фон)”

Спасибо, наконец то разобрался во всех перипетиях заливки и цвета шрифта.

Пожалуйста, Виктор. Очень рад, что статья пригодилась.

как проверить наличие фона?

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

Привет, Иван!
Посчитать ячейки с одинаковым фоном можно с помощью цикла.
Для реализации этого примера сначала выбираем в таблице ячейку с нужным цветом заливки. Затем запускаем код, который определяет цветовой индекс фона активной ячейки, диапазон таблицы вокруг нее и общее количество ячеек с такой заливкой в таблице.

Каким образом можно использовать не в процедуре, а именно в пользовательской функции VBA свойство .Interior.Color?
Скажем, проверять функцией значение какой-то ячейки и подкрашивать ячейку в зависимости от этого.

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

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

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

Для подкрашивания ячейки в зависимости от ее значения используйте процедуру Sub или штатный инструмент Excel – условное форматирование.

а как можно закрасить только пустые ячейки ?

Евгений, спасибо за ссылку на интересный прием.

Евгений, день добрый.
Подскажите пожалуйста, как назначить ячейке цвет через значение RGB, которое в ней записано. Или цвет другой ячейки.

Привет, Александр!
Используйте функцию InStr, чтобы найти положение разделителей, а дальше функции Left и Mid. Смотрите пример с пробелом в качестве разделителя:

Range ( "A1" ) . Interior . Color = RGB ( Left ( Range ( "A1" ) , n1 - 1 ) , _ Mid ( Range ( "A1" ) , n1 + 1 , n2 - n1 - 1 ) , Mid ( Range ( "A1" ) , n2 + 1 ) )

Или еще проще с помощью функции Split:

Range ( "A1" ) . Interior . Color = RGB ( a ( 0 ) , a ( 1 ) , a ( 2 ) )

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

spisok = spisok & vbNewLine & Cells ( i1 , i2 ) . Address If Cells ( i , "D" ) . Interior . Color = c Then Cells ( i , "E" ) = "Да"

Евгений, спасибо за подсказку.
Все получилось

добрый день! подскажите, пожалуйста, как сделать, чтобы результаты выводились на отдельный лист ?
заранее спасибо!

Sheets ( "Лист2" ) . Cells ( n , 1 ) = "Список адресов ячеек с цветом " & tsvet Sheets ( "Лист2" ) . Cells ( n , 1 ) = Cells ( i1 , i2 ) . Address

Добрый день, Алексей!
Примените условное форматирование:

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

Процедура добавления ячеек

Сразу обратим внимание на то, как именно с технологической стороны выполняется процедура добавления ячеек. По большому счету то, что мы называем «добавлением», по сути, является перемещением. То есть, ячейки просто сдвигаются вниз и вправо. Значения, которые находятся на самом краю листа, таким образом, при добавлении новых ячеек удаляются. Поэтому нужно за указанным процессом следить, когда лист заполняется данными более, чем на 50%. Хотя, учитывая, что в современных версиях Excel имеет на листе 1 миллион строк и столбцов, на практике такая необходимость наступает крайне редко.

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

Итак, теперь перейдем к конкретным способам добавления элементов на лист.

Способ 1: Контекстное меню

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

Выделяем элемент листа, куда хотим вставить новую ячейку. Кликаем по нему правой кнопкой мыши. Запускается контекстное меню. Выбираем в нем позицию «Вставить…».

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

После этого элементы будут добавлены по тому же алгоритму, который мы описывали выше, но только целой группой.

Способ 2: Кнопка на ленте

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

«Главная»

«Вставить»

«Ячейки»

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

«Вставить»

«Главная»

А вот при выделении вертикальной группы ячеек мы получим несколько иной результат.

«Вставить»

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

«Вставить»

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

«Вставить»

«Вставить ячейки…»

Способ 3: Горячие клавиши

Самый быстрый способ добавить элементы листа в Экселе – это воспользоваться сочетанием горячих клавиш.

Ctrl+Shift+=

Как видим, существуют три основных способа вставки ячеек в таблицу: с помощью контекстного меню, кнопок на ленте и горячих клавиш. По функционалу эти способы идентичные, так что при выборе, прежде всего, учитывается удобство для самого пользователя. Хотя, безусловно, наиболее быстрый способ – это применения горячих клавиш. Но, к сожалению, далеко не все пользователи привыкли держать существующие комбинации горячих клавиш Экселя у себя в памяти. Поэтому далеко не для всех этот быстрый способ будет удобен.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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