Try these I found some time back in these pages:
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 standard VGA mode (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:
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. This subroutine works with both 16-bit Excel 5 and 32-bit Excel 7 and Excel 97.
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
Determine the screen size
With the macro below you can return the screen size with the function GetSystemMetrics32.
Declare Function GetSystemMetrics32 Lib "User32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Sub DisplayMonitorInfo()
Dim w As Long, h As Long
w = GetSystemMetrics32(0) ' width in points
h = GetSystemMetrics32(1) ' height in points
MsgBox Format(w, "#,##0") & " x " & Format(h, "#,##0"), vbInformation, "Monitor Size (width x height)"
End Sub
