Excel VBA launch multiple macros at the same time

Question

I have this code that paints (B;5) cell red, and starts moving it back and forth.

Declare Sub Sleep Lib "kernel32" (ByVal dwMillisecons As Long)

Private Sub Button1_Click()
Move
End Sub

Sub Move()
gr = 1
st = 1
While Cells(2, 2) = 0
If st > 1 Then
  Cells(5, st - 1).Clear
  End If
Cells(5, st + 1).Clear
Cells(5, st).Interior.Color = vbGreen
st = st + gr
If st > 48 Then
gr = -1
End If
If st < 2 Then
gr = 1
End If
Sleep 100
 DoEvents
 Wend
End Sub

How to make that it would paint (B;7) and (B,9) cells and also would start moving them at the same time?

It paints a cell in green color, and starts to move it back and forth. — Faux
– Faux, Commented May 24, 2015 at 12:22

Siddharth Rout · Accepted Answer · 2015-05-24 13:54:06Z

3

Your code

If st > 1 Then Cells(5, st - 1).Clear
Cells(5, st + 1).Clear
Cells(5, st).Interior.Color = vbGreen

takes care of row 5. Simply add those 3 lines again for for 7 and 9

Sub Move()
    gr = 1
    st = 1

    While Cells(2, 2) = 0
        If st > 1 Then Cells(5, st - 1).Clear
        Cells(5, st + 1).Clear
        Cells(5, st).Interior.Color = vbGreen

        If st > 1 Then Cells(7, st - 1).Clear
        Cells(7, st + 1).Clear
        Cells(7, st).Interior.Color = vbGreen

        If st > 1 Then Cells(9, st - 1).Clear
        Cells(9, st + 1).Clear
        Cells(9, st).Interior.Color = vbGreen

        st = st + gr

        If st > 48 Then gr = -1

        If st < 2 Then gr = 1

        Sleep 100
         DoEvents
    Wend
End Sub

answered May 24, 2015 at 13:54

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Faux Over a year ago

Is it possible that they would start from different locations?

to StackOverflow · Accepted Answer · 2015-05-24 14:09:07Z

2

Excel VBA is single-threaded.

In order to have multiple macros running concurrently, you can:

Start each macro on a timer event (Application.OnTime)
Ensure each macro calls DoEvents periodically to allow the other concurrent macros to run.

Alternatively, you can have each of your macros run once (e.g. to paint a cell red), then before exiting, call Application.OnTime to schedule its next execution.

answered May 24, 2015 at 14:09

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

1 Comment

DecimalTurn Over a year ago

Even when using OnTime, you won't be able to get concurrency in VBA, macros will still run sequentially. Demo : gist.github.com/DecimalTurn/176198fe379325be10e35e0d751905a8

Gary's Student · Accepted Answer · 2015-05-24 14:34:54Z

If you would like to get several boxes moving back and forth at the same time, then try running RTE():

Declare Sub Sleep Lib "kernel32" (ByVal dwMillisecons As Long)
Public BegunA As Boolean
Public BegunB As Boolean
Public BegunC As Boolean
Public wf As WorksheetFunction

Sub RTE()
   Dim IAmTheCount As Long
   BegunA = False
   BegunB = False
   BegunC = False
   Set wf = Application.WorksheetFunction
   IAmTheCount = 1

   While IAmTheCount < 50
      Sleep 100
      DoEvents
      Call MoveA
      Call MoveB
      Call MoveC
      IAmTheCount = IAmTheCount + 1
   Wend

End Sub
Sub MoveA()
   Static gr As Long
   Static st As Long
   If Not BegunA Then
      BegunA = True
      st = wf.RandBetween(2, 9)
      gr = wf.RandBetween(1, 2)
      If gr = 2 Then gr = -1
   End If

   Cells(5, 1).EntireRow.Clear
   Cells(5, st).Interior.Color = vbGreen
   st = st + gr

   If st > 10 Then
      gr = -1
   End If

   If st < 2 Then
      gr = 1
   End If
End Sub
Sub MoveB()
   Static gr As Long
   Static st As Long
   If Not BegunB Then
      BegunB = True
      st = wf.RandBetween(2, 9)
      gr = wf.RandBetween(1, 2)
      If gr = 2 Then gr = -1
   End If

   Cells(6, 1).EntireRow.Clear
   Cells(6, st).Interior.Color = vbYellow
   st = st + gr

   If st > 10 Then
      gr = -1
   End If

   If st < 2 Then
      gr = 1
   End If
End Sub
Sub MoveC()
   Static gr As Long
   Static st As Long
   If Not BegunC Then
      BegunC = True
      st = wf.RandBetween(2, 9)
      gr = wf.RandBetween(1, 2)
      If gr = 2 Then gr = -1
   End If

   Cells(7, 1).EntireRow.Clear
   Cells(7, st).Interior.Color = vbRed
   st = st + gr

   If st > 10 Then
      gr = -1
   End If

   If st < 2 Then
      gr = 1
   End If
End Sub

Collectives™ on Stack Overflow

Excel VBA launch multiple macros at the same time

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related