Solved: Display text in word as VBA

[AdrianK]

Hello,

Wondering if anyone can help me.

I'm putting together some documentation in word for some excel macros that I have written.

In the appendix I am including the text for all the code.

Is there any way to display this code in word as it would appear in the VB Editor? (In essence doing what the VBA button does to code on here?)


sub Trim ()

' Define Variable
Dim cl As Variant

' Loop through cells removing excess spaces
' Show text with comments
For Each cl In Selection
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl

End sub


Such as the above, but in word, with commments, format, colours?

There might not be any way, but thought i'd ask.

Cheers,

Adrian

[fumei]

This has come up a number of times over the years. Many people have come up various macro logic to take the plain text of the code and try to duplicate what the VBE does.

It is not trivial, if you wish to fully duplicate it. If you want to just do a few things (like comments and procedure names) it is not too bad.

There are commercial products that do a fair job, for example PrettyCode.Print.

http://submain.com/products/prettycode.print.aspx#features

However, it is $90.

There was a very involved thread in this forum (did you search here for this subject?).

http://www.vbaexpress.com/forum/showthread.php?t=15451&highlight=format+code

Bottom line? There is no native way to do this.

[AdrianK]

Thanks for that,

That helps a lot, i'll have a look at that code and see what I can do from that.

Yes I did give it a couple of searches, unfortunately nothing relevant came up, and there were so many variations on how the thread could have been worded I must admit I gave up.

Thanks for the help.

Adrian

[AdrianK]

Thanks Fumei,

I didn't need anything too complicated, and all my commenting is on an individual line, so simply used the following, a mixture of the stuff mentioned, some extra terms and with a find & replace at the end to tidy up the .End s.


' Makes text in word appear with some of the code markup

Sub MakeCode()
Dim aWord, NextWord, var, aLine, MyKeywords()

Dim sText As String
Dim sLines() As String
Dim sWords() As String
Dim i As Long, j As Long, k As Long
Dim strPattern As String
Dim strMatches() As String

MyKeywords = Array("On", "Error", "Resume", "Next", _
"Function", "String", "Object", "GoTo", _
"If", "With", "End", "With", "End Sub", "Next", "Then", "Sub", _
"Const", "Dim", "Set", "Declare", _
"Object", "Is", "In", "Not", "Nothing", "As", "Exit", "Public", "Private", _
"Option", "Optional", "Explicit", "For", "Each", "True", "False")

' First reset all colors in selection range
Selection.Font.Color = wdColorBlack

' This will color keywords Blue
For Each aWord In Selection.Range.Words
For var = 0 To UBound(MyKeywords)
' Check for As keyword to highlight next variable (next word)
If aWord = "As " Then
Set NextWord = aWord.Next
NextWord.Expand unit:=wdWord
NextWord.Font.Color = wdColorBlue
End If

If Trim(aWord) = MyKeywords(var) Then
aWord.Font.Color = wdColorBlue
End If
Next
Next

' Search through, replacing comments
Dim oPara As Paragraph
For Each oPara In Selection.Paragraphs
Select Case Asc(oPara.Range.Text)
Case 32
' a space, or 12 spaces (ie.Tabs in the VBE)
' and if the SECOND word is a comma
If Asc(oPara.Range.Words(2)) = 39 Then
oPara.Range.Font.Color = _
wdColorGreen
' a comment
End If
Case 39
' a comma
oPara.Range.Font.Color = _
wdColorGreen
End Select
Next

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ".End"
.Replacement.Text = ".End"
.Forward = True
.Format = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

End Sub


Many thanks for pointing me in the right direction.

Cheers,

:beerchug:
Adrian