ToDo utility in Excel VBA?

A

Amit Shanker

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
 
D

Dave O

How about
MsgBox("Don't forget to do this thing!")

I frequently use MsgBox as a debugging tool.
 
S

scattered

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
 
A

Amit Shanker

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
 
S

scattered

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)
 
S

scattered

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
 
A

Amit Shanker

John,

Much appreciated - thanks!

Amit


scattered said:
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
Click to expand...
 
A

Amit Shanker

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) :

which I changed to

and strangely, all is well with the world. Wondering what that was about?
FYI, I'm on WinXP Pro & Office 2003.

Thanks again,
Amit
 
S

scattered

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

General question about 2003 and 2007 VBA in Excel 2
VBA Causing Excel To Crash 2
VBA (excel) quits on workbooks.open 7
VBA in Excel guide 2
How to get the available languages (MUI) of Excel using VBA? 0
Help in Excel VBA for simple task 2
How to convert VBA code into EXE file which can be executed by Excel workbook? 2
macro in excel to copy from excel to word 1

Top