As a result of Excel is primarily an software for knowledge evaluation, generally its presentation instruments endure by comparability. For instance, generally your knowledge has textual content of assorted size and may exceed the obtainable printing house.
This text will present you find out how to calculate the proper column width to make sure the textual content matches inside the web page. A typical state of affairs is likely to be 2 commonplace columns after which a third column displaying feedback:
Title, Date, Remark
Though Excel has some instruments obtainable, such because the auto-fit function there’s nothing like being answerable for your software to get the end result you want.
We’ll write some code which can calculate the width of the obtainable printing house, so we will decide the column widths to make sure environment friendly printing.
Calculating The Out there Printing Area On The Web page
One of many issues is that the models of measurement for web page measurement, margins and columns are inconsistent and we have to standardize the models earlier than figuring out the proper column width.
A technique ahead is to transform all of the models into factors, somewhat than inches or centimeters. The web page measurement is initially returned in inches and is transformed, whereas left and proper margins are already in factors.
pgWid = Utility.InchesToPoints(ActiveSheet.PageSetup.PaperSize)
leftMargin = ActiveSheet.PageSetup.LeftMargin
rightMargin = ActiveSheet.PageSetup.RightMargin
Subsequently, some easy maths will give us the obtainable printing house and that is the width we have to match our third column into.
content material = pgWid - leftMargin - rightMargin
col3=content-columns(1).Width - columns(2).Width
Now we have the width required for column three in factors and that is superb, besides that column width is not measured in factors, however in Excel’s personal unit based mostly on font sizes. So, we’ll create an element to transform the width based mostly on the measurements in cell 1.
' width in factors - learn solely.
pts = Columns(1).Width
' width in Excel's measurement unit
wd = Columns(1).ColumnWidth
issue = pts / wd
col3 = col3 /issue
Lastly we have now worth for the width of column three and we use that within the following code plus setting phrase wrap to true and the row peak to auto match.
Columns(three).ColumnWidth = col3
Columns(three).wrapText=true
Rows(three).EntireRow.AutoFit
Abstract
Whereas the calculations for web page measurement and columns is usually a little complicated, it is worthwhile persevering. A very good data of how the totally different components of a spreadsheet are measured permits simpler preparation of knowledge for printing.