0

I have an excel workbook with five different recorded macros on five different sheets. Is it possible to create another macro and run those five macros consequentially?

The difficulty is those macros are not specified to each worksheet. I have to run those macros each time manually.

Any thoughts are appreciated! Thank you.

0m3r
  • 12,286
  • 15
  • 35
  • 71
Boomshakalaka
  • 521
  • 1
  • 6
  • 19
  • 1
    By "at the same time" do you mean that they HAVE to run concurrently or is it ok if the macro run sequentially? – GTPV Nov 13 '18 at 15:11
  • Because concurrently isn't, strictly speaking, possible with single-threaded vba... – Mistella Nov 13 '18 at 15:12
  • 1
    It would be easier to have the five different macros in a normal module that reference the five sheets and call them from there. – Darren Bartrup-Cook Nov 13 '18 at 15:12
  • Hi @GTPV, consequentially works for me. – Boomshakalaka Nov 13 '18 at 15:13
  • @Darren Bartrup-Cook, because I recorded those macros for formats, the codes are really big. – Boomshakalaka Nov 13 '18 at 15:15
  • I'd suggest removing the default values that recorded code gives you. Recorded code normally is verbose - putting a border around 2 cells is thirty lines of recorded code, but can be done with `Sheet6.Range("E2:F2").BorderAround ColorIndex:=0, Weight:=xlThin`. Not sure whether the best thing here is to ask you to post your code or describe what your code does. – Darren Bartrup-Cook Nov 13 '18 at 15:22

1 Answers1

3

As explained in this question: Multithreading in VBA cannot be done natively.

Can't be done natively with VBA. VBA is built in a single-threaded apartment. The only way to get multiple threads is to build a DLL in something other than VBA that has a COM interface and call it from VBA.

So running all 5 macros at the same time would require a lot of work.

But OP mentioned in the comment that running all 5 macros sequentially would be an option.

What you can do is:

  1. Add a new module

Add a new module

  1. Add a new public sub in this module

add a new sub

  1. Reference all macro names in this sub

Example of how this macro could look like:

Public Sub allMacros()
    macroName1
    macroName2
    macroName3
    Sheet1.macroNameNotUnique
    Sheet2.macroNameNotUnique
End Sub

Now running this macro will run all the specified macros sequentially.

GTPV
  • 407
  • 3
  • 5
  • Thank you so much. The answer helps me! – Boomshakalaka Nov 13 '18 at 17:28
  • You know, I'd assumed the macros were in `Worksheet` modules which are `Private` so this way wouldn't work - but OP recorded the macros which automatically puts them in normal modules... I should read the questions more thoroughly. – Darren Bartrup-Cook Nov 13 '18 at 17:29
  • Hi@GTPV, I am a little confused about Sheet1.macroNameNotUnique What should be the input for "macroNameNotUnique"? – Boomshakalaka Nov 13 '18 at 19:26
  • @Boomshakalaka glad I could help. If your macros require no input then there should be no input when you call the macros. If some of your macros do not have a unique name then you first need to specify the name of the module where the macro is located. For instance: "module1.macroNameNotUnique". Finally, if your macro requires an input then you just add it after. For example: "uniqueMacroName input1, input2, inputN" – GTPV Nov 14 '18 at 14:33
  • @GTPV Thanks for the reply – Boomshakalaka Nov 15 '18 at 22:34