Forum Discussion

ADGToreUp's avatar
ADGToreUp
Brass Contributor
Jul 25, 2022
Solved

Slow Loading .xlsb File

Good morning everyone,

I have a 2.5mb .xlsb file that takes approximately five minutes to load.  It is only one sheet, but has some VBA in the background.  It is a tracker, and I have entered in a lot of "Worksheet_BeforeDoubleClick" functionality so that the users can easily update the tables with just using their mouse.  The code below is the only code in the workbook.  Everything runs very quickly once the file has opened, so I am unaware of why it takes so long to open, even from a saved copy on my desktop. (So I know that it is not our shared files where other .xlsb files open quickly)  All help greatly appreciated.  

 

VBA Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("F11:L44")) Is Nothing Then
If Target.Value = "/" Then
Target.Value = "P"
ActiveSheet.Range("A22").Select
Exit Sub
End If

If Target.Value = "O" Then Target.Value = "/"
If Target.Value = "P" Then Target.Value = "O"
If Target.Value = Empty Then Target.Value = "/"
ActiveSheet.Range("A22").Select
End If

If Not Intersect(Target, Range("R11:T44")) Is Nothing Then
If Target.Value = "/" Then
Target.Value = "P"
ActiveSheet.Range("A22").Select
Exit Sub
End If

If Target.Value = "O" Then Target.Value = "/"
If Target.Value = "P" Then Target.Value = "O"
If Target.Value = Empty Then Target.Value = "/"
ActiveSheet.Range("A22").Select
End If

If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(3).Range) Is Nothing Then
If Target.Value = "Received" Then
Target.Value = "Initializing"
ActiveSheet.Range("A22").Select
Exit Sub
End If

If Target.Value = "Routed" Then Target.Value = "Received"
If Target.Value = "Initializing" Then Target.Value = "Routed"
If Target.Value = Empty Then Target.Value = "Received"
ActiveSheet.Range("A22").Select
End If


If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(12).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If

If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(4).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If

If Not Intersect(Target, ListObjects("ApprovalTracker").ListColumns(1).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If

If Not Intersect(Target, ListObjects("ApprovalTracker").ListColumns(5).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If


If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(2).Range) Is Nothing Then
If Target.Value = "Yes" Then
Target.Value = "No"
ActiveSheet.Range("A22").Select
Exit Sub
End If

If Target.Value = "No" Then Target.Value = "Yes"
If Target.Value = Empty Then Target.Value = "Yes"
ActiveSheet.Range("A22").Select
End If

End Sub

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Maybe a (hidden) range name with an external link? Those can slow down the opening considerably.
    • ADGToreUp's avatar
      ADGToreUp
      Brass Contributor
      Where would I check for this? I suppose it is possible that I added something by mistake, but the code I pasted above is the only VBA used in the workbook. I checked connections and do not see any.
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        ADGToreUp You can take my word for it. You should check for this as in my experience (broken) links hidden away in range names can cause precisely what you describe: a slowdown of loading your file.

Resources