PDA

View Full Version : Solved: Set column width in pixels



dlh
09-21-2009, 02:21 PM
Is there any other way to programmatically change column width besides the ColumnWidth property?

ColumnWidth is hard to use because its units are character-widths in the document's default font. I'm trying to write a macro to copy a range to a new sheet while eliminating all redundant merged cells. The problem is recreating the merged column widths with single columns: e.g., when measured in characters, the width of merged column A & B ≠ width of A + width of B.

dlh
09-22-2009, 11:23 AM
Solved my own problem. There's a linear relationship between width in characters and width in points (or pixels).

(width in points) = (conversion factor) * (width in characters) + (cell margin in points)

On my system the conversion factor = 5.25 points per character, the cell margin = 3.75 points. I'm not sure if it's universal or dependent on the display device.

A function used to figure out the conversion:

Function GetScale(wb as Workbook) as Double()
Dim Result(1 To 2) as Double
Dim Scratch As Workbook
Dim default As String
Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
Set Scratch = Application.Workbooks.Add
default = "Normal"
With Scratch.Styles(default).Font
.Name = wb.Styles(default).Font.Name
.Size = wb.Styles(default).Font.Size
.Bold = wb.Styles(default).Font.Bold
.Italic = wb.Styles(default).Font.Italic
End With
x1 = CDbl(1) 'characters
x2 = CDbl(255) 'characters
Scratch.Sheets(1).Columns(1).ColumnWidth = x1
y1 = Scratch.Sheets(1).Columns(1).Width 'points
Scratch.Sheets(1).Columns(1).ColumnWidth = x2
y2 = Scratch.Sheets(1).Columns(1).Width 'points
Result(1) = (y2 - y1) / (x2 - x1) 'points per character
Result(2) = y1 - x1 * Result(1) 'points
GetScale = Result()
Scratch.Close SaveChanges:=False
End Function

mdmackillop
09-23-2009, 09:16 AM
MsgBox ActiveWindow.PointsToScreenPixelsX(Columns(1).Width)

Cyberdude
09-23-2009, 01:38 PM
Remarkable response, Malcom! Good show! :clap:

Sid