Excel VBA - scroll by Tick Count

I'm working on an 'animation' idea.
I have this data in an array. (loaded from a sheet)
Scroll by tickWhen run, and when the Tick Count is reached I want to ScrollColumn to the column shown.

Thanks for any ideas.


FYI: it will start when a button is clicked.
hindersalivaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike in ITIT System AdministratorCommented:
Can you show the code that you currently have to get to the TickCount?

Once you reach it you could do something like:
ActiveWindow.SmallScroll ToRight:=1

Open in new window

That will scroll 1 column to the right. So if you have columns  A - O showing it will scroll right and show B - P.
Without knowing what the rest of your code is there isn't much else I can help with.
hindersalivaAuthor Commented:
Mike, I have figured out the scrolling as, eg.
    ActiveWindow.ScrollColumn = 5

Open in new window


My problem is, how can I get VBA procedure to 'listen' to the Tick Count and execute the above when each tick count is reached (the time as per the array or grid - see the image in my question) .

So, when Tick is 0354 this should execute
ActiveWindow.ScrollColumn = 2
So it will be something like a 'slide show'.

FYI: the code for the ticks is
Public Declare Function GetTickCount Lib "Kernel32" () As Long

    lngTime = GetTickCount()

Open in new window

Martin LissOlder than dirtCommented:
This works but I had to make the tick values much larger to be able to see the scrolling. You'd be better off using the clock time.

Note that the "array" is built into the code.

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub ScrollByTick()
    Dim lngTicks As Long
    Dim lngIndex As Long
    Dim varrTicks As Variant
    Dim varrCols As Variant
    
    varrTicks = Array(1, 3540000, 5000000, 10000000)
    varrCols = Array(1, 2, 3, 4)
    
    Do Until lngTicks > varrTicks(UBound(varrTicks))
        lngTicks = lngTicks + 1
        For lngIndex = 0 To UBound(varrTicks)
            If lngTicks = varrTicks(lngIndex) Then
                ActiveWindow.ScrollColumn = varrCols(lngIndex)
                Exit For
            End If
        Next
    Loop

End Sub

Open in new window

Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
hindersalivaAuthor Commented:
Martin, it works. Thanks.  I see that you're not using the GetTickCount API function. Let me explain why I was going in that direction.

My 'slide show' should advance to the next column (per array) when a precise time is reached. The 'time' is as per every 4 bars in a piece of music being played simultaneously - I would have got these times (in real-time or the wave) and put them into the array. The aim is, I intend to capture the 'slide show' and the audio by screen recording.

So, I'm thinking
lngTicks = lngTicks + 1

Open in new window

would go at a speed that cannot be controlled.

I'm hoping GetTickCount would have a precise time. Am I right in thinking that?
Martin LissOlder than dirtCommented:
I see that you're not using the GetTickCount API function
You're right! I meant to. Be back in a bit.
Martin LissOlder than dirtCommented:
The problem is that when actually using GetTickCount I think that the ticks will go faster than the code so it will be hard to react to it when it is exactly one of the values in the array.
Martin LissOlder than dirtCommented:
See my How to time code article.
Martin LissOlder than dirtCommented:
I think I have it.

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub ScrollByTick()
    Dim lngNow As Long
    Dim lngIndex As Long
    Dim lngStart As Long
    Dim varrTicks As Variant
    Dim varrCols As Variant
    
    varrTicks = Array(1, 354, 500, 1000)
    varrCols = Array(1, 2, 3, 4)
    
    lngStart = GetTickCount()
    
    Do
        lngNow = GetTickCount()
        ' Check to see if we should stop the loop
        If (lngNow - lngStart > varrTicks(UBound(varrTicks))) Then
            ' Scroll to the last column in the array and then get out
            ActiveWindow.ScrollColumn = varrCols(UBound(varrCols))
            Exit Do
        End If
        For lngIndex = 0 To UBound(varrTicks) - 1
            If (lngNow - lngStart >= varrTicks(lngIndex)) And _
               (lngNow - lngStart < varrTicks(lngIndex + 1)) Then
                ActiveWindow.ScrollColumn = varrCols(lngIndex)
                Exit For
            End If
        Next
    Loop

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.