Running Multiple VBAs at once

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Hello,
I searched for this and could not find the answer. I have 3 VBAs that would like to run at once. Even though I copy paste the code as shown below, Excel is not running all 3 tasks. I have to delete or manually copy paste each one and run again. Is there a way to make all 3 run one after the other?:

VBA Code:
Sub Dannottheman3()

Dim Cl As Range



For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))

If LCase(Cl.Value) Like "*lesson*" Then

Cl.Interior.Color = rgbLightBlue

Cl.Font.Bold = True

End If

Next Cl

End Sub



Sub Test2()

Application.ScreenUpdating = False

With ActiveSheet.Columns("A")

.ColumnWidth = 95

.WrapText = True

End With

Columns("A:B").NumberFormat = "General"

Application.ScreenUpdating = True

End Sub


Sub Dannottheman()

Dim Ary As Variant, Nary As Variant

Dim r As Long



Ary = Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2

ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)

For r = 1 To UBound(Ary)

Nary(r * 2 - 1, 1) = Ary(r, 1)

Nary(r * 2, 1) = Ary(r, 2)

Next r

With Range("A1").Resize(UBound(Nary))

.NumberFormat = "@"

.Value = Nary

End With

End Sub
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,521
Office Version
  1. 365
Platform
  1. Windows
Option 1:- Make them into one sub instead of 3.
Option 2:- Add a Run command at the end of the fist sub Run Test2 to start the second and another at the end of the second to start the third.
Option 3:- Create a 4th sub with 3 run commands, one for each of your existing subs.
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
This is what I have based on Option 2 above but I am getting an error that "comments can only appear after..."

VBA Code:
Sub Dannottheman()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
  
   Ary = Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r * 2 - 1, 1) = Ary(r, 1)
      Nary(r * 2, 1) = Ary(r, 2)
   Next r
   With Range("A1").Resize(UBound(Nary))
      .NumberFormat = "@"
      .Value = Nary
   End With
End Sub
Run Dannottheman3

Sub Dannottheman3()
   Dim Cl As Range
  
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If LCase(Cl.Value) Like "*quiz*" Then
         Cl.Interior.Color = rgbLightBlue
         Cl.Font.Bold = True
      End If
   Next Cl
End Sub
Run Test2

Sub Test2()
Application.ScreenUpdating = False
With ActiveSheet.Columns("A")
   .ColumnWidth = 95
   .WrapText = True
End With
Columns("A:B").NumberFormat = "General"
Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,521
Office Version
  1. 365
Platform
  1. Windows
The run command needs to go at the end of the sub, not after it (last line before End Sub).
 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
639
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

If Subs are in the same project you maybe use "Call Test2" or just "Test2" instead of "Run Test2"
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,521
Office Version
  1. 365
Platform
  1. Windows
Oops, bad advice from me. @eduzs is correct, it should be Call test2 not Run test2
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007

ADVERTISEMENT

Oops, bad advice from me. @eduzs is correct, it should be Call test2 not Run test2
For some reason, it only does one of the two VBAs (the second one), if I change the position of the VBAs, it again only does the second one. I experimented changing the position of "Call Sub Test2" but it doesn't seem to make a difference. This is what I have:

VBA Code:
Option Explicit
Sub Dannottheman3()
    Dim Cl     As Range
    Dim srch As Variant
    For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
        For Each srch In Array("*quiz*", "*unit", "*assessment*", "*test*") '<= note the different use of the jolly (could be useful)
            If LCase(Cl.Value) Like srch Then
                Cl.Interior.Color = rgbLightBlue
                Cl.Font.Bold = True
                Exit For
            End If
        Next srch
    Next Cl
End Sub

Call Sub Test2

Sub Test2()
Application.ScreenUpdating = False
With ActiveSheet.Columns("A")
   .ColumnWidth = 95
   .WrapText = True
End With
Columns("A:B").NumberFormat = "General"
Application.ScreenUpdating = True
End Sub
 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
639
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
VBA Code:
Call Sub Test2
This does not exists.

Try:
VBA Code:
Sub Dannottheman3()
    ....
   Call Test2
End Sub
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
VBA Code:
Call Sub Test2
This does not exists.

Try:
VBA Code:
Sub Dannottheman3()
    ....
   Call Test2
End Sub
I just did. It runs Test2 but does not perform the task of "Sub Dannottheman3" (first VBA)
 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
639
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try to insert at the end of each sub with the sub you want to execute next "Call ....".
You need to run the 1st sub manually or call with other methods.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,524
Messages
5,572,640
Members
412,478
Latest member
MakeItWorkVBA
Top