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 a certain range to show so you’ll need to adjust Excel’s zoom factor.
Here is a way by using GetSystemMetrics API function of windows :
CODE :
Declare Function GetSystemMetrics32 Lib "User32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long Sub DisplayScreenResolution() Dim lngWidth As Long Dim lngHeight As Long lngWidth = GetSystemMetrics32(0) ' width in points lngHeight = GetSystemMetrics32(1) ' height in points MsgBox Format(lngWidth, "#,##0") & " x " & Format(lngHeight, "#,##0"), vbInformation, "Screen Resolution (width x height)" End Sub
If I’m not mistaken, the code above actually returns the height and width of the computer screen in pixels, not points.
If you run this sub when the Excel file opens you get the point values for the height and width, for whatever computer screen it’s run on, from a maximized Excel window.
Public ScreenWidth As Long
Public ScreenHeight As Long
Sub ScreenWidthHeight()
With Application
.ScreenUpdating = False
.WindowState = xlMaximized
ScreenWidth = .Width
ScreenHeight = .Height
.WindowState = xlMinimized
.ScreenUpdating = True
End With
Exit Sub
End Sub