Declaring Variables Across all Sub Procedures

I am using Microsoft 2010. I am new to Excel Macro VBA. I am still confused about the differences between Sub, Public, Global, Function, Dim, and Set. No idea when to use which.

Currently, I have a lot of worksheets and cells that I want to give a variable or name to.

This is not the actual code, but just an example of what I am trying to do.

Sub Example()

'Define Worksheets

Set sheet1 = Worksheets("Red")
Set sheet2 = Worksheets("Blue")

'Define Cells

Set var1 = sheet1.Cells(1,1)
Set var2 = sheet2.Cells(2,2)

'A long code of procedures follow using this variables.

End Sub

Now, I have another set of procedures where I want to use those same variables without having to define them again.

For example,

Sub Example2()

sheet1.Cells(3,3) = var1+var2

End Sub

I know I am supposed to use "Public" or something, but I keep getting errors or my code doesn't run. The only way it runs is if I redefine those variables under each new procedure "Sub ____()."

Also, I want to define these variables, such that I also don't need to re-define them under "Private Sub" (whatever that is).

Also, how do I set up this forum, so I am not viewing my message via HTML format?

Thanks a plenty!

Why don't you want to redefine the variables?

Why would you need to rewrite code?

Norie, I can't tell if you're being a troll or not.

I don't need to rewrite the code, but I would need to copy/paste all my variable statements under each Sub Procedure. Do you not understand my question?

In order for Sub Example2() to work, I would need to copy/paste the definitions again. Otherwise, var1, var2, sheet1, and sheet2 will not be recognized in the new procedure. I need to define these variables across ALL procedures, so I don't have to keep copy/pasting the same lines under every new procedure.

Sub Example()

'Define Worksheets

Set sheet1 = Worksheets("Red")
Set sheet2 = Worksheets("Blue")

'Define Cells

Set var1 = sheet1.Cells(1,1)
Set var2 = sheet2.Cells(2,2)

'A long code of procedures follow using this variables.

End Sub


For new procedure, I would need to redefine all the variables:


Sub Example2()


'Define Worksheets

Set sheet1 = Worksheets("Red")
Set sheet2 = Worksheets("Blue")

'Define Cells

Set var1 = sheet1.Cells(1,1)
Set var2 = sheet2.Cells(2,2)

sheet1.Cells(3,3) = var1+var2

End Sub


VERSUS just

Sub Example2()

sheet1.Cells(3,3) = var1+var2

End Sub

Please Login or Register  to view this content.

You need a really good reason to do this, though -- inappropriate use of public variables makes debugging difficult, code delicate, and well-structured design impossible.

Troll?

I asked a legitimate question.

You said you had to rewrite your code, which I assumed meant rewriting existing code not adding to existing code.

If you really want to use public variables, which can be a bad idea, then declare them as public in a standard module outwith any other subs.

There might be different ways to do this, something that will keep tighter control on the variables.

For example you could pass the sheets/ranges etc as arguments to the subs.

Please Login or Register  to view this content.