Results 1 to 2 of 2

Thread: VBA Code to Get A Monitors Pixel Setting

  1. #1
    New Member
    Join Date
    Dec 2002


    Does anyone know of any VBA commands or code that would get the computers pixel display setting.

    If I knew this, I could use it to scale XY charts.

    Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Nov 2002


    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)
    ' 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts