Working with the Screen
The procedures included in this section all relate to the Windows screen and can be found in the MScreen module of the API Examples.xls workbook.
Reading the Screen Resolution
The GetSystemMetrics API function has been used to illustrate the general concepts above. It can be used to discover many of the simpler aspects of the operating system, from whether a mouse or network is present to the height of the standard window title bar. By far its most common use in Excel is to find the screen resolution, to check that it is at least a minimum size (for example, 800x600) or to work out which userform to display if you have different layouts optimized for different resolutions. The code in Listing 9-2 wraps the GetSystemMetrics API function, exposing it as separate ScreenWidth and ScreenHeight functions.
Listing 9-2 Reading the Screen Resolution
'Declare all the API-specific items Private to the module Private Declare Function GetSystemMetrics Lib "user32" _ (ByVal nIndex As Long) As Long Private Const SM_CXSCREEN = 0 'Screen width Private Const SM_CYSCREEN = 1 'Screen height 'The width of the screen, in pixels Public Function ScreenWidth() As Long ScreenWidth = GetSystemMetrics(SM_CXSCREEN) End Function 'The height of the screen, in pixels Public Function ScreenHeight() As Long ScreenHeight = GetSystemMetrics(SM_CYSCREEN) End Function
Finding the Size of a Pixel
In general, Excel measures distances in points, whereas most API functions use pixels and many ActiveX controls (such as the Microsoft Flexgrid) use twips. A point is defined as being 1/72 (logical) inches, and a twip is defined as 1/20th of a point. To convert between pixels and points, we need to know how many pixels Windows is displaying for each logical inch. This is the DPI (dots per inch) set by the user in Control Panel > Display > Settings > Advanced > General > Display, which is usually set at either Normal size (96 DPI) or Large size (120 DPI). In versions of Windows prior to XP, this was known as Small Fonts and Large Fonts. The value of this setting can be found using the GetDeviceCaps API function, which is used to examine the detailed capabilities of a specific graphical device, such as a screen or printer.
One of the fundamental features of Windows is that applications can interact with all graphical devices (screens, printers, or even individual picture files) in a standard way. This is achieved by operating through a layer of indirection called a device context, which represents a drawing layer. An application obtains a reference (handle) to the drawing layer for a specific device (for example, the screen), examines its capabilities (such as the size of a dot, whether it can draw curves and how many colors it supports), draws onto the drawing layer and then releases the reference. Windows takes care of exactly how the drawing layer is represented on the graphical device. In this example, we're only examining the screen's capabilities.
The code to retrieve the size of a pixel is shown in Listing 9-3. Remember that when adding this code to an existing module, the declarations must always be placed at the top of the module.
Listing 9-3 Finding the Size of a Pixel
Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X 'A point is defined as 1/72 inches Private Const POINTS_PER_INCH As Long = 72 'The size of a pixel, in points Public Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function
The first thing to notice about this routine is that we cannot just call GetDeviceCaps directly; we need to give it a handle to the screen's device context. This handle is obtained by calling the GetDC function, where the zero parameter conveniently gives us the device context for the screen. We then call GetDeviceCaps, passing the constant LOGPIXELSX, which asks for the number of pixels per logical inch horizontally. (For screens, the horizontal and vertical DPI is the same, but it might not be for printers, which is why circles on screen often print out as ovals.) With Normal size chosen, we get 96 dots per inch. We divide the 72 points per inch by the 96 DPI, telling us that a dot (that is, pixel) is 0.75 points; so if we want to move something in Excel by one pixel, we need to change its Top or Left by 0.75. With Large Size selected, a pixel is 0.6 points.
Every time we use GetDC to obtain a handle to a device context, we use up a small amount of Window's graphical resources. If we didn't release the handle after using it, we would eventually use up all of Window's graphical resources and crash. To avoid that, we have to be sure to release any resources we obtain, in this case by calling ReleaseDC.