If Excel's limits (e.g. 65536 rows) inspire you to check out using a
database, go straight to Access, the default choice because all Excel
users have it. Other databases are not available so don't try looking,
you won't find any. And don't just use Access as a data sto ensure
you completely re-write your spreadsheet as an Access application.
Access (don't call it MS Access because doing so prevents the helpful
'Access data' and 'access data' confusion) is a relational database:
pay no attention to the Jet engine behind the curtain. You need the
Access application installed on your machine to be able to access
Access databases; you can access the data from with Excel by
automating Access in VBA code.

The term 'named range' is preferred because that's what Lotus 1-2-3
(RIP) called them; 123 was the granddaddy of spreadsheets meaning most
Excel users have 123 experience. The term 'defined Name' (note the
pedantic capitalization) is too technical, overly Excelish and
ultimately misleading because a Name can only ever refer to a Range
object.

Use public variables in standard modules liberally. It saves a lot of
learning e.g. the concept of scope, passing arguments to functions
ByVal or ByRef, what a class module is for, etc. Other handy things to
check out are Goto and GoSub which have long been considered harmless.

Feel free to rely on the implicit default behavior of a featu
everyone is familiar with it and its behavior is guaranteed never to
change, even when you move to the highly dubious VB.NET (note C# is
not suitable for experienced VBA users). For example, don't specify an
object's default property e.g. use Range("A1") in place of
Range("A1").Value, especially when assigning it to a public variable
of type Variant (but don't declare it as Variant, use the implicit Dim
MyVar1), and do not qualify the object - Excel will work out what you
mean - and never, repeat never, qualify the object with the class name
'Excel' as in Excel.Application.ActiveWindow. Naturally there are
exceptions where default beavior is not to be trusted e.g. never
merely let a local object variable go out of scope but instead
explicitly set it to Nothing, e.g. Set MyObject = Nothing, because VB
won't release it unless told to do so (it only cleans up those
variables to which you don't have access to e.g. where you've used
With MyObject..End With).

If you choose to go down the road less travelled and use a class e.g.
by creating a userform, don't worry about the class not being aware of
its own members. This is common practice in object oriented
programming because you always get a handy collection to loop through
at run-time e.g. For Each ctl In Me.Controls to check that the
controls you put on at design-time are still there.

I have discovered a truly remarkable way of programmatically clearing
the Immediate Window (without using SendKeys) which this margin is too
small to contain.

Never try to take the credit / We'll all assume Chip Pearson said it.

That's it. See ya.

--

Thanks, onedaywhen but I fail to see why you thought any of this needed
saying.
These are the bits we already know about programming Excel.

It would have been much more helpful if you had posted a list of places
where ready-written code was available.

This would help us find a list of questions to ask which we could be
absolutely sure had been addresssed lot of times before.

onedaywhen wrote:
If Excel's limits (e.g. 65536 rows) inspire you to check out using a
database, go straight to Access, the default choice because all Excel
users have it. Other databases are not available so don't try looking,
you won't find any. And don't just use Access as a data sto ensure
you completely re-write your spreadsheet as an Access application.
Access (don't call it MS Access because doing so prevents the helpful
'Access data' and 'access data' confusion) is a relational database:
pay no attention to the Jet engine behind the curtain. You need the
Access application installed on your machine to be able to access
Access databases; you can access the data from with Excel by
automating Access in VBA code.

The term 'named range' is preferred because that's what Lotus 1-2-3
(RIP) called them; 123 was the granddaddy of spreadsheets meaning most
Excel users have 123 experience. The term 'defined Name' (note the
pedantic capitalization) is too technical, overly Excelish and
ultimately misleading because a Name can only ever refer to a Range
object.

Use public variables in standard modules liberally. It saves a lot of
learning e.g. the concept of scope, passing arguments to functions
ByVal or ByRef, what a class module is for, etc. Other handy things to
check out are Goto and GoSub which have long been considered harmless.

Feel free to rely on the implicit default behavior of a featu
everyone is familiar with it and its behavior is guaranteed never to
change, even when you move to the highly dubious VB.NET (note C# is
not suitable for experienced VBA users). For example, don't specify an
object's default property e.g. use Range("A1") in place of
Range("A1").Value, especially when assigning it to a public variable
of type Variant (but don't declare it as Variant, use the implicit Dim
MyVar1), and do not qualify the object - Excel will work out what you
mean - and never, repeat never, qualify the object with the class name
'Excel' as in Excel.Application.ActiveWindow. Naturally there are
exceptions where default beavior is not to be trusted e.g. never
merely let a local object variable go out of scope but instead
explicitly set it to Nothing, e.g. Set MyObject = Nothing, because VB
won't release it unless told to do so (it only cleans up those
variables to which you don't have access to e.g. where you've used
With MyObject..End With).

