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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,204
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
 

Herve

Board Regular
Joined
May 6, 2003
Messages
68
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!
 

Herve

Board Regular
Joined
May 6, 2003
Messages
68
I think I foud how to do it. Will post shortly for those who are interested.
 

Herve

Board Regular
Joined
May 6, 2003
Messages
68
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
 

iamtywilson

New Member
Joined
Feb 13, 2012
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,847
Messages
5,489,229
Members
407,682
Latest member
gmb2521

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top