需求:VBA中可使用DEBUG.PRINT语句在立即窗口中输出一些调试信息,但并没有保存下来,容易丢失且不易查看。如果能将这些信息输出的文件,相当于生成了程序的日志文件,那作用可就大了。
代码:
Sub getComment()
Dim varComment As String
Dim c As Comment
Dim sht As Worksheet
Dim n As Integer
Dim objFSO As Object, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Users\[username]\Documents\test.txt", 2)
objFile.Write "" 'This clears the text file
With ThisWorkbook
For Each sht In .Worksheets
With sht.Range("A1")
On Error Resume Next
Set c = .Comment
If Not c Is Nothing Then
n = n + 1
varComment = n & ". " & sht.name & "——" & Mid(c.Text, 17) & vbCrLf
Debug.Print varComment
objFile.Write varComment
End If
End With
Next
End With
objFile.Close
End Sub
效果:
备注:这篇文章和上一篇基本相同,为什么写两篇?这样可以方便需要的人通过搜索引擎检索到相应的信息。