Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #109044
| From | Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> |
|---|---|
| Newsgroups | microsoft.public.excel.programming |
| Subject | Re: Error in VB Editor Coding |
| Date | 2016-07-03 22:02 +0100 |
| Organization | A noiseless patient Spider |
| Message-ID | <nlbuie$nus$1@dont-email.me> (permalink) |
| References | <nl3t7i$t4$1@dont-email.me> <nl6b13$h5p$1@dont-email.me> <nl8uru$ib8$1@dont-email.me> <nlbbdv$kbi$1@dont-email.me> |
On 03-Jul-16 4:37 PM, Peter T wrote: > "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 > > Hi Peter Thanks for the response. I will try that and thanks for the FWIW tip - always looking for simplification :) Best wishes Tim
Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Find similar | Unroll 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