This page will highlight some API functions that can be used in Excel. From time to time I will add new APIs to this page (as I write the sample code).

The first one is GetSystemMetrics, which is found in user32.dll. First we place the following code at the top of a standard module in the VB IDE.

Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

' constants from http://support.microsoft.com/kb/210603
' full list on Win32 SDK
Public Enum SystemMetrics
SM_CXSCREEN = 0  'Width of screen
SM_CYSCREEN = 1  'Height of screen
SM_CXVSCROLL = 2  'Width of arrow bitmap on  vertical scroll bar
SM_CYHSCROLL = 3  'Height of arrow bitmap on  horizontal scroll bar
SM_CYCAPTION = 4  'Height of caption or title
SM_CXBORDER = 5  'Width of window frame that cannot be sized
End Enum

This code serves two purposes: it declares the API function, and creates a list of enumerated constants we'll use when calling the function (to create Intellisense). Note that this is only a partial list of constants. Visit How to Use the GetSystemMetrics() API Call for more, and check out the Windows SDK for the complete list.

The intermediate function we'll call to use GetSystemMetrics is as follows:

Function GetSysMetrics(gsmNumber As SystemMetrics) As Long
  GetSysMetrics = GetSystemMetrics(gsmNumber)
End Function

Sample usage:

Sub tst()
  Debug.Print GetSysMetrics(SM_CXSCREEN)
End Sub