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
 
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
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
Your code in post 12 works fine for me when adding the line Call test2
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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