PDA

View Full Version : Copying and Storing VBA Code in DB



YellowLabPro
03-27-2007, 04:50 AM
I had an idea that will need help from the experts here. When I am searching for a particular macro, trying to remember where I created it or it is stored is becoming increasingly more difficult.
The thought of opening every module and copying it to a worksheet cell or maybe a word document is frightening :bug: and I am not sure the best way to store it. I have read that there is no global search to look through modules to find keywords that might assist in locating a particular module.

So I was thinking that if a program could be written to open modules and private worksheet subs and copy the code to some location for easier global reference, a library if you will, this would be super handy and easier on the brain. A few details would also be nifty to incorporate, the name of the book where this is stored, a time stamp, and a input box that would allow to put some comments associated w/ the particular macro, ie. what it was designed for, etc...

If this already exists, please forgive, I have searched the site and found nothing yet.

Feedback is always welcome,

YLP

mdmackillop
03-27-2007, 04:55 AM
Hi Yelp,
Did you see this KB item (http://vbaexpress.com/kb/getarticle.php?kb_id=403) by Johnske

YellowLabPro
03-27-2007, 05:01 AM
I sure didn't. I searched generally and did not turn up this. This is super, thanks.
Are there any suggestions on how to store one's macros, eg. It is advisable to store all the macros in Personal.xls, ( which I don't think is the case, but for example sake)?

Simon Lloyd
03-27-2007, 05:18 AM
Malcom, thats a great entry by Johnske, however, when you run the refresh/make list it has a "runtime error 1004 programmatic access to VBProject not trusted" any idea what the problem is?, the error stops at this line For Each Component In ThisWorkbook.VBProject.VBComponentsin this module "VBA Library_Code"

Regards,
Simon

mdmackillop
03-27-2007, 05:47 AM
Hi Simon,
You have to check Trust Access To Visual Basic Project checkbox in the security dialog.

mdmackillop
03-27-2007, 05:54 AM
Are there any suggestions on how to store one's macros, eg. It is advisable to store all the macros in Personal.xls, ( which I don't think is the case, but for example sake)?
I store most of mine in Personal.xls, but where I need a lot of "specialist" code for a certain type of workbook, I store it in an add-in which is loaded/unloaded along with that workbook type.
Wherever you store it, be sure to keep a spare copy in a safe place.

johnske
03-27-2007, 06:49 AM
I had an idea that will need help from the experts here. When I am searching for a particular macro, trying to remember where I created it or it is stored is becoming increasingly more difficult.
The thought of opening every module and copying it to a worksheet cell or maybe a word document is frightening :bug: and I am not sure the best way to store it. I have read that there is no global search to look through modules to find keywords that might assist in locating a particular module.

So I was thinking that if a program could be written to open modules and private worksheet subs and copy the code to some location for easier global reference, a library if you will, this would be super handy and easier on the brain. A few details would also be nifty to incorporate, the name of the book where this is stored, a time stamp, and a input box that would allow to put some comments associated w/ the particular macro, ie. what it was designed for, etc...

If this already exists, please forgive, I have searched the site and found nothing yet.

Feedback is always welcome,

YLPIf you want to print it out, here's something to play with (this is a WIP)...

To start with, for Office 2002 or higher you will need to check the "Trust Access to Visual Basic Project" box.

Then you'll need to run AddReference, after that, you can then run GetProjectCodeForPrinting - this code creates a new workbook with a sheet dedicated to the code in each code module in the active workbook (you can choose to manually save this {or not} afterwards) and the code is colour-formatted as per the VBIDE so it can be printed if you like...


Option Explicit
'
Sub AddReference()
Dim Reference As Object
With ThisWorkbook.VBProject
For Each Reference In .References
If Reference.Description Like _
"Microsoft Visual Basic for Applications Extensibility*" Then Exit Sub
Next
.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
End With
End Sub

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

Private Sub FormatCodeAsPerVBIDE()
Dim Cell As Range
Dim N As Long
For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp).Address)
With Cell
If LTrim(.Text) Like "'*" Then
'if the whole line's been commented out
'just colour it green & go to the next line
.Font.ColorIndex = 10
Else
'else it's not a comment line, so search through
'each line for the keywords and colour any
'keywords blue
GoSub HighlightKeywords
End If
End With
If Cell.Row > 5 Then ActiveWindow.SmallScroll Down:=1
Next Cell
'ask if a printout is wanted
' If MsgBox("Print Code?", vbYesNo, "Print Hard Copy") = vbYes Then
' With ActiveSheet
' .PageSetup.RightHeader = "Page &P"
' .PrintOut from:=1, To:=2
' End With
' End If
Application.Goto [A1], True
Exit Sub
HighlightKeywords:
With Cell
For N = 1 To Len(.Text)
'colour keywords blue
With .Characters(N, 4)
Select Case .Text
Case " As ", " Do ", " If ", " In ", " Is ", " On ", " Or ", " To "
.Font.ColorIndex = 5
End Select
End With
With .Characters(N, 5)
Select Case .Text
Case " And ", " Dim ", " End ", " For ", _
" Get ", " Let ", " New ", " Not ", " Set ", " Sub "
.Font.ColorIndex = 5
End Select
End With
With .Characters(N, 6)
Select Case .Text
Case " Byte ", " Call ", " Case ", _
" Date ", " Each ", " Else ", " Enum ", _
" Exit ", " GoTo ", " Like ", " Lock ", _
" Loop ", " Long ", " Next ", " Null ", _
" Seek ", " Step ", " Text ", " Then ", _
" True ", " Type ", " With "
.Font.ColorIndex = 5
Case " Byte,", " Byte)", " Date,", " Date)", " Long,", _
" Long)", " CDbl(", " CLng(", " CSng(", " CStr("
Cell.Characters(N, 5).Font.ColorIndex = 5
End Select
End With
With .Characters(N, 7)
Select Case .Text
Case " ByRef ", " ByVal ", " Const ", " Empty ", " Erase ", _
" Error ", " False ", " GoSub ", " Input ", " Print ", " While ", " Until "
.Font.ColorIndex = 5
Case " CBool("
Cell.Characters(N, 6).Font.ColorIndex = 5
Case "(ByRef ", "(ByVal "
Cell.Characters(N + 1, 6).Font.ColorIndex = 5
End Select
End With
With .Characters(N, 8)
Select Case .Text
Case " Binary ", " Double ", " ElseIf ", " Friend ", " Object ", _
" Option ", " Public ", " Random ", " Resume ", " Return ", _
" Single ", " Static ", " Select ", " GoTo 0 "
.Font.ColorIndex = 5
Case " Double,", " Double)", " Object,", " Object)", " Single,", _
" Single)", " String ", " String,", " String)"
Cell.Characters(N, 7).Font.ColorIndex = 5
End Select
End With
With .Characters(N, 9)
Select Case .Text
Case " Boolean ", " Compare ", " Decimal ", " Declare ", _
" Integer ", " Nothing ", " Private ", " Variant "
.Font.ColorIndex = 5
Case " Boolean,", " Boolean)", " Decimal,", " Decimal)", _
" Integer,", " Integer)", " Variant,", " Variant)"
Cell.Characters(N, 8).Font.ColorIndex = 5
End Select
End With
With .Characters(N, 10)
Select Case .Text
Case " Explicit ", " Function ", " Optional "
.Font.ColorIndex = 5
End Select
End With
With .Characters(N, 12)
Select Case .Text
Case " WithEvents "
.Font.ColorIndex = 5
End Select
End With
'now deal with comments on the same line as the code
If Not .Characters(N, 2).Text = "'[" And Not .Characters(N, 2).Text = "'!" Then
If .Characters(N, 1).Text = "'" Then
.Characters(N, Len(.Text)).Font.ColorIndex = 10
End If
End If
Next N
End With
Return
End Sub

YellowLabPro
03-27-2007, 06:55 AM
Thanks Johnske,
I will give it a go.
http://www.vbaexpress.com/forum/image.php?u=391&dateline=1143181359 (http://www.vbaexpress.com/forum/member.php?u=391) is this a kitty w/ a rifle? the detail is a little hard to see.

Simon Lloyd
03-27-2007, 06:55 AM
well that was the height of laziness for me...never even checked that!, thanks Malcom

johnske
03-27-2007, 07:37 AM
...http://www.vbaexpress.com/forum/image.php?u=391&dateline=1143181359 (http://www.vbaexpress.com/forum/member.php?u=391) is this a kitty w/ a rifle? the detail is a little hard to see.Don't worry, your doggies safe - it's only a toy rifle

YellowLabPro
03-27-2007, 07:44 AM
:rofl:
I had not considered that, rather, he had found a better mouse catcher, his version of VBA......:rotlaugh: