ExcelBanter - First Aid For Macros

You can have multiple procedures in a single module. If you are using the
recorder, it tends to record in a new module each time you re-open the
workbook and start recording. You can always copy the code to an existing
module and then delete the new one when you are done. The advice given here
is that generally, a module should not have a size larger than around 64K.
(you can see by exporting the module and seeing what the file size is - you
export similar to deleting, except you choose export specifically rather
than delete).

In a single procedure, you should not Dim the same variable more than once.
Excel will notifiy you if you do, so I would assume that while you may have
dim'd it twice, it was probably in different procedures (subroutines or
functions). A variable declared within an individual procedure is local to
that procedure so you can have multiple procedures use identical variable
names, as long as you recognize that these are separate variables.

Sub Macro1()
dim Target as Range
set Target = ActiveCell
Target.Clear
End Sub

Sub Macro2()
Dim Target as Range
Target.Formula = "=Sum(A1:A10)"
End Sub

If you ran macro1 and then macro2, expecting the activecell (target) to get
the formula =Sum(A1:A10), you would be disappointed. In Macro2, the
variable target is not related to the variable target in Macro1. In fact,
since target is never set to a range in Macro2, it would raise an error when
an attemt is made to assign the formula string to the formula property.

You can get more information on this by looking up the "Scope" topic in
Excel VBA help.

I can't say what is causing a problem with not being able to put in another
msgbox. But the error of invalid outside a procedure generally is because
you have something like

Target.Value = "aBCD"

Sub Macro1()
' some code here
End Sub

The assignment statement outside a procedure can cause that error. In the
very top of a module you can declare variables that have module level scope
or project level scope

Dim MyTarget as Range ' module level scope
Public AnotherTarget as Range ' project level scope

Sub InitializeMyTarget()
Set MyTarget = ActiveCell
End Sub

Sub InitializeAnotherTarget()
Set AnotherTarget = Worksheets("Sheet2").Range("B9")
End Sub

Sub Macro1()
Dim Target as Range
InitializeMyTarget
InitializeAnotherTarget
MyTarget.Value = "MyTarget " & MyTarget.Address
AnotherTarget.Value = "AnotherTarget " & AnotherTarget.Address
set target = MyTarget.Offset(10,10)
Target.Value = "Target " & Target.Address
Target.Value = AnotherTarget.Value
msgbox Target.Value & vbNewLine _
& MyTarget.Value & vbNewLine _
& AnotherTarget.Value
End Sub

Here we see that Macro1 can see the variables MyTarget and AnotherTarget
because they are within scope (Even though they are declared elsewhere). I
could change them here by adding code to set them to a new range

set MyTarget = MyTarget.Offset(3.1)

Although if I ran Macro1 again, it would reinitialize MyTarget to the
activecell. So look over the help on scope.

Anyway, you should be able to put in as many msgbox commands as you wish.

--
Regards,
Tom Ogilvy

"Chuckles123" wrote in message
...

Tom,

I did as you suggested; I had a bunch of empty Modules. I now have 13
Modules and 13 Macros -- is there supposed to be a one-to-one
relationship? I have noticed that the workbook saves faster now.

I apologize for my sloppy wording the Dim lines. What I meant to
say was, in two separate sections of my code, I had 'Dim Target as
Range' lines; under each of these lines, I had two lines of code (the
Target.Formula and Target.Value lines). I was just trying to make the
point that I had a total of 6 lines using the word 'Target'; a minor
amount of coding.

Is it possible that all of the empty Modules and/or the Dim lines had
anything to do with the problems that I had?

Thanks for a response,
Chuckles123

--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile:
http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=266313