• 목록

• 페이스북 공유
• 트위터 공유

출처 ::: Display Screen Size and Other Metrics with VBA UDF - wellsr ...

We will use VBA to develop an Excel user-defined function (UDF) capable of returning certain dimensional information about your computer’s display screen, including the screen size. In particular, it will determine the zoom factor for Excel and other Office applications to display items actual-size (as when printed). If your computer is configured with multiple monitors, this UDF’s return value will apply to the screen currently displaying Excel’s ActiveWindow. Although this discussion is focused on Excel, the results apply generally to all Windows applications.

• Dots/Inch, Pixels/Inch, and Points
• The Screen Function Module
• Screen Function Results
• Final Thought

Dots/Inch, Pixels/Inch, and Points

Microsoft Windows normally assumes a logical value of 96 dots per inch for desktop and laptop computer display screens. (Tablet computers like Surface Pro might have a different logical value.) But your screen’s display density depends on its physical dimensions (inches) and the video controller’s resolution (pixels). We will use the term pixels per inch (ppi) when referring to display screens and dots per inch (dpi) when referring to the logical value assumed by Windows.

Windows also measures certain items using points. There are 72 standard points per inch. Using 96 dpi, Windows assumes each pointrequires 1.333… dots for display (or 4 dots for every 3 points).

When in Normal view, Excel sets row height in terms of points. (Logical pixels, which are actually dots in our terminology, might also be indicated.) But have you ever set a row height of 72 points (96 dots) and measured with a ruler? If so, you might find it measures more or less than an inch, depending on your monitor and Excel’s zoom factor (in the View ribbon).

Excel in Normal view also sets column width in points, but this is simply confusing. A column width of N “points” actually means N zero characters (0) will fit in the column when it is formatted with the Normal style font (see the Home ribbon). For example, my Normal style uses Calibri (Body) Regular size 11 font; therefore, setting a column width of 8 “points” will fit 8 zeros (00000000) of Calibri Regular size 11. Excel calls this 8 “point” width equivalent to 61 logical pixels (or dots) assuming 96 dpi, so this 8 “point” width is actually 61x72/96=45.75 points (0.635 inch).

Switching to Page Layout view (on the View ribbon), Excel uses inches instead of points for row height and column width. However, the column width seems to change when the view is changed. In my example above, the 8 “point” column changed to 0.69 inch (66 logical pixels instead of 61). If you can explain why, please post a Comment below. Horizontal and vertical rulers are displayed in Page Layout view. As mentioned before, your ruler might not match Excel’s ruler depending on your monitor and the zoom factor.

The Screen Function Module

Now let’s find out how to get dimensional information about a computer’s actual display screen. The complete VBA code for our Screen UDF module is listed below. Further discussion follows the listing.

Notice everything in our VBAProject’s module is declared Private except Function Screen; only that UDF may be accessed outside the module. There are 9 Declare statements for Windows API functions; 5 of these are included to support multi-monitor configurations. The Windows API functions require several Const statements, 2 Type statements, and 1 Enum statement.

This module requires VBA7, which was introduced with Office 2010. If VBA7 is not available, the UDF will probably work correctly if the following changes are made to all Declare statements: Delete all PtrSafe and change all LongPtr to Long.

The Screen function takes one text argument named Item and returns a Variant result representing that Item. Comments within the Screen function identify recognized values for Item, which are self-descriptive. (See the comments in the VBA code listed above.) The dimensional metric Item values have both a long-form and a short-form. For example, if Item is either “HorizontalResolution” or “pixelsX” the return value will be the display screen’s horizontal resolution in pixels. Alphabetic case of Item is ignored as are leading and trailing space characters, so both of the following cell formulas will yield the same result:

	=Screen("pixelsX")	=Screen("  PiXeLsX  ")

The following cell formula will return a comma-separated list of all recognized Item values with optional short-form values in parentheses:

	=Screen("Help")

Here is that formula’s result:

HorizontalResolution (pixelsX), VerticalResolution (pixelsY), WidthInches (inchesX), HeightInches (inchesY), DiagonalInches (inchesDiag), PixelsPerInchX (ppiX), PixelsPerInchY (ppiY), PixelsPerInch (ppiDiag), WinDotsPerInchX (dpiX), WinDotsPerInchY (dpiY), WinDotsPerInch (dpiWin), AdjustmentFactor (zoomFac), IsPrimary, DisplayName, Update, Help.

The Application.Volatile statement is included to ensure all cells referencing the Screen function are updated when the Workbook is opened or when such a cell is recalculated. If Excel’s ActiveWindow is moved from one screen to another in a multi-monitor configuration, it will probably be necessary to force an update by pressing function key F9.

The purpose of the code related to nMonitors, hWnd, and hDC is to determine the number of monitors present and which screen is displaying Excel’s ActiveWindow. The procedure is simpler when there is only one monitor.

Finally, a Select Case statement is used to resolve the result requested by Item. Much of the calculation is based on the Pythagorean theorem to determine the hypotenuse of a right triangle. It is arranged to reduce the amount of calculation for a given Item, although this might not be apparent. If Item is not recognized, a #VALUE! error is returned.

You might wonder about the following two statements associated with DisplayName:

vResult = tMonitorInfo.szDevice & vbNullCharvResult = Left(vResult, (InStr(1, vResult, vbNullChar) - 1))

This is necessary when GetMonitorInfo returns the fixed-size null-terminated (string-zero) member szDevice of the MONITORINFOEXstructure (Type) tMonitorInfo. We are only interested in the portion preceding the first vbNullChar. Another vbNullChar is added in the first statement to insure InStr does not return zero.

Screen Function Results

The following two screen shots illustrate results for two single-monitor configurations. The first is a desktop computer with a 25” monitor. The second is a laptop computer purported to have a 15.6” display (actually 15.5”). The results were created by entering the following formula into cell A2

=Screen(A1)

then copying that formula across row 2 (range B2:O2). Notice the different zoom factors necessary to display Windows applications actual-size. By setting your zoom to this size, the size displayed on your screen should match the “real” size of the application.

The following screen shots represent a multi-monitor configuration with two equivalent 23” monitors that are almost perfectly matched to Windows’ assumed 96 dpi. Notice DISPLAY1 is primary and DISPLAY2 is not.

We have only been able to test the Screen function with a few computer configurations. If you observe unsatisfactory results with your configuration, please describe the details including VBA version and Windows version in a Comment below.

Final Thought

The Screen UDF is patterned after Excel’s built-in information functions CELL and INFO, which return a single result requested by a text argument. Screen could be made shorter and more efficient if converted to a Sub procedure with no argument and run as a Macro. In this case, the full set of results could be reported in a new Worksheet added to ActiveWorkbook. Or the Macro could be made independent of Excel by reporting results in a MsgBox or a UserForm or an output file. (See our list of UserForm tutorials and File Input/Output tutorials.)

That’s all for this tutorial. If you’re serious about writing macros, subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.

Oh, and if you have a question, post it in our VBA Q&A community.