Stupid VBA Tricks

VBA stands for Visual Basic for Applications. This scripting language is available to all users except those of you who use Excel 2008 for the Mac. It's available for Excel 2004 and 2011 for the Mac, and any version of Excel for the PC since about Windows 95. It's a scripting language to enhance Excel's functionality, as well as do stupid tricks, like these.

Comments with a Date Stamp

A little VBA goes a long way sometimes. In this code snippet, I'll give the user the chance to enter a comment using an InputBox, and then insert the current date, plus color the comment to boot:
Sub colorComment()
Dim strCommentText As String
Dim strCurrentDate As String
Dim varCell As Variant
strCurrentDate = Format(Now(), "mm-dd-yy")
varCell = ActiveCell.Address
strCommentText = InputBox("Enter comment text")

If Len(Range(varCell).Comment.Text) = 0 Then
End If

Range(varCell).Comment.Text Text:=Application.UserName & vbLf & strCommentText
ActiveCell.Comment.Visible = True
Range(varCell).Comment.Shape.Select True
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 42
ActiveCell.Comment.Visible = False
End Sub

Creating a Table of Contents

When I have large spreadsheets, I find it's useful to create a table of contents.

I used to do it manually, and then I realized how easy it was to make a macro to do it.

In summary, figure out how many worksheets there are in the book, then add a hyperlink to each respective sheet:

*UPDATE 07-06-2015*
I had a bug in this code, which caused it to fail. The issue was that I didn't have a single quote before and after the sheet name - if you have a sheet called something like Stored Grades S2 2015, then Excel needs the single quotes to manage the spaces in the name of the sheet.

Sub TOC()
'create a local variable for the worksheet, so we can refer to it throughout the subroutine
Dim ws As Worksheet
'go to the first worksheet, so we can put the "TOC" worksheet before it
Worksheets.Add before:=Sheets(1)
'Why "TOC?" Table of Contents, of course! :-)
ActiveSheet.Name = "TOC"
'Create a header, make the text bold, add the text "Sheet Name"
With ActiveCell
.Value = "Sheet Name"
.Font.Bold = True
End With
'go through each worksheet
'write the name of the worksheet, then create a hyperlink to that worksheet
'this is where "ws" comes in handy; "ws" is the way we can tell Excel, "next sheet in the book"
ActiveCell.Offset(1, 0).Activate
For Each ws In ActiveWorkbook.Sheets
ActiveCell.Value = ws.Name
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'"& ws.Name &"'" & "!A1", TextToDisplay:=ws.Name
ActiveCell.Offset(1, 0).Activate
'get rid of row 2?! because it has a hyperlink to the "TOC" worksheet. That doesn't help much, does it?
End Sub

Dear Suzi: Challenge Accepted

Sub countWS()
MsgBox (ActiveWorkbook.Sheets.Count)
End Sub