The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Markus

Hi Markus:

Try this tiny macro:

Sub row_your_boat()
For i = 7 To 23
Rows(i).RowHeight = Rows(i).RowHeight + 8
Next
End Sub

--
Gary''s Student - gsnu2007b


"Markus Obermayer" wrote:

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Markus

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

This should work for the general case. Select the rows you want to change
and then run (Alt+F8) this macro...

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * 0.75
Next
End Sub

The 0.75 Pixels to Points conversion factor was obtained by calculation
using the information at this web page...

http://office.microsoft.com/en-us/ex...346241033.aspx

after opening up the "How is row height measured?" link at the bottom of the
page. I tried setting the spreadsheet to different values and it seems to
work. However, it is possible that there is a dependency on the screen's
font size (dpi) setting (at least in the Windows world) since the 96 in the
"1-inch to 96-pixel" equivalency looks suspiciously like the 96 dpi setting
which is the standard for Windows (but which can be customized via the
Display Panel). If you are willing to work in Points directly, simply remove
the 0.75 multiplication factor and change the prompt in the InputBox
function call to say Points instead of Pixels.

Rick

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

This should work for the general case. Select the rows you want to change
and then run (Alt+F8) this macro...

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * 0.75
Next
End Sub

The 0.75 Pixels to Points conversion factor was obtained by calculation
using the information at this web page...

http://office.microsoft.com/en-us/ex...346241033.aspx

after opening up the "How is row height measured?" link at the bottom of
the page. I tried setting the spreadsheet to different values and it seems
to work. However, it is possible that there is a dependency on the
screen's font size (dpi) setting (at least in the Windows world) since the
96 in the "1-inch to 96-pixel" equivalency looks suspiciously like the 96
dpi setting which is the standard for Windows (but which can be customized
via the Display Panel). If you are willing to work in Points directly,
simply remove the 0.75 multiplication factor and change the prompt in the
InputBox function call to say Points instead of Pixels.

Okay, yes, the conversion factor **is** dependent on the DPI setting (if you
are using Windows). Here is the Windows code to resize your selected rows by
a specified number of pixels. Copy/paste the following code into your code
window and then run (Alt+F8) the IncreaseRowHeightsInPixels subroutine from
your spreadsheet after selecting the rows you want to increase by the number
of pixels you will specify when asked...

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSY = 90 'Pixels/inch in Y

'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72

'The size of a pixel, in points
Public Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long
hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSY)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC
End Function

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * PointsPerPixel
Next
End Sub


Rick

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Try this tiny macro:

Sub row_your_boat()
For i = 7 To 23
Rows(i).RowHeight = Rows(i).RowHeight + 8
Next
End Sub

To be clear for the OP, your subroutine increases the RowHeight by 8 points,
not 8 pixels.

I do notice a slight discrepancy (not your fault, the system's font handler
is doing it) in that the default standard height for Arial - 10 Points is
12.75 points high; adding 8 to that should make the rows 20.75 points high,
but my system shows it becoming 20.25 points high instead.

Rick

Odd... I replied to the OP's message which is cross-posted to two
newsgroups, but my message only picked up this newsgroup to reply to. This
is the first time I can recall this (a newsgroup being ignored in a reply)
ever happening... I wonder why?

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

This should work for the general case. Select the rows you want to change
and then run (Alt+F8) this macro...

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * 0.75
Next
End Sub

The 0.75 Pixels to Points conversion factor was obtained by calculation
using the information at this web page...

http://office.microsoft.com/en-us/ex...346241033.aspx

after opening up the "How is row height measured?" link at the bottom of
the page. I tried setting the spreadsheet to different values and it seems
to work. However, it is possible that there is a dependency on the
screen's font size (dpi) setting (at least in the Windows world) since the
96 in the "1-inch to 96-pixel" equivalency looks suspiciously like the 96
dpi setting which is the standard for Windows (but which can be customized
via the Display Panel). If you are willing to work in Points directly,
simply remove the 0.75 multiplication factor and change the prompt in the
InputBox function call to say Points instead of Pixels.

Rick

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Here is some Windows-only code to resize rows which you pre-selected by a
specified number of pixels. Copy/paste the following code into your code
window and then run (Alt+F8) the IncreaseRowHeightsInPixels subroutine from
your spreadsheet after selecting the rows you want to increase by the number
of pixels you will specify when asked...

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSY = 90 'Pixels/inch in Y

'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72

'The size of a pixel, in points
Public Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long
hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSY)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC
End Function

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * PointsPerPixel
Next
End Sub


Rick

Okay, now I am really confused. I posted this message as a direct response
to the OP's message over in the microsoft.public.excel.programming newsgroup
and it never got posted there... instead, it just got posted over here. I
don't understand why. When I posted my response to Gary''s Student, it went
to both listed newsgroups as it should have; but the messages I posted to
the OP's initial posting only seems to go to this newsgroup. I really don't
understand why... does anyone have an explanation for this? Did I
accidentally change a setting or something?

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Here is some Windows-only code to resize rows which you pre-selected by a
specified number of pixels. Copy/paste the following code into your code
window and then run (Alt+F8) the IncreaseRowHeightsInPixels subroutine
from your spreadsheet after selecting the rows you want to increase by the
number of pixels you will specify when asked...

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSY = 90 'Pixels/inch in Y

'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72

'The size of a pixel, in points
Public Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long
hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSY)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC
End Function

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * PointsPerPixel
Next
End Sub


Rick

Rick,
For what it is worth...
I count six messages from you in the Misc group and only two in Programming
as responses to this post.
I am using Outlook Express as the news reader.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Rick Rothstein (MVP - VB)"
wrote in message
Okay, now I am really confused. I posted this message as a direct response
to the OP's message over in the microsoft.public.excel.programming newsgroup
and it never got posted there... instead, it just got posted over here. I
don't understand why. When I posted my response to Gary''s Student, it went
to both listed newsgroups as it should have; but the messages I posted to
the OP's initial posting only seems to go to this newsgroup. I really don't
understand why... does anyone have an explanation for this? Did I
accidentally change a setting or something?

Rick

According to Micorsoft, in the site listed below, 6 points equals 8 pixels.
However, it seems to me that I read somewhere that Excel adjusts row height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

"Markus Obermayer" wrote:

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Markus

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each and
for several different initial RowHeight settings and, in each test, it has
always increased the height by the number of pixels I specified. Of course,
that is not *proof* that it will always work for every combination of font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this happening
if you increase the spreadsheet's font size by one at a time and look at the
resulting RowHeight that results. Here is the process being done for Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel number
increase. What I am pretty sure is happening is that the font is rounded to
the nearest whole number of pixels for the give fontsize and then the point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on the
Windows operating system; I have no idea how all of this would translate to
on a Mac).

Rick

Thank you. I am using Window Mail (which is really Vista's version of
Outlook Express) and I see the same thing (although one of those was force
posted to the programming newsgroup directly; that is, it was not sent as a
Reply to any existing message).

Rick


"Jim Cone" wrote in message
...
Rick,
For what it is worth...
I count six messages from you in the Misc group and only two in
Programming
as responses to this post.
I am using Outlook Express as the news reader.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Rick Rothstein (MVP - VB)"
wrote in message
Okay, now I am really confused. I posted this message as a direct response
to the OP's message over in the microsoft.public.excel.programming
newsgroup
and it never got posted there... instead, it just got posted over here. I
don't understand why. When I posted my response to Gary''s Student, it
went
to both listed newsgroups as it should have; but the messages I posted to
the OP's initial posting only seems to go to this newsgroup. I really
don't
understand why... does anyone have an explanation for this? Did I
accidentally change a setting or something?

Rick

Hi Rick, No intent to challenge or dispute your code. I was just pointing
out that Excel is not conducive to precision measurement. I have noted that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each and
for several different initial RowHeight settings and, in each test, it has
always increased the height by the number of pixels I specified. Of course,
that is not *proof* that it will always work for every combination of font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this happening
if you increase the spreadsheet's font size by one at a time and look at the
resulting RowHeight that results. Here is the process being done for Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel number
increase. What I am pretty sure is happening is that the font is rounded to
the nearest whole number of pixels for the give fontsize and then the point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on the
Windows operating system; I have no idea how all of this would translate to
on a Mac).

Rick

The 12.4 and 12.52 (row heights) that you cite are the automatically
determined point sizes corresponding to the selected font and its selected
font size... these (point sizes) cannot be set directly because the number
you pick might not correspond to a font size which will physically occupy a
whole number of pixels. But, because font sizing **is** set by the number of
pixels, you can exactly set the number of pixels a font will be allowed to
occupy which, in turn, has a calculated font size for that number of pixels
and, hence, an equivalent point size to match it. I'm not sure if I stated
that clearly or not; but it is, in essence, what the code I posted is doing.

Rick


"JLGWhiz" wrote in message
...
Hi Rick, No intent to challenge or dispute your code. I was just
pointing
out that Excel is not conducive to precision measurement. I have noted
that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or
some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution
settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each
and
for several different initial RowHeight settings and, in each test, it
has
always increased the height by the number of pixels I specified. Of
course,
that is not *proof* that it will always work for every combination of
font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world
display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number
of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and
then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this
happening
if you increase the spreadsheet's font size by one at a time and look at
the
resulting RowHeight that results. Here is the process being done for
Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel
number
increase. What I am pretty sure is happening is that the font is rounded
to
the nearest whole number of pixels for the give fontsize and then the
point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion
for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily
than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on
the
Windows operating system; I have no idea how all of this would translate
to
on a Mac).

Rick

You explained it well enough. I understand the font/pixel relationship and
agree with everything you have stated about setting the pixel measurements.
I think we were on two different trains of thought, so rather than confuse
the OP, I'll drop off this one.

"Rick Rothstein (MVP - VB)" wrote:

The 12.4 and 12.52 (row heights) that you cite are the automatically
determined point sizes corresponding to the selected font and its selected
font size... these (point sizes) cannot be set directly because the number
you pick might not correspond to a font size which will physically occupy a
whole number of pixels. But, because font sizing **is** set by the number of
pixels, you can exactly set the number of pixels a font will be allowed to
occupy which, in turn, has a calculated font size for that number of pixels
and, hence, an equivalent point size to match it. I'm not sure if I stated
that clearly or not; but it is, in essence, what the code I posted is doing.

Rick


"JLGWhiz" wrote in message
...
Hi Rick, No intent to challenge or dispute your code. I was just
pointing
out that Excel is not conducive to precision measurement. I have noted
that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or
some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution
settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each
and
for several different initial RowHeight settings and, in each test, it
has
always increased the height by the number of pixels I specified. Of
course,
that is not *proof* that it will always work for every combination of
font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world
display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number
of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and
then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this
happening
if you increase the spreadsheet's font size by one at a time and look at
the
resulting RowHeight that results. Here is the process being done for
Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel
number
increase. What I am pretty sure is happening is that the font is rounded
to
the nearest whole number of pixels for the give fontsize and then the
point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion
for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily
than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on
the
Windows operating system; I have no idea how all of this would translate
to
on a Mac).

Rick

JLG

You could set the row heights in mm which is not OS font or
resolution-dependent.

Ole Erlandson has code for setting row and column dimensions in mm.

http://www.erlandsendata.no/english/...vbawssetrowcol


Gord Dibben Excel MVP

On Sun, 9 Dec 2007 14:10:01 -0800, JLGWhiz
wrote:

Hi Rick, No intent to challenge or dispute your code. I was just pointing
out that Excel is not conducive to precision measurement. I have noted that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each and
for several different initial RowHeight settings and, in each test, it has
always increased the height by the number of pixels I specified. Of course,
that is not *proof* that it will always work for every combination of font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this happening
if you increase the spreadsheet's font size by one at a time and look at the
resulting RowHeight that results. Here is the process being done for Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel number
increase. What I am pretty sure is happening is that the font is rounded to
the nearest whole number of pixels for the give fontsize and then the point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on the
Windows operating system; I have no idea how all of this would translate to
on a Mac).

Rick