If you choose to go down the road less travelled and use a class e.g.
by creating a userform, don't worry about the class not being aware of
its own members. This is common practice in object oriented
programming because you always get a handy collection to loop through
at run-time e.g. For Each ctl In Me.Controls to check that the
controls you put on at design-time are still there.

I have discovered a truly remarkable way of programmatically clearing
the Immediate Window (without using SendKeys) which this margin is too
small to contain.

Never try to take the credit / We'll all assume Chip Pearson said it.

That's it. See ya.

--

You do understand that this was tongue-in-cheek/irony and the opposite was
intended? (I hope you weren't agreeing with the advice <g)

Read back through some of his recent postings and it appears he is not
having a fun week.

I quote:

This is your captain speaking. The irony filter has now been
extinguished.

--
Regards,
Tom Ogilvy

"Steve Garman" wrote in message
...
Thanks, onedaywhen but I fail to see why you thought any of this needed
saying.
These are the bits we already know about programming Excel.

It would have been much more helpful if you had posted a list of places
where ready-written code was available.

This would help us find a list of questions to ask which we could be
absolutely sure had been addresssed lot of times before.

onedaywhen wrote:
If Excel's limits (e.g. 65536 rows) inspire you to check out using a
database, go straight to Access, the default choice because all Excel
users have it. Other databases are not available so don't try looking,
you won't find any. And don't just use Access as a data sto ensure
you completely re-write your spreadsheet as an Access application.
Access (don't call it MS Access because doing so prevents the helpful
'Access data' and 'access data' confusion) is a relational database:
pay no attention to the Jet engine behind the curtain. You need the
Access application installed on your machine to be able to access
Access databases; you can access the data from with Excel by
automating Access in VBA code.

The term 'named range' is preferred because that's what Lotus 1-2-3
(RIP) called them; 123 was the granddaddy of spreadsheets meaning most
Excel users have 123 experience. The term 'defined Name' (note the
pedantic capitalization) is too technical, overly Excelish and
ultimately misleading because a Name can only ever refer to a Range
object.

Use public variables in standard modules liberally. It saves a lot of
learning e.g. the concept of scope, passing arguments to functions
ByVal or ByRef, what a class module is for, etc. Other handy things to
check out are Goto and GoSub which have long been considered harmless.

Feel free to rely on the implicit default behavior of a featu
everyone is familiar with it and its behavior is guaranteed never to
change, even when you move to the highly dubious VB.NET (note C# is
not suitable for experienced VBA users). For example, don't specify an
object's default property e.g. use Range("A1") in place of
Range("A1").Value, especially when assigning it to a public variable
of type Variant (but don't declare it as Variant, use the implicit Dim
MyVar1), and do not qualify the object - Excel will work out what you
mean - and never, repeat never, qualify the object with the class name
'Excel' as in Excel.Application.ActiveWindow. Naturally there are
exceptions where default beavior is not to be trusted e.g. never
merely let a local object variable go out of scope but instead
explicitly set it to Nothing, e.g. Set MyObject = Nothing, because VB
won't release it unless told to do so (it only cleans up those
variables to which you don't have access to e.g. where you've used
With MyObject..End With).

If you choose to go down the road less travelled and use a class e.g.
by creating a userform, don't worry about the class not being aware of
its own members. This is common practice in object oriented
programming because you always get a handy collection to loop through
at run-time e.g. For Each ctl In Me.Controls to check that the
controls you put on at design-time are still there.

I have discovered a truly remarkable way of programmatically clearing
the Immediate Window (without using SendKeys) which this margin is too
small to contain.

Never try to take the credit / We'll all assume Chip Pearson said it.

That's it. See ya.

--

onedaywhen wrote ...

I have discovered a truly remarkable way of
programmatically clearing the Immediate
Window (without using SendKeys) which
this margin is too small to contain.

I couldn't let this one go (Fermat parody, right?), the offer of a
Chip Pearson autographed bottle of home-brewed hot sauce proving just
too irresistible:

http://www.google.com/groups?threadm...%40tkmsftngp04

I've some up with the code, Excel and VB6 versions (the latter being
VB6 code to clear the Excel Immediate Window but can be easily
modified to clear the VB6 immediate window). How may I claim my prize?

'<--- Excel Version ---
' Code in a standard module
Option Explicit

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80

Private m_KeyboardState(0 To 255) As Byte
Private m_hSaveKeystate As Long

Sub ClearImmediateWindow()

Dim hChild As Long
Dim hParent As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get handle to Immediate Window
strCaptionVbe = Excel.Application.VBE.MainWindow.Caption
hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, _
CLASS_IMMEDIATE, CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Simulate depressing of CTRL key
GetKeyboardState m_KeyboardState(0)
m_hSaveKeystate = m_KeyboardState(VK_CONTROL)
m_KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState m_KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"

