Excel VBA Replacing Loop with Array for Performance Gains

Question

Earlier today I got some help in developing a bit of code that would take the contents of a cell and place it in a comment that could display when being moused over.

It works great, but on a 6000 row spreadsheet it can take a while. I read here that you can potentially replace looping logic with array logic to speed up the process.

Any idea where I'd start in turning this from loop-based to array-based?

Dim iCell                       As Range
On Error Resume Next 'included because of an error when the .Formula function was triggered
    For Each iCell In Selection
        With iCell
            If CStr(.Value) <> "" Then
                .ClearComments
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=CStr(.Value)
                .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
                .Comment.Shape.ScaleHeight 5.87, msoFalse, msoScaleFromTopLeft '2.26 was the original height
            End If
            If .Formula <> "" Then
                .ClearComments
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=CStr(.Formula)
                .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
                .Comment.Shape.ScaleHeight 5.87, msoFalse, msoScaleFromTopLeft
            End If
        End With
    Next
End Sub

As with last time, any and all help is appreciated, be it guidance or examples or a solution - I intend to try and reverse engineer it to teach myself more about this stuff. Thanks!

To use an Array, you can Dim aMyArray As Variant, aMyArray = Selection.Value Then loop through aMyArray(r,c) with limits r = LBound(aMyArray,1) to UBound(aMyArray,1), c = LBound(aMyArray,2) to UBound(aMyArray,2). But based on your IF blocks, it won't work on Array. You can check blank cells with IsEmpty(iCell). You can use iCell.HasFormula to check if it's storing a formula or a value. You are at the moment gets only the formula. — PatricK
– PatricK, Commented Feb 5, 2016 at 4:54
@Patrick That wont help here as the OP needs to add a comment box - OP its true that arrays are better than ranges for working on data. But you need to work with ranges to add comments, it can't be done with arrays. — brettdj
– brettdj, Commented Feb 5, 2016 at 7:56

brettdj · Accepted Answer · 2016-02-05 08:07:17Z

This is an example of how you could improve your necessary range loop:

Using SpecialCells to work on formulae without looking at each cell.
Optimising Application settings.
Taking the line that hide comments out of the loop.

code

Sub Recut()
Dim rng1 As Range
Dim rng2 As Range
Dim lngCalc As Long

'look only at formulae
On Error Resume Next
Set rng1 = Selection.SpecialCells(xlFormulas)
On Error GoTo 0

If rng1 Is Nothing Then Exit Sub

'Improve application settings for speed
With Application
    lngCalc = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

For Each rng2 In rng1.Cells
With rng2
       .ClearComments
       .AddComment
       .Comment.Text Text:=CStr(.Formula)
       .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
       .Comment.Shape.ScaleHeight 5.87, msoFalse, msoScaleFromTopLeft
End With
Next

'take comment display out of the loop
Application.DisplayCommentIndicator = xlCommentIndicatorOnly

With Application
    .ScreenUpdating = True
    .Calculation = lngCalc
    .DisplayAlerts = True
End With

End Sub

Collectives™ on Stack Overflow

Excel VBA Replacing Loop with Array for Performance Gains

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related