CODEWITHSUNDEEP
vbaexcel
Kyoujin
Kyoujin

Reputation: 324

Transferring comments from one worksheet to another without using clipboard

I have a VBA script which add's comments to a background worksheet, which is working great. The problem I am having is moving this to a front worksheet.

I can use copy and paste special xlPasteComments but this then really slows down the update process. I have included below a section of what will be repeating code. If I use values it does not include the comments (I left this in to show) and I have tried Dim separating them out but this just causes as error with object not being supported.

If ws.Range("B9") = ("January") Then
Dim a As Long
Dim b As Long
    ws.Range("J8:AN51").Value = area.Range("E2:AI45").Value
    'This brings up a 438 runtime error (object doesnt support this propery 
    or method)
    a = ws.Range("J8:AN51").Comments
    b = area.Range("E2:AI45").Comments
    a = b
    'area.Range("E2:AI45").Copy
    'ws.Range("J8:AN51").PasteSpecial xlPasteComments
    ws.Range("J62:AN63").Value = area1.Range("E47:AI48").Value
    ws.Range("J55:AN55").Value = area.Range("E52:AI52").Value

I have checked on Google but it just keeps bringing up how to copy values within a cell, and what I am after is just the comments, (as the values are already copied)

Upvotes: 4

Views: 764

Answers (2)

DecimalTurn
DecimalTurn

Reputation: 4129

My initial idea was to try to load all the comments in an VBA array and then use this comment array to write to the other worksheet.

So, I tried to adapt this technique from Chip Pearson's website that does exactly that but for cell values.

Unfortunatly, using .comment.text on a range with multiple cells won't return an array which means that this method won't work.

This means that in order to transfer the comments to the other sheet using VBA, you would need to go through all cells one by one in the range (as a collection perhaps). Although I'm sure this would work, it most likely won't be faster than using xlPasteComments.

I would then resolve to use the usual VBA techniques to make your macro run faster by deactivating certain settings like automatic calculation, screen updating and events. Here is an example of how I would implement it (including some error handling):

Sub Optimize_VBA_Performance_Example()
    Const proc_name = "Optimize_VBA_Performance_Example"

    'Store the initial setting to reset it at the end
    Dim Initial_xlCalculation_Setting As Variant
    Initial_xlCalculation_Setting = Application.Calculation

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
    End With

    On Error GoTo Error_handler



    'Your code



    'Restore initial settings (before exiting macro)
    With Application
        .Calculation = Initial_xlCalculation_Setting
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayStatusBar = True
    End With

Exit Sub

Error_handler:

    'Restore initial settings (after error)
    With Application
        .Calculation = Initial_xlCalculation_Setting
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayStatusBar = True
    End With

    'Display error message
    Call MsgBox("Run-time error '" & Err.Number & "': " & Err.Description & vbNewLine & _
                "While running: " & proc_name & vbNewLine, _
                vbCritical, "Error")

End Sub

Upvotes: 1

cstricklan
cstricklan

Reputation: 661

If you only care about the text of the comment (and not the formatting), you can use the Range.Comment.Text object to copy the comment text. The main difficulty arises in error handling whether or not the comment exists. Then just loop through all the cells in your source range and assign the comment to the destination range.

Sub copyComment(source As Range, dest As Range)
    Dim t As String
    ' first set up error handling to exit the sub if the source cell doesn't have a comment
    On Error GoTo ExitCopyComment
    t = source.Comment.Text
    ' change error handling to go to next line
    On Error Resume Next
    ' assign the text to an existing comment at the destination
    ' use this 1,1 offset (first cell in range) syntax to overcome parser
    ' issue about assignment to constant
    dest(1, 1).Comment.Text = t
    ' if that produced an error then we need to add a comment
    If (Err) Then
        dest.AddComment t
    End If

ExitCopyComment:
    ' clear error handling
    On Error GoTo 0
End Sub

Sub test()
    Dim cell As Range
    Sheet1.Activate
    ' loop through all cells in source
    For Each cell In Sheet1.Range("E47:AI48").Cells
        ' calculate destination range as offset from source cell
        Call copyComment(cell, Sheet2.Cells(cell.Row + 15, cell.Column + 5))
    Next cell
End Sub

Upvotes: 0

Related Questions