ActiveX Scroll Bar Slow

Question

I'm putting together a spreadsheet that dynamically charts a subset data from a large time series. The idea is to allow the user to specify the start time of the data that is charted. This is accomplished using an ActiveX textbox. Additionally, I would like to allow the user to scroll through the time series. This is accomplished by including an ActiveX scrollbar. To keep things synchronous, both the textbox and scrollbar refer to the same cell (a named range called 'datum').

Here is where things get interesting. I've written three subs:

-One updates the time axis of the chart by referring to the datum.

Sub ReScaleChartAxis()

 Dim chtMin, chtMax As Long
 Dim grphSht, dtSht As Worksheet
 Dim minDepthCell As Range

 Set grphSht = Sheets("PlotSheet")
 Set dtSht = Sheets("Data")

 Set minDepthCell = dtSht.Range("datum")

 'Optimize
 Application.ScreenUpdating = False
 Application.EnableEvents = False

 'Determine Max/Min of time used in chart
 chtMin = Val(minDepthCell.Value)
 chtMax = chtMin + 500

 'Update chart
 With grphSht.ChartObjects("Plot").Chart.Axes(xlCategory)
     .MaximumScale = chtMax
     .MinimumScale = chtMin
 End With

 'Reset Optimization
 Application.EnableEvents = True
 Application.ScreenUpdating = True

End Sub

-One modifies the datum when the user enters a new value in the textbox.

Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 Dim datMin, datMax As Double
 If KeyCode = vbKeyReturn Then

   datMin = Sheets("Data").Range("dat_min").Value
   datMax = Sheets("Data").Range("dat_max").Value

   If Not IsNumeric(TextBox1.Value) Then
       TextBox1.Value = Sheets("Data").Range("datum")
   ElseIf Val(TextBox1.Value) <= datMin Then
       Sheets("Data").Range("datum") = datMin
       TextBox1.Value = datMin
   ElseIf Val(TextBox1.Value) >= (datMax - 450) Then
       Sheets("Data").Range("datum") = datMax - 500
       TextBox1.Value = datMax - 500
   Else
       Sheets("Data").Range("datum") = Val(TextBox1.Value)
   End If      
 End If
End Sub

-One calls the axis rescaling when the scrollbar changes.

Private Sub ScrollBar1_Change()
 Call ReScaleChartAxis
 TextBox1.Value = Sheets("Data").Range("datum")
 TextBox1.Select
End Sub

What is strange is that if the scrollbar is changed by user its event is triggered and the chart updates, AFTER hanging for about 1-2 seconds. Whereas, if the user enters a new value in the textbox, the entry goes through validation, updates the datum cell, then triggers the scrollbar_change event, and completes instantly.

This is a bit of a conundrum to me, to borrow an analogy from a friend, it's as though excel can run 3 miles faster than it can run 2. Does the ActiveX scrollbar have that much overhead with user interaction? I'm attaching an example.

Thank you in advance for any guidance.

This sounds as if an event is triggered by some action taken by an even procedure. I can't find it though. Are there more event procedures in your workbook? The thing to do would be to set `Application.EnableEvents = False` at the beginning of an event procedure and back to `True` at the end. You might want to do the same for `Application.ScreenUpdating`. — Variatus, Apr 28 '17 at 01:33
@Variatus Thanks for the feedback. You're right that there is a bit more in this book than I've posted. In the process of sanitizing the code for posting I dropped the 'optimization'. The 'ReScaleChartAxis' sub disables both screen updating and events, so the only part of the code that runs without the optimization are the last two lines of the Scrollbar1_Change event. I've tried putting the optimization in this event but it made no difference. Any other thoughts? — fallengyro, Apr 28 '17 at 02:03
I tested those two lines specifically to see if they trigger the KeyDown event, but on my Excel they didn't. Did you step through your code with F8? The 2-second delay you experience seems to point at some loop being executed. — Variatus, Apr 28 '17 at 02:48
@Variatus So I've created a completely stripped down version of my original workbook. It only contains the scrollbar_change event and the chart rescaling (module). I've put a break point at the first line of the scrollbar_change. This highlighted something interesting that I haven't seen before (also, haven't looked for). The lag I'm seeing occurs between when the event fires and the point when the ReScaleChartAxis is called. — fallengyro, Apr 28 '17 at 03:45
Look at where the code is. I would have `ReScaleChartAxis` in a normal code module, but not sure. The other thing to look at is workbook calculation. Perhaps the graph sheet is calculated several times over. Try setting calculation to manual until all data are in place. — Variatus, Apr 28 '17 at 05:33
@Variatus, I think I didn't properly convey my last message. In the stripped down sheet, I watched exactly where I was in the code, but because it's an event, you can't initiate it by stepping in. So I set a break point on the first line of the scrollbar_change event. Point being, I click the scrollbar, wait two seconds, and then I reach the BP. This means the lag is occurring before any of my code has even executed. This points to a bottleneck in the worksheet calculations (as you suggested) due to the scrollbar's linked cell changing when a user modifies the bar. To try to address this... — fallengyro, Apr 28 '17 at 12:06
(continued)...I've added the [MSDN Microtimer](https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx), to see what in the worksheet could be slowing things down. I use this code frequently to look for worksheet bottlenecks. Unfortunately, even a full workbook calculation ran in under .02 seconds. So this is another dead end. I'm becoming convinced that there is something inherently laggy about the activex scrollbar. Also, I tested moving the 'ReSCaleChartAxis from a module to the sheet with the event trigger. No effect, I wouldn't expect there would be though. — fallengyro, Apr 28 '17 at 12:27
@Variatus Thank you for all of your feedback. Even though I still don't have a an answer to 'why', you've helped drive me to a point where I can specifically point to 'what' and 'where' my problem resides. Very much appreciated. Cheers — fallengyro, Apr 28 '17 at 15:03

ActiveX Scroll Bar Slow

0 Answers0