Home » Word Press » Using VBA To Determine The Page Size And Required Column Width In Excel

Using VBA To Determine The Page Size And Required Column Width In Excel

Because Excel is essentially an utility for knowledge research, every so often its presentation gear undergo by means of comparability. For instance, every so often your knowledge has textual content of more than a few duration and might exceed the to be had printing house.

This article will display you find out how to calculate the proper column width to make sure the textual content suits inside the web page. An ordinary situation could also be 2 same old columns after which a third column showing feedback:


Name, Date, Comment

Although Excel has some gear to be had, such because the auto-fit function there's not anything like being in keep an eye on of your utility to get the output you wish to have.

We're going to jot down some code which is able to calculate the width of the to be had printing house, so we will be able to decide the column widths to make sure environment friendly printing.

Calculating The Available Printing Space On The Page

One of the issues is that the devices of dimension for web page measurement, margins and columns are inconsistent and we wish to standardize the devices ahead of figuring out the proper column width.

One means ahead is to transform all devices into issues, fairly than inches or centimeters. The web page measurement is to begin with returned in inches and is transformed, whilst left and proper margins are already in issues.


PgWid = Application.InchesToIssues (ActiveSheet.PageSetup.PaperSize)
LeftMargin = ActiveSheet.PageSetup.LeftMargin
RightMargin = ActiveSheet.PageSetup.RightMargin

Therefore, some easy maths will give us the to be had printing house and that is the width we wish to match our 3rd column into.


Content = pgWid - leftMargin - rightMargin
Col3 = content-columns (1) .width - columns (2) .width

Now we've were given the width required for column 3 in issues and that's nice, apart from that column width is now not measured in issues, however in Excel's personal unit in keeping with font sizes. So, we'll create an element to transform the width in keeping with the measurements in mobile 1.


'Width in issues - learn best.
Pts = Columns (1) .Width
'Width in Excel's dimension unit
Wd = Columns (1) .ColumnWidth
Factor = pts / wd
Col3 = col3 / issue

Finally we have now worth for the width of column 3 and we use that within the following code plus atmosphere phrase wrap to true and the row peak to auto match.


Columns (three) .ColumnWidth = col3
Columns (three) .wrapText = true
Rows (three) .EntireRow.AutoFit

Summary

While the calculations for web page measurement and columns generally is a little complicated, it's profitable persevering. A just right wisdom of the way the other components of a spreadsheet are measured permits more uncomplicated preparation of information for printing.

Check Also

Advanced Event Manager   WordPress Calendar Plugin (Calendars)

Advanced Event Manager – WordPress Calendar Plugin (Calendars)

Advanced Event Manager – WordPress Calendar Plugin (Calendars). Change log 11.11.2017 Initial Release Related