Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109040

Re: Error in VB Editor Coding

Show all headers | View raw

"Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> wrote in message
> On 01-Jul-16 7:01 PM, Peter T wrote:
>> "Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> wrote in message
>>> Hi
>>>
>>> I have used the code  below to close the extra VB windows that end up
>>> being open when using the VBA editor.
>>>
>>> Sub CloseVBEWindows()
>>> Dim W As VBIDE.Window
>>>
>>> ThisWorkbook.VBProject.VBE.MainWindow.SetFocus
>>> For Each W In Application.VBE.Windows
>>>      If W.Type = vbext_wt_CodeWindow Or W.Type = vbext_wt_Designer Then
>>>          If Application.VBE.ActiveWindow.Caption <> W.Caption Then
>>>                         W.Close
>>>          End If
>>>      End If
>>> Next W
>>> End Sub
>>>
>>> It works fine on a 64-bit laptop running Windows 10 and Excel 2010.
>>> When I run it on a 64-bit laptop with windows 7 and Excel Professional
>>> Plus I get the error 424 (object required) at the following line:
>>> If W.Type = vbext_wt_CodeWindow Or W.Type = vbext_wt_Designer Then
>>>
>>> The extensibility library 5.3 has been set in references.
>>>
>>> Can anyone suggest a possible solution, please
>>
>> That ought to work, I have something similar (and more to collapse all
>> projects). Curious though why you get that error there and not on the for
>> each line. If there was anything wrong with the reference you would have 
>> got
>> a compile error. When it breaks see what W refers to in Locals, alt-v, s
>>
>> If you want to try Mike's suggestion and declare W as object, also change
>> the named vbext constants to 0 and 1 respectively (or simply if W.Type <= 
>> 1)
>> to rule out the extensibility reference.
>>
>> Peter T
>>
>>
>>
>
> Hi Peter
>
> Thanks for the suggestion
>
> I found that on the Acer machine the following code still failed in 
> runtime with the same error for one VBE window (see below):
>
> Sub A_CloseVBEWindowsTEST()
>
> Dim W As Object
> Dim iCounter As Integer
>
> ThisWorkbook.VBProject.VBE.MainWindow.SetFocus
>
>
> Debug.Print "total No of windows = " & Application.VBE.Windows.Count
> iCounter = Application.VBE.Windows.Count
>
> For Each W In Application.VBE.Windows
>     Debug.Print "W #" & iCounter & " is currently: " & (W.Caption)
>
>     iCounter = iCounter - 1
> Next W
> End Sub
>
> It produced this in the Immediate Window on another machine:
>
> total No of windows = 9
> W #9 is currently: Close VBE windows, ex Acer test on Mac TEMP.xlsm - 
> Sheet1 (Code)
> W #8 is currently: mClearVBECodeWindows (Code)
> W #7 is currently: Project - VBAProject
> W #6 is currently:
> W #5 is currently: Properties
> W #4 is currently: Object Browser
> W #3 is currently: Watches
> W #2 is currently: Locals
> W #1 is currently: Immediate
>
> I think it is failing on the "blank" window i.e. Window #6 on the Acer but 
> am not sure - what is that window - I recognise the others?
>
> Any help welcome
>

Did you try what I suggested before, look at the errant W in Locals, and the 
way I suggested when it breaks after determining it's a module window. That 
#6 above be the one that's causiong problems but without knowing could be 
some other unrelated window.

Trivial but FWIW ThisWorkbook.VBProject.VBE.MainWindow is the same as 
Application.VBE

Peter T 

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Error in VB Editor Coding Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> - 2016-06-30 20:50 +0100
  Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-06-30 19:44 -0400
  Re: Error in VB Editor Coding Mike S <mscir@yahoo.com> - 2016-06-30 17:41 -0700
  Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-01 19:01 +0100
    Re: Error in VB Editor Coding Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> - 2016-07-02 18:48 +0100
      Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-02 18:38 -0400
        Re: Error in VB Editor Coding Tim Childs <tsnip1IgnoreThis@yahoo.co.uk> - 2016-07-03 05:11 +0100
          Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-03 00:16 -0400
            Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-03 17:02 +0100
              Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-03 14:49 -0400
                Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-03 16:38 -0400
                Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-04 11:31 +0100
                Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 06:39 -0400
                Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 06:41 -0400
                Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-04 12:18 +0100
                Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 07:23 -0400
                Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-04 12:49 +0100
                Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 09:20 -0400
      Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-03 16:37 +0100
        Re: Error in VB Editor Coding Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> - 2016-07-03 22:02 +0100

csiph-web