Resizing cell in inches

Hi,

Does the following help
http://support.microsoft.com/kb/73591

Remember 1inch = 96 pixels = 72 points

Rgds

I think pixels/inch varies by display.

Perhaps you can adapt this:

Sub MakeSquareCells()
    ' shg 2006
    ' for my LaserJet:
    Const fHScal    As Single = 1.08
    Const fVScal    As Single = 0.99

    Dim fPts        As Single
    Dim fCnv        As Single
    Dim sUnits      As String
    Dim r           As Range

    sUnits = LCase(Application.InputBox(Prompt:="Inches (inch) or millimeters (mm)?", _
                                        Title:="Units?", _
                                        Default:="inch", _
                                        Type:=2))
    If sUnits <> "inch" And sUnits <> "mm" Then Exit Sub

    fPts = Application.InputBox(Prompt:="Cell size, in " & sUnits & "?", _
                                Title:="Size?", _
                                Default:="1", _
                                Type:=1)

    fCnv = IIf(sUnits = "inch", 72, 72 / 25.4)
    fPts = fCnv * fPts  ' convert to points

    If fPts < 0.01 Or fPts > 409 Then Exit Sub

    Set r = ActiveWindow.RangeSelection
    SquareCells r, fPts, fHScal, fVScal

    MsgBox Title:="SquareCells", _
           Prompt:=Format(fPts / fCnv, "0.000") & " (Target size, " & sUnits & ")" & vbLf & _
                   Format(r.Columns(1).Width / fCnv, "0.000") & " (Actual width)" & vbLf & _
                   Format(r.Rows(1).Height / fCnv, "0.000") & " (Actual height)" & vbLf & _
                   Format(r.Columns(1).Width / r.Rows(1).Height, "0.000") & " (Aspect)"
End Sub

Function SquareCells(r As Range, _
                     ByVal fPts As Single, _
                     Optional fHScal As Single = 1, _
                     Optional fVScal As Single = 1) As Single
    ' Sets all cells in r to be approximately fPts x fPts (points)
    ' Returns the actual aspect H/W
    ' fHScal and fVScal compensate for the printer; obtain by measuring

    ' For scale factors:
    ' Make a sheet with cells of nominal size (e.g., 1") and scale factors set to 1
    ' Measure the horizontal size as printed; aay it's 1.03 inches. Use 1.03 as fHScal
    ' Ditto for vertical size and fVScal

    Dim i           As Long
    Dim sDim        As String

    r.Select

    For i = 1 To 4
        r.Columns.ColumnWidth = r.Columns(1).ColumnWidth / r.Columns(1).Width * fPts / fHScal
        r.Rows.RowHeight = fPts / fVScal
    Next i

    SquareCells = r.Columns(1).Width / r.Rows(1).Height
End Function

Hi Richard,
Thanks for that prompt reply. That link helped me understand better how characters fit into a cell.

Hi shg,
That code is great, especially fVsacl & fHSacl And the flexibilty between Cms & Inches. Wonder how long it took you to come up with this. I was not comfortable working with pixels as they vary.
But i think it needs one correction. In this line ---- fCnv = IIf(sUnits = "inch", 72, 72 / 25.4) ----- there is a typo. For cms, fCnv should be 72 / 2.54. Correct me if I am wrong.

One more thing, since I am still learning, why is SquareCells a function and not a regular sub ? I thought functions were required when a procedure needs to give out a result, like UDFs. Please, can you explain ?

I found this too, at http://www.dailydoseofexcel.com/arch...ths-in-points/ . The suggestion that setting column width needs to be done 3times atleast seems to be true.

Thanks once again and regards to both of you,

Geetha

That code is great, ...

You're welcome, glad it helped.

For cms, fCnv should be 72 / 2.54

The alternative units are mm, not cm

why is SquareCells a function and not a regular sub

It returns the actual aspect of the cells.

The actual aspect of the cells - what does it mean ?

Here, the ratio of height to width.

And how does a Function procedure do that ?

Look at the last line of code.