• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel metrics: how to workaround with pixels, points and characters

SirJB7

SirJB7

Excel Rōnin
Hi!

Last week I was checking the non-replied posts and I found one from user pacochin that was referred to a diagram of train's time schedule. I liked the design so I decided to excercise the old and forgotten objects like the 'Line' one.

Everything went on without problems, exception made with Excel metrics, as I tried to adjust row heights and column widths from VBA code. Just maybe there's an analogy between how Excel measures heights and widths and the infamous 640Kb of memory in very earliers PC.

As I have always wondered why there wasn't a straight and clear relation between the pair of numbers displayed when we change a row height or a column width, I first decided to go to Microsoft website. As expected, I got dissapointed when I only found this http://office.microsoft.com/en-us/excel-help/measurement-units-and-rulers-in-excel-HP001151724.aspx. (For those who'll follow the links chain, what does a unit may have to do related to the width of the 0 character of normal style font??? Isn't it a little obscure?).

Searching on the web, I only found references directs or indirects to that article or its inner figures. I was beginning to ask me seriously why, when I decided to take the experimental path, so I build a spreadsheet with differents heights and widths and fonts and sizes, wrote a little VBA code, and got the data in a clean new sheet.

Think it was easy? So did I, just as I divided the figures got to obtain the magic number called ratio... and nothing! Differences everywhere in column widths, and something more accurate, nearly exact, in row heights: the same relation 4:3 that MS stated in their very-extremely brief and incomplete (I now understand why, it's impossible to explain how do they measure without getting embarrased) page ut-supra.

I had to do work with pixels (the number between parenthesis in sheets) for the column widths, and in points (the number not between parenthesis in sheets) for the row heights. And I'm still wondering why, for the sake of whoever everyone wants to, why the properties Row(i).RowHeight and Column(j).ColumnWidth retrieve incoherent and incongruent values?

Even after having made what I would call nearly magic, I doubted it would function, so I zoomed to the maximum available 400% the worksheet, only to confirm what my eyes make me thought it was still wrong: I had to define an adjusting correction constant to each row, remembering that someplace somewhere I had seen that the border's single line was 0.75 point width.

So, I honestly believe that is incredible that after more than 10 or 15 years of Excel evolution, Microsoft still doesn't arrange methods simple and clear and congruent to accomplish such silly things like resizing rows and columns.

Or, in the other hand, why am I so incapable of finding the so simple instruction, method or property to discover that 1+1 still remains 2, and not something like a gaussian distribution of the first million Pi's digits.

Thanks a lot in advance.

Regards!

PS: if anyone at Chandoo.org considers that this post should be moved to forum "VBA & Macros", please do it if it's possible, or let me know it to repost. Thanks.
 
SirJB7


These are probably better posts to describe what is going on:


http://support.microsoft.com/kb/214123

or

http://processtrends.com/pg_charts_cell_size.htm
 
Hi, Hui!

I decided to walk the Socratic path of "I only know that I know nothing", and began from scratch. Clean new workbook (none of the previous used for testing), different fonts, type of letter and borders, wrote down manually from Excel, let a Sub did its work from VBA, coded formulaes as stated in the second link from your post (honestly the one from MS is embarrasing, I just added two graphics copied from processtrends because it's absolutely clear the incongruency, but that's irrelevant).

Let's focus on the problem: column width is incredible, it seems Pandora's box.

I'd appreciate if you could check my calculations in sheet "Measures" of the uploaded file (http://www.2shared.com/file/7Un8wExL/Excel_metrics__how_to_workarou.html), 'cause even they're very very simple, I'm unable to reproduce the very same figures that they say can be calculated with the simple formula "Width[chars]=(Width[pixels]-5)/5". I think I managed to transform it correctly in "Width[pixels]=(Width[chars]*5)+5", otherwise I'm really mad!

I've found differences between the values of widths theoretical and real, and that differences doesn't follow a lineal pattern at least, that's to say the difference in % isn't constant.

Columns B:E are copied from screen, F:G generated with Sub RetrieveMeasures, H:I calculated with processtrends.com formula, and J is the difference in %.

Thanks again, and good luck for all! I think we'll need it. :)

Further suggestions or ideas, more than welcome.

Regards!
 
Hi ! Is this post still relevant ? 'Cause I've opened this Pandora's box

to make VBA efficient to convert pixels to .columnwith (characters) ...
 
Hi, Marc L!

Now it's still relevant if there's something new that contributes to solve or explain the issues.

Regards!
 
So the .ColumnWidth depends on the screen's dpi and the default police in a worksheet.

I no really find how Excel calculate this .ColumnWidth but a way to perform an accurate conversion from pixels to "width of characters" ...


I've tested my code on computers with differents dpi or default police, it seems efficient.

It looks like yours above, pixels - ret / div but the way is how to compute these "ret" & "div",

without falling in a "gas-works" (so was my first version which worked only for 96 dpi & the Arial 10 police).

Last point : it's under Excel 2003 for Windows.


If still interested, should I publish the code in this post or in an other section of this forum ?


Regards
 
Hi, Marc L!

If it's related to this topic, which I think it is, you can post it here, or start another topic and paste here the link to that topic so people who might read this one could have the chance to get the proper link.

If I were you I'd post a link to the file here as it seems to be strictly related to this issue, but both alternatives are equally valid.

Regards!
 
Thanks SirJB7 !   Before the code, some points ...


- The code is extracted from a global project which purpose is to manage the width of cells relative to pictures, like a catalog.

   So if some instructions could appear odd, don't forget behind the global project and others using the conversion function.


- The code is "as is" from a ThisWorbook class ...

   Px_ variables and the Pix2Col function could be declared as Public in a conversion module standard.


- Before using the Pix2Col function, PixInit must be called before to compute the variables  (see the PixTest example).


- A Windows gdi32 function is called to obtain the screen's dpi.


Now the code is in the next reply ...
 
Code: 
'  Pixels to ColumnWidth  v2              2012/09/01              Excel 2003 SP3 / Windows

'  PxU : pixels <=> .ColumnWidth = 1

'  Pixels X = points X / PxP      Example :  .Width / PxP

'  Pixels Y = points Y / PyP                .Height / PyP
 
 
Private PxD As Integer, PxP, PxR As Integer, PxU As Integer, PyP
 
 
Private Declare Function GetDC Lib "user32" (ByVal hWnd&) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc&, ByVal nIndex&) As Long
 
 
Function GetDev(ByVal L&)
         GetDev = GetDeviceCaps(GetDC(0), L&)
