Is there any way to print a message to the console from an Excel vba macro? I have a small VB .NET console application that calls an excel macro, and I'd like the error messages from the macro code to print to the console. Debug.print doesn't help as it only prints to the immediate window. Any ideas?
A:
Why don't you pass it a file path and write all the errors to the file.
ChaosPandion
2010-01-26 20:21:56
A:
You can try using Debug.WriteLine() to output your debugging information to the Visual Studio Output window, but I have the nagging feeling you can't do that unless you're writing a Console application.
If it indeed doesn't work, try using the Console.SetOut() method to redirect the Console.WriteLine() strings to a StreamWriter, which could be a file on your machine. Not the ideal, but possibly a workable solution?
Phil.Wheeler
2010-01-26 20:23:49
A:
You could create a COM component in your VB.NET app that you can call from VBA and write to your application's console.
burnside
2010-01-26 21:57:23
Is it worth it? I don't think so.
ChaosPandion
2010-01-26 22:00:31
Depends on the situation. We have a .net application that does some stuff then launches excel reports. The data for the reports is fetched via a COM interface from VBA in excel so that the user can customise the report. We currently don't but I can see time when we may have to display status updates from the report in the main app that launched the reports.
burnside
2010-01-26 22:48:54
A:
Why don't you return a string containing the error messages from the macro to the .Net code, and have the .Net code write it to the console?
Alternatively, if these are really errors, raise errors from the macro using Err.Raise and handle them in the .Net code with a Try Catch block that writes the details to the console.
MarkJ
2010-01-26 23:28:59
A:
I found that there's no communication between a VB .NET project and a macro that it calls - the macro can't access the VB's console, and it can't throw back an exception. If an error occurs in the macro, it's not detected by the VB .NET code. The VB project just calls the macro, it executes, and then the project continues running, with no knowledge of what happened in the macro. The way I got around this was that I had the macro write the error to a specific designated cell in the Excel spreadsheet, and then the VB project read that and used it to determine if there was an error, and then would write it to the console if there was. Sort of a cheat fix, but it worked. Thanks for all the suggestions!
froadie
2010-02-03 14:26:37