Window Coordinate and Dimension, and Screen details

This page explains how to know your monitor screen dimension and color depth and identify the coordinate (left, top, right and bottom) and dimension (width an height)of the Excel window you are working with, using API functions.

1. SubProcedure

The information are not made available using normal VBA function but need Window API function. Therefore it is necessary to define the required window API functions, above all.
The FindWindow function and GetWindowRect are associated for this purpose. The FindWindow function is supposed to return the handle of the window (hwnd)and the handle is referred to by the GetWindowRect. The handle (hwnd) is gained by the Application function Application.hwnd as well without using the FindWindow API function. RECT type is not recognized by Excel VBA and needs to be specific and for this purpose it is necessary to define UDT(user defined type) consisting of Left, Top, Right and Bottom.
The API functions are contained in class module and the calling procedure is in standard module to display the result on the message box.

2. User Interface

There are several methods to facilitate to run the program in terms of user interface. This article uses an Excel shape, rectangle. Click Insert tab on the Ribbon⇨ Illustrations ⇨ shapes. The shape is assigned to Macro subprocedure Windowmetrices. This is done by right clicking the rectangle⇨Assign Macro⇨Select the procedure name⇨OK.

Draw a shape and customize text and shape fill and so on. Assign the shape to the macro which you want to trigger by clicking the shape

User Interface for window metrices

3. Associated VBA Code

3.1 In Class Module

Declare API functions and define property procedures referring to the API functions in a class module.

API functions are for 32 bit and if you want to run in 64 bit machine, then refer to an article API function 64-bit and 32-bit interoperability 

Option Explicit
Option Compare Text
'window API to return the handle(hwnd) of window
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
 ByVal lpClassName As String, _
 ByVal lpWindowName As String) As Long
 
'Window API to return window dimension of the hwnd of window
Private Declare Function GetWindowRect Lib "user32" ( _
 ByVal hwnd As Long, _
 lpRect As RECT) As Long
 
'User defined type(UDT) for a type "RECT"
Private Type RECT
   Left As Long
   Top As Long
   Right As Long
   Bottom As Long
End Type
'Constant for screen dimension
Private Const SM_CYSCREEN As Long = 1 'Screen height
Private Const SM_CXSCREEN As Long = 0 'Screen width

'API Call to retrieve system information
Private Declare Function GetSystemMetrics Lib "user32" ( _
ByVal nIndex As Long) As Long

'GetDC returns the device context (DC) of a window or other device, given the object's handle.
'When you are finished using the device context, you should use ReleaseDC to free up system resources.
Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "Gdi32" ( _
ByVal hDC As Long, _
ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, _
ByVal hDC As Long) As Long

Private Const HORZRES As Long = 8
Private Const VERTRES As Long = 10
Private Const BITSPIXEL As Long = 12
Private Const VREFRESH As Long = 116
Dim hwnd As Long
Sub class_initialize()
    'Get the handle (hwnd) of Excel window
     hwnd = FindWindow("XLMAIN", Application.Caption)
End Sub
Public Property Get WindowLeft() As Long
    Dim WDimension As RECT
    'Get the window’s dimensions into the RECT structure
    GetWindowRect hwnd, WDimension
    WindowLeft = WDimension.Left
End Property
Public Property Get WindowTop() As Long
    Dim WDimension As RECT
    'Get the window’s dimensions into the RECT structure
    GetWindowRect hwnd, WDimension
    WindowTop = WDimension.Top
End Property
Public Property Get WindowRight() As Long
    Dim WDimension As RECT
    'Get the window’s dimensions into the RECT structure
    GetWindowRect hwnd, WDimension
    WindowRight = WDimension.Right
End Property
Public Property Get WindowBottom() As Long
    Dim WDimension As RECT
    'Get the window’s dimensions into the RECT structure
    GetWindowRect hwnd, WDimension
    WindowBottom = WDimension.Bottom
End Property
Public Property Get WindowWidth() As Long
    WindowWidth = WindowRight - WindowLeft
End Property
Public Property Get WindowHeight() As Long
    WindowHeight = WindowBottom - WindowTop
End Property
'Retrieve the screen height, in pixels
Public Property Get ScreenHeight() As Long
    ScreenHeight = GetSystemMetrics(SM_CYSCREEN)
End Property
'Retrieve the screen width, in pixels
Public Property Get ScreenWidth() As Long
    ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Property
Public Property Get ColorDepth() As Integer
    Dim hDC As Long
    'A device context is the canvas on which a window is drawn
    hDC = GetDC(0)
    ColorDepth = GetDeviceCaps(hDC, BITSPIXEL)
    ReleaseDC 0, hDC
End Property

3.2 In Standard Module

A routine to call the properties defined in class module is located in a standard module. The routine needs to point to the class module by a declaration Dim cl_window As New C_Windowso that the routing is allowed to use the properties.

Option Explicit
Sub Windowmetrices()
    Dim cl_window As New C_Window
    Dim msg As String
    With cl_window
        msg = "Screen Info: " & vbNewLine
        msg = msg & "Height in pixel :" & .ScreenHeight & vbNewLine
        msg = msg & "Width in pixel :" & .ScreenWidth & vbNewLine
        msg = msg & "Color depth :" & .ColorDepth & vbNewLine & vbNewLine
        msg = msg & "Window dimensions:" & vbNewLine
        msg = msg & " Left  : " & .WindowLeft & vbNewLine
        msg = msg & " Right : " & .WindowRight & vbNewLine
        msg = msg & " Top   : " & .WindowTop & vbNewLine
        msg = msg & " Bottom: " & .WindowBottom & vbNewLine
        msg = msg & " Width : " & .WindowWidth & vbNewLine
        msg = msg & " Height: " & .WindowHeight
    End With
    MsgBox msg
End Sub

4. Outcome

When you click the rectangle in a worksheet, a message box appears with all the information related to screen and window.

Message box of window metrices

5. Download the source

Download source code and workbook

Dated on: 26-November-2018