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
Sub tst() Debug.Print GetSysMetrics(SM_CXSCREEN) End Sub