Hi,
Does anybody know of an Excel utility that would scan all code in a VBA
project, and come up with a list of ToDo items? I know such utilities are
available for many programming languages/tools.
The rationale behind this request is to, for example, insert ToDo statements
in a project while coding, so that I do not forget to complete some task
later on.
Thanks,
Amit
How about
MsgBox("Don't forget to do this thing!")
I frequently use MsgBox as a debugging tool.
Hi
A quick solution is to declare a public string variable "ToDo" in a
standard module and then put an assignment of the form ToDo = "do this"
wherever you want a ToDo reminder. Assumming that you have Option
Explicit turned on everwhere - simply commenting out the declaration
and compiling the project will cause the compiler to flag those
locations. The problem is that the VBA compiler doesn't give a list of
errors but just directs you to the first one. If you want to generate a
list then you would might need to get into scripting the VBE and
searching the code (presumably for comments of a certain form), which
is nontrivial. The Wrox book on Excel VBA by Green et al has a chapter
on that.
Hope this helps
Thanks both for your suggestions. Both are workable, and I can go down
either route. Was being lazy though, and hoping that someone had written it
somewhere :-) Maybe I'll write this utility on my own.
Amit
"scattered" <jcoleman@franciscan.edu> wrote in message
news:1108144295.468077.62280@l41g2000cwc.googlegroups.com...
> Hi
> A quick solution is to declare a public string variable "ToDo" in a
> standard module and then put an assignment of the form ToDo = "do this"
> wherever you want a ToDo reminder. Assumming that you have Option
> Explicit turned on everwhere - simply commenting out the declaration
> and compiling the project will cause the compiler to flag those
> locations. The problem is that the VBA compiler doesn't give a list of
> errors but just directs you to the first one. If you want to generate a
> list then you would might need to get into scripting the VBE and
> searching the code (presumably for comments of a certain form), which
> is nontrivial. The Wrox book on Excel VBA by Green et al has a chapter
> on that.
>
> Hope this helps
>
Amit,
I came up with the following code. To use it you could first enter
the code in a standard module called ToDo, export the module and then
import the module into any project you want.
You need to include a reference to the Microsoft Visual Basic for
Applications Extensibility library in any project that uses it. I am
using Excel 2000 in Windows XP. If your configuration is different
there is a potential for incompatibilities. The comment in the code
explains how to use it. It picks out comment blocks whose first line
begins 'ToDo or 'To do. Subsequent lines don't need to begin with a
Todo. Note that anything like
av = sum/num 'to do: handle case div by zero
would have to be rewritten as
av = sum/num
'to do: handle case div by zero
or it would be missed. At the cost of a little more parsing you could
remove that restriction if you want to.
Here is a sample output:
--------------------------------------------------
ToDo list for VBAProject(TSP.xls)
--------------------------------------------------
1) ThisWorkbook, Line 3:
to do: debug this stupid thing
2) Sheet1, Line 334:
To do: get a better sort routine
This one isn't much better than bubble sort
3) Sheet1, Line 644:
todo: find exact solution
4) Module1, Line 2:
todo: nothing
5) ToDo, Line 71:
Todo: decide if you want to strip leading
spaces from a ToDo block line or leave the
indenting. The code as written strips spaces
6) ToDo, Line 85:
to do: it would be nice to be able to assign priorities
to the to dos and then sort them
The code:
_____________________________________________________
Option Explicit
'This module is designed to implement a simple VBE ToDo list
'
'The ToDos are represented by comment blocks (contiguous blocks of
comment
'lines) in which the first line begins with "To do" or "ToDo".
'Note that the ToDo block must start with a complete comment line.
'The output of the program is printed to the Immediate Window,
'but it would be easy to modify to print to a text file as well.
'To use this type "ToDoList" in the immediate window from anywhere
'in the project. Type "ToDoList False" to localize the list to
'component whose code window you are currently viewing
'
'Make Sure to include a reference to the Microsoft VBA Extensibility
'Library in your project in tools->references.
Private toDoCount As Long
Sub ToDoList(Optional ListAll As Boolean = True)
Dim myVBE As VBIDE.VBE
Set myVBE = Application.VBE
Dim myProj As VBIDE.VBProject
Set myProj = myVBE.ActiveVBProject
Dim cmp As VBIDE.VBComponent
Dim myName As String
Dim A As Variant
A = Split(myProj.Filename, "\")
myName = A(UBound(A))
toDoCount = 0
Debug.Print String(50, "-")
Debug.Print "ToDo list for " & myProj.Name _
& "(" & myName & ")"
Debug.Print String(50, "-")
If ListAll Then
For Each cmp In myProj.VBComponents
Check cmp
Next cmp
Else
Set cmp = myVBE.ActiveCodePane.CodeModule.Parent
Check cmp
End If
If toDoCount = 0 Then Debug.Print "No items to display"
End Sub
Sub Check(cmp As VBIDE.VBComponent)
Dim i As Long, n As Long
Dim codeLine As String
Dim ToDo As String
Dim myCode As VBIDE.CodeModule
Set myCode = cmp.CodeModule
n = myCode.CountOfLines
i = 1
Do While i <= n
codeLine = LTrim(myCode.Lines(i, 1))
If Not codeLine Like "'*" Then 'not a candidate
i = i + 1
Else
If UCase(LTrim(Mid(codeLine, 2))) Like "TO DO*" Or _
UCase(LTrim(Mid(codeLine, 2))) Like "TODO*" Then
'In a ToDo block!
toDoCount = toDoCount + 1
Debug.Print toDoCount & ") " & cmp.Name _
& ", Line " & i & ":"
Do While i <= n And LTrim(codeLine) Like "'*"
Debug.Print Mid(LTrim(codeLine), 2)
'Todo: decide if you want to strip leading
'spaces from a ToDo block line or leave the
'indenting. The code as written strips spaces
i = i + 1
If i <= n Then codeLine = myCode.Lines(i, 1)
Loop
Debug.Print " " 'this triggers a blank line
Else
i = i + 1
End If
End If
Loop
End Sub
'to do: it would be nice to be able to assign priorities
'to the to dos and then sort them
______________________________________________________________
Hope this helps
- John (aka scattered - you should see my desk)
Amit,
Here is a final version of the code I posted before. I have removed the
arbitrary restriction that ToDo blocks must begin with a whole-line
comment. The listing now provides the names and types of the procedures
in which a ToDo block is located. Finally, I have given a version
"ToDoReport" which sends output to a text file instead of the Immediate
window. The only drawback is that you need to add references to both
the VBA Extensibility and the Scripting Runtime libraries.
Typical output now looks like:
--------------------------------------------------
ToDo List for VBAProject(StackExample.xls)
--------------------------------------------------
1) Sheet1, Line 11, Procedure btnCreate_Click:
to do: create a peek function
2) Sheet2, Line 65, Procedure btnParse_Click:
to do: modify sub to parse infix as well as postfix expressions
3) Stack, Line 3, Get IsEmpty:
to do: add a count property
4) frmMain, Line 4, Procedure btnOk_Click:
to do: check to see that all options selected
The code:
___________________________________________________________________
Option Explicit
'This module is designed to implement a simple VBE ToDo list
'
'The ToDos are represented by comments or comment blocks
'which begin with "To do" or "ToDo". The output of the program
'is either printed to the Immediate Window or dumped to a text file
'To use this type "ToDoList" in the Immediate Window from anywhere
'in the project. Type "ToDoList False" or "ToDoList ListAll := False"
'to localize the list to the component whose code window you are
'currently viewing. To print to a file type "ToDoReport" (with an
'optional false as before).
'
'Make Sure to include references to the Microsoft VBA Extensibility
'Library and the Microsoft Scripting Runtime Library
'in your project via tools->references.
Private toDoCount As Long
Private toDoString As String
Sub ToDoList(Optional ListAll As Boolean = True)
GetToDoList ListAll
Debug.Print toDoString
End Sub
Sub ToDoReport(Optional ListAll As Boolean = True)
Dim fso As New FileSystemObject
Dim ts As TextStream
Dim defaultName As String
Dim fileName As Variant
defaultName = ActiveWorkbook.Name & "ToDo.txt"
fileName = Application.GetSaveAsFilename(defaultName, _
"Text files (*.txt),*.txt", , "To Do Report")
If fileName Then
GetToDoList (ListAll)
Set ts = fso.CreateTextFile(fileName)
ts.Write toDoString
ts.Close
End If
End Sub
Private Sub GetToDoList(ListAll As Boolean)
Dim myVBE As VBIDE.VBE
Set myVBE = Application.VBE
Dim myProj As VBIDE.VBProject
Set myProj = myVBE.ActiveVBProject
Dim cmp As VBIDE.VBComponent
Dim myName As String, title As String
Dim A As Variant
A = Split(myProj.fileName, "\")
myName = A(UBound(A))
title = "ToDo List for " & myProj.Name _
& "(" & myName & ")"
toDoString = String(50, "-") & vbCrLf
toDoCount = 0
If ListAll Then
toDoString = toDoString & title & vbCrLf _
& String(50, "-") & vbCrLf
For Each cmp In myProj.VBComponents
Check4ToDos cmp
Next cmp
Else
Set cmp = myVBE.ActiveCodePane.CodeModule.Parent
toDoString = toDoString & title & ", " _
& cmp.Name & vbCrLf & String(50, "-") & vbCrLf
Check4ToDos cmp
End If
If toDoCount = 0 Then
toDoString = toDoString & "No items to display"
End If
End Sub
Private Sub Check4ToDos(cmp As VBIDE.VBComponent)
Dim i As Long, n As Long
Dim codeLine As String
Dim ToDo As String
Dim myCode As VBIDE.CodeModule
Set myCode = cmp.CodeModule
Dim procKind As vbext_ProcKind
Dim procName As String
Dim A As Variant
n = myCode.CountOfLines
i = 1
Do While i <= n
codeLine = myCode.Lines(i, 1)
If Not codeLine Like "*'*" Then 'not a candidate
i = i + 1
Else
A = Split(codeLine, "'")
codeLine = A(UBound(A))
codeLine = "'" & LTrim(codeLine)
If UCase(LTrim(Mid(codeLine, 2))) Like "TO DO*" Or _
UCase(LTrim(Mid(codeLine, 2))) Like "TODO*" Then
'In a ToDo block!
toDoCount = toDoCount + 1
procName = myCode.ProcOfLine(i, procKind)
If Len(procName) > 0 Then
procName = ", " & KindString(procKind) & procName
End If
toDoString = toDoString & toDoCount _
& ") " & cmp.Name & ", Line " _
& i & procName & ":" & vbCrLf
Do While i <= n And LTrim(codeLine) Like "'*"
toDoString = toDoString _
& Mid(LTrim(codeLine), 2) & vbCrLf
i = i + 1
If i <= n Then codeLine = myCode.Lines(i, 1)
Loop
toDoString = toDoString & vbCrLf
Else
i = i + 1
End If
End If
Loop
End Sub
Private Function KindString(procKind As vbext_ProcKind) As String
Select Case procKind
Case vbext_pk_Get
KindString = "Get "
Case vbext_pk_Let
KindString = "Let "
Case vbext_pk_Set
KindString = "Set "
Case vbext_pk_Proc
KindString = "Procedure "
End Select
End Function
_______________________________________________________
Hope this helps
-John Coleman
John,
Much appreciated - thanks!
Amit
"scattered" <jcoleman@franciscan.edu> wrote in message
news:1108395460.334970.279690@g14g2000cwa.googlegroups.com...
> Amit,
> Here is a final version of the code I posted before. I have removed the
> arbitrary restriction that ToDo blocks must begin with a whole-line
> comment. The listing now provides the names and types of the procedures
> in which a ToDo block is located. Finally, I have given a version
> "ToDoReport" which sends output to a text file instead of the Immediate
> window. The only drawback is that you need to add references to both
> the VBA Extensibility and the Scripting Runtime libraries.
>
> Typical output now looks like:
>
> --------------------------------------------------
> ToDo List for VBAProject(StackExample.xls)
> --------------------------------------------------
> 1) Sheet1, Line 11, Procedure btnCreate_Click:
> to do: create a peek function
>
> 2) Sheet2, Line 65, Procedure btnParse_Click:
> to do: modify sub to parse infix as well as postfix expressions
>
> 3) Stack, Line 3, Get IsEmpty:
> to do: add a count property
>
> 4) frmMain, Line 4, Procedure btnOk_Click:
> to do: check to see that all options selected
>
> The code:
>
> ___________________________________________________________________
>
> Option Explicit
>
> 'This module is designed to implement a simple VBE ToDo list
> '
> 'The ToDos are represented by comments or comment blocks
> 'which begin with "To do" or "ToDo". The output of the program
> 'is either printed to the Immediate Window or dumped to a text file
> 'To use this type "ToDoList" in the Immediate Window from anywhere
> 'in the project. Type "ToDoList False" or "ToDoList ListAll := False"
> 'to localize the list to the component whose code window you are
> 'currently viewing. To print to a file type "ToDoReport" (with an
> 'optional false as before).
> '
> 'Make Sure to include references to the Microsoft VBA Extensibility
> 'Library and the Microsoft Scripting Runtime Library
> 'in your project via tools->references.
>
> Private toDoCount As Long
> Private toDoString As String
>
> Sub ToDoList(Optional ListAll As Boolean = True)
> GetToDoList ListAll
> Debug.Print toDoString
> End Sub
>
> Sub ToDoReport(Optional ListAll As Boolean = True)
> Dim fso As New FileSystemObject
> Dim ts As TextStream
> Dim defaultName As String
> Dim fileName As Variant
>
> defaultName = ActiveWorkbook.Name & "ToDo.txt"
> fileName = Application.GetSaveAsFilename(defaultName, _
> "Text files (*.txt),*.txt", , "To Do Report")
>
> If fileName Then
> GetToDoList (ListAll)
> Set ts = fso.CreateTextFile(fileName)
> ts.Write toDoString
> ts.Close
> End If
> End Sub
>
> Private Sub GetToDoList(ListAll As Boolean)
> Dim myVBE As VBIDE.VBE
> Set myVBE = Application.VBE
> Dim myProj As VBIDE.VBProject
> Set myProj = myVBE.ActiveVBProject
> Dim cmp As VBIDE.VBComponent
> Dim myName As String, title As String
> Dim A As Variant
>
> A = Split(myProj.fileName, "\")
> myName = A(UBound(A))
> title = "ToDo List for " & myProj.Name _
> & "(" & myName & ")"
> toDoString = String(50, "-") & vbCrLf
> toDoCount = 0
>
> If ListAll Then
> toDoString = toDoString & title & vbCrLf _
> & String(50, "-") & vbCrLf
> For Each cmp In myProj.VBComponents
> Check4ToDos cmp
> Next cmp
> Else
> Set cmp = myVBE.ActiveCodePane.CodeModule.Parent
> toDoString = toDoString & title & ", " _
> & cmp.Name & vbCrLf & String(50, "-") & vbCrLf
> Check4ToDos cmp
> End If
>
> If toDoCount = 0 Then
> toDoString = toDoString & "No items to display"
> End If
>
> End Sub
>
> Private Sub Check4ToDos(cmp As VBIDE.VBComponent)
> Dim i As Long, n As Long
> Dim codeLine As String
> Dim ToDo As String
> Dim myCode As VBIDE.CodeModule
> Set myCode = cmp.CodeModule
> Dim procKind As vbext_ProcKind
> Dim procName As String
> Dim A As Variant
> n = myCode.CountOfLines
> i = 1
> Do While i <= n
> codeLine = myCode.Lines(i, 1)
> If Not codeLine Like "*'*" Then 'not a candidate
> i = i + 1
> Else
> A = Split(codeLine, "'")
> codeLine = A(UBound(A))
> codeLine = "'" & LTrim(codeLine)
> If UCase(LTrim(Mid(codeLine, 2))) Like "TO DO*" Or _
> UCase(LTrim(Mid(codeLine, 2))) Like "TODO*" Then
> 'In a ToDo block!
> toDoCount = toDoCount + 1
> procName = myCode.ProcOfLine(i, procKind)
> If Len(procName) > 0 Then
> procName = ", " & KindString(procKind) & procName
> End If
> toDoString = toDoString & toDoCount _
> & ") " & cmp.Name & ", Line " _
> & i & procName & ":" & vbCrLf
> Do While i <= n And LTrim(codeLine) Like "'*"
> toDoString = toDoString _
> & Mid(LTrim(codeLine), 2) & vbCrLf
> i = i + 1
> If i <= n Then codeLine = myCode.Lines(i, 1)
> Loop
> toDoString = toDoString & vbCrLf
> Else
> i = i + 1
> End If
> End If
> Loop
> End Sub
>
> Private Function KindString(procKind As vbext_ProcKind) As String
> Select Case procKind
> Case vbext_pk_Get
> KindString = "Get "
> Case vbext_pk_Let
> KindString = "Let "
> Case vbext_pk_Set
> KindString = "Set "
> Case vbext_pk_Proc
> KindString = "Procedure "
> End Select
> End Function
>
> _______________________________________________________
>
> Hope this helps
>
> -John Coleman
>
John, as a follow-up, wanted to let you know that it all works very well. I
have tweaked the code enough so that it is now part of a custom add-in I
have written, and works just as I intended.
There was one line of code that you provided which was throwing up an error
every time (Type mismatch error) :
>> If fileName Then
which I changed to
>> If fileName <> False Then
and strangely, all is well with the world. Wondering what that was about?
FYI, I'm on WinXP Pro & Office 2003.
Thanks again,
Amit
Amit,
I'm glad that you found it useful. Learning about scripting the VBE
has been on my to do list for a while and I had some time on my hands,
so I thought I would use your post as an excuse to learn something
useful. As far as the type mismatch, I guess 2003 doesn't do the same
implicit type cast that Excel 2000 apparantly does in that situation,
so I should probably avoid that C-style idiom in the future.
Take care
-John
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks