Determine the screen size using VBA in Microsoft Excel

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




  1. When I enter this code into a module, the following section appears red:
    Declare Function GetSystemMetrics32 Lib "User32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
    And if I try to run the DisplayMonitorInfo code then I get a message that says that the GetSystemMetrics32 function is not defined.
    I have also gotten a message with this first block of code that says "The code in this project must be updated for us on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."
    I googled this and found this from microsoft:
    However, the resolution says "To resolve this issue, ignore the "Compile error" and run the VBA code in the 64-bit version of the Office 2010 program." But this doesn't make any sense to me. How can I ignore the compile error and run the code anyway? Also, I'm running Office 365, not Office 2010 - is it just asking me to run it in an older version (that wouldn't be helpful)?
    Thanks for any help.

  2. This worked for making the window smaller but my combo boxes, buttons,....basically eveything on the form stayed the same size as before so its just cut off the features of my form. How do you get the boxes to scale down as well?

  3. This function returns number of pixels, not number of points. For my screen, I need to multiply this value by .78 to get number of points. The .78 comes from measuring the actual height of the screen in inches. I am looking for a way to determine the value for any monitor so I can compute appropriate coordinates for generated form displays

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.