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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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:
Upvote 0
The run command needs to go at the end of the sub, not after it (last line before End Sub).
 
Upvote 0
If Subs are in the same project you maybe use "Call Test2" or just "Test2" instead of "Run Test2"
 
Upvote 0
Oops, bad advice from me. @eduzs is correct, it should be Call test2 not Run test2
 
Upvote 0
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
 
Upvote 0
VBA Code:
Call Sub Test2
This does not exists.

Try:
VBA Code:
Sub Dannottheman3()
    ....
   Call Test2
End Sub
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top