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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
11,521
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
11,521
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
11,521
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,118,521
Messages
5,572,628
Members
412,475
Latest member
JaredNAU
Top