Instead of explaining the steps to creating the grid, I'll just provide a couple of tips. First, the column width is dependent upon the font of the Normal style (which by default is usually Arial). Quoting from Excel's VBA help file (the ColumnWidth property):
"One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used."The row height is measured in points (1pt = 1/72 inch or about 1/28 centimeters). A point is a "unit of measure referring to the height of a printed character" (quoting from the VBA help file for the RowHeight property).
Now, assuming that you are using an Arial font, you create different grid sizes using the following table:
| Font Size | Row Height | Column Width |
|---|---|---|
| 10pt | 12.75 | 1.71 |
| 8pt | 11.25 | 1.43 |
| 6pt | 8.25 | 0.92 |
To make sure that the cells are square, create a square using the drawing tools (making sure you hold down the Shift key, or you'll end up with some arbitrary rectangle).
I've notice that when I print from Excel, the grid doesn't always end up square, so the drawing is no longer the same aspect ratio as on the screen. To get around this, I just copy and paste into PowerPoint. There may be a better solution, but I haven't found it yet.
If you are looking for a way to print graph paper, the following site has some very handy free downloadable graph paper in PDF format.
Here a few related forum threads:
Square Cells:
http://www.mrexcel.com/archive2/50400/58517.htm
http://www.experts-exchange.com/Manager/Applications/MS_Office/Q_10297832.html
Measure Height/Width in Millimeters:
http://www.mrexcel.com/board2/viewtopic.php?t=53527
Posts
4 comments:
The code to square up cells in Excel is pretty easy. You can read the column width in points, and adjust the columnwidth in character units. If you set the row height first, this will square up the columns:
Sub SquareCells()
Dim i As Integer
For i = 1 To 4
With ActiveSheet
.Columns.ColumnWidth = _
.Columns("A").ColumnWidth / .Columns("A").Width * _
.Rows(1).Height
End With
Next
End Sub
You need to loop a few times, because at first attempt, Excel tries to use an arbitrary fraction of a character. In Arial, Excel likes increments of 1/7, in Arial Narrow it's 1/6. For wider columns, you only need a couple loops, but for smaller ones (like the needlepoint grid I wrote this for), you need at least three. Four seems robust enough, and it's so quick no human can detect the looping. (Use ScreenUpdating = False/True if you do notice it.)
The non squareness of the printed grid relates to the fact that the screen fonts are only an approximate representation of the printed truetype fonts, blah blah. What I do is print out one page, get a ruler and measure the grid, and determine a fudge factor to multiply the column width by to square it up. It's only off by a couple percent, but that's enough to look wrong.
- Jon
Good luck with the new blog, I look forward to reading it!
Ross
Wow... look at all the information out here... Square cells in Excel. And I was actually just wondering this. Thanks for whoever posted this... keep up the good work!
Actually, I have been using the following code, which sets the width first:
Private Sub MakeSquareCells()
Dim duh As Long
Dim myPts As Long
Dim myRange As Range
On Error GoTo TheEnd
Set myRange = Application.InputBox("Select a range in which to create square cells", Type:=8)
On Error Resume Next
If myRange.Cells.Count = 0 Then Exit Sub
duh = Val(InputBox("Input Column Width: "))
Application.ScreenUpdating = False
For Each c In myRange.Columns
c.EntireColumn.ColumnWidth = duh
Next c
myPts = myRange(1).Width
For Each r In myRange.Rows
r.EntireRow.RowHeight = myPts
Next r
Application.ScreenUpdating = True
TheEnd:
End Sub
Post a Comment