Determining The User’s Video Resolution
How you can determine the current video resolution? There are two ways that I'm aware of:
- Maximize Excel's window and then access the Application's Width and Height properties
- Use a Windows API function
This document presents VBA code to demonstrate both of these techniques.
Getting Excel's window size
The VBA subroutine below maximizes Excel's window, and then displays the width and height.
Sub ShowAppSize() ' Maximize the window Application.WindowState = xlMaximized ' Get the dimensions appWidth = Application.Width appHeight = Application.Height ' Show a message box Msg = "Excel's window size is: " Msg = Msg & appWidth & " X " & appHeight MsgBox Msg End Sub
This subroutine is quite straightforward, and works with Excel 5 or later versions. The disadvantage is that Excel's metric system does not correspond to pixels. For example, when the video resolution is 1024 X 768 pixels, the preceding subroutine reports that the maximized window size is 774 X 582.
Using the GetSystemMetrics API function
The subroutine below demonstrates how to use a Windows API function to determine the current video resolution. The result is expressed in pixels.
' API declaration Declare Function GetSystemMetrics32 Lib "user32" _ Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 Sub DisplayVideoInfo() vidWidth = GetSystemMetrics32(SM_CXSCREEN) vidHeight = GetSystemMetrics32(SM_CYSCREEN) Msg = "The current video mode is: " Msg = Msg & vidWidth & " X " & vidHeight MsgBox Msg End Sub
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks