Программно задать ширину столбца Excel в пикселах (Width, ColumnWidth)

Как программно (макросом) задать ширину столбца в пикселах

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

Получить ширину столбца в пикселах - не проблема, для этого есть свойство Width:

MsgBox "Ширина столбца с активной ячейкой: " & ActiveCell.Width

Но, увы, это свойство доступно только для чтения, - а для назначения ширины столбца нам придётся изменять значение другого свойства - ColumnWidth, которое задаётся отнюдь не в пикселах, а в символах (количестве знаков, которые поместятся в ячейке)

Результат - мы не можем со 100% точностью задать ширину столбца, чтобы она была равна заданному количеству пикселей.

Стал искать решение проблемы на сайте Microsoft - и увидел чудный макрос, который сначала ставит приблизительную ширину, и потом В ЦИКЛЕ подгоняет (увеличивает или уменьшает) значение ширины, до тех пор, пока результат не приблизитс к ожидаемому.

Я и сам пользовался подобным решением - для макроса вставки картинок в Excel, но когда пришла пора реализовать то же самое в универсальной программе для вставки изображений в Excel, понял, что надо искать более удобный способ

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

И вот что получилось:

Sub SetColumnWidth()
    w = 100    ' желаемая ширина столбца в пикселах

    cw = IIf(w > 9, (w / 0.75 - 5) / 7, w / 9)    ' вычисляем ширину в символах

    ActiveCell.ColumnWidth = cw    ' устанавливаем новую ширину

    MsgBox "Новая ширина: " & ActiveCell.Width    ' смотрим, что получилось
End Sub

Запустив этот макрос, вы увидите, что установленная ширина столбца почти совпадает с исходной.

Небольшое расхождение обычно некритично (всё-таки, Excel - это не графический редактор, где важна точность)

Еще один макрос - для тестирования применённого способа перевода Width в ColumnWidth:

Sub Test_ColWidth()
    For w = 1 To 400 Step 2    ' желаемая ширина столбца

        cw = IIf(w > 9, (w / 0.75 - 5) / 7, w / 9)    ' вычисляем ColumnWidth
        ActiveCell.ColumnWidth = cw    ' и применяем к активной ячейке

        Debug.Print "Желаемая ширина = " & w, "полученная ширина = " & ActiveCell.Width, _
                    "ColumnWidth (в символах) = " & Format(cw, "0.00"), _
                    "совпадение = " & Format(ActiveCell.Width / w, "0.00%")
    Next w
End Sub

Результат работы этого макроса:

Желаемая ширина = 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%

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

"Магические константы" в коде Андрея:
12 - ширина в пикселях символа "0" шрифта Calibri, размера 11. Который является дефолтным шрифтом Excel с версии 2007.
7 - ширина в пикселях символа "0" шрифта Arial, размера 10. Который является дефолтным шрифтом Excel до 2007.

Т.е. на разных системах формула может отличаться, в зависимости от версии Excel.

Для установки ширины ячейки, заданной в пикселях (PixelSize - требуемый размер в пикселях):

Dim PixelSize As Integer
Dim ColumnSize As Double
...
If PixelSize <= 12 Then
ColumnSize = PixelSize / 12
Else
ColumnSize = 1 + (PixelSize - 12) / 7
End If
ActiveCell.ColumnWidth = ColumnSize

Для установки высоты ячейки, заданной в пикселях (PixelSize - требуемый размер в пикселях):

Dim PixelSize As Integer
Dim RowSize As Double
...
RowSize = PixelSize * 0.75
ActiveCell.RowHeight = RowSize

Если размеры ячейки необходимо задавать в мм, то предварительно переведите мм в пиксели, используя формулу (mmSize - требуемый размер в мм):

Dim mmSize As Double
Dim PixelSize As Integer
...
PixelSize = mmSize * 48 / 12.7

А свойства ActiveCell.Width и ActiveCell.Height возвращают вообще-то не пиксели, а пункты, и чтобы из них получить пиксели, их надо поделить на 0.75

PixelSize должно быть либо целого типа либо вещественное, но округленное до целых, т.к. пиксели д.б. целыми

Игорь, спасибо!
Воспользовался вашим способом.

Алишер, в Excel есть такая возможность, - называется «умные таблицы».
Выделяете таблицу свою (вместе с заголовками), и на ленте (вкладка Главная) нажимаете «Форматировать как таблицу»
После этого, при добавлении новой записи ниже таблицы, Excel автоматически скопирует форматирование предыдущей строки (со всеми выпадающими списками)
PS: работа и вид этих таблиц зависит от версии Excel

Как бы сделать следующее: Есть список (Вверху: шапка, исходные данные, ниже -тело списка, но для заполнения пока доступна одна пустая строка, определенные строки ниже - скрыты), по мере заполнения строки выше, автоматически отображается след. строка (одна) ниже? Наподобие таблиц access. Буду весьма признателен подсказке

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
         ___   ____              _             
__ __ |_ _| / ___| _ _ (_) _ __ ___
\ \/ / | | \___ \ | | | | | | | '_ ` _ \
> < | | ___) | | |_| | | | | | | | | |
/_/\_\ |___| |____/ \__,_| _/ | |_| |_| |_|
|__/
Введите код, изображенный в стиле ASCII-арт.

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

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