Correction
In theory the method I originally presented should work, however there are issues with the Excel PointsToScreenPixels methods. An internet search on the functions indicates that results are unreliable at best. Therefore, I am recommend using the Win32 API function GetWindowRect to retrieve the Excel application's position and size. The API function definitions where obtained from http://www.pinvoke.net/index.aspx.
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
test()
End Sub
Sub test()
Dim meDPI As PointF = GetDPI(Me.Handle)
Dim app As New Excel.Application
app.Visible = True
Dim appHwnd As IntPtr = New IntPtr(app.Hwnd)
' setting Excel's size and position -- just for test verification purposes
SetWindowPos(appHwnd, IntPtr.Zero, 10, 10, 500, 300, SetWindowPosFlags.DoNotActivate)
Dim rc As RECT
GetWindowRect(appHwnd, rc) ' retrieve Excel's size and position into rc
app.UserControl = True ' return control to the user
Console.WriteLine("Excel located at X: {0}, Y: {1}, Width: {2}, Height: {3}", rc.Left, rc.Top, rc.Width, rc.Height)
Me.Location = rc.Location
Me.Size = rc.Size
Me.Activate() ' bring this form to the front
Me.Opacity = 0.5 ' allow to view thru to Excel
End Sub
Public Shared Function GetDPI(hwnd As IntPtr) As PointF
Dim ret As PointF
Using g As Graphics = Graphics.FromHwnd(hwnd)
ret.X = g.DpiX
ret.Y = g.DpiY
End Using
Return ret
End Function
<DllImport("user32.dll")> _
Private Shared Function GetWindowRect(ByVal hWnd As IntPtr, ByRef lpRect As RECT) As Boolean
End Function
<StructLayout(LayoutKind.Sequential)> _
Public Structure RECT
Private _Left As Integer, _Top As Integer, _Right As Integer, _Bottom As Integer
Public Sub New(ByVal Rectangle As Rectangle)
Me.New(Rectangle.Left, Rectangle.Top, Rectangle.Right, Rectangle.Bottom)
End Sub
Public Sub New(ByVal Left As Integer, ByVal Top As Integer, ByVal Right As Integer, ByVal Bottom As Integer)
_Left = Left
_Top = Top
_Right = Right
_Bottom = Bottom
End Sub
Public Property X As Integer
Get
Return _Left
End Get
Set(ByVal value As Integer)
_Right = _Right - _Left + value
_Left = value
End Set
End Property
Public Property Y As Integer
Get
Return _Top
End Get
Set(ByVal value As Integer)
_Bottom = _Bottom - _Top + value
_Top = value
End Set
End Property
Public Property Left As Integer
Get
Return _Left
End Get
Set(ByVal value As Integer)
_Left = value
End Set
End Property
Public Property Top As Integer
Get
Return _Top
End Get
Set(ByVal value As Integer)
_Top = value
End Set
End Property
Public Property Right As Integer
Get
Return _Right
End Get
Set(ByVal value As Integer)
_Right = value
End Set
End Property
Public Property Bottom As Integer
Get
Return _Bottom
End Get
Set(ByVal value As Integer)
_Bottom = value
End Set
End Property
Public Property Height() As Integer
Get
Return _Bottom - _Top
End Get
Set(ByVal value As Integer)
_Bottom = value + _Top
End Set
End Property
Public Property Width() As Integer
Get
Return _Right - _Left
End Get
Set(ByVal value As Integer)
_Right = value + _Left
End Set
End Property
Public Property Location() As Point
Get
Return New Point(Left, Top)
End Get
Set(ByVal value As Point)
_Right = _Right - _Left + value.X
_Bottom = _Bottom - _Top + value.Y
_Left = value.X
_Top = value.Y
End Set
End Property
Public Property Size() As Size
Get
Return New Size(Width, Height)
End Get
Set(ByVal value As Size)
_Right = value.Width + _Left
_Bottom = value.Height + _Top
End Set
End Property
Public Shared Widening Operator CType(ByVal Rectangle As RECT) As Rectangle
Return New Rectangle(Rectangle.Left, Rectangle.Top, Rectangle.Width, Rectangle.Height)
End Operator
Public Shared Widening Operator CType(ByVal Rectangle As Rectangle) As RECT
Return New RECT(Rectangle.Left, Rectangle.Top, Rectangle.Right, Rectangle.Bottom)
End Operator
Public Shared Operator =(ByVal Rectangle1 As RECT, ByVal Rectangle2 As RECT) As Boolean
Return Rectangle1.Equals(Rectangle2)
End Operator
Public Shared Operator <>(ByVal Rectangle1 As RECT, ByVal Rectangle2 As RECT) As Boolean
Return Not Rectangle1.Equals(Rectangle2)
End Operator
Public Overrides Function ToString() As String
Return "{Left: " & _Left & "; " & "Top: " & _Top & "; Right: " & _Right & "; Bottom: " & _Bottom & "}"
End Function
Public Overloads Function Equals(ByVal Rectangle As RECT) As Boolean
Return Rectangle.Left = _Left AndAlso Rectangle.Top = _Top AndAlso Rectangle.Right = _Right AndAlso Rectangle.Bottom = _Bottom
End Function
Public Overloads Overrides Function Equals(ByVal [Object] As Object) As Boolean
If TypeOf [Object] Is RECT Then
Return Equals(DirectCast([Object], RECT))
ElseIf TypeOf [Object] Is Rectangle Then
Return Equals(New RECT(DirectCast([Object], Rectangle)))
End If
Return False
End Function
End Structure
<DllImport("user32.dll", SetLastError:=True)> _
Private Shared Function SetWindowPos(ByVal hWnd As IntPtr, ByVal hWndInsertAfter As IntPtr, ByVal X As Integer, ByVal Y As Integer, ByVal cx As Integer, ByVal cy As Integer, ByVal uFlags As SetWindowPosFlags) As Boolean
End Function
<Flags> _
Private Enum SetWindowPosFlags As UInteger
''' <summary>If the calling thread and the thread that owns the window are attached to different input queues,
''' the system posts the request to the thread that owns the window. This prevents the calling thread from
''' blocking its execution while other threads process the request.</summary>
''' <remarks>SWP_ASYNCWINDOWPOS</remarks>
ASynchronousWindowPosition = &H4000
''' <summary>Prevents generation of the WM_SYNCPAINT message.</summary>
''' <remarks>SWP_DEFERERASE</remarks>
DeferErase = &H2000
''' <summary>Draws a frame (defined in the window's class description) around the window.</summary>
''' <remarks>SWP_DRAWFRAME</remarks>
DrawFrame = &H20
''' <summary>Applies new frame styles set using the SetWindowLong function. Sends a WM_NCCALCSIZE message to
''' the window, even if the window's size is not being changed. If this flag is not specified, WM_NCCALCSIZE
''' is sent only when the window's size is being changed.</summary>
''' <remarks>SWP_FRAMECHANGED</remarks>
FrameChanged = &H20
''' <summary>Hides the window.</summary>
''' <remarks>SWP_HIDEWINDOW</remarks>
HideWindow = &H80
''' <summary>Does not activate the window. If this flag is not set, the window is activated and moved to the
''' top of either the topmost or non-topmost group (depending on the setting of the hWndInsertAfter
''' parameter).</summary>
''' <remarks>SWP_NOACTIVATE</remarks>
DoNotActivate = &H10
''' <summary>Discards the entire contents of the client area. If this flag is not specified, the valid
''' contents of the client area are saved and copied back into the client area after the window is sized or
''' repositioned.</summary>
''' <remarks>SWP_NOCOPYBITS</remarks>
DoNotCopyBits = &H100
''' <summary>Retains the current position (ignores X and Y parameters).</summary>
''' <remarks>SWP_NOMOVE</remarks>
IgnoreMove = &H2
''' <summary>Does not change the owner window's position in the Z order.</summary>
''' <remarks>SWP_NOOWNERZORDER</remarks>
DoNotChangeOwnerZOrder = &H200
''' <summary>Does not redraw changes. If this flag is set, no repainting of any kind occurs. This applies to
''' the client area, the nonclient area (including the title bar and scroll bars), and any part of the parent
''' window uncovered as a result of the window being moved. When this flag is set, the application must
''' explicitly invalidate or redraw any parts of the window and parent window that need redrawing.</summary>
''' <remarks>SWP_NOREDRAW</remarks>
DoNotRedraw = &H8
''' <summary>Same as the SWP_NOOWNERZORDER flag.</summary>
''' <remarks>SWP_NOREPOSITION</remarks>
DoNotReposition = &H200
''' <summary>Prevents the window from receiving the WM_WINDOWPOSCHANGING message.</summary>
''' <remarks>SWP_NOSENDCHANGING</remarks>
DoNotSendChangingEvent = &H400
''' <summary>Retains the current size (ignores the cx and cy parameters).</summary>
''' <remarks>SWP_NOSIZE</remarks>
IgnoreResize = &H1
''' <summary>Retains the current Z order (ignores the hWndInsertAfter parameter).</summary>
''' <remarks>SWP_NOZORDER</remarks>
IgnoreZOrder = &H4
''' <summary>Displays the window.</summary>
''' <remarks>SWP_SHOWWINDOW</remarks>
ShowWindow = &H40
End Enum
End Class
Please note that in testing the above code, the WinForm application is declared to be DPI aware by having the following in its app.Manifest file.
<application xmlns="urn:schemas-microsoft-com:asm.v3">
<windowsSettings>
<dpiAware xmlns="http://schemas.microsoft.com/SMI/2005/WindowsSettings">true</dpiAware>
</windowsSettings>
</application>
Do Not Use
The Application.Height Property and Application.Width Property is measured in points not pixels. You can use the Window.PointsToScreenPixelsX Method and the Window.PointsToScreenPixelsY methods to compute the width and height in pixels to set your form size.
width = exclApp.ActiveWindow.PointsToScreenPixelsX(exclApp.Width)
height = exclApp.ActiveWindow.PointsToScreenPixelsY(exclApp.Height)
I do not know if you will also have to declare your addin as DPI aware to avoid Windows's scaling your form.
Note: Base on testing in Excel, only the ActiveWindow will yield a value.
