Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Square Grid (any)

    What row height and column width do I use to create a grid of perfect squares? Thanks!

    -tmg9671

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Square Grid (any)

    This problem is not trivial, since Excel measures row height in points and column width in a number of zeros (characters). The following macro will make the column width equal to the row height of the first row, so set the row height to what you want before running the macro. The loop is because Excel often does not set things correctly the first time.

    Sub MakeSquareCells()
    Dim i As Integer
    For i = 1 To 4
    With ActiveSheet
    .Cells.ColumnWidth = .Cells(1, 1).ColumnWidth * _
    .Cells(1, 1).RowHeight / .Cells(1, 1).Width
    End With
    Next i
    End Sub

  3. #3
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Square Grid (any)

    Thank you!!! It worked great!!

    -Troy

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Square Grid (any)

    Hans, I'm intrigued. Is the 4 iterations of particular significance, or does it correspond to a level of confidence for XL to get things "right"?

    Alan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Square Grid (any)

    It doesn't have an intrinsic meaning. In my experience, the column width changes the first three times, and becomes stable after that. So three iterations would probably be OK, the four is to be on the safe side.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Square Grid (any)

    Hi Hans,

    Curiously, despite your efforts the end result usually isn't a square. You can test this yourself with:

    Sub TestGrid()
    Dim i As Integer
    Dim j As Integer
    With ActiveSheet
    .Cells(1, 1).RowHeight = 10
    For i = 1 To 20
    .Cells(1, 1).RowHeight = .Cells(1, 1).RowHeight + 0.25
    For j = 1 To 4
    .Cells.ColumnWidth = .Cells(1, 1).ColumnWidth * _
    .Cells(1, 1).RowHeight / .Cells(1, 1).Width
    Next j
    MsgBox "Width: " & .Cells(1, 1).Width & vbCrLf & "Height: " & .Cells(1, 1).RowHeight
    Next i
    End With
    End Sub

    On my system, the result is usually out by 0.25 or 0.5, depending on the row height

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Square Grid (any)

    I know - the code I posted is the best approximation I could find. Width increases in units of .75 points and RowHeight in units of .25 points on my system, so a discrepancy of .25 is "natural". Why it is off by .5 sometimes beats me (it doesn't help to increase the number of iterations).
    The cells look pretty square to the eye, though.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Square Grid (any)

    I don't think I've ever understood this question - why is it difficult to make a grid of squares? Can't you just resize all the columns and rows to the same number of pixels and come out with perfect squares? Or have I oversimplified this and missed something?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Square Grid (any)

    Perfectly acceptable <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •