# GetWindow, FindWindow, EnumWindow, and EgtWindowX



## hatman (Aug 24, 2010)

So I have a solution that works (though it isn't quite as bullet-proof as I would like).  What I am interested in is an explanation why NONE of the seemingly more bullet-proof solutions work.

In controlling Excel from a VB 6.0 application, at some point, the user can display the Format Axis dialog of the Excel instance, which is done programmatically using the Dialogs().Show method.  The problem comes in where I need to halt the recalculation before the dialog is displayed, and resume recalc after it is manually closed.  Oh, and the user may have other instances of the Excel Application open, not under of the VB 6.0 application.  To do this, i need teh HWND of the Format Axis window, then periodically monitor it unil it is closed.

My first approach was to use the HWND of the XLMAIN window to find the appropriate child window.  But after 3 different methods (some of which I later read in various articles actually call EnumWindows in teh background anyway, so perhaps they aren't as unique as I thought) I determined that the Format Axis window isn't visible as a child.  WHY NOT???

The FindWindow function works to find the most recently created inistance of the window, which is probably fine, since it gets called immediately after the instance I am looking for is created, making it extremely unlikely to get stuck with the wrong instance.


```
Option Explicit
Private Declare Function EnumChildWindows _
    Lib "user32" _
    (ByVal hWndParent As Long, _
    ByVal lpEnumFunc As Long, _
    ByVal lParam As Long) As Long
    
Public Declare Function GetClassName _
    Lib "user32" Alias "GetClassNameA" _
    (ByVal hwnd As Long, _
    ByVal lpClassName As String, _
    ByVal nMaxCount As Long) As Long
    
Public Declare Function GetWindowText _
    Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, _
    ByVal lpString As String, _
    ByVal cch As Long) As Long
    
Public Declare Function GetWindowTextLength _
    Lib "user32" Alias "GetWindowTextLengthA" _
    (ByVal hwnd As Long) As Long
    
Public Declare Function FindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    
Public Declare Function GetParent _
    Lib "user32.dll" _
    (ByVal hwnd As Long) As Long
    
Public Declare Function FindWindowX _
    Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As Long, _
    ByVal lpsz2 As Long) As Long
    
Public Declare Function GetWindow _
    Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal wCmd As Long) As Long
Private Const GW_CHILD = 5
Private Const GW_HWNDNEXT = 2
Private Const GW_HWNDPREV = 3
Dim windows() As Long
Dim windowsCount As Long
Sub Compare_Them_All()
    Sheet3.Rows.Clear
    Chart4.Activate
    
    Chart4.Axes(xlPrimary).Select
    
    Application.Dialogs(xlDialogFormatLegend).Show
    Call EnumWindow_Method
    
    Call FindWindowX_Method
    
    Call FindWindow_Method
    
    Call GetWindow_Method
End Sub
Sub EnumWindow_Method()
    Dim a() As Long
    Dim cnt As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    sClass = Space$(256)
    GetClassName Application.hwnd, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(Application.hwnd) + 1, Chr$(0))
    GetWindowText Application.hwnd, ControlText, Len(ControlText)
    
    Sheet3.Range("A1").Value = Application.hwnd
    Sheet3.Range("B1").Value = getwindowclass
    Sheet3.Range("C1").Value = ControlText
    
    
    a = ChildWindows(Application.hwnd)
    
    For cnt = 1 To UBound(a, 1)
    
        sClass = Space$(256)
        GetClassName a(cnt), sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        ControlText = String(GetWindowTextLength(a(cnt)) + 1, Chr$(0))
        GetWindowText a(cnt), ControlText, Len(ControlText)
        
        Sheet3.Range("A" & cnt + 1).Value = a(cnt)
        Sheet3.Range("B" & cnt + 1).Value = getwindowclass
        Sheet3.Range("C" & cnt + 1).Value = ControlText
        
    Next cnt
End Sub
Function ChildWindows(ByVal hwnd As Long) As Long()
    windowsCount = 0
    
    EnumChildWindows hwnd, AddressOf EnumWindows_CBK, 1
    
    ReDim Preserve windows(windowsCount) As Long
    
    ChildWindows = windows()
    
End Function

Private Function EnumWindows_CBK(ByVal hwnd As Long, ByVal lParam As Long) As Long
    
    If windowsCount = 0 Then
    
        ReDim windows(100) As Long
        
    ElseIf windowsCount >= UBound(windows) Then
    
        ReDim Preserve windows(windowsCount + 100) As Long
        
    End If
    
    
    windowsCount = windowsCount + 1
    
    windows(windowsCount) = hwnd
    
    EnumWindows_CBK = 1
    
End Function
Sub FindWindowX_Method()
    Dim hChild As Long
    Dim cnt As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    hChild = FindWindowX(Application.hwnd, 0, 0, 0)
    
    cnt = 2
    
    Do
       
        sClass = Space$(256)
        GetClassName hChild, sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        ControlText = String(GetWindowTextLength(hChild) + 1, Chr$(0))
        GetWindowText hChild, ControlText, Len(ControlText)
        
        Sheet3.Range("E" & cnt).Value = hChild
        Sheet3.Range("F" & cnt).Value = getwindowclass
        Sheet3.Range("G" & cnt).Value = ControlText
        
        hChild = FindWindowX(Application.hwnd, hChild, 0, 0)
       
       cnt = cnt + 1
       
    Loop Until hChild = 0
End Sub
Sub FindWindow_Method()
    Dim DialogHwnd As Long
    Dim parent_h As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    'this one only gets the MOST RECENT instance.
    
    DialogHwnd = FindWindow("NUIDialog", "Format Axis")
    
    parent_h = GetParent(DialogHwnd)
    
    sClass = Space$(256)
    GetClassName DialogHwnd, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(DialogHwnd) + 1, Chr$(0))
    GetWindowText DialogHwnd, ControlText, Len(ControlText)
    
    Sheet3.Range("M2").Value = DialogHwnd
    Sheet3.Range("N2").Value = getwindowclass
    Sheet3.Range("O2").Value = ControlText
    
    sClass = Space$(256)
    GetClassName parent_h, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(parent_h) + 1, Chr$(0))
    GetWindowText parent_h, ControlText, Len(ControlText)
    
    Sheet3.Range("M1").Value = parent_h
    Sheet3.Range("N1").Value = getwindowclass
    Sheet3.Range("O1").Value = ControlText
End Sub
Sub GetWindow_Method()
    Dim lngSrch As Long
    Dim ControlText As String
    Dim sClass As String
    Dim getwindowclass As String
    Dim cnt As Integer
    
    lngSrch = Application.hwnd
    
    lngSrch = GetWindow(lngSrch, GW_CHILD)
    
    cnt = 2
    
    Do
        lngSrch = GetWindow(lngSrch, GW_HWNDNEXT)
        
        If lngSrch = 0 Then
        
            Exit Do
            
        End If
        
        ControlText = String(GetWindowTextLength(lngSrch) + 1, Chr$(0))
        GetWindowText lngSrch, ControlText, Len(ControlText)
        
        sClass = Space$(256)
        GetClassName lngSrch, sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        Sheet3.Range("I" & cnt).Value = lngSrch
        Sheet3.Range("J" & cnt).Value = getwindowclass
        Sheet3.Range("K" & cnt).Value = ControlText
        
        cnt = cnt + 1
        
    Loop
End Sub
```


----------



## RoryA (Aug 24, 2010)

As far as I can tell the parent of the dialog window is actually the VBE window, so once you have the dialog handle you can use GetParent to test if the parent is the VBE for your application.
For some reason, using FindWindowEx (or FindWindowX in your case) and specifying the VBE handle as the first argument does not work.


----------



## hatman (Aug 24, 2010)

Can you explain why you say that the parent of the Format Axis dialog is the VBE? When I use GetParent on the Dialog, it returns the XLMain Window.



> : getwindowclass_parent : "XLMAIN" : String
> : ControlText_parent : "Microsoft Excel - Book2" : String


----------



## RoryA (Aug 24, 2010)

It might be because I was stepping through the code in the VBE. If you know the parent is always XLMAIN, then you don't have a problem.


----------



## hatman (Aug 24, 2010)

I agree in part.  Using FindWindow, only the most recently created instance is returned.  I can take the HWND returned (as you suggest), get the parent, and validate it against the XLMain, to verify that I have the correct instance of the dialog.  As far as I can tell, that works fine.  And as I stated in the origonal post: I can live with that solution, for all cases where the most recently created instance is in fact the one I am interested in.  But what if it is not?  I can't forsee a scenario where this would be an issue, but just because I can't forsee it does not mean it can't happen.  In that case, FindWindow won't locate the "next" instance.  While I'd like a more bullet-proof solution for this case, I think I can probably live without it.

Though I am still curious why, if XLMain is the parent of the dialog, none of the other three methods detect the dialog as a child of XLMain.

I guess I was trying to start a discussion (albeit extremely geeky), not necessarily elicit a solution to a particular problem (hence a Lounge post, rather than in the Questions forum).

I'd be interested in any other thoughts/theories...


----------



## RoryA (Aug 24, 2010)

You should be able to use FindWindowEx to find the next instance of the dialog, should one exist, then check its parent handle.

As to why it is the way it is, I suspect they ****ed something up when they made the dialogs modeless in 2007.


----------



## hatman (Aug 24, 2010)

rorya said:


> You should be able to use FindWindowEx to find the next instance of the dialog, should one exist, then check its parent handle.


 
That's what I thought.  Passing the XLMain HWND as the argument for the parent window does not yield the Format Axis dialog (as shown in the code I posted).  When the parent window is omitted, of course, the function searches within windows that are children of the desktop, which the Format Axis dialog is not, so again, it is not found.



rorya said:


> As to why it is the way it is, I suspect they ****ed something up when they made the dialogs modeless in 2007.


 
That's probably the best answer I'm liable to get... I hadn't thought of that.


----------



## RoryA (Aug 24, 2010)

> When the parent window is omitted, of course, the function searches within windows that are children of the desktop, which the Format Axis dialog is not, so again, it is not found.


that's how I found it. Used 0& as the first two arguments.


----------



## hatman (Aug 24, 2010)

Interesting.  When I tried FindWindowEx before, I passed the Desktop HWND as retrieved using the GetDesktopWindow API (cause that's teh usage/example I found on teh net).  That caused it to parse only the top level windows.  At which point, I changed to pass in the XLMain as the parent window, which failed to yield the dialog.  But leaving the argument as a null causes it to parse the lower level windows also, as I now see.  

Thanx Rory!  My new code looks more like this:


```
Function Get_Format_Axis_HWND(XLMAin_hwnd As Long) As Long
    Dim hChild As Long
    
    hChild = FindWindowX(0&, 0&, "NUIDialog", "Format Axis")
    
    Do Until GetParent(hChild) = XLMAin_hwnd Or hChild = 0
       
        hChild = FindWindowX(0&, hChild, "NUIDialog", "Format Axis")
       
    Loop
    
    Get_Format_Axis_HWND = hChild
End Function
```


----------



## Jaafar Tribak (Aug 24, 2010)

Hi hatman.

I don't have Excel 2007 so I couldn't test the code. However I would take a different approach specially if there is a scenario where different instances could be open simultaniously.

I would install a _CBT_ hook immediately before calling the Format Axis Dialogue window and watch for the _HCBT_DESTROYWND_ hook ID which indicates that the Axis Format window has been closed.

This approach avoids the need to loop through endless windows plus will always return the desired instance.

Something like this :


```
Option Explicit
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
 
Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" _
(ByVal idHook As Long, _
ByVal lpfn As Long, _
ByVal hmod As Long, _
ByVal dwThreadId As Long) As Long
 
Private Declare Function CallNextHookEx Lib "user32" _
(ByVal hHook As Long, _
ByVal nCode As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
 
Private Declare Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long
 
Private Declare Function GetCurrentThreadId Lib "kernel32" _
() As Long
 
Private Declare Function GetClassName Lib "user32.dll" _
Alias "GetClassNameA" _
(ByVal hwnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
 
Private Const WH_CBT As Long = 5
Private Const HCBT_DESTROYWND = 4
Private Const GWL_HINSTANCE As Long = (-6)
 
Private lCBTHook  As Long
 
Sub Test()
 
    Sheet3.Rows.Clear

    Chart4.Activate
    
    Chart4.Axes(xlPrimary).Select
 
    lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, _
    GetAppInstance, GetCurrentThreadId)
 
    Application.Dialogs(xlDialogFormatLegend).Show
 
End Sub
 
 
Private Function CBTProc _
(ByVal idHook As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
    Dim sBuffer As String
    Dim lRetVal As Long
 
 
    Select Case idHook
 
        Case Is = HCBT_DESTROYWND 'a Wnd has been closed.
 
            'if so, is it our Format Axis wnd ?
            sBuffer = Space(256)
            lRetVal = GetClassName(wParam, sBuffer, 256)
 
            If Left(sBuffer, lRetVal) = "NUIDialog" Then
 
                'it's our Format Axis wnd. Remove the CBT hook now.
                UnhookWindowsHookEx lCBTHook
 
                MsgBox "The Format Axis Window has been close." & _
                vbNewLine & "You can resume calculation now."
 
            End If
 
    End Select
 
    CBTProc = CallNextHookEx _
    (lCBTHook, idHook, ByVal wParam, ByVal lParam)
 
End Function
 
 
Private Function GetAppInstance() As Long
 
    GetAppInstance = GetWindowLong _
    (FindWindow("XLMAIN", Application.Caption), GWL_HINSTANCE)
 
End Function
```
 
Note that the hook is immediatly removed upon closing the Format Axis Dialogue so it won't have a bad impact on performance or code stability.


----------



## hatman (Aug 24, 2010)

So I have a solution that works (though it isn't quite as bullet-proof as I would like).  What I am interested in is an explanation why NONE of the seemingly more bullet-proof solutions work.

In controlling Excel from a VB 6.0 application, at some point, the user can display the Format Axis dialog of the Excel instance, which is done programmatically using the Dialogs().Show method.  The problem comes in where I need to halt the recalculation before the dialog is displayed, and resume recalc after it is manually closed.  Oh, and the user may have other instances of the Excel Application open, not under of the VB 6.0 application.  To do this, i need teh HWND of the Format Axis window, then periodically monitor it unil it is closed.

My first approach was to use the HWND of the XLMAIN window to find the appropriate child window.  But after 3 different methods (some of which I later read in various articles actually call EnumWindows in teh background anyway, so perhaps they aren't as unique as I thought) I determined that the Format Axis window isn't visible as a child.  WHY NOT???

The FindWindow function works to find the most recently created inistance of the window, which is probably fine, since it gets called immediately after the instance I am looking for is created, making it extremely unlikely to get stuck with the wrong instance.


```
Option Explicit
Private Declare Function EnumChildWindows _
    Lib "user32" _
    (ByVal hWndParent As Long, _
    ByVal lpEnumFunc As Long, _
    ByVal lParam As Long) As Long
    
Public Declare Function GetClassName _
    Lib "user32" Alias "GetClassNameA" _
    (ByVal hwnd As Long, _
    ByVal lpClassName As String, _
    ByVal nMaxCount As Long) As Long
    
Public Declare Function GetWindowText _
    Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, _
    ByVal lpString As String, _
    ByVal cch As Long) As Long
    
Public Declare Function GetWindowTextLength _
    Lib "user32" Alias "GetWindowTextLengthA" _
    (ByVal hwnd As Long) As Long
    
Public Declare Function FindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    
Public Declare Function GetParent _
    Lib "user32.dll" _
    (ByVal hwnd As Long) As Long
    
Public Declare Function FindWindowX _
    Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As Long, _
    ByVal lpsz2 As Long) As Long
    
Public Declare Function GetWindow _
    Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal wCmd As Long) As Long
Private Const GW_CHILD = 5
Private Const GW_HWNDNEXT = 2
Private Const GW_HWNDPREV = 3
Dim windows() As Long
Dim windowsCount As Long
Sub Compare_Them_All()
    Sheet3.Rows.Clear
    Chart4.Activate
    
    Chart4.Axes(xlPrimary).Select
    
    Application.Dialogs(xlDialogFormatLegend).Show
    Call EnumWindow_Method
    
    Call FindWindowX_Method
    
    Call FindWindow_Method
    
    Call GetWindow_Method
End Sub
Sub EnumWindow_Method()
    Dim a() As Long
    Dim cnt As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    sClass = Space$(256)
    GetClassName Application.hwnd, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(Application.hwnd) + 1, Chr$(0))
    GetWindowText Application.hwnd, ControlText, Len(ControlText)
    
    Sheet3.Range("A1").Value = Application.hwnd
    Sheet3.Range("B1").Value = getwindowclass
    Sheet3.Range("C1").Value = ControlText
    
    
    a = ChildWindows(Application.hwnd)
    
    For cnt = 1 To UBound(a, 1)
    
        sClass = Space$(256)
        GetClassName a(cnt), sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        ControlText = String(GetWindowTextLength(a(cnt)) + 1, Chr$(0))
        GetWindowText a(cnt), ControlText, Len(ControlText)
        
        Sheet3.Range("A" & cnt + 1).Value = a(cnt)
        Sheet3.Range("B" & cnt + 1).Value = getwindowclass
        Sheet3.Range("C" & cnt + 1).Value = ControlText
        
    Next cnt
End Sub
Function ChildWindows(ByVal hwnd As Long) As Long()
    windowsCount = 0
    
    EnumChildWindows hwnd, AddressOf EnumWindows_CBK, 1
    
    ReDim Preserve windows(windowsCount) As Long
    
    ChildWindows = windows()
    
End Function

Private Function EnumWindows_CBK(ByVal hwnd As Long, ByVal lParam As Long) As Long
    
    If windowsCount = 0 Then
    
        ReDim windows(100) As Long
        
    ElseIf windowsCount >= UBound(windows) Then
    
        ReDim Preserve windows(windowsCount + 100) As Long
        
    End If
    
    
    windowsCount = windowsCount + 1
    
    windows(windowsCount) = hwnd
    
    EnumWindows_CBK = 1
    
End Function
Sub FindWindowX_Method()
    Dim hChild As Long
    Dim cnt As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    hChild = FindWindowX(Application.hwnd, 0, 0, 0)
    
    cnt = 2
    
    Do
       
        sClass = Space$(256)
        GetClassName hChild, sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        ControlText = String(GetWindowTextLength(hChild) + 1, Chr$(0))
        GetWindowText hChild, ControlText, Len(ControlText)
        
        Sheet3.Range("E" & cnt).Value = hChild
        Sheet3.Range("F" & cnt).Value = getwindowclass
        Sheet3.Range("G" & cnt).Value = ControlText
        
        hChild = FindWindowX(Application.hwnd, hChild, 0, 0)
       
       cnt = cnt + 1
       
    Loop Until hChild = 0
End Sub
Sub FindWindow_Method()
    Dim DialogHwnd As Long
    Dim parent_h As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    'this one only gets the MOST RECENT instance.
    
    DialogHwnd = FindWindow("NUIDialog", "Format Axis")
    
    parent_h = GetParent(DialogHwnd)
    
    sClass = Space$(256)
    GetClassName DialogHwnd, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(DialogHwnd) + 1, Chr$(0))
    GetWindowText DialogHwnd, ControlText, Len(ControlText)
    
    Sheet3.Range("M2").Value = DialogHwnd
    Sheet3.Range("N2").Value = getwindowclass
    Sheet3.Range("O2").Value = ControlText
    
    sClass = Space$(256)
    GetClassName parent_h, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(parent_h) + 1, Chr$(0))
    GetWindowText parent_h, ControlText, Len(ControlText)
    
    Sheet3.Range("M1").Value = parent_h
    Sheet3.Range("N1").Value = getwindowclass
    Sheet3.Range("O1").Value = ControlText
End Sub
Sub GetWindow_Method()
    Dim lngSrch As Long
    Dim ControlText As String
    Dim sClass As String
    Dim getwindowclass As String
    Dim cnt As Integer
    
    lngSrch = Application.hwnd
    
    lngSrch = GetWindow(lngSrch, GW_CHILD)
    
    cnt = 2
    
    Do
        lngSrch = GetWindow(lngSrch, GW_HWNDNEXT)
        
        If lngSrch = 0 Then
        
            Exit Do
            
        End If
        
        ControlText = String(GetWindowTextLength(lngSrch) + 1, Chr$(0))
        GetWindowText lngSrch, ControlText, Len(ControlText)
        
        sClass = Space$(256)
        GetClassName lngSrch, sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        Sheet3.Range("I" & cnt).Value = lngSrch
        Sheet3.Range("J" & cnt).Value = getwindowclass
        Sheet3.Range("K" & cnt).Value = ControlText
        
        cnt = cnt + 1
        
    Loop
End Sub
```


----------



## hatman (Aug 25, 2010)

Jaafar Tribak said:


> Hi hatman.
> 
> I don't have Excel 2007 so I couldn't test the code. However I would take a different approach specially if there is a scenario where different instances could be open simultaniously.
> 
> ...


 
Interesting. Without testing it, my first impression is that this is a very efficient approach, with several key advantages over my previous plan. I'm wondering how it will play out with the larger project it is going to be placed into. 

Correct me if I am wrong: It looks like when the window close event gets raised, the CBTProc function gets called. How will that interact with other code that is running simultaneously?


----------



## Jaafar Tribak (Aug 25, 2010)

> Interesting. Without testing it, my first impression is that this is a very efficient approach, with several key advantages over my previous plan. I'm wondering how it will play out with the larger project it is going to be placed into.
> 
> Correct me if I am wrong: It looks like when the window close event gets raised, the CBTProc function gets called. How will that interact with other code that is running simultaneously?


 
the CBTProc function is called right before displaying the window and remains running until the window is closed . Once the window is closed the close event gets raised . 

Having other code running simultaneously while the CBT hook is installed shouldn't be a problem. But you should test it nonetheless.


----------



## hatman (Sep 25, 2010)

I finally got time to implement and test this (man but it's been a busy month, and it's not gonna let up any time soon).

Jaafar, this is a sweet little method when run under the Excel application.  I'm still not sure I completely understand it all, but it looks to be EXACTLY what I'm looking for.  It seems stable, effective and all o fteh other superlatives you want to insert here.

One minor problem:

I can't get it to do it's thing in VB 6.0.  I'm wondering if if the thread ID's aren't getting crossed up somehow.  Perhaps the dwThreadId when calling SetWindowsHookEx should not be the current thread, but instead one of teh trheads from teh XL application?  One of teh the things that mystifies me is how you are identifying the thread for the AxisDialog... I assume that you are somehow capturing it on the fly, but it seems implicit rather than explicit.  

In debug mode, I can see that the CBTProc gets called/raised iteratively until the axis dialog is closed... at least when I run this through the Excel VBE.  When I move all of this code into my VB 6.0 application, which is automating Excel, I can't see that CBTProc gets called/raised at all.

If you get a chance to look at this, I'd appreciate it.  Thanx Jaafar!


----------



## Jaafar Tribak (Sep 26, 2010)

hatman.

AFAIK, you can't hook a foreign process without loading a dll into the foreign process memory space which is not straightforward.

However since you are automating Excel from VB6 , then presumably you have a pointer to the workbook where the Format Axis dialog is being displayed in which case you could conviniently run the hooking Macro remotely from VB6 via the the Application _Run_ Method.

So based on the code I posted, if you leave the whole code inside the target workbook ( instead of placing the code in VB6 ) and remotely call the _Test_ routine from the VB6 application something along the lines of :


```
Dim wb As Object
Set wb = GetObject("C:\Target.xls") 'change target path as required.
Call wb.Application.Run(wb.Name & "!Test")
```
 
In this fashion, the hook code will run inside the Excel process memory space and should then work as is.


----------



## hatman (Sep 27, 2010)

Okay.  That makes sense.  If I were automating XL with an existing workbook, that approach would make a lot of sense.  But what I am really doing is starting a new XL Application instance, and creating a new blank workbook from scratch, to be the repository for live streaming telemetry data from the Space Station.  Then, when the user is done recording the data, the instance of XL gets set free with all of the data available for viewing and manipulating in it's own file.

I made my other approach work.  Since the data refreshes once per second, the code only pings the FromatAxis window once per second after it is opened, then when it is no longer detected, the code goes dormant again.  

Thanx for your input, Jafaar.  As always, your response certainly gave me a lot to chew on and forced me to push my limits on API calls and the nitty gritty workings of how windows handles this stuff on a lower level.  But I'm afraid I'm just not going to be able to make this approach work within this application.  But I will certainly be keeping it in mind for some other projects I have brewing in the back of my head right now.


----------



## Jaafar Tribak (Sep 27, 2010)

Hi hatman.

If you are starting a new XL Application instance and creating the workbook from scratch then you could easily export the whole hook code before hand and save it somewhere to disk.

Then you could just import the bas file into the newly created workbook on the fly and run the code like this :


```
Dim oApp As Object
Dim oWB As Object
 
Sub Macro()
 
    Set oApp = CreateObject("Excel.Application")
    
    Set oWB = oApp.Workbooks.Add
    
    oWB.SaveAs "C:\Target.xls"
    
    oWB.VBProject.VBComponents.Import "C:\Module1.bas"
    
    Call oWB.Application.Run(oWB.Name & "!Test")
    
    oWB.Close True
    
    ' Kill "C:\Module1.bas"    '\\ Optionally delete the bas file.
    
    oApp.Quit
 
End Sub
```
 
You will need to have the Macro security "Trust Access to the VBProject Access" set. -  And even temporarly changing this security setting could be easily done via a little code.

If this doesn't work for you then the only thing I can think of is ,as I said before, have the hook code in a ActiveX dll and load the dll into the excel application process space. I haven't done this before but i'll see if I can give it a shot.


----------



## hatman (Sep 27, 2010)

Thanx Jaafar.  I didn;t expect you to put quite that much thought into it.  I'll try it out and see how I like the final form.


----------

