"Run" code causing macros to run twice and/or stopping

[Jim A]

Hi - I have been building some macros that are to be run within other macros.
Weird stuff was happening!

I used the "Call" command instead of "Run." Everything finally worked
perfect!!

Being new to VBA I do not know the reason behind the "Run" command being
different and causing my problems. BUT "call" sure is the answer to my
recent irritations.

After I got the darn things to finally work, I decided to do the responsible
thing and use Option Explicit, which helped me clean up my code tremendously!!

 

[Shane Devenshire]

Hi,

First, unless you have a reason to use Call or Run you can use the name of
the macro without either of these:

Sub Main()
Welcome
Sort
Report
End Sub

This would run the Welcome, Sort and Report macros one after the other.

I'm not sure what you question is, but to fix code we need to see it.

Option Explicit just means you need to declare all variables used in the
code. It would seem that in general this would have no effect on using Call,
Run or the above approach.

 

[Chip Pearson]

I never use Call, but with Call you must provide the name of the
procedure to run when you write the code. E.g.,

Call ProcABC

It cannot be changed to another procedure at run time. With Run, you
can build up the procedure name at run time and then call it. E.g.,

Dim S As String
S = "Proc"
S = S & "ABC"
Application.Run S

Another distinction between Call and Run when working with code in two
(or more) workbooks. Suppose the procedure "ProcABC" resides in Book2
and you want to call that from code within Book1. For Call to work,
Book1 you would have to establish a reference (VBA, Tools menu,
References) to Book2. Then Call will work. With Run, you need not have
a reference between the two workbooks. You just include the workbook
name in the string to Run. E.g.,

Application.Run "[Book2.xls]!ProcABC"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)