1

Is there a way to use ElementfromPoint from UIAutomationClient with VBA (EXCEL)

I have always an compilation error : "User-Defined type may not be passed ByVal"

Sub Test_ElementFromPoint()
Dim uiAuto As New UIAutomationClient.CUIAutomation8
Dim elmRibbon As UIAutomationClient.IUIAutomationElement
Dim pt As tagPOINT

pt.x = 541
pt.y = 99
Set elmRibbon = uiAuto.ElementFromPoint(pt)
MsgBox elmRibbon.CurrentName
End Sub

Well if i can't use elementfromPoint from vba i can use AccessibleObjectFromPoint from Iaccessible to start my query but with it i don't get all the information (.currentHelpText) from ElementFromIAccessible

Code 1

Public Sub Sample()
    MsgBox "button", vbSystemModal
    TrouveButton "Paste"
End Sub

Public Sub TrouveButton(ByVal TabName As String)
    Dim uiAuto As UIAutomationClient.CUIAutomation
    Dim elmRibbon As UIAutomationClient.IUIAutomationElement
    Dim elmRibbonTab As UIAutomationClient.IUIAutomationElement
    Dim cndProperty As UIAutomationClient.IUIAutomationCondition
    Dim aryRibbonTab As UIAutomationClient.IUIAutomationElementArray
    Dim ptnAcc As UIAutomationClient.IUIAutomationLegacyIAccessiblePattern
    Dim accRibbon As Office.IAccessible
    Dim i As Long

    Set elmRibbonTab = Nothing    '???
    Set uiAuto = New UIAutomationClient.CUIAutomation
    Set accRibbon = Application.CommandBars("Ribbon")
    Set elmRibbon = uiAuto.ElementFromIAccessible(accRibbon, 0)
     Set cndProperty = uiAuto.CreatePropertyCondition(UIA_ClassNamePropertyId, "NetUIRibbonButton")

    Set aryRibbonTab = elmRibbon.FindAll(TreeScope_Subtree, cndProperty)
    For i = 0 To aryRibbonTab.Length - 1
        Debug.Print aryRibbonTab.GetElement(i).CurrentName
        If aryRibbonTab.GetElement(i).CurrentName = TabName Then
            Set elmRibbonTab = aryRibbonTab.GetElement(i)
            Exit For
        End If
    Next
    If elmRibbonTab Is Nothing Then Exit Sub
    With elmRibbonTab
        MsgBox "Name: " & .CurrentName _
             & vbCr & "------------------------------------" _
             & vbCr & "CurrentHelpText: " & CStr(.CurrentHelpText) , , "ui automation"
    End With
End Sub

code 2

Option Explicit

Private Type POINTAPI
    x As Long
    Y As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    #If Win64 Then
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal arg1 As LongPtr, ppacc As IAccessible, pvarChild As Variant) As Long
        Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    #Else
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    #End If
#Else
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#End If

Private Const CHILDID_SELF = &H0&
Private Const S_OK As Long = &H0


Sub Sample2()
'move the mouse on PASTE BUTTON 
    Beep
    Application.OnTime DateAdd("s", 3, Now), "get_element_under_mouse"
End Sub

Private Sub get_element_under_mouse()
    Dim oIA As IAccessible
    Dim oCmbar As CommandBar
    Dim lResult As Long
    Dim tPt As POINTAPI
    Dim oButton As IAccessible

    GetCursorPos tPt

    #If Win64 Then
        Dim lngPtr As LongPtr
        CopyMemory lngPtr, tPt, LenB(tPt)
        lResult = AccessibleObjectFromPoint(lngPtr, oIA, 0)
    #Else
        lResult = AccessibleObjectFromPoint(tPt.x, tPt.Y, oIA, 0)
    #End If

    If lResult = S_OK Then
        '  On Error Resume Next

    End If

    Dim uiAuto As UIAutomationClient.CUIAutomation
    Dim elmRibbon As UIAutomationClient.IUIAutomationElement
    Dim uielmt As UIAutomationClient.IUIAutomationElement
    Dim cndProperty As UIAutomationClient.IUIAutomationCondition

    Dim i As Long

    On Error Resume Next
    Set uiAuto = New UIAutomationClient.CUIAutomation

    ' uiAuto.p
    Set elmRibbon = uiAuto.ElementFromIAccessible(oIA, 0)

    If Not elmRibbon Is Nothing Then
        MsgBox "Name: " & elmRibbon.CurrentName _
             & vbCr & "------------------------------------" _
             & vbCr & "CurrentHelpText: " & CStr(elmRibbon.CurrentHelpText) , , "ui automation"

End If

End Sub
Oliv
  • 11
  • 3
  • Is this a [mcve]? Because I don't see where you're passing a UDT anywhere – Mathieu Guindon Sep 29 '17 at 14:36
  • The parameter for `ElementFromPoint` requires `pt`, not x and y. Even so, the error still persists. – Brian M Stafford Sep 29 '17 at 14:45
  • Then you're F'd. – Mathieu Guindon Sep 29 '17 at 14:53
  • Yes it's a mcve, – Oliv Oct 01 '17 at 13:40
  • @Mat'sMug sorry i don't understand (i'm french)>"Then you're F'd." – Oliv Oct 01 '17 at 13:42
  • @BrianMStafford i have test both and it's always the same error code – Oliv Oct 01 '17 at 13:45
  • @Oliv It's not usable in VBA, because the `pt` parameter is expected to be passed by value, and VBA can only pass the `tagPOINT` struct by reference. "You're F'd" == "c'est foutu" (le fameux mot en F!) ;-) – Mathieu Guindon Oct 01 '17 at 15:35
  • @Mat'sMug OK i'M F. lol, please see my second issue – Oliv Oct 01 '17 at 16:00
  • Not sure what the (new) question is though. – Mathieu Guindon Oct 01 '17 at 16:04
  • I use this code to get element from OFFICE RIBBON; With the First code elmRibbonTab.CurrentHelptext is returned the right stuff but with te second currentHelptext is empty – Oliv Oct 01 '17 at 16:11
  • @Mat'sMug funny thing this post from a month ago is the only reference to that particular problem over the internet and that I have today just been experiencing the same thing... So one must conclude VBA IDE developers have not thought of this when preventing users from passing UDT ByVal ??... Is there absolutely not workaround for this ? Theoretically, would it be possible to code a C++ DLL Add-in for Excel only to receive the ByRef UDT and then repass it ByVal to the UIAutomationClient.CUIAutomation8.ElementFromPoint method ? – hymced Nov 16 '17 at 16:09
  • @hymced I'm 100% sure this has nothing to do with the IDE, and 99% it has nothing to do with the language either, ...and everything to do with COM and whatever COM type a VBA UDT translates to (`RECORD`?). IOW if you made a C++ program that used the same data structures you'd run into the exact same issue. I'd question the need for UI automation in the first place: seems like a poor work-around for lack of proper unit tests. – Mathieu Guindon Nov 16 '17 at 16:46
  • @Mat'sMug I am not entirely sure to understand what you mean by "poor work-around for lack of proper unit tests". Anyway I am just exploring possibilities, and I came onto this before I found the Excel.Window.RangeFromPoint method. Now I am just wondering! I dont understand why my idea with the DLL won't work. Does this mean that the ElementFromPoint(ByVal tagPOINT) has no way to work in any language ? – hymced Nov 16 '17 at 17:08
  • I guess you could try a `Declare` statement that imports the function and wraps the parameter in a `Variant`. My point about unit testing, is that if I understand correctly the point of UI automation is to test things, right? Turns out, that's also the point of *unit tests* - except when your code isn't written in a properly decoupled way, it's impossible to write unit tests for it, so you automate testing with UI automation. – Mathieu Guindon Nov 16 '17 at 17:11

0 Answers0