Print VBA code
Results 1 to 7 of 7

Thread: Print VBA code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2003
    Location
    Houston, Texas
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Print VBA code

    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.
    Hervé

  2. #2
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print VBA code

    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.

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,191
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Print VBA code

    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

  4. #4
    Board Regular
    Join Date
    May 2003
    Location
    Houston, Texas
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print VBA code

    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!
    Hervé

  5. #5
    Board Regular
    Join Date
    May 2003
    Location
    Houston, Texas
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print VBA code

    I think I foud how to do it. Will post shortly for those who are interested.
    Hervé

  6. #6
    Board Regular
    Join Date
    May 2003
    Location
    Houston, Texas
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print VBA code

    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
    Hervé

  7. #7
    New Member
    Join Date
    Feb 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print VBA code

    [QUOTE=Herve;230650]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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •