Home |
Search |
Today's Posts |
#1
|
|||
|
|||
ToDo utility in Excel VBA?
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 |
#2
|
|||
|
|||
How about
MsgBox("Don't forget to do this thing!") I frequently use MsgBox as a debugging tool. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message ups.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 |
#5
|
|||
|
|||
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) |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
John,
Much appreciated - thanks! Amit "scattered" wrote in message oups.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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I isolate my Excel server (automation) from other Excel instances? | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Use Excel for todo lists? | New Users to Excel |