Windows APIs and VSTO Excel Add-ins

[Bridge Guy]

I am developing an Excel add-in using VSTO in Visual Studio with VB.NET. I would like to capture the cursor position on the mouse in screen/ pixel coordinates. I downloaded the following subroutine that is written by others and posted on an Excel VBA forum. The subroutine uses the Windows API function GetCursorPos. The function returns Result and the X and Y coordinates of the declared IICoord structure as 0. The POINTAPI was declared as a Type in the original post and I have used the VB.NET structure here.

I have used a limited amount of Windows APIs in my VSTO projects before with good results. Any insight into what may cause the problem would be appreciated.

Thanks

Private Declare Function GetCursorPos Lib "user32" (ByRef lpPoint As POINTAPI) As Long

Public Structure POINTAPI
Public X As Long
Public Y As Long
End Structure

Sub GetCursorPosDemo()

Dim llCoord As POINTAPI
Dim Result As Object

' Get the cursor positions
Result = GetCursorPos(llCoord)

' Display the cursor position coordinates
MsgBox("X Position: " & llCoord.X & vbNewLine & "Y Position: " & llCoord.Y)

End Sub

 

[Dan_W]

I've not written a VSTO in VB.NET, so I'm just guessing but:

1. Is that how you declare Win32 APIs in VB.NET? That would be a valid declaration in 32bit VBA, but not in 64bit VBA, for example.
2. You've declared the Result variable to be an object, but the return type of the API is a Long - is that normally how you would do it in VB.NET?

 

[Bridge Guy]

Dan,

I am using the following products;
Windows 10 32-bit
Excel for Microsoft Office 365 MSO 32-bit
Visual Studio 2019

Addressing comment 1, I have successfully used Win32 APIs in VB.NET previously in developing stand alone windows desk top applications.

In the development of the referenced VSTO Excel add-in using Visual Studio, the following Win32 API declarations and functions work as advertised.

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long

I have attempted to use the following API declaration in lieu of the API used in my post, but the Visual Studio IDE does not recognize it.

Private Declare PtrSafe Function GetCursorPos Lib "USER32" (lpPoint As POINTAPI) As LongPtr

When using this declaration, the IDE Error list gives 2 errors. It does not recognize PtrSafe and states 'Sub' or 'Function' expected. It also does not recognize Function in the declaration and gives the
error 'Lib' is expected.

Comment 2, yes, I have previously declared the variable Result as an object.

Please let me know if you have any suggestions on what I may be overlooking.

Thanks for your time Dan.

 

[Dan_W]

Thats ok. The LongPtr is a VBA specific data type - I only mention the API declaration point as a way of raising the office bitness point. It sounds like you're compiling the VSTO for 32bit and not 64bit, then. In which case, nothing is immediately jumping out at me. As an aside, I saw this - does this assist at all: cursor position using GetCursorPos gives PInvokeStackImbalance error

 

[Bridge Guy]

Dan,

I used the GetCursorPos API function declaration from the example code found in the link you provided. It worked! The declaration attributes used in the example was the difference.

I had resorted to declaring the API functions (wo attributes) in a module in Excel and saving it as a .xlsm file and then calling the subroutines as a macro from my add-in. Invoking subroutines in an Excel workbook module is not as desirable because it would require modifying the code in the module when issuing updates. Being able to call the API functions directly from my code is much easier and less maintenance when issuing updates.

Thanks for your time Dan.

 

[Dan_W]

Thats great. Glad you were able to solve it, and thank you kindly for sharing the results. You've inspired me to look into VSTOs (I had been avoiding the topic...)