1
\$\begingroup\$

I have written the following VBA script that searches for a text string in column CI and if it finds it the specific text the script adds a formula to a series of cells. The script itself runs correctly but I think it is taking much longer to run than it should. I am assuming there are some form efficient ways to write the code.

I have other scripts in the same Excel workbook that take 30 seconds to run but this script takes almost 7 minutes. It is searching through just over 900 cells in the test case I am using. Which in the grand scheme of thing, that is not really that many cells. How can I make it more efficient?

Private Sub Material_Formulas()

Dim lRow As Integer
Dim tiesMaterial As String
Dim result As String

lRow = Sheets("Material").Range("A2").End(xlDown).Row
lCol = Sheets("Material").Range("A2").End(xlToRight).Column

'Starts the count at column CU
endCount = lCol - 1

    For c = 99 To endCount
        For r = 3 To lRow

        tiesMaterial = Cells(r, 87).Value


        'Looks to see if the cells starting at CU2 contains a number and then iterates through each cell in row 3 to add a formula
        If tiesMaterial = "TIES MATERIAL" Then

            materialID = Sheets("Material").Cells(r, "CQ").Address(False, False)
            materialYear = Sheets("Material").Cells(2, c).Address(False, False)

            'Starting in cell CU3 it adds the formula =INDEX(BOM_Summary_Array,MATCH(CQ3,BOM_Summary_ID,0),MATCH(CU2,BOM_Summary_Head,0))
            Sheets("Material").Cells(r, c).Formula = "=INDEX(BOM_Summary_Array,MATCH(Material!" & materialID & ",BOM_Summary_ID,0),MATCH(Material!" & materialYear & ",BOM_Summary_Head,0))"

        End If

        Next r
    Next c

End Sub
\$\endgroup\$
  • 2
    \$\begingroup\$ You need to disable UI refreshing and automatic recalculation on each cell update for as long as the loop is running. Here is everything you should need to improve it blogs.office.com/2009/03/12/… \$\endgroup\$ – t3chb0t Jun 21 '17 at 14:04
  • 1
    \$\begingroup\$ Holy crap where have you been the last week @t3chb0t? I may have just posted this question but I have been struggling with it for the last week! I do not know if I feel more relieved to have a solution or stupid because the answer was so simple. Thanks @t3chb0t you're a lifesaver! \$\endgroup\$ – Part_Time_Nerd Jun 21 '17 at 14:22
  • \$\begingroup\$ @t3chb0t: could you please add your solution as an answer so the question could be "officially" answered? \$\endgroup\$ – Máté Juhász Jun 26 '17 at 10:06
2
\$\begingroup\$

When working with Excel and writing to cells the most common performance issue come from the sheet being constantly updated in the background while the script is running. In order to gain more speed you should save the state of a few properties for as long as the script is running and restore their values when it finishes.

For example:

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

Reference: Excel VBA Performance Coding Best Practices

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.