Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Excel VBA - scroll by Tick Count
I'm working on an 'animation' idea.
I have this data in an array. (loaded from a sheet)
When 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.
I have this data in an array. (loaded from a sheet)
When 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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you show the code that you currently have to get to the TickCount?
Once you reach it you could do something like:
Without knowing what the rest of your code is there isn't much else I can help with.
Once you reach it you could do something like:
ActiveWindow.SmallScroll ToRight:=1
Without knowing what the rest of your code is there isn't much else I can help with.
Mike, I have figured out the scrolling as, eg.
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
ActiveWindow.ScrollColumn = 5
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()
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.
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
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
I'm hoping GetTickCount would have a precise time. Am I right in thinking that?
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
I'm hoping GetTickCount would have a precise time. Am I right in thinking that?
I see that you're not using the GetTickCount API functionYou're right! I meant to. Be back in a bit.
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial