Resizing cell in inches

**Geetha Gupta** · 11-27-2010, 12:34 AM

Hi Everybody,

I need to redo cell sizes – the width and the height in inches
While searching, I came across the a macro at http://processtrends.com/pg_charts_cell_size.htm

As per this line of code, it seems cell width can be set only in number of characters the cell can accommodate.
Columns(what_col).ColumnWidth = wide_char

OK. So, with Application.DefaultWebOptions.PixelsPerInch, it is possible to convert the reqd cell width in inches to pix and use the formula
Say,
ColumnWidthInPix = ColumnWidthInInches * Application.DefaultWebOptions.PixelsPerInch

And then define cell width in characters with
wide_char = (wide_pix - 5) / 5.

Now the question is
1. Why this “-5” and the division by 5 while converting from pix to character ? Pixel sizes vary. So why this constant “-5” and division by 5 ?
2. There seems to some connection with the width of standard font and cell sizes. If so what? How is it possible to find the width of the standard font via VBA ?
3. Or is there a way to define cell width in inches directly ?

Thanks in advance,

Geetha

**Richard Buttrey** · 11-27-2010, 07:43 AM

Hi,

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

Remember 1inch = 96 pixels = 72 points

Rgds

**shg** · 11-27-2010, 11:13 AM

I think pixels/inch varies by display.

Perhaps you can adapt this:

Please Login or Register  to view this content.

**Geetha Gupta** · 11-27-2010, 09:05 PM

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

**shg** · 11-27-2010, 09:28 PM

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.

**Geetha Gupta** · 11-28-2010, 08:19 AM

Dear shg,
I am sorry, I did'nt notice that "mm". For some reason , i assumed it was cm.

I think your code sets columns and rows 4times. So, rows too need repeated setting, is it ? but since rows are set in points, which is already being done, why do it again and again ?

The actual aspect of the cells - what does it mean ? And how does a Function procedure do that ? I think I am deviating. So if the answer is short, it is OK. Else i should start a new thread. Please advice.

Thanks and regards,

Geetha

**shg** · 11-28-2010, 09:22 PM

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.

**Geetha Gupta** · 11-28-2010, 11:39 PM

Hi shg,

Got it.

Thanks.

Resizing cell in inches

LinkBack

Thread Tools

Rate This Thread

Display

Resizing cell in inches

Re: Resizing cell in inches

Re: Resizing cell in inches

Re: Resizing cell in inches

Re: Resizing cell in inches

Re: Resizing cell in inches

Re: Resizing cell in inches

Re: Resizing cell in inches

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions