Code Really Slows Down Sheet

[EvonS]

Ever since I added the following code to my sheet (which is activated by a button), the performance of my sheet is A LOT slower. Are there any ideas as to why? Thanks in advance for the help.

Sub insertVeryLongHyperlink()


    Dim curCell As Range
    Dim longHyperlink As Variant
    Dim x As Integer
    Dim situation As Variant
    Dim emails As Variant
    Dim tdate As Integer
    Dim sdate As Integer
    


    x = 2
    
    Do
     
            situation = Cells(x, 1)
            emails = Cells(x, 6)
            Set curCell = Range("G" & x) ' or use any cell-reference
            longHyperlink = "mailto:" & emails & [G1] & "?subject=" & situation & " Thread" & "&body= Please use this email thread to communicate updates and next steps" ' Or a Cell reference like [C1]
        
            curCell.Hyperlinks.Add Anchor:=curCell, _
                            Address:=longHyperlink, _
                            SubAddress:="", _
                            ScreenTip:=" - Click here to create email thread", _
                            TextToDisplay:="Create " & situation & " Email Thread"
      
      
            x = x + 1
    Loop Until Cells(x, 6) = 0




End Sub

 

[Jerry Sullivan]

Hi EvonS,

The presence of this code in your workbook should not make any noticeable difference in the performance of worksheet unless you are calling the code with an event procedure (like Worksheet_Calculate). If this code is only called by your button, then this isn't the cause of the problem.

It's possible that something else occurred about the same time you added this code that is causing the slow down. For example, in the development of this code, you might have inadvertently changed the Used Range to all rows in the worksheet.

 

[EvonS]

Thanks for your reply. I was messing around with some other modules I've deleted them. Maybe it's a computer issue. Also, the code takes a while to process (about 30sec to go through 6 loops). Is that normal? Is there something that can be done to speed it up?

 

[Jerry Sullivan]

Having a lot of VBA code stored in the workbook wouldn't have any noticeable effect if it isn't being executed.

Have you tried resetting the used range of your workbook? You can do that by deleting all the rows below the ones you are actually using, and all the ones to the right of the ones you are using.

Other things that can slow down a workbook include slow or complex formulas, references to other workbooks, and large numbers of conditional formatting rules.

 

[EvonS]

Having a lot of VBA code stored in the workbook wouldn't have any noticeable effect if it isn't being executed.

Have you tried resetting the used range of your workbook? You can do that by deleting all the rows below the ones you are actually using, and all the ones to the right of the ones you are using.

Other things that can slow down a workbook include slow or complex formulas, references to other workbooks, and large numbers of conditional formatting rules.

Alright I will try it out. I have quite a bit of conditional formatting on one of the pages so I will try to reduce it. Thanks for taking the time to respond.