Working With Comments in Excel

March 3rd, 2008

Here's a few lines of code that demonstrates how to hide, show, or delete all comments in the activesheet. You can easily tweak the code to make it work on sheets other than the activesheet.

Sub HideAllComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Visible = False
    Next c
End Sub
Sub ShowAllComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Visible = True
    Next c
End Sub
Sub DeleteAllComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Delete
    Next c
End Sub

Or, maybe if you want comments written by yourself to stand out, change the comment shape from a rectangle to any available shape.

Sub HighlightMyComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Visible = True
        If c.Author = Application.UserName Then
            c.Shape.AutoShapeType = msoShape16pointStar
        End If
    Next c
End Sub

With the above code, comments that you authored will show like this:

16 point star comment

Posted in Excel, How To, VBA | 2 Comments




How to get the column letter of the rightmost used column

March 1st, 2008
Public Function GetLastColumnLetter() As String
    Dim strColumnLetter As String
    Dim ipos As Integer
   
    With ActiveSheet.Range("IV1").End(xlToLeft).EntireColumn
        GetLastColumnLetter = .Address(False, False)
    End With
    ipos = InStr(1, GetLastColumnLetter, ":")
   
    GetLastColumnLetter = Left(GetLastColumnLetter, ipos - 1)

End Function

Sub Test()
    MsgBox GetLastColumnLetter()
End Sub

Posted in Excel, How To, VBA | 3 Comments




Worksheet Functions in VBA

September 14th, 2007

Most worksheet functions are available for use in VBA using the WorksheetFunction object. For example, the worksheet function:

=SUM(A1:A100)

will have this equivalent in VBA:

WorksheetFunction.Sum(Range("A1:A100"))

Try putting values in range A1:A10, and try this sample code:

Sub test()
    Dim r As Range
   
    Set r = Worksheets("Sheet1").Range("A1:A10")
    MsgBox "Sum = " & GetSum(r) & vbNewLine & "Max Value = " & GetMax(r), vbOKOnly, "A1:A10"
   
End Sub

Function GetSum(rSum As Range) As Double
    GetSum = WorksheetFunction.Sum(rSum)
End Function

Function GetMax(rMax As Range) As Double
    GetMax = WorksheetFunction.Max(rMax)
End Function

Posted in Excel, VBA | No Comments






Next Page »

(C) by Virgilio Adriano. All rights reserved. Powered by WordPress.
Entries and comments feeds.
It took 0.450 seconds to load this page.