• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Animating Text in excel

A

Abhijeet

Active Member
Hi
From this code Hi there word Animating but tell me any word in D6 that can Animate how to do this



Sub Tester1()
Dim sp As String
Dim sTxt As String
Dim x As Integer, y As Integer
Dim Start, delay

sTxt = "Hi there!!"

For y = 1 To 15 '15 Loops through the scrolling
For x = 1 To 30 'Index number of times
Start = Timer 'Set start to internal timer
delay = Start + 0.15 'Set delay for .15 secs

Do While Timer < delay 'Do the display routine
[D6] = Space(x) & sTxt 'Show 1 str @ a time
DoEvents 'do there things
Loop 'Loop until delay is up
DoEvents
Start = Timer 'and reset the timer
delay = Start + 0.15 'and the delay
Next x 'Show the next str
Next y 'Do this again - 15

[D6] = "" 'Reset

End Sub
 
Hi Abhijeet ,

Instead of the line :

sTxt = "Hi there!!"

put in the line :

sTxt = [D6]

Put in the string you wish should be animated in the cell D6.

Narayan
 
Hi Abhijeet,

Try this:

Code: 
Sub Tester1()

Dim sp As String
Dim sTxt As String
Dim x As Integer, y As Integer
Dim Start As Variant, delay As Variant
Dim rng As Range, rngCell As Range

Set rng = ActiveSheet.Range("d2:e10")

For Each rngCell In rng

  sTxt = rngCell.Value

  For y = 1 To 1 '15 Loops through the scrolling
    For x = 1 To 60 'Index number of times
      Start = Timer 'Set start to internal timer
      delay = Start + 0.15 'Set delay for .15 secs
     
      Do While Timer < delay 'Do the display routine
        [D2] = Space(x) & sTxt 'Show 1 str @ a time
        DoEvents 'do there things
      Loop 'Loop until delay is up
      DoEvents
      Start = Timer 'and reset the timer
      delay = Start + 0.15 'and the delay
    Next x 'Show the next str
  Next y 'Do this again - 15
Next rngCell

[D2] = "" 'Reset

'Clean up
Set rng = Nothing

End Sub

Regards,

Peter
 

Attachments

Hi Aberjeet,

I am not convinced that you can run a macro (indefinately) until the Excel file is closed. You could run the macro when the user tries to close the workbook but for the sake of simplicity, I would recommend adjusting the following line of code

Code: 
For y = 1 To 1 'Loops through the scrolling

To
Code: 
For y = 1 To n 'Loops through the scrolling

Where n is a large number.

You will have to wait until the macro finishes before doing anything else with this workbook.
 

Hi PeterB !

VBA is mono-tasking but for advanced developers, 2 ways to run a parallel procedure :​
  • by Windows API : CreateThread & CloseHandle from kernel32 library;
  • by a VBScript procedure …
 
I make no guarantees about this macro not running away from you if you set a large run time :DD, but here's how you could write a looping macro so that you can still be making changes to the workbook.
Code: 
'Setup our local variables
Dim i As Integer
Dim endTime As Date

'This is the macro we call to start things off
Sub StartTimer()
'How long to run loop for
endTime = Now + TimeValue("00:00:10")
Call MakeChange
End Sub

'Our slave macro
Private Sub MakeChange()
Dim myRange As Range
'Where are the cells with items we want to
'loop through
Set myRange = ActiveSheet.Range("D2:D10")

'Which cell gets changed?
Range("F1").Value = myRange(i Mod myRange.Cells.Count).Value
i = i + 1

'If we haven't passed end time, setup a new macro call
'to occur sometime in the future
If Now < endTime Then
    Application.OnTime Now + TimeValue("00:00:01"), "MakeChange"
End If
End Sub
 
One thing about the OnTime method...even if you close the workbook, the procedure has still be stored in XL's memory, so it will try and reopen the workbook just so it can run the macro if you haven't reached the endTime.
 
@Mark L Thanks for your comment. I am aware of using VB script for calculations (probably from the ExcelHero blog post that Narayan refers to above) but thought I would keep the response straight forward. However, I have never come across those Windows API calls. Do you have an example please?

Many thanks,

Peter.
 
PeterB said:
Do you have an example please?
Click to expand...

Code to insert only in a standard module and launch Demo procedure :​
Code: 
Private Declare Function CreateThread& Lib "kernel32" (zero1 As Any, ByVal zero2&, _
                        ByVal lpStartAddress&, zero3 As Any, ByVal zero4&, zero5&)

Private Declare Function CloseHandle& Lib "kernel32" (ByVal hObject&)


Private Sub ParallelTask()
    MsgBox "That works !!!", , "  Parallel Task"
End Sub


Sub Demo()
    CloseHandle CreateThread(0, 0, AddressOf ParallelTask, 0, 0, Empty)
    MsgBox "Parallel process created …", , "  Main Task"
End Sub
 

You're welcome !

I forgot to mention that my example code works only for 32 bits Excel version …
Must be adapted with Declare PtrSafe for 64 bits Excel version
and replace Any & Long by LongPtr …

Outside the bible MSDN documentation, see also All API …​
 
Last edited:
@Marc L
Hi, buddy!
That remembers me the old times when Dan Appleman's Visual Basic Programmer's Guide to the Win32 API was just published... Yeah, without the x64 fix.
ReCarlsGardsBerg!
 
You must log in or register to reply here.
Back
Top