|
Print Variables ?
Can I print variables in VBA directly, without storing the values in cells
and printing the cells ? Thanks for your help -- jake |
Print Variables ?
Hi
you will need some kind of text editor environment to print in presumably? Notepad maybe...or do you mean something else when you say "print"? regards Paul Jakobshavn Isbrae wrote: Can I print variables in VBA directly, without storing the values in cells and printing the cells ? Thanks for your help -- jake |
Print Variables ?
More detail would be helpful on answering your question but here are
some options that might help: 1) To temporarily display the values of variables at any point in the code you can use MsgBox. MsgBox "MyVariable =" & MyVariable 2) To send data to the Immediate window of the Visual Basic Editor use Debug.Print. If you want to save the data or print the data you can copy it from the Immediate window and paste it in a file (text, spreadsheet, etc.) Debug.Print "MyVariable =" & MyVariable 3) To send data directly to a text file: Open MyTextFile For Output As #1 ' MyTextFile needs to be defined with path and filename Print #1, "MyVariable =" & MyVariable Close #1 You could also send data directly to a temporary worksheet or workbook, print or save the data, then close or delete the data. If you're interested in this approach reply with more detail and I'll try to help. Dave Parker wrote: Hi you will need some kind of text editor environment to print in presumably? Notepad maybe...or do you mean something else when you say "print"? regards Paul Jakobshavn Isbrae wrote: Can I print variables in VBA directly, without storing the values in cells and printing the cells ? Thanks for your help -- jake |
Print Variables ?
Thank you Paul & Dave
What I am trying to accomplish is to get VBA outputs printed (on paper). Here is a simple example Sub Macro1() value1 = 10 Cells(1, 1).Value = value1 Application.DisplayAlerts = False ActiveSheet.PageSetup.PrintArea = "$A$1" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub 1) because I am using a cell, the code needs to know what cell is available and not being used by the worksheet itself 2) because I am using a cell, the code needs to set the PrintArea on the worksheet, thus destroying the PrintArea already established for the worksheet For these reasons I would rather not use the worksheet for printing, but print directly from VBA, if this is possible. -- jake " wrote: More detail would be helpful on answering your question but here are some options that might help: 1) To temporarily display the values of variables at any point in the code you can use MsgBox. MsgBox "MyVariable =" & MyVariable 2) To send data to the Immediate window of the Visual Basic Editor use Debug.Print. If you want to save the data or print the data you can copy it from the Immediate window and paste it in a file (text, spreadsheet, etc.) Debug.Print "MyVariable =" & MyVariable 3) To send data directly to a text file: Open MyTextFile For Output As #1 ' MyTextFile needs to be defined with path and filename Print #1, "MyVariable =" & MyVariable Close #1 You could also send data directly to a temporary worksheet or workbook, print or save the data, then close or delete the data. If you're interested in this approach reply with more detail and I'll try to help. Dave Parker wrote: Hi you will need some kind of text editor environment to print in presumably? Notepad maybe...or do you mean something else when you say "print"? regards Paul Jakobshavn Isbrae wrote: Can I print variables in VBA directly, without storing the values in cells and printing the cells ? Thanks for your help -- jake |
Print Variables ?
I'd be inclined to write the information to a text file, then use a
shell command to print the data. This gives you some flexibility if your needs change: 1) You can append new data to the end of the text file. 2) You can reprint the file if you lose the orginal hardcopy or the printer dies in the middle of the job. 3) You can email the file to others. 4) You can import the file into Excel to manipulate, sort, or use the data. Here's an example: MyPath = "C:" MyTextFile = "TextFile2.txt" ChDir (MyPath) Open MyPath & "\" & MyTextFile For Output As #1 Print #1, "MyVariable =" & MyVariable Close #1 Shell ("print /d:\\Server1\Ricoh7000 " & MyTextFile) Note the space at the end of the printer name. Also I used a networked printer. Refer to the following website for info on how to define the printer name: (sorry if the long url gets mangled by Google groups): http://www.microsoft.com/resources/d....mspx?mfr=true The downside to this is that the print out may not be formatted as nicely printing from Excel. Dave Parker Jakobshavn Isbrae wrote: Thank you Paul & Dave What I am trying to accomplish is to get VBA outputs printed (on paper). Here is a simple example Sub Macro1() value1 = 10 Cells(1, 1).Value = value1 Application.DisplayAlerts = False ActiveSheet.PageSetup.PrintArea = "$A$1" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub 1) because I am using a cell, the code needs to know what cell is available and not being used by the worksheet itself 2) because I am using a cell, the code needs to set the PrintArea on the worksheet, thus destroying the PrintArea already established for the worksheet For these reasons I would rather not use the worksheet for printing, but print directly from VBA, if this is possible. -- jake " wrote: More detail would be helpful on answering your question but here are some options that might help: 1) To temporarily display the values of variables at any point in the code you can use MsgBox. MsgBox "MyVariable =" & MyVariable 2) To send data to the Immediate window of the Visual Basic Editor use Debug.Print. If you want to save the data or print the data you can copy it from the Immediate window and paste it in a file (text, spreadsheet, etc.) Debug.Print "MyVariable =" & MyVariable 3) To send data directly to a text file: Open MyTextFile For Output As #1 ' MyTextFile needs to be defined with path and filename Print #1, "MyVariable =" & MyVariable Close #1 You could also send data directly to a temporary worksheet or workbook, print or save the data, then close or delete the data. If you're interested in this approach reply with more detail and I'll try to help. Dave Parker wrote: Hi you will need some kind of text editor environment to print in presumably? Notepad maybe...or do you mean something else when you say "print"? regards Paul Jakobshavn Isbrae wrote: Can I print variables in VBA directly, without storing the values in cells and printing the cells ? Thanks for your help -- jake |
Print Variables ?
Thank you Dave. Once I started working with your solution, I realized the
added benefit of have the results available to other applications as well. I appreciate your time & knowledge -- jake " wrote: I'd be inclined to write the information to a text file, then use a shell command to print the data. This gives you some flexibility if your needs change: 1) You can append new data to the end of the text file. 2) You can reprint the file if you lose the orginal hardcopy or the printer dies in the middle of the job. 3) You can email the file to others. 4) You can import the file into Excel to manipulate, sort, or use the data. Here's an example: MyPath = "C:" MyTextFile = "TextFile2.txt" ChDir (MyPath) Open MyPath & "\" & MyTextFile For Output As #1 Print #1, "MyVariable =" & MyVariable Close #1 Shell ("print /d:\\Server1\Ricoh7000 " & MyTextFile) Note the space at the end of the printer name. Also I used a networked printer. Refer to the following website for info on how to define the printer name: (sorry if the long url gets mangled by Google groups): http://www.microsoft.com/resources/d....mspx?mfr=true The downside to this is that the print out may not be formatted as nicely printing from Excel. Dave Parker Jakobshavn Isbrae wrote: Thank you Paul & Dave What I am trying to accomplish is to get VBA outputs printed (on paper). Here is a simple example Sub Macro1() value1 = 10 Cells(1, 1).Value = value1 Application.DisplayAlerts = False ActiveSheet.PageSetup.PrintArea = "$A$1" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub 1) because I am using a cell, the code needs to know what cell is available and not being used by the worksheet itself 2) because I am using a cell, the code needs to set the PrintArea on the worksheet, thus destroying the PrintArea already established for the worksheet For these reasons I would rather not use the worksheet for printing, but print directly from VBA, if this is possible. -- jake " wrote: More detail would be helpful on answering your question but here are some options that might help: 1) To temporarily display the values of variables at any point in the code you can use MsgBox. MsgBox "MyVariable =" & MyVariable 2) To send data to the Immediate window of the Visual Basic Editor use Debug.Print. If you want to save the data or print the data you can copy it from the Immediate window and paste it in a file (text, spreadsheet, etc.) Debug.Print "MyVariable =" & MyVariable 3) To send data directly to a text file: Open MyTextFile For Output As #1 ' MyTextFile needs to be defined with path and filename Print #1, "MyVariable =" & MyVariable Close #1 You could also send data directly to a temporary worksheet or workbook, print or save the data, then close or delete the data. If you're interested in this approach reply with more detail and I'll try to help. Dave Parker wrote: Hi you will need some kind of text editor environment to print in presumably? Notepad maybe...or do you mean something else when you say "print"? regards Paul Jakobshavn Isbrae wrote: Can I print variables in VBA directly, without storing the values in cells and printing the cells ? Thanks for your help -- jake |
| All times are GMT +1. The time now is 02:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com