Can I print variables in VBA directly, without storing the values in cells
and printing the cells ?

Thanks for your help
--
jake

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

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

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

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

Sorry it looks like I gave the wrong url:
http://www.microsoft.com/resources/d....mspx?mfr=true

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

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