Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column width in pixels how?
Hello,
How can I determine the column width in pixels in VBA (excel 2002 sp2)? In a new workbook I try to determine the width of column A. Clicking between 2 columns displays the following information (provided by excel - the pixel value is in this case correct): "Width: 8.43 (64 pixels)" So I need the value 64 in my VBA code, but HOW? Allready tried to following: range("A1").width returns 48 (?!?!?!) range("A1").Columnwidth returns 8.43 ActiveWindow.PointsToScreenPixelsX(range("A1").wid th) returns 146 ActiveWindow.PointsToScreenPixelsX(range("A1").wid th) returns 106 Also I have exactly the same problem when trying to determine the row height... Can anybody help me? thanks in advance, Robbie De Sutter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column width in pixels how?
Hi Robbie,
COLUMNWIDTH: One unit of column width is equal to the width of one character in the Normal style. range("A1").Columnwidth returns 8.43 characters WIDTH: Returns or sets an object's width, in points. range("A1").width returns 48 points. 72 point/inch=.6666"=64 pixels @ 96 pixels/inch So... Sub testwidth() Sheets("sheet3").Activate screenres = 96 '96/inch mypoints = Sheets("sheet3").Range("A1").Width ' returns 48 points mychars = Sheets("sheet3").Range("A1").ColumnWidth ' returns 8.43 chars mypixels = (mypoints / 72) * screenres 'pixel width of column Debug.Print mypoints, mychars, mypixels ' returns 48 8.43 64 End Sub The column width is 48 points or 8.43 characters or 64 pixels. I couldn't get ActiveWindow.PointsToScreenPixelsX to return anything but zero. -- John johnf202 at hotmail dot com "robbie de sutter" wrote in message ... Hello, How can I determine the column width in pixels in VBA (excel 2002 sp2)? In a new workbook I try to determine the width of column A. Clicking between 2 columns displays the following information (provided by excel - the pixel value is in this case correct): "Width: 8.43 (64 pixels)" So I need the value 64 in my VBA code, but HOW? Allready tried to following: range("A1").width returns 48 (?!?!?!) range("A1").Columnwidth returns 8.43 ActiveWindow.PointsToScreenPixelsX(range("A1").wid th) returns 146 ActiveWindow.PointsToScreenPixelsX(range("A1").wid th) returns 106 Also I have exactly the same problem when trying to determine the row height... Can anybody help me? thanks in advance, Robbie De Sutter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column width in pixels how?
Hello John, all
Thank you very much for your reply. It works like a charm ;) The ActiveWindow.PointsToScreenPixelsX returns zero when the cell is not visible (for example when the vba editor is in front of the worksheet). Any sollution with the ActiveWindow.PointsToScreenPixelsX would have required that the workbook and worksheet is visible and on top of all other windows, but your solution is much better. Meanwhile I've found on the internet the following subroutine to determine the current DPI setting of the device (in VBA): --- 'define API entries Private Declare Function CreateICA Lib "gdi32" (ByVal sDriver As String, _ ByVal sDevice As String, ByVal sOut As String, ByVal pDVM As Long) As Long Private Declare Function DeleteDC Lib "gdi32" (ByVal hDC As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, _ ByVal nIndex As Long) As Long 'retrieve X and Y DPI setting Sub GetScreenDPI() Dim hDC As Long Dim x As Long Dim y As Long hDC = CreateICA("DISPLAY", vbNullString, vbNullString, 0) If (hDC < 0) Then x = GetDeviceCaps(hDC, 88) 'screen res x y = GetDeviceCaps(hDC, 90) 'screen res y DeleteDC (hDC) End If End Sub --- Sincerly yours, Robbie De Sutter "jaf" wrote in message ... Hi Robbie, COLUMNWIDTH: One unit of column width is equal to the width of one character in the Normal style. range("A1").Columnwidth returns 8.43 characters WIDTH: Returns or sets an object's width, in points. range("A1").width returns 48 points. 72 point/inch=.6666"=64 pixels @ 96 pixels/inch So... Sub testwidth() Sheets("sheet3").Activate screenres = 96 '96/inch mypoints = Sheets("sheet3").Range("A1").Width ' returns 48 points mychars = Sheets("sheet3").Range("A1").ColumnWidth ' returns 8.43 chars mypixels = (mypoints / 72) * screenres 'pixel width of column Debug.Print mypoints, mychars, mypixels ' returns 48 8.43 64 End Sub The column width is 48 points or 8.43 characters or 64 pixels. I couldn't get ActiveWindow.PointsToScreenPixelsX to return anything but zero. -- John johnf202 at hotmail dot com "robbie de sutter" wrote in message ... Hello, How can I determine the column width in pixels in VBA (excel 2002 sp2)? In a new workbook I try to determine the width of column A. Clicking between 2 columns displays the following information (provided by excel - the pixel value is in this case correct): "Width: 8.43 (64 pixels)" So I need the value 64 in my VBA code, but HOW? Allready tried to following: range("A1").width returns 48 (?!?!?!) range("A1").Columnwidth returns 8.43 ActiveWindow.PointsToScreenPixelsX(range("A1").wid th) returns 146 ActiveWindow.PointsToScreenPixelsX(range("A1").wid th) returns 106 Also I have exactly the same problem when trying to determine the row height... Can anybody help me? thanks in advance, Robbie De Sutter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can we change units of column width to pixels? | Excel Discussion (Misc queries) | |||
column pixels vs column width | Excel Discussion (Misc queries) | |||
How to change column pixels to the standard size? | Excel Worksheet Functions | |||
Size of column in pixels | Excel Discussion (Misc queries) | |||
some columns 30 characters 155 pixels others 310 pixels why | Excel Discussion (Misc queries) |