Global Variables across multiple subs

[scottleger4]

I'm having trouble with dimensioning variables to be used across multiple subs. I'm trying to keep the length of the individual subs as short as possible, and currently the only working version requires me to run the dims in every sub. In its simplest form...

Sub Master
RunSub1
RunSub2
End Sub

Sub RunSub1
Dim Variable1 As String
Dim Variable1Entry as Range
Set Variable1Entry = Sheet.Range("A1")
End Sub

Sub RunSub2
Do Until Variable1Entry.Value = ""
...
...
...
Set Variable1Entry = Variable1Entry.Offset(1, 0)
Loop
End Sub


Everything seems to work fine until VBA jumps to the "Do Until" and then debugs saying "Object Required" which I assume means the previous dim I did for that value isn't 'sticking.' There are currently about 30 variables I need to use, so copying those to the start of every sub would make my code REALLY long. I'm sure it is something pretty simple that I just can't come up with. Thoughts? Thanks in advance.

 

[Fluff]

You need to declare your variables a Public & they need to go at the very top of a regular module (before any code)
Like

Option Explicit
Public Variable1 As String
Public Variable1Entry As Range

Sub Master()
RunSub1
RunSub2
End Sub

Sub RunSub1()
Set Variable1Entry = sheet.Range("A1")
End Sub

Sub RunSub2()
Do Until Variable1Entry.Value = ""
...
...
...
Set Variable1Entry = Variable1Entry.Offset(1, 0)
Loop
End Sub

and make sure that they are not re-declared in any of the code

 

[scottleger4]

So, I did as you said, but am now getting a debug "Compile Error" when is gets to a "Set" command. Do I have to "Set" each range in the individual macros each time?

 

[Joe4]

You would use "Set" to set any objects, like ranges.
But you do not use the "Set" keyword when setting string, numeric, or boolean variables.
So you would not use "Set" in setting the "Variable1" value.

 

[scottleger4]

Sorry if I'm just asking a dumb question, but I'm not sure I understand what you mean. In my code, I use "Set" as...

Dim Variable1 As String
Dim Variable1Entry as Range
Set Variable1Entry = Sheet.Range("A1")

...so that when my loop finishes doing what I want it to do, it moves down the list until there is no more data. Do I have to use the line...

Set Variable1Entry = Sheet.Range("A1")

...in the individual subs, or can I specify those at the top like I did all the Publics? If needed, I can post the entire code, but it's long. =)

 

[Joe4]

Sorry if I'm just asking a dumb question, but I'm not sure I understand what you mean.

My comment would have to do with the other variable "Variable1".
What does the section of code that sets that value look like?

Also, you can declare any public values at the top outside of procedures, but when setting their values, those lines of codes must be within some procedure (cannot occur outside of the procedures).

 

[Fluff]

You only need to set the variable once, but in this line

Set Variable1Entry = sheet.Range("A1")

what is "sheet", it has not been declared or set?

 

[scottleger4]

@joe...thanks, I see what you are saying and that makes sense.
@Fluff...the sheet is properly set in another line I didn't include here. I was just hammering out that particular text for the sake of simplicity, no problems there.

Thanks for all the help. If I run into any other issues here I will post another reply.

 

[scottleger4]

Following up here...I think I might have done something wrong as I'm getting a new problem after adding the Public statements at the top. Getting a "Compile Error: Variable not defined" that debugs on the "For Each x" in this sub...

Sub Upper()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Loop to cycle through each cell in the specified range.
For Each x In Range("F6:N105")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

This isn't a Sub being called on in the macro I was trying to run, and if I get rid of it altogether (no big deal) I get the same Compile error on a different variable in a different sub...rinse and repeat. Didn't have any issues with those prior to making the global/public changes. Thoughts here? Thanks in advance.

 

[Joe4]

Do you have the "Option Explicit" line at the top of your VBA code?
If so, you are required to declare ALL the variables that you are using.
Have you declared "x" anywhere? If not (and it is not a public variable), just declare it right in the procedure itself.