Vba excel ширина столбца
Обновлено: 06.07.2024
Вроде бы - элементарное действие. но нет, здесь Microsoft нам усложнил задачу.
Получить ширину столбца в пикселах - не проблема, для этого есть свойство Width:
Но, увы, это свойство доступно только для чтения, - а для назначения ширины столбца нам придётся изменять значение другого свойства - ColumnWidth, которое задаётся отнюдь не в пикселах, а в символах (количестве знаков, которые поместятся в ячейке)
Результат - мы не можем со 100% точностью задать ширину столбца, чтобы она была равна заданному количеству пикселей.
Стал искать решение проблемы на сайте Microsoft - и увидел чудный макрос, который сначала ставит приблизительную ширину, и потом В ЦИКЛЕ подгоняет (увеличивает или уменьшает) значение ширины, до тех пор, пока результат не приблизитс к ожидаемому.
Я и сам пользовался подобным решением - для макроса вставки картинок в Excel, но когда пришла пора реализовать то же самое в универсальной программе для вставки изображений в Excel, понял, что надо искать более удобный способ
К тому же, подбор ширины столбца в цикле - очень медленный способ.
И вот что получилось:
Запустив этот макрос, вы увидите, что установленная ширина столбца почти совпадает с исходной.
Небольшое расхождение обычно некритично (всё-таки, Excel - это не графический редактор, где важна точность)
Еще один макрос - для тестирования применённого способа перевода Width в ColumnWidth:
Результат работы этого макроса:
Желаемая ширина = 3 полученная ширина = 3 ColumnWidth (в символах) = 0,33 совпадение = 100,00%
Желаемая ширина = 5 полученная ширина = 5,25 ColumnWidth (в символах) = 0,56 совпадение = 105,00%
Желаемая ширина = 7 полученная ширина = 6,75 ColumnWidth (в символах) = 0,78 совпадение = 96,43%
Желаемая ширина = 9 полученная ширина = 9 ColumnWidth (в символах) = 1,00 совпадение = 100,00%
Желаемая ширина = 11 полученная ширина = 11,25 ColumnWidth (в символах) = 1,38 совпадение = 102,27%
Желаемая ширина = 13 полученная ширина = 12,75 ColumnWidth (в символах) = 1,76 совпадение = 98,08%
Желаемая ширина = 15 полученная ширина = 15 ColumnWidth (в символах) = 2,14 совпадение = 100,00%
Желаемая ширина = 17 полученная ширина = 17,25 ColumnWidth (в символах) = 2,52 совпадение = 101,47%
Желаемая ширина = 19 полученная ширина = 18,75 ColumnWidth (в символах) = 2,90 совпадение = 98,68%
Желаемая ширина = 21 полученная ширина = 21 ColumnWidth (в символах) = 3,29 совпадение = 100,00%
Желаемая ширина = 23 полученная ширина = 23,25 ColumnWidth (в символах) = 3,67 совпадение = 101,09%
Желаемая ширина = 25 полученная ширина = 24,75 ColumnWidth (в символах) = 4,05 совпадение = 99,00%
Желаемая ширина = 27 полученная ширина = 27 ColumnWidth (в символах) = 4,43 совпадение = 100,00%
Желаемая ширина = 29 полученная ширина = 29,25 ColumnWidth (в символах) = 4,81 совпадение = 100,86%
Желаемая ширина = 31 полученная ширина = 30,75 ColumnWidth (в символах) = 5,19 совпадение = 99,19%
Желаемая ширина = 33 полученная ширина = 33 ColumnWidth (в символах) = 5,57 совпадение = 100,00%
Желаемая ширина = 35 полученная ширина = 35,25 ColumnWidth (в символах) = 5,95 совпадение = 100,71%
Желаемая ширина = 37 полученная ширина = 36,75 ColumnWidth (в символах) = 6,33 совпадение = 99,32%
Желаемая ширина = 39 полученная ширина = 39 ColumnWidth (в символах) = 6,71 совпадение = 100,00%
Желаемая ширина = 41 полученная ширина = 41,25 ColumnWidth (в символах) = 7,10 совпадение = 100,61%
Желаемая ширина = 43 полученная ширина = 42,75 ColumnWidth (в символах) = 7,48 совпадение = 99,42%
Желаемая ширина = 45 полученная ширина = 45 ColumnWidth (в символах) = 7,86 совпадение = 100,00%
Желаемая ширина = 47 полученная ширина = 47,25 ColumnWidth (в символах) = 8,24 совпадение = 100,53%
Желаемая ширина = 49 полученная ширина = 48,75 ColumnWidth (в символах) = 8,62 совпадение = 99,49%
Желаемая ширина = 51 полученная ширина = 51 ColumnWidth (в символах) = 9,00 совпадение = 100,00%
Желаемая ширина = 53 полученная ширина = 53,25 ColumnWidth (в символах) = 9,38 совпадение = 100,47%
Желаемая ширина = 55 полученная ширина = 54,75 ColumnWidth (в символах) = 9,76 совпадение = 99,55%
Желаемая ширина = 57 полученная ширина = 57 ColumnWidth (в символах) = 10,14 совпадение = 100,00%
Желаемая ширина = 59 полученная ширина = 59,25 ColumnWidth (в символах) = 10,52 совпадение = 100,42%
Желаемая ширина = 61 полученная ширина = 60,75 ColumnWidth (в символах) = 10,90 совпадение = 99,59%
Желаемая ширина = 63 полученная ширина = 63 ColumnWidth (в символах) = 11,29 совпадение = 100,00%
Желаемая ширина = 65 полученная ширина = 65,25 ColumnWidth (в символах) = 11,67 совпадение = 100,38%
Желаемая ширина = 67 полученная ширина = 66,75 ColumnWidth (в символах) = 12,05 совпадение = 99,63%
Желаемая ширина = 69 полученная ширина = 69 ColumnWidth (в символах) = 12,43 совпадение = 100,00%
Желаемая ширина = 71 полученная ширина = 71,25 ColumnWidth (в символах) = 12,81 совпадение = 100,35%
Желаемая ширина = 73 полученная ширина = 72,75 ColumnWidth (в символах) = 13,19 совпадение = 99,66%
Желаемая ширина = 75 полученная ширина = 75 ColumnWidth (в символах) = 13,57 совпадение = 100,00%
Желаемая ширина = 77 полученная ширина = 77,25 ColumnWidth (в символах) = 13,95 совпадение = 100,32%
Желаемая ширина = 79 полученная ширина = 78,75 ColumnWidth (в символах) = 14,33 совпадение = 99,68%
Желаемая ширина = 81 полученная ширина = 81 ColumnWidth (в символах) = 14,71 совпадение = 100,00%
Желаемая ширина = 83 полученная ширина = 83,25 ColumnWidth (в символах) = 15,10 совпадение = 100,30%
Желаемая ширина = 85 полученная ширина = 84,75 ColumnWidth (в символах) = 15,48 совпадение = 99,71%
Желаемая ширина = 87 полученная ширина = 87 ColumnWidth (в символах) = 15,86 совпадение = 100,00%
Желаемая ширина = 89 полученная ширина = 89,25 ColumnWidth (в символах) = 16,24 совпадение = 100,28%
Желаемая ширина = 91 полученная ширина = 90,75 ColumnWidth (в символах) = 16,62 совпадение = 99,73%
Желаемая ширина = 93 полученная ширина = 93 ColumnWidth (в символах) = 17,00 совпадение = 100,00%
Желаемая ширина = 95 полученная ширина = 95,25 ColumnWidth (в символах) = 17,38 совпадение = 100,26%
Желаемая ширина = 97 полученная ширина = 96,75 ColumnWidth (в символах) = 17,76 совпадение = 99,74%
Желаемая ширина = 99 полученная ширина = 99 ColumnWidth (в символах) = 18,14 совпадение = 100,00%
Желаемая ширина = 101 полученная ширина = 101,25 ColumnWidth (в символах) = 18,52 совпадение = 100,25%
Желаемая ширина = 103 полученная ширина = 102,75 ColumnWidth (в символах) = 18,90 совпадение = 99,76%
Желаемая ширина = 105 полученная ширина = 105 ColumnWidth (в символах) = 19,29 совпадение = 100,00%
Желаемая ширина = 107 полученная ширина = 107,25 ColumnWidth (в символах) = 19,67 совпадение = 100,23%
Желаемая ширина = 109 полученная ширина = 108,75 ColumnWidth (в символах) = 20,05 совпадение = 99,77%
Желаемая ширина = 111 полученная ширина = 111 ColumnWidth (в символах) = 20,43 совпадение = 100,00%
Желаемая ширина = 113 полученная ширина = 113,25 ColumnWidth (в символах) = 20,81 совпадение = 100,22%
Желаемая ширина = 115 полученная ширина = 114,75 ColumnWidth (в символах) = 21,19 совпадение = 99,78%
Желаемая ширина = 117 полученная ширина = 117 ColumnWidth (в символах) = 21,57 совпадение = 100,00%
Желаемая ширина = 119 полученная ширина = 119,25 ColumnWidth (в символах) = 21,95 совпадение = 100,21%
Желаемая ширина = 121 полученная ширина = 120,75 ColumnWidth (в символах) = 22,33 совпадение = 99,79%
Желаемая ширина = 123 полученная ширина = 123 ColumnWidth (в символах) = 22,71 совпадение = 100,00%
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
В 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 будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Размер ячейки по высоте и ширине определяется высотой строки и шириной столбца, на пересечении которых она находится. Если, в вашем случае, нежелательно изменять размеры всей строки или всего столбца, используйте объединенные ячейки нужной величины.
Обратите внимание, что высота строки задается в пунктах, а ширина столбца в символах, поэтому их числовые значения не соответствуют друг другу по фактическому размеру.
Высота строки и ширина столбца в Excel
Программно, без дополнительных макросов, можно изменять высоту строки только в пунктах, а ширину столбца только в символах.
На сайте поддержки офисных приложений Microsoft так написано об этих величинах:
- высота строки может принимать значение от 0 до 409 пунктов, причем 1 пункт приблизительно равен 1/72 дюйма или 0,035 см;
- ширина столбца может принимать значение от 0 до 255, причем это значение соответствует количеству символов, которые могут быть отображены в ячейке.
Смотрите, как сделать все ячейки рабочего листа квадратными.
Высота строки
Для изменения высоты строки используйте свойство RowHeight объекта Range. И не важно, будет объект Range представлять из себя выделенный произвольный диапазон, отдельную ячейку, целую строку или целый столбец — высота всех строк, пересекающихся с объектом Range будет изменена после присвоения свойству RowHeight этого объекта нового значения.
Примеры изменения высоты строк:
Пример 1
Изменение высоты отдельной ячейки:
в результате, строка, в которой находится активная ячейка, приобретает высоту, равную 10 пунктам.
Пример 2
Изменение высоты строки:
в результате, третья строка рабочего листа приобретает высоту, равную 30 пунктам.
Пример 3
Изменение высоты ячеек заданного диапазона:
в результате, каждой из первых шести строк рабочего листа будет задана высота, равная 20 пунктам.
Пример 4
Изменение высоты ячеек целого столбца:
в результате, всем строкам рабочего листа будет назначена высота, равная 15 пунктам.
Ширина столбца
Для изменения ширины столбца используйте свойство ColumnWidth объекта Range. Как и в случае с высотой строки, не важно, будет объект Range представлять из себя выделенный произвольный диапазон, отдельную ячейку, целую строку или целый столбец — ширина всех столбцов, пересекающихся с объектом Range будет изменена после присвоения свойству ColumnWidth этого объекта нового значения.
Примеры изменения ширины столбцов:
Пример 1
Изменение ширины отдельной ячейки:
в результате, столбец, в котором находится активная ячейка, приобретает ширину, равную 15 символам.
Пример 2
Изменение ширины столбца:
в результате, третий столбец рабочего листа (столбец «C») приобретает ширину, равную 50 символам.
Пример 3
Изменение ширины ячеек заданного диапазона:
в результате, каждому из первых четырех столбцов рабочего листа будет задана ширина, равная 25 символам.
Пример 4
Изменение ширины ячеек целой строки:
в результате, всем столбцам рабочего листа будет назначена ширина, равная 35 символам.
Автоподбор ширины
Для автоподбора ширины ячейки в соответствие с размером ее содержимого используйте следующий код:
Свойство ColumnWidths можно использовать для указания ширины каждого столбца в поле списка с несколькими столбцами. Для чтения и записи, String.
Синтаксис
выражения. ColumnWidths
выражение Переменная, представляющие объект ListBox.
Примечания
Свойство ColumnWidths содержит значение, указывав ширину каждого столбца в дюймах или сантиметрах, в зависимости от системы измерений (США или метрики), выбранной в поле Системы измерения на вкладке Номер диалогового окна Региональные параметры панели управления Windows. Параметр по умолчанию — 1 дюйм или 2,54 сантиметра. Параметр свойства ColumnWidths должен быть значением от 0 до 22 дюймов (55,87 см) для каждого столбца в поле списка или в поле комбо.
Чтобы отделить записи столбцов, используйте полуколоны (;) как сепараторы списка (или сепаратор списка, выбранный в поле сепаратор списка на вкладке Номер диалоговой окне Региональные параметры).
Значение 0 приводит к скрытию столбца. Любые или все параметры свойства ColumnWidths могут оставаться пустыми. Пустой параметр можно создать, указав разделитель без предшествующего ему значения. В результате пустых значений Microsoft Access автоматически устанавливает ширину столбца по умолчанию, которая изменяется в зависимости от количества столбцов и ширины окна комбо или списка.
В Visual Basic используйте строковую экспрессию для набора значений ширины столбца в twips. Ширина столбцов разделена полуколонами. Чтобы указать другую единицу измерения, включай единицу измерения (см или в). Например, следующее выражение строки указывает ширину трех столбцов в сантиметрах.
Вы также можете использовать это свойство, чтобы скрыть один или несколько столбцов.
Если оставить параметр свойства ColumnWidths пустым, Access задает ширину каждого столбца как общую ширину поля списка или комбо, разделенного на количество столбцов.
Если задатая ширина столбца слишком широка, чтобы полностью отображаться в поле комбо или списке, наиболее правильные столбцы скрыты и появится горизонтальная полоса прокрутки.
Если указать ширину для некоторых столбцов, но оставить параметр для других пустым, Access делит оставшуюся ширину на количество столбцов, для которых не указана ширина. Минимальная рассчитанная ширина столбца — 1440 twips (1 дюйм).
Например, следующие параметры применяются к 4-дюймовой поле списка с тремя столбцами.
Это свойство отличается от свойства ColumnWidth, которое указывает ширину указанного столбца в таблице данных.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Читайте также: