Excel Debugger Extremely Slow

[missingsc]

Starting this week, I noticed the functions in my spreadsheet going extremely slow compared to weeks prior. I don't have time indicators because the change is so drastic it's on the minutes order of magnitude. A function call that took seconds last week takes a few minutes this week. What changed? Nothing as far as I can tell, no data change, no code change, I just hit the function again because I couldn't remember if I had run it at the end of last week.

So, I decided to look into the VBA debugger and step through lines, and I now see that the debugger itself is extremely slow. For example, a simple if statement which compares a variable to a string literal, when I hit the "Step Into" option (F8), it takes noticeable seconds for it to move past that simple if statement. It does this on all my lines of code, even when setting a variable to a string literal. Lines such as this take seconds for the debugger to move from one line to the next:
If my_var = "TEST" Then
my_new_var = "TESTING"
End If

Does anyone have a suggestion of what to look at? I've watched the task manager and CPU and Memory do not change much when going through the debugger. CPU is usually 10% or lower, and my Memory is usually 49% or lower. I've tried with all applications except Excel closed and have the same results.

Thank you for anything you can suggest. I really appreciate it.

 

[Joe4]

Yeah, sounds like you may have some sort of memory leak or something. But without being able to see the code, it is impossible for us to comment on it.

 

[missingsc]

Understood. Here is the copy button function I have when copying assignments from one month to the next in the workbook. As I noted, the logic previous to this week functioned very quickly. I haven't made code changes in weeks for this. So, whatever has changed was outside of the lines of code and the data in the workbook (new data wasn't input for several weeks either).

