Determining The User’s Video Resolution

Category: VBA Functions | [Item URL]

How you can determine the current video resolution? There are two ways that I'm aware of:

1. Maximize Excel's window and then access the Application's Width and Height properties
2. 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