Determining the Video Resolution
You may develop an Excel application that needs to adjust itself depending on the video resolution of the user. For example, you might have an application that requires that a certain range of cells be visible on the screen. If Windows is running in a low resolution mode (such as 640 X 480), the information may not fit so you'll need to adjust Excel's zoom factor.
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.
The first part of the code declares the API functions - one for 32-bit and one for
16-bit. The "bitness" of Excel is determined by examining the leftmost character
of the string that is returned by the Version property of the Application object. Based in
this, the appropriate API function is called.
' 32-bit API declaration
Declare Function GetSystemMetrics32 Lib "user32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
' 16-bit API declaration
Declare Function GetSystemMetrics16 Lib "user" _
Alias "GetSystemMetrics" (ByVal nIndex As Integer) As Integer
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
''''''''''''''''''''''
Sub DisplayVideoInfo()
If Left(Application.Version, 1) = 5 Then
' 16-bit Excel
vidWidth = GetSystemMetrics16(SM_CXSCREEN)
vidHeight = GetSystemMetrics16(SM_CYSCREEN)
Else
' 32-bit Excel
vidWidth = GetSystemMetrics32(SM_CXSCREEN)
vidHeight = GetSystemMetrics32(SM_CYSCREEN)
End If
Msg = "The current video mode is: "
Msg = Msg & vidWidth & " X " & vidHeight
MsgBox Msg
End Sub