How to make My excel Macro run faster

[Anike42]

Currently I am using a worksheet that uses hundreds of hundreds of checkboxes linked to their cells. I find this macro is very long for the amount of times I have to use it and is slowing down my program

this is the Macro I am using:

Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

is there a way to make it faster?

 

[AlphaFrog]

Hi and welcome to the board.

This should be a little faster.

[color=darkblue]Sub[/color] AddCheckBoxes()
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] Range
    [color=darkblue]With[/color] Application
        .ScreenUpdating = [color=darkblue]False[/color]
        .Calculation = xlManual
        .EnableEvents = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] c [color=darkblue]In[/color] Selection
        [color=darkblue]With[/color] ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height)
            .LinkedCell = c.Address
            .Characters.Text = ""
            .Name = c.Address
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]With[/color] c
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, _
                                  Formula1:="=" & c.Address & "=TRUE"
            .FormatConditions(1).Font.ColorIndex = 6    [color=green]'change for other color when ticked[/color]
            .FormatConditions(1).Interior.ColorIndex = 6    [color=green]'change for other color when ticked[/color]
            .Font.ColorIndex = 2    [color=green]'cell background color = White[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] c
    [color=darkblue]With[/color] Application
        .ScreenUpdating = [color=darkblue]True[/color]
        .Calculation = xlAutomatic
        .EnableEvents = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Michael M]

At the start of the code put

Application.screenupdating=False

and at the end of the code

Application.screenupdating=True

 

[boboivan]

Hi guys!

I want also to make the following Macro to run faster. Can you please help me with this?



Function NetWkDays2(strName As String)
Dim myCol As New Collection, lastRow As Long, i As Long, isHol As Boolean
Dim dataRange As Range, rCell As Range

Application.Volatile

With Sheets("2013") '<---Adjust sheet name
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
Set dataRange = .Range("D2:D" & lastRow)

For Each rCell In dataRange
If UCase(.Cells(rCell.Row, "Q").Value) = UCase(strName) Then
For i = rCell.Value To .Cells(rCell.Row, "Y").Value - 1
isHol = Evaluate("=ISNUMBER(Match(" & i & ",ZileLibere,0))")

If Evaluate("WEEKDAY(" & i & ",2)") < 6 And isHol = False Then
On Error Resume Next
myCol.Add i, CStr(i)
On Error GoTo 0
End If

Next i
End If
Next rCell

End With

NetWkDays2 = myCol.Count

End Function
Sub ZileLucratoare()


End Sub