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

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

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

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy