CODEWITHSUNDEEP
excelvba
sigil
sigil

Reputation: 9546

How to set Excel column widths to a certain number of pixels?

I have the following data set on a worksheet:

SheetName|ColumnIndex|Pixels
---------+-----------+------
abc      |1          |50
abc      |2          |150
def      |1          |125

For each sheet, I'd like to set the column width to the appropriate number of pixels, using something like:

Sub setColumn (sheetName As string, columnIndex As long, pixels As long)
    width=getWidthInCharacters(pixels)
    ThisWorkbook.Sheets(sheetName).Cells(1, columnIndex).EntireColumn.ColumnWidth = width
End Sub

I haven't been able to figure out how to write the getWidthInCharacters() function. How do I convert pixels to characters, or possibly set .ColumnWidth to pixels directly?

Upvotes: 4

Views: 13775

Answers (2)

Thomas Ertl
Thomas Ertl

Reputation: 122

.ColumnWidth does not depend on theme font selection, but pixel width does: https://support.microsoft.com/en-us/kb/214123

'pixel width of column A
Debug.Print (Columns("A").Width / 72) * ThisWorkbook.WebOptions.PixelsPerInch

After some reading and thinking, my solution:

Sub setColumnWidth(rColumnWidth As Range, iPixelWidth As Integer)
    ' set column width by pixels

    ' check status ScreenUpdating
    Dim bScreenUpdatingState As Boolean
    bScreenUpdatingState = Application.ScreenUpdating
    ' set status ScreenUpdating
    If bScreenUpdatingState = True Then Application.ScreenUpdating = False

    Dim iPointsPerInch As Byte
    iPointsPerInch = 72

    Dim iPixelsPerInch As Byte
    iPixelsPerInch = ThisWorkbook.WebOptions.PixelsPerInch

    ' check 2 column widths: get iPointDelta
    Dim rColumn As Range
    Set rColumn = rColumnWidth.EntireColumn
    rColumn.ColumnWidth = 1
    Dim iPoint_1 As Single
    iPoint_1 = rColumn.Width
    rColumn.ColumnWidth = 2
    Dim iPoint_2 As Single
    iPoint_2 = rColumn.Width
    Dim iPointDelta As Single
    iPointDelta = iPoint_2 - iPoint_1

    ' set column width to iPixelWidth
    Dim iPoint_New As Single
    iPoint_New = iPixelWidth / iPixelsPerInch * iPointsPerInch
    Dim iChar_New As Single
    iChar_New = (iPoint_New - (iPointDelta - 1.5)) / iPointDelta
    rColumn.ColumnWidth = iChar_New

    ' reset status ScreenUpdating
    If bScreenUpdatingState = True Then Application.ScreenUpdating = True
End Sub

To run the sub setColumnWidth:

Sub call_setColumnWidth()
    Dim r As Range
    Set r = ActiveSheet.Range("C1")
    setColumnWidth r, 70
End Sub

Upvotes: 2

tobriand
tobriand

Reputation: 1167

I'm sorry to tell you, but in my experience, you can't. Column width is measured in points, and whilst you can - in theory - convert points to pixels, Excel won't listen very precisely when you assign them. They also seem to vary somewhat from monitor to monitor. Basically, points are fractions of inches, pixels are dots on the screen. Windows has a notion (right or wrong) of how many pixels there are to a point given a particular output device.

You can write a function that tweaks column width, but usually the approach has to be

  • Find the smallest contextual value that excel is willing to increment a column width by (say, store the original value, then assign .ColumnWidth = dblOriginal + 0.01. Check if columnwidth has changed - if it has, you just made a 1-pixel adjustment. If it hasn't, you need a bigger number than 0.01.
  • Find a final column width in pixels that you want, and repeat this first step until you've incremented the column width that many times.
  • Check the result, and see if it looks OK.

Word of warning: this is horrible, slow, and not good code, and if they've fixed column widths in versions of Excel after 2010, then you might be lucky and just be able to use a pixels-to-points function, convert and assign. There are some around, just in my experience they didn't give me consistent results on different screens on the same machine. Really weird that one.

Upvotes: 2

Related Questions