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
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
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.
I run the first one manually but Excel only performs the second sub, not the first one
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Doesn't work...it only runs the second sub. If you can please insert the Call "test2" where you think it belongs to see if this is what I am getting wrong. I tried different locations and it makes no difference:


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


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
12,659
Office Version
  1. 365
Platform
  1. Windows
I have to delete or manually copy paste each one and run again.
This part of your post is ringing some very loud alarm bells. That says to me that one is making changes which are preventing the others from working properly.
For what you're asking, the method is
VBA Code:
Sub test1()
'some code to run

Call test2
End Sub

Sub test2()
'more code to run

Call test3
End Sub

Sub test3()
'even more code to run
End Sub
So that test2 starts just before test1 finishes, likewise test3 starts just before test1 finishes. This is what you have asked for, although in reality I think that it could be a long way from what you need.
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
This part of your post is ringing some very loud alarm bells. That says to me that one is making changes which are preventing the others from working properly.
For what you're asking, the method is
VBA Code:
Sub test1()
'some code to run

Call test2
End Sub

Sub test2()
'more code to run

Call test3
End Sub

Sub test3()
'even more code to run
End Sub
So that test2 starts just before test1 finishes, likewise test3 starts just before test1 finishes. This is what you have asked for, although in reality I think that it could be a long way from what you need.
The first VBA is only shading a cell "x" color and making the text bold. The second VBA is enlarging the column width. What one does should not have any bearing on the other one and yet, I can't get them to work together...
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Your code in post 12 works fine for me when adding the line Call test2
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
It will work the same on any version there is nothing version specific in your code, if there was it would cause a runtime error rather than do nothing.

All I did was edit the code in post 12 as per the guidance that we provided, then enter some random text into column A of a blank sheet with some of them containing strings from your array.
The bold font and blue fill were applied to cells containing text from the array and the column width was adjusted as per the code in test2()

As there is no sheet specified in the code, it will run on the sheet that is currently visible if you run the code from the excel ribbon, or the last sheet that you looked at if you run it from the vba editor.

I've included the code below with the edit that works fine for me, if this does not work for you then it implies that there is something with your workbook (not your version of excel) that is causing the problem.

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
    Call Test2
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
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
It will work the same on any version there is nothing version specific in your code, if there was it would cause a runtime error rather than do nothing.

All I did was edit the code in post 12 as per the guidance that we provided, then enter some random text into column A of a blank sheet with some of them containing strings from your array.
The bold font and blue fill were applied to cells containing text from the array and the column width was adjusted as per the code in test2()

As there is no sheet specified in the code, it will run on the sheet that is currently visible if you run the code from the excel ribbon, or the last sheet that you looked at if you run it from the vba editor.

I've included the code below with the edit that works fine for me, if this does not work for you then it implies that there is something with your workbook (not your version of excel) that is causing the problem.

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
    Call Test2
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
Thanks, I appreciate the help, but I literally copy-pasted the code you provided above and it still only resizes the column; it does not shade or make bold the text found. But again, if I only take the first part of the code and use only that by itself, it will do it. It happens with different xlsx files I have tried it on (at least 6 different ones or more I tested it on). Very strange
 

Watch MrExcel Video

Forum statistics

Threads
1,130,073
Messages
5,639,920
Members
417,119
Latest member
adityaj252

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
Top