2

Question

I would like to know whether it is possible to copy or extract the contents of the Immediate window in Excel VBA, so I can store it somewhere. I already know you can copy and paste from the window manually, I am interested in whether it is possible to do it with code (preferably in VBA itself).

Explanation

Questions similar to this have been asked a few times on SO (e.g. this and this), no-one has given a definitive answer and I was unable to find one from a quick search. Most of the time, answers respond asking why anyone would want to do that, and give ways to get around it in the circumstances provided in the question (e.g. how to output messages to a file or cell range). Given this, I have thought of a couple of example scenarios where someone might want to do this and cannot (easily) get around it.

Example scenarios

A) I am developing a large workbook including a large set of macros, debugging as I go. This includes using debug.print statements and directly querying in the Immediate window, e.g. ? myVar. At the end of the session, I would like to be able to automatically copy the contents of the immediate window into a log file, so I know what happened during my debug session afterwards. This could, for example, make use of the Workbook_BeforeClose event procedure.

B) I am working with two workbooks that contain VBA projects - one I can't edit, another that I can and am working on. The one I can't edit outputs debug information to the immediate window via debug.print that I would like to store somewhere (but I don't really care where).

Disclaimer

I ask this question purely out of curiosity. I suspect I already know the answer (it's not possible to do this), but I am not sure.

Community
  • 1
  • 1
SteveES
  • 544
  • 5
  • 10
  • 4
    *I would like to be able to automatically copy the contents of the immediate window into a log file, so I know what happened during my debug session afterwards.* I don't understand how this is preferable to simply writing out to the log file directly. – David Zemens Mar 24 '17 at 15:50
  • What I would do is spin up my own custom logging function, one that does both: 1) issues a `Debug.Print` statement (if you really need it) and 2) writes the same statements to a .txt log file. Then, instead of calling `Debug.Print "some statement"` you would do something like `Call MyDebugFunction("some statement")`. – David Zemens Mar 24 '17 at 15:52
  • 4
    Think of the Immediate Window as a console - that's basically what it is. Its window doesn't contain any controls to capture output *from* - it's pretty much drawing itself on top of the client area. AFAIK, the only way to "capture" output to the Immediate Window is to use APIs to simulate user input to select, copy, and paste. – Comintern Mar 24 '17 at 15:52
  • @DavidZemens - 1) I don't actually want to do either of my examples; I was trying to think of a context in which you _might_ want to copy the contents from the Immediate window :). 2) Setting up a debug function won't copy my manual queries typed into the Immediate window itself (but yes, this is definitely a better way to capture your own debug messages). – SteveES Mar 24 '17 at 16:03
  • 5
    If you did manage to get a copy/paste method working, you'd still have to watch out for filling up the window anyway, as it only holds so much text and then starts losing the older lines. I'd definitely go for a logfile approach instead. – CLR Mar 24 '17 at 16:04
  • @CLR raises a very valid concern, it will truncate large lists of data, you'll lose all but the most recent lines. – David Zemens Mar 24 '17 at 16:05
  • @CLR good point that I hadn't thought of, clearly I should have thought harder about my made up examples. – SteveES Mar 24 '17 at 16:15
  • I use `debug.print` a lot.. but then I came from the very early days of basic. I still long for a `debug.cls` command!! – CLR Mar 24 '17 at 16:18
  • @Mat'sMug My theoretical "can't edit" could be "not allowed to edit" or something similar - e.g. a third party workbook my clients use that I'm not allowed to change. My interest is really academic - how would you do it if you really had/wanted to, or do you just live with being unable get it. – SteveES Mar 24 '17 at 16:19

2 Answers2

0

Yes-- but not with control-c .... select what you need and then drag and drop

Greyhound
  • 1
  • 1
0

1-Create a sheet named "debug.print"

2-Hide it:

Sheets("debug.print").Visible = 2 'xlSheetVeryHidden, only can be visible with vba

3-Create this function:

Function debug_print(c As String)
    ThisWorkbook.Sheets("debug.print").[a1048576].End(xlUp).Offset(1).Value = c
    Debug.Print c
End Function

4-Replace your codes "debug.print" to "debug_print"

5-Example:

Sub blablabla()
    debug_print "i am doing whatever"
End Sub

But: Using "?" in the immediate window will not save

There is many ways to export this as file now like CSV, TXT, save in SQL etc...

Erick de Vathaire
  • 166
  • 2
  • 5