CODEWITHSUNDEEP
vb.netexcel
Code Pope
Code Pope

Reputation: 5459

Set Excel Column width in pixel via VB.NET

Is there a way to set the columnwidth in an Excel document via VB.NET in pixels? I am using currently the following codeline: 

Sheets(i).Columns("A:A").ColumnWidth = 3.14

Another problem is that I used a macro to record the width of the column and inserted it with the above statement in my code. But when I run the code the column width is 3,42 instead of 3,14.
Can anybody help me?

Upvotes: 0

Views: 28508

Answers (2)

BGD
BGD

Reputation: 219

Excel doesn't use the concept of pixels when it comes to width of the columns.

One unit of column width is equal to the width of one character in the Normal style as documented on MSDN here: [https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.namedrange.columnwidth?redirectedfrom=MSDN&view=vsto-2017#Microsoft_Office_Tools_Excel_NamedRange_ColumnWidth][1]

Hope this helped.

The other way to solve this issue is to convert pixels to above mentioned character and set it.

Upvotes: 1

Steve
Steve

Reputation: 5545

Based on this post, you can perform a calculation to convert the excel unit of measure to pixels.

Here is a snippet from that web site:

COLUMNWIDTH: One unit of column width is equal to the width of one character in the Normal style. range("A1").Columnwidth returns 8.43 characters

WIDTH: Returns or sets an object's width, in points. range("A1").width returns 48 points. 72 point/inch=.6666"=64 pixels @ 96 pixels/inch

So...

Sub testwidth()
Sheets("sheet3").Activate
screenres = 96 '96/inch
mypoints = Sheets("sheet3").Range("A1").Width
'> returns 48 points
mychars = Sheets("sheet3").Range("A1").ColumnWidth
'> returns 8.43 chars
mypixels = (mypoints / 72) * screenres 'pixel width of column
Debug.Print mypoints, mychars, mypixels
'> returns 48 8.43 64
End Sub

The column width is 48 points or 8.43 characters or 64 pixels.

Upvotes: 2

Related Questions