Hummm, not the most robust code - you may have to flick between the VBE and the Excel window, I don't have time to develop it further, but it gets the job done for now.
Â
Â
VBA:
-
Private Declare Function SetParent Lib "user32" ( _
-
ByVal hWndChild As Long, _
-
ByVal hWndNewParent As Long) As Long
-
-
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 SetForegroundWindow Lib "user32" ( _
-
ByVal hWnd As Long) As Long
-
-
Private Declare Function SetWindowPos Lib "user32" ( _
-
ByVal hWnd As Long, _
-
ByVal hWndInsertAfter As Long, _
-
ByVal x As Long, ByVal y As Long, _
-
ByVal cx As Long, _
-
ByVal cy As Long, _
-
ByVal wFlags As Long) As Long
-
Public Sub GetIMWin()
-
Dim PWHand As Long
-
Dim CWHand As Long
-
Dim Res As Long
-
-
'''Get excel desktop handel
-
PWHand = FindWindowEx(Application.hWnd, 0&, "XLDESK", vbNullString)
-
'''open VBE
-
If Application.VBE.MainWindow.Visible = False Then
-
Application.VBE.MainWindow.Visible = True
-
End If
-
'''open IM window if not open
-
If Application.VBE.Windows("immediate").Visible = False Then
-
Application.VBE.MainWindow.SetFocus
-
Application.SendKeys ("!G") '''oh send keys;-(
-
End If
-
'''Get IM window handel
-
CWHand = GetIM
-
'''Set IM as child of excel desktop window
-
Res = SetParent(CWHand, PWHand)
-
-
Application.VBE.MainWindow.Visible = True
-
-
With Application.VBE.Windows("immediate")
-
.Visible = True
-
.Height = 100
-
.Width = 400
-
.Top = 100
-
.Left = 400
-
   End With
-
End Sub
-
-
Function GetIM() As Long
-
Dim VBEHand As Long
-
VBEHand = FindWindow("wndclass_desked_gsk", Application.VBE.MainWindow.Caption)
-
GetIM = FindWindowEx(VBEHand, 0&, "VbaWindow", "immediate")
-
If GetIM = 0 Then
-
GetIM = FindWindow("VBFloatingPalette", "immediate")
-
End If
-
End Function
Related posts:
Ross
I had a little play around with this the other day - brilliant idea.
I can see a few uses for the window and for the technique itself.
cheers
Simon
Thanks Simon,
It's never something that I've ever really wanted but I have hear it ask about before, so when I saw the API code somewhere I gave it a go. To be honest I used a Z order approach to begin with but that did not work, this kind does.
cheers
Ross
Ross
I never wanted it till I saw it, and tried it. It so much better than clicking back and forth to the VBE. I have some plans to play around with this in the near future - I'll let you know how I get on.
cheers
Simon
[...] That was until I found Ross’s bit of code to rip the immediate window out of VBA and put it in the Excel desktop instead. What a great idea, I wish I’d thought of it. I didn’t, but it did inspire this little form based tool. Its as tatty as, just a mega rough and ready thing that I probably wont bother to develop further. No idea if its any use, of if I’ve missed something obvious, and there is a more simple way to do this. If so, be sure to let us know via comments. [...]
[...] It is a bit like a spreadsheet with a code editing window tacked below. Both Ross and I have tackled this issue with VBA in different ways in the past. Here is Ross’s code to unlock the VBAIDE Immediate window to float over the grid. [...]