Code Printing

[MWE]

Does anyone have a simple method to print out code modules and have the results look like compilations of old, i.e., each module starts on a new page, etc. I wrote a postprocessor for Word that does most of what I want, but there must be an easier way.

thanks

[johnske]

Not sure if this's what you're after (it's a mod from a very-much WIP)


Option Explicit
'
Sub GetProjectCodeForPrinting()
'
Dim N As Long
Dim Component As VBComponent
Dim OpenBook As Workbook
'
Set OpenBook = ActiveWorkbook
'
Application.ScreenUpdating = False
Workbooks.Add (xlWBATWorksheet)
'
GoSub FormatSheet
'
For Each Component In OpenBook.VBProject.VBComponents
With [A3]
.Value = " " & Component.Name & " Code Module"
With .Font
.Size = 9
.Bold = True
.Underline = True
.ColorIndex = 11
End With
End With
'
With Component.CodeModule
For N = 1 To .CountOfLines
If .Lines(N, 1) = Empty Then
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = "'"
Else
'put space before and after codeline (this is needed)
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = " " & .Lines(N, 1) & " "
End If
Next
End With
'Call FormatCodeAsPerVBIDE
'Call ConvertToBBcode
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
GoSub FormatSheet
Next
'
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete 'there's 1 too many sheets
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'
Exit Sub
'
FormatSheet:
'
With ActiveWindow
.DisplayHeadings = False
.DisplayGridlines = False
End With
'
With ActiveSheet
With .Cells.Font
.Name = "Verdana"
.Size = 8
End With
'
With .[A1]
.Value = " " & OpenBook.Name & " " & OpenBook.VBProject.Name
With .Font
.Size = 12
.Bold = True
.Underline = True
End With
End With
End With
Return
'
End Sub

[Marcster]

There's a product selling for $59 that will print your VBA code in colour, to PDF, HTML, rich text etc...

http://www.starprint2000.com/vbaprint_features.html

Marcster.

[johnske]

Why pay when you can do it for nothing?

[MWE]

Not sure if this's what you're after (it's a mod from a very-much WIP)


Option Explicit
'
Sub GetProjectCodeForPrinting()
'
Dim N As Long
Dim Component As VBComponent
Dim OpenBook As Workbook
'
Set OpenBook = ActiveWorkbook
'
Application.ScreenUpdating = False
Workbooks.Add (xlWBATWorksheet)
'
GoSub FormatSheet
'
For Each Component In OpenBook.VBProject.VBComponents
With [A3]
.Value = " " & Component.Name & " Code Module"
With .Font
.Size = 9
.Bold = True
.Underline = True
.ColorIndex = 11
End With
End With
'
With Component.CodeModule
For N = 1 To .CountOfLines
If .Lines(N, 1) = Empty Then
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = "'"
Else
'put space before and after codeline (this is needed)
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = " " & .Lines(N, 1) & " "
End If
Next
End With
'Call FormatCodeAsPerVBIDE
'Call ConvertToBBcode
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
GoSub FormatSheet
Next
'
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete 'there's 1 too many sheets
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'
Exit Sub
'
FormatSheet:
'
With ActiveWindow
.DisplayHeadings = False
.DisplayGridlines = False
End With
'
With ActiveSheet
With .Cells.Font
.Name = "Verdana"
.Size = 8
End With
'
With .[A1]
.Value = " " & OpenBook.Name & " " & OpenBook.VBProject.Name
With .Font
.Size = 12
.Bold = True
.Underline = True
End With
End With
End With
Return
'
End Sub

thanks. This is specific to Excel and I am looking for something more general. However, it contians some useful concepts and I will see what I can do with it.

[MWE]

There's a product selling for $59 that will print your VBA code in colour, to PDF, HTML, rich text etc...

http://www.starprint2000.com/vbaprint_features.html

Marcster.thanks for the lead. For now, I will poke at what is available for free as source code (and can thus be tweaked to do exactly what I want)

[johnske]

thanks. This is specific to Excel and I am looking for something more general. However, it contians some useful concepts and I will see what I can do with it.Maybe this can be of more use then...

Option Explicit

Private Sub AddReference()
'call this with a workbook open event
Dim Reference As Object

With ThisWorkbook.VBProject
For Each Reference In .References
If Reference.Description Like "Microsoft Forms 2.0 Object Library" Then Exit Sub
Next
.References.AddFromGuid "{0D452EE1-E08F-101A-852E-02608C4D0BB4}", 2, 0
End With

End Sub


Sub CopyCodeToClipboard()

Dim CodeText As String, CodeBody As New DataObject

With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
CodeText = .Lines(1, .countoflines)
With CodeBody
.SetText CodeText
.PutInClipboard
End With
MsgBox "Module1 code is in the clipboard - paste it to where-ever you want"
End With

Set CodeBody = Nothing

End Sub
If you want to format it in colour as per the VBIDE, let me know and I'll give you what I have so far :)