Print VBA code

Herve

Board Regular
Joined
May 6, 2003
Messages
68
Hello All!

Is there a way to increase the left margin when printing VBA code? (I mean without offsetting each line in the actual VBA code).

Thanks for any input.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
One method would be to highlight and Copy the VBA code, Paste it into a Word document, asign the left margin you want, and print the code from Word.
 
Upvote 0
You don't need to involve a second party program like Word; instead do a temporary export to an Excel text file and kill it afterwards. Modify for module name, font style, and InchesToPoints depending on desired width of indentation.

Thanks to Hans Herber for the base code.

Sub PrintModule()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
ThisWorkbook.VBProject.VBComponents("Module1").Export "test.txt"
Workbooks.OpenText "test.txt"
With ActiveSheet
.Cells.Font.Name = "Courier"
.PageSetup.LeftMargin = Application.InchesToPoints(1)
.Cells.PrintOut
End With
ActiveWorkbook.Close
Kill "test.txt"
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
 
Upvote 0
Thanks Ralph (simple and effective!) and Tom (see question below).

The problem with the Excel solution is that it prints long commands on 2 pages. I changed it as follows to solve that (I also print a footer with the module name and page #):

-------------------------
Sub Print_Module()
Dim Module As String
Module = InputBox("Enter module to print")
With Application
.ScreenUpdating = False
.DisplayAlerts = False
ThisWorkbook.VBProject.VBComponents(Module).Export Module & ".txt"
Workbooks.OpenText Module & ".txt"
Columns("A:A").ColumnWidth = 90
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.AddIndent = False
.ShrinkToFit = False
.Font.Name = "Courier"
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(1.5)
.LeftFooter = "&F"
.CenterFooter = "Page &P of &N"
End With
.Cells.PrintOut
ActiveWorkbook.Close
Kill Module & ".txt"
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
--------------------------------

I have been trying to print the whole project code (Objects, Forms, and Modules) at once using something like:

For each oModule in ThisWorkbook.VBProject.VBComponents
ThisWorkbook.VBProject.VBComponents(oModule).Export oModule & ".txt"
.....
Next oModule

but it does not work.
Does anyone knows how to do that?

Thanks!
 
Upvote 0
If you are interested, the code below lets you choose a module/object/userform among the list of available items in your project (generated automatically) or the complete project, and print it.
Long lines are wrapped around, footer indicates module name, page x of x, and date.
The left margin, font, and point size are easily editable in the code.

You will have to create a UserForm (Print_Module_MsgBox) with an ListBox (Module_List), an OptionButton (Complete_Project), and a CommandButton (code: Print_Module_MsgBox.Hide).

Hope it's useful to someone :)

-----------------
Option Explicit


Sub Print_Module_Select()

Dim i As Integer
Dim Module() As String
Dim ModuleToPrint As String
Dim Count As Integer
Dim User_Answer As String
Dim Msg As Integer
Dim Complete_Project As OptionButton
Dim One_Module As OptionButton
Dim Module_List As ListBox

On Error GoTo No_Selection

With Application
.ScreenUpdating = False
.DisplayAlerts = False

'Find the names/number of the various Objects/Forms/Modules in the Project
Count = ThisWorkbook.VBProject.VBComponents.Count
ReDim Module(1 To Count)
For i = 1 To Count
Module(i) = ThisWorkbook.VBProject.VBComponents(i).Name
Next i

'Load the list box with the various modules for selection
With Print_Module_MsgBox.Module_List
.Clear
For i = 1 To Count
.AddItem Module(i)
Next i
End With

'User to select which module(s) to print
Print_Module_MsgBox.Show vbModal

If Print_Module_MsgBox.Complete_Project Then 'Print complete project
Unload Print_Module_MsgBox
Msg = MsgBox("Are you sure you want to print the whole project?", vbCritical + vbYesNo, "It's a lot of paper!")
If Msg = 7 Then ' Ooops!
Exit Sub
End If
For i = 1 To Count
ModuleToPrint = Module(i)
Call Print_Module_Print(ModuleToPrint)
Next i
Else 'Print single module
ModuleToPrint = Print_Module_MsgBox.Module_List.Value
Unload Print_Module_MsgBox
Call Print_Module_Print(ModuleToPrint)
End If

.DisplayAlerts = True
.ScreenUpdating = True

End With
Exit Sub

No_Selection:

Msg = MsgBox("You must select a module or select 'Print Complete Project' option.", vbCritical + vbOKOnly, "No Selection!")
Unload Print_Module_MsgBox

End Sub

Sub Print_Module_Print(ModuleToPrint)

ThisWorkbook.VBProject.VBComponents(ModuleToPrint).Export ModuleToPrint & ".txt"
Workbooks.OpenText ModuleToPrint & ".txt", DataType:=xlDelimited
Columns("A:A").ColumnWidth = 90
Columns("A:A").Select
With Selection
.WrapText = True
.Font.Name = "Courier"
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(1.5)
.LeftFooter = "&F"
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
End With

With ActiveSheet.Cells.PrintOut
End With

ActiveWorkbook.Close
Kill ModuleToPrint & ".txt"

End Sub
 
Upvote 0
If you are interested, the code below lets you choose a module/object/userform among the list of available items in your project (generated automatically) or the complete project, and print it.
Long lines are wrapped around, footer indicates module name, page x of x, and date.
The left margin, font, and point size are easily editable in the code.

You will have to create a UserForm (Print_Module_MsgBox) with an ListBox (Module_List), an OptionButton (Complete_Project), and a CommandButton (code: Print_Module_MsgBox.Hide).

Hope it's useful to someone :)



SIXTEEN YEARS LATER I USED THIS CODE AND IT WORKED LIKE A CHAMP!
Herve if you are still alive, THANK YOU!
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top