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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
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