Как программно (макросом) задать ширину столбца в пикселах
Вроде бы - элементарное действие... но нет, здесь 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 - требуемый размер в пикселях):
Для установки высоты ячейки, заданной в пикселях (PixelSize - требуемый размер в пикселях):
Если размеры ячейки необходимо задавать в мм, то предварительно переведите мм в пиксели, используя формулу (mmSize - требуемый размер в мм):
А свойства ActiveCell.Width и ActiveCell.Height возвращают вообще-то не пиксели, а пункты, и чтобы из них получить пиксели, их надо поделить на 0.75
PixelSize должно быть либо целого типа либо вещественное, но округленное до целых, т.к. пиксели д.б. целыми
Игорь, спасибо!
Воспользовался вашим способом.
Алишер, в Excel есть такая возможность, - называется «умные таблицы».
Выделяете таблицу свою (вместе с заголовками), и на ленте (вкладка Главная) нажимаете «Форматировать как таблицу»
После этого, при добавлении новой записи ниже таблицы, Excel автоматически скопирует форматирование предыдущей строки (со всеми выпадающими списками)
PS: работа и вид этих таблиц зависит от версии Excel
Как бы сделать следующее: Есть список (Вверху: шапка, исходные данные, ниже -тело списка, но для заполнения пока доступна одна пустая строка, определенные строки ниже - скрыты), по мере заполнения строки выше, автоматически отображается след. строка (одна) ниже? Наподобие таблиц access. Буду весьма признателен подсказке
Отправить комментарий