Private Sub copyButton_Click()
    Dim fromAssignments As Object
    Dim currAssignments As Object
    Dim copyToMonthDates As Object
    Dim copyToDateCounter As Integer
    Dim endFound As Boolean
    Dim rowCounter As Integer
    Dim copyFromMonth As String
    Dim copyToMonth As String
    Dim numDates As Integer

    copyFromMonth = copyFromMonthListBox.Value
    copyToMonth = copyToMonthListBox.Value

    ' first check to see all items were selected. if not, error and send back.
    If (resourcesListbox.Value = "") Or (copyFromMonthListBox.Value = "") Or (copyToMonthListBox.Value = "") Then
        Let temp = MsgBox("Please make all selections before continuing.", vbOKOnly)
    Else
        Set fromAssignments = CreateObject("Scripting.Dictionary")
        Set currAssignments = CreateObject("Scripting.Dictionary")
        Set copyToMonthDates = CreateObject("Scripting.Dictionary")
        copyToDateCounter = 1
            
        ' gather the existing projects and their mappings and store them before we clear and reset
        endFound = False
        rowCounter = 2
        Do
            ' go through the Assignments tab and find all assignments for the resource in the copy from month
            If Worksheets("Assignments").Cells(rowCounter, 1).Value <> "" Then
                If (Worksheets("Assignments").Cells(rowCounter, 1).Value = resourcesListbox.Value) And (MonthName(Month(Worksheets("Assignments").Cells(rowCounter, 4).Value)) = copyFromMonthListBox.Value) Then
                    ' this assignment is for the month we are copying from. check to see if we already added it to the array. if we did,
                    ' add the number of hours to what is already there.
                    If fromAssignments.Exists(Worksheets("Assignments").Cells(rowCounter, 2).Value) Then
                        Let tempHours = fromAssignments.Item(Worksheets("Assignments").Cells(rowCounter, 2).Value)
                        tempHours = tempHours + Worksheets("Assignments").Cells(rowCounter, 3).Value
                        fromAssignments.Item(Worksheets("Assignments").Cells(rowCounter, 2).Value) = tempHours
                    Else
                        fromAssignments.Add Worksheets("Assignments").Cells(rowCounter, 2).Value, Worksheets("Assignments").Cells(rowCounter, 3).Value
                    End If
                ElseIf (Worksheets("Assignments").Cells(rowCounter, 1).Value = resourcesListbox.Value) And (MonthName(Month(Worksheets("Assignments").Cells(rowCounter, 4).Value)) = copyToMonth) Then
                    ' this assignment is for the current month. we want to keep a list of these so we don't accidentally, make multiple assignments
                    ' for the same project on the same date via the copy.
                    If currAssignments.Exists(Worksheets("Assignments").Cells(rowCounter, 2).Value) Then
                        ' do nothing since it is already an assignment
                    Else
                        currAssignments.Add Worksheets("Assignments").Cells(rowCounter, 2).Value, Worksheets("Assignments").Cells(rowCounter, 3).Value
                    End If
                End If
            Else
                endFound = True
            End If
        
            rowCounter = rowCounter + 1
        Loop Until endFound
        
        ' now that we have all the assignments for the month, add them to the chosen month to copy to.
        ' first get all the Monday dates of the month we are going to copy into
        numDates = 0
        inputDate = DateSerial(Year(Now()), 1, 1)
        If MonthName(Month((inputDate - Weekday(inputDate, vbMonday) + 1))) = copyToMonth Then
            copyToMonthDates.Add copyToDateCounter, (inputDate - Weekday(inputDate, vbMonday) + 1)
            copyToDateCounter = copyToDateCounter + 1
            numDates = numDates + 1
        End If
        For i = 2 To 52
            inputDate = (DateAdd("d", 7, inputDate))
            If MonthName(Month((inputDate - Weekday(inputDate, vbMonday) + 1))) = copyToMonth Then
                copyToMonthDates.Add copyToDateCounter, (inputDate - Weekday(inputDate, vbMonday) + 1)
                copyToDateCounter = copyToDateCounter + 1
                numDates = numDates + 1
            End If
        Next i
        
        For Each monthDate In copyToMonthDates.Keys()
            For Each newAssignment In fromAssignments.Keys()
                ' add the new assignment to the list by first inserting a new row at the very top,
                ' but only add it if there isn't already an assignment on the same date
                If Not currAssignments.Exists(newAssignment) Then
                    Worksheets("Assignments").Rows("2:2").Select
                    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    Worksheets("Assignments").Range("A2").Select
                    
                    Worksheets("Assignments").Cells(2, 1).Value = resourcesListbox.Value
                    Worksheets("Assignments").Cells(2, 2).Value = newAssignment
                    Worksheets("Assignments").Cells(2, 3).Value = CInt((fromAssignments(newAssignment) / numDates) + 0.5)
                    Worksheets("Assignments").Cells(2, 4).Value = copyToMonthDates(monthDate)
                End If
            Next
        Next
    End If
    
    ' check to see if we should rebuild the dashboard
    Let temp = MsgBox("Would you like to rebuild the dashboard?", vbYesNo)
    If (temp = "6") Then
        Unload Me
        buildDashboard
    Else
        Unload Me
    End If
    
    ' rebuild the project details now that new projects have been entered then referesh the pivot table
    buildProjectDetails_Click
    Worksheets("Project Dashboard").PivotTables("projectDashboardPvt").PivotCache.Refresh
End Sub

 

[Joe4]

I don't do much with Object oriented programming myself, but from what I have seen from others is that they often set their object to nothing at the end of the code, i.e.

    Set fromAssignments = Nothing
    Set currAssignments = Nothing
    Set copyToMonthDates = Nothing

Also, it appears that your code may call other procedures too, so there could be issues in those procedures as well, namely:
buildDashboard
and
buildProjectDetails_Click

 

[missingsc]

I made a change and moved those both into the code block that asks if you want to run them or not. I ran the function again to copy, and what I notice is that in the section that is inserting rows at the top of the sheet, the logic is moving so slowly to perform that I can actually see each row being inserted into the sheet, there's like a second or two delayed between each row insertion (note I removed the enable events change to see if I could see anything). So this is before those other calls could be made, even though I'm saying no to them.

Also to note, when saying no to run those functions at the end of the copy, all it does then is the "Unload Me" line. When doing that, the form takes 10-20 seconds to unload and get back to the spreadsheet. There should be no logic running besides unloading the form, so what could be causing that lag?