Thanks:  0
Likes:  0

# Thread: Column width in centimeters?

1. ## Column width in centimeters?

I have a paper form that I would like to fill in by using the data entered in cells in Excel and then printing on to the form.

To get the form to match the Excel cells/columns, I need to be able to specify the column width in Centimeters and not the default pixels that Excel displays.

Is this possible, or am I trying something that isn't possible?

2. ## Re: Column width in centimeters?

See:
Q130050 - XL: Macros to Set Row Height and Column Width (in inches or
centimeters)
http://support.microsoft.com/support.../Q130/0/50.ASP

In the above article, scroll down to see a macro that allows you to specify the row and column widths in centimeters.

Column widths are not really measured in any "unit" as such. Instead, the number refers to the number of characters which can be displayed in the column. For variable width fonts such as Arial, the "0" character is used. In VBA, the ColumnWidth property uses this measure of width, and the Width property uses Points.

This may be useful to you:

1 point = 1/72 inches.
1 inch = 72 points
! point = 0.035 centimeters (or .35 millimetre)

Regards,

Mike

3. ## Re: Column width in centimeters?

Further to my previous post, see these macros from http://www.erlandsendata.no/english/...owcolumnmm.htm

1. Set row height and column width in millimeters
Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
If ColNo < 1 Or ColNo > 255 Then Exit Sub
Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
End Sub

Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
If RowNo < 1 Or RowNo > 65536 Then Exit Sub
Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub

2. This example macro shows how you can set the row height for row 3 and the column width for column C to 3.5 cm:
Sub ChangeWidthAndHeight()
SetColumnWidthMM 3, 35
SetRowHeightMM 3, 35
End Sub

HTH

Mike

4. ## Re: Column width in centimeters?

Mike

Thanks very much. I'm not having a good day today !!

Do I need to run the first macro and then the second? I've tried both but can't seem to get it to work.

My requirements are for width only:

SetColumnWidthMM 1, 30
SetColumnWidthMM 2, 30
SetColumnWidthMM 3, 109
SetColumnWidthMM 4, 23
SetColumnWidthMM 5, 19
SetColumnWidthMM 6, 19
SetColumnWidthMM 7, 22
SetColumnWidthMM 8, 22
SetColumnWidthMM 9, 18

5. ## Re: Column width in centimeters?

Zorax,

Sorry for the delay in getting back to you (it’s now 12.45am Sunday morning my time).

These are the macros you need. They work perfectly on my machine (Excel XP). Put both macros in a standard module.

Code:
```Sub ChangeWidthAndHeight()
SetColumnWidthMM 1, 30
SetColumnWidthMM 2, 30
SetColumnWidthMM 3, 109
SetColumnWidthMM 4, 23
SetColumnWidthMM 5, 19
SetColumnWidthMM 6, 19
SetColumnWidthMM 7, 22
SetColumnWidthMM 8, 22
SetColumnWidthMM 9, 18

End Sub```
Code:
```Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
If ColNo < 1 Or ColNo > 255 Then Exit Sub
Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
End Sub```
Note that the first macro “ChangeWidthAndHeight” calls the second macro – only the first macro will show in the macro dialog box (Tools menu | Macro | Macro – click the macro and then click Run).

I should mention that “CentimetersToPoints” is a VBA application (see the second macro). From the VBE help file – to set the left margin of Sheet1 to 5 centimeters:
Code:
```Worksheets("Sheet1").PageSetup.LeftMargin = _
Application.CentimetersToPoints(5)```
Regards,

Mike

6. ## Re: Column width in centimeters?

Mike

Thank you, it works perfectly. As a beginner, there are bits of this I don't understand (like where is the call procedure in the first macro and why doesn't the second macro show in the dialog box) to name just a couple of things!

Anyway, I'll keep looking at it and perhaps it'll make sense eventually.

Thanks again.

7. ## Re: Column width in centimeters?

Hi Zorax,

To help you understand what is happening consider the two simple routines below.
Code:
```Sub test()
Dim strMyString As String
'declare a variable of String data type
strMyString = "Hello"
'add a value to that variable
DisplayString strMyString
'pass that value as an argument to another routine
End Sub

Sub DisplayString(strAString As String)
'a routine that takes a String data type as an argument
MsgBox strAString
'and then uses that value
End Sub```
Now, the first routine is calling the second with the line "DisplayString strMyString". This calls the routine (DisplayString) and passes an argument (strMyString) to that routine. The second routine then uses that argument. Note that an alternative way of calling the routine would be "Call DisplayString(strMyString)", which is perhaps a little more self-explanatory.

As for the routines showing in the list of available macros - the first will and the second will not. The reason, as you may now have guessed, is that the first is run without any arguments and so may simply be executed. The second will only run with the necessary argument passed to it and so will not be displayed in the list.

The same basis applies to the routines that you have been given.

HTH

8. ## Re: Column width in centimeters?

Thanks for that Richie, now I understand. In my ignorance I always thought that you had to use the call command. I also now understand why the second routine wouldn't show in the dialog box.

Thanks again, I'm learning !!

9. ## Re: Column width in centimeters?

This is a really old thread, but I just stumbled upon this when I was trying to adjust my column width and row height in cm.

I'm using Excel 2010. Under Excel 2010, there is another view mode, the page layout mode. In that mode, everything is in cm, as long as you default the measurement unit into cm.

So, instead of via VB, you can do it via page layout mode.

HTH
Henry

## User Tag List

#### Posting Permissions

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