How to get the screen resolution in VBA

[Stefan Mueller]

In native VB I get the screen resolution with
Screen.Width \ Screen.TwipsPerPixelX
Screen.Height \ Screen.TwipsPerPixelY

And if I'd like to position a window at the right bottom corner I can
calculate the position with
Screen.Width - Me.Width
Screen.Height - Me.Height

However, 'Screen' doesn't exist in VBA (e.g. Excel).
Does anyone know how I can do it in VBA?

Stefan

 

[Halim]

Hi,

I use this way:
Option Explicit
Private Declare Function GetSystemMetrics Lib _
"User32" (ByVal nIndex As Long) As Long
Private Const SM_CXSCREEN = 0
Private Const SM_CYSCREEN = 1

Public Function ScreenHeight() As Long
ScreenHeight = GetSystemMetrics(SM_CYSCREEN)
End Function

Public Function ScreenWidth() As Long
ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Function

I've modified from original by Stephen Bullen

 

[Rick Rothstein \(MVP - VB\)]

Here is a way to get the dimensions of the screen's physical "work" area
(the part left over after accounting for the Windows TaskBar and any other
taskbars that might be on the screen)...

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Private Declare Function MoveWindow Lib "user32" _
(ByVal hWnd As Long, _
ByVal X As Long, _
ByVal Y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal bRepaint As Long) As Long

Private Const SPI_GETWORKAREA = 48

Private Declare Function SystemParametersInfo Lib "user32" _
Alias "SystemParametersInfoA" _
(ByVal uAction As Long, _
ByVal uParam As Long, _
lpvParam As Any, _
ByVal fuWinIni As Long) As Long

Sub GetWorkArea(waLeft As Integer, waTop As Integer, _
waWidth As Integer, waHeight As Integer)
Dim rcWork As RECT
SystemParametersInfo SPI_GETWORKAREA, 0, rcWork, 0
With rcWork
waLeft = .Left
waTop = .Top
waWidth = (.Right - .Left)
waHeight = (.Bottom - .Top)
End With
End Sub

Your can call the above subroutine using this sample code...

Private Sub CommandButton1_Click()
Dim L As Integer
Dim T As Integer
Dim W As Integer
Dim H As Integer
GetWorkArea L, T, W, H
MsgBox "Left: " & L & vbCrLf & _
"Top: " & T & vbCrLf & _
"Width: " & W & vbCrLf & _
"Height: " & H
End Sub

Rick