Is it possible to run VBA macro in phases so that the runtime is reduced?

user_13257241 0
Viewed 48

I am trying to build a worksheet that allows me and my colleagues to fill in variables in a worksheet, as one would do during an intake conversation with a customer. I have succeeded so far, but the macro has become very very slow... I already disabled ScreenUpdating, DisplayPageBreaks, Calculation and Events.

Actually, I am pretty sure it takes so long because of the extensive Private Sub Worksheet_Change. I use some initial formatting for when the cell is still empty, so that my colleagues know what to put in the cell. But this process is repeated individually for about 170 rows. It looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Cells(19,3).Value="" Then Cells(19,3).Value="Fill in x.."
     Cells(19,3).Font.Fontstyle = "Italic"
     Cells(19,3).Font.ColorIndex = 16

  If Cells(20,3).Value="" Then Cells(20,3).Value="Fill in y.."
     Cells(20,3).Font.Fontstyle = "Italic"
     Cells(20,3).Font.ColorIndex = 16

  If Cells(25,2).Value="" Then Cells(25,2).Value="Fill in z.."
     Cells(25,2).Font.Fontstyle = "Italic"
     Cells(25,2).Font.ColorIndex = 16

  If Cells(70,3) = True Then Cells(70,3).Value = "Comment.."
  If Cells(70,3) = False Then Cells(70,3).Value = ""
     Cells(70,3).Font.Fontstyle = "Italic"
     Cells(70,3).Font.ColorIndex = 16

And this goes on for lots of other cells. I would like to explore whether it is possible to only run pieces of the Private Sub Worksheet_Change at the time. For example, that it only checks whether a cells changes in the chapter that the person in currently working in. Could someone help me with this, or put me in the right direction?

Thanks in advance!

formatting vba excel
1 Answers

If If - Then statement is completed in one line as in all of your cases, then the code next to it will run irrespective of the if result immediately above them. So, in your case all statements will be run (including If conditions and others) making the if statements redundant. So even if Cells(19,3).Value<>"" it will still check the condition and will make Cells(19,3).Font italic and colorindex 16. Better to use if blocks with actions on the next lines and with end ifs

Worksheet.Change event occurs when cells on the worksheet are changed by the user or by an external link. This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation. So, if the user is updating the sheet manually, only one cell will be changed at a time.

Hence, every time when a single cell is changed macro will check all 170 rows and execute all the statements irrespective of the outcome of the if statements as mentioned above and there will be delay.

user_98080630
Related