End Function
 
 
Function Pix2Col(Pix, Optional Pts As Boolean = False) As Single
       V = Round(Pix / IIf(Pts, PxP, 1) + 0.001, 0)
 
    If V > 0 Then
              C = Round(IIf(V < PxU, V / PxU, (V - PxR) / PxD) + 0.0001, 2)
        Pix2Col = IIf(C > 255, 255, C)
    End If
End Function
 
 
Private Sub PixInit()
    If PxP > 0 Then OK = Pix2Col(ActiveCell.Width, True) = ActiveCell.ColumnWidth
 
    If Not OK Then
        If PxP = 0 Then PxP = 72 / GetDev(88):  PyP = 72 / GetDev(90)
 
        With Columns(Columns.Count)
                      CW = .ColumnWidth
            .ColumnWidth = 1
                     PxU = .Width / PxP
            .ColumnWidth = .Parent.StandardWidth
                     PxD = Fix(.Width / PxP / .ColumnWidth)
            Do
                     PxR = Round(.Width / PxP - .ColumnWidth * PxD, 0)
                     nOk = PxR <> PxU - PxD
                     PxD = PxD + nOk
            Loop While nOk And PxD > 1
 
            .ColumnWidth = CW
        End With
 
        If PxD < 2 Then Beep: End
    End If
End Sub
 
 
'--------------------------- Example ---------------------------
 
Function Pts2(V, D)
         Pts2 = Format(V, "0.00 points = ") & V / D & " pixels"
End Function
 
 
Sub PixTest()
    PixInit
 
    With ActiveCell
        MsgBox "X :  " & Pts2(.Width, PxP) & "    (.ColumnWidth = " & _
                Format(Pix2Col(.Width, True), "0.00)") & vbLf & vbLf & _
                "Y :  " & Pts2(.Height, PyP), , "  ActiveCell"
    End With
End Sub
 
Hi Marc L, I think this is what I'm looking for, but I don't quite understand all the code (probably because it's written in VB instead of VBA?). I'm especially referring to the "&#39;"(and similar) entries. I'll try to decipher this on my own, but appreciate any help with this.

Marc L said:
[pre]
Code: 
&#39;   Pixels to ColumnWidth  v2               2012/09/01              Excel 2003 SP3 / Windows

&#39;   PxU : pixels &#60;=&#62; .ColumnWidth = 1

&#39;   Pixels X = points X / PxP       Example :  .Width / PxP

&#39;   Pixels Y = points Y / PyP              .Height / PyP

End Sub
[/pre]
Click to expand...
 
You must log in or register to reply here.
Back
Top