End Sub

Sub DoCleanUp()

' Restore keyboard state
GetKeyboardState m_KeyboardState(0)
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)

End Sub
'</--- Excel Version ---

'<--- VB6 Version ---
Option Explicit

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetWindowThreadProcessId _
Lib "user32" (ByVal hwnd As Long, _
lpdwProcessId As Long) As Long
Private Declare Function GetCurrentThreadId _
Lib "kernel32" () As Long
Private Declare Function AttachThreadInput _
Lib "user32" (ByVal idAttach As Long, _
ByVal idAttachTo As Long, _
ByVal fAttach As Long) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80
Private Sub Form_Load()

Dim xlApp As Object
Dim hChild As Long
Dim KeyboardState(0 To 255) As Byte
Dim hParent As Long
Dim hProcessID As Long
Dim hThreadID As Long
Dim hCurrentThreadID As Long
Dim hSaveKeystate As Long
Dim hRet As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get running instance of Excel
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0

If xlApp Is Nothing Then
MsgBox "Excel not running."
Exit Sub
End If

' Get handle to Immediate Window
On Error Resume Next
strCaptionVbe = xlApp.VBE.MainWindow.Caption
On Error GoTo 0

hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, CLASS_IMMEDIATE,
CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Get thread info
hThreadID = GetWindowThreadProcessId(hChild, vbNull)
hCurrentThreadID = GetCurrentThreadId()

' Attach Excel thread
hRet = AttachThreadInput(hThreadID, hCurrentThreadID, 1)

' Simulate depressing of CTRL key
GetKeyboardState KeyboardState(0)
hSaveKeystate = KeyboardState(VK_CONTROL)
KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Restore keyboard state
GetKeyboardState KeyboardState(0)
KeyboardState(VK_CONTROL) = hSaveKeystate
SetKeyboardState KeyboardState(0)

' Re-attched thread
hRet = AttachThreadInput(hThreadID, hCurrentThreadID, 0)

End Sub
'</--- VB6 Version ---

Jamie

--

Jamie,

I can't get the code to work. It will activate the Immediate
window, but won't clear it. I tested in 97, 2002, and 2003.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jamie Collins" wrote in message
om...
onedaywhen wrote ...

I have discovered a truly remarkable way of
programmatically clearing the Immediate
Window (without using SendKeys) which
this margin is too small to contain.

I couldn't let this one go (Fermat parody, right?), the offer
of a
Chip Pearson autographed bottle of home-brewed hot sauce
proving just
too irresistible:


http://www.google.com/groups?threadm...%40tkmsftngp04

I've some up with the code, Excel and VB6 versions (the latter
being
VB6 code to clear the Excel Immediate Window but can be easily
modified to clear the VB6 immediate window). How may I claim my
prize?

'<--- Excel Version ---
' Code in a standard module
Option Explicit

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80

Private m_KeyboardState(0 To 255) As Byte
Private m_hSaveKeystate As Long

Sub ClearImmediateWindow()

Dim hChild As Long
Dim hParent As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get handle to Immediate Window
strCaptionVbe = Excel.Application.VBE.MainWindow.Caption
hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, _
CLASS_IMMEDIATE, CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Simulate depressing of CTRL key
GetKeyboardState m_KeyboardState(0)
m_hSaveKeystate = m_KeyboardState(VK_CONTROL)
m_KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState m_KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"

End Sub

Sub DoCleanUp()

' Restore keyboard state
GetKeyboardState m_KeyboardState(0)
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)

End Sub
'</--- Excel Version ---

'<--- VB6 Version ---
Option Explicit

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetWindowThreadProcessId _
Lib "user32" (ByVal hwnd As Long, _
lpdwProcessId As Long) As Long
Private Declare Function GetCurrentThreadId _
Lib "kernel32" () As Long
Private Declare Function AttachThreadInput _
Lib "user32" (ByVal idAttach As Long, _
ByVal idAttachTo As Long, _
ByVal fAttach As Long) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80
Private Sub Form_Load()

Dim xlApp As Object
Dim hChild As Long
Dim KeyboardState(0 To 255) As Byte
Dim hParent As Long
Dim hProcessID As Long
Dim hThreadID As Long
Dim hCurrentThreadID As Long
Dim hSaveKeystate As Long
Dim hRet As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get running instance of Excel
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0

If xlApp Is Nothing Then
MsgBox "Excel not running."
Exit Sub
End If

' Get handle to Immediate Window
On Error Resume Next
strCaptionVbe = xlApp.VBE.MainWindow.Caption
On Error GoTo 0

hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, CLASS_IMMEDIATE,
CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Get thread info
hThreadID = GetWindowThreadProcessId(hChild, vbNull)
hCurrentThreadID = GetCurrentThreadId()

' Attach Excel thread
hRet = AttachThreadInput(hThreadID, hCurrentThreadID, 1)

' Simulate depressing of CTRL key
GetKeyboardState KeyboardState(0)
hSaveKeystate = KeyboardState(VK_CONTROL)
KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Restore keyboard state
GetKeyboardState KeyboardState(0)
KeyboardState(VK_CONTROL) = hSaveKeystate
SetKeyboardState KeyboardState(0)

' Re-attched thread
hRet = AttachThreadInput(hThreadID, hCurrentThreadID, 0)

End Sub
'</--- VB6 Version ---

Jamie

--

"Chip Pearson" wrote ...

Jamie,

I can't get the code to work. It will activate the Immediate
window, but won't clear it. I tested in 97, 2002, and 2003.

Chip,
This is very frustrating! I suspect sabotage <g.

Seriously though, I've now tested in Excel97 (Win2K), Excel2000
(Win98, WinXP), Excel2002 (Win2K) and Excel2003 (Win2K, WinXP) and it
worked every time. It also worked for a colleague on Excel2000 (NT4),
just to prove it was a trick of my real and virtual machines.

In Excel2002 and above, the code as posted ('Excel version' only) will
cause a run-time error if you have not granted trust access to Visual
basic project. I wouldn't expect the behavior you describe i.e.
activating but not clearing the Immediate Window so I can't guess why
it's not working for you.

I'd be grateful if anyone else out there could test my code. Here is
the Excel version again, with the fix to handle the situation where
trust access to Visual basic project has not been granted. Paste the
following code into a standard .bas module in an open workbook, type
some text into the Immediate Window, optionally close the VBE, run the
ClearImmediateWindow macro. Does the Immediate Window clear for you?

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80

Private m_KeyboardState(0 To 255) As Byte
Private m_hSaveKeystate As Long

Sub ClearImmediateWindow()

Dim hChild As Long
Dim hParent As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get handle to Immediate Window
'On Error Resume Next
strCaptionVbe = Excel.Application.VBE.MainWindow.Caption
On Error GoTo 0

hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, _
CLASS_IMMEDIATE, CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Simulate depressing of CTRL key
GetKeyboardState m_KeyboardState(0)
m_hSaveKeystate = m_KeyboardState(VK_CONTROL)
m_KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState m_KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"

End Sub

Sub DoCleanUp()

' Restore keyboard state
GetKeyboardState m_KeyboardState(0)
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)

End Sub
'---------------------

Thanks,
Jamie.

--

Jamie,

I just tried it, and it does indeed work in Excel 97 and 2003.
Good work!


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jamie Collins" wrote in message
om...
"Chip Pearson" wrote ...

Jamie,

I can't get the code to work. It will activate the Immediate
window, but won't clear it. I tested in 97, 2002, and 2003.

Chip,
This is very frustrating! I suspect sabotage <g.

Seriously though, I've now tested in Excel97 (Win2K), Excel2000
(Win98, WinXP), Excel2002 (Win2K) and Excel2003 (Win2K, WinXP)
and it
worked every time. It also worked for a colleague on Excel2000
(NT4),
just to prove it was a trick of my real and virtual machines.

In Excel2002 and above, the code as posted ('Excel version'
only) will
cause a run-time error if you have not granted trust access to
Visual
basic project. I wouldn't expect the behavior you describe i.e.
activating but not clearing the Immediate Window so I can't
guess why
it's not working for you.

I'd be grateful if anyone else out there could test my code.
Here is
the Excel version again, with the fix to handle the situation
where
trust access to Visual basic project has not been granted.
Paste the
following code into a standard .bas module in an open workbook,
type
some text into the Immediate Window, optionally close the VBE,
run the
ClearImmediateWindow macro. Does the Immediate Window clear for
you?

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80

Private m_KeyboardState(0 To 255) As Byte
Private m_hSaveKeystate As Long

Sub ClearImmediateWindow()

Dim hChild As Long
Dim hParent As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get handle to Immediate Window
'On Error Resume Next
strCaptionVbe = Excel.Application.VBE.MainWindow.Caption
On Error GoTo 0

hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, _
CLASS_IMMEDIATE, CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Simulate depressing of CTRL key
GetKeyboardState m_KeyboardState(0)
m_hSaveKeystate = m_KeyboardState(VK_CONTROL)
m_KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState m_KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"

End Sub

Sub DoCleanUp()

' Restore keyboard state
GetKeyboardState m_KeyboardState(0)
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)

End Sub
'---------------------

Thanks,
Jamie.

--