Can I keep varibles set in one proc. to use in another?

VBnovice

New Member
Joined
Nov 7, 2005
Messages
17
Hi, I have a problem where a procedure has got too large (+2000 lines) and on splitting it I need to have variables set in the first sub used in the later subs. I have been trying to tie them together by having one proc calling the parts in sequence but the variables don't follow on. any help appreciated. :rolleyes:
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Declare them as public outside of the module.

e.g.

Public my_variable as integer

sub first
my_variable = 5
call second
end sub

sub second
msgbox my_variable
end sub

running sub "first" will produce a message box displaying 5.
 
Upvote 0
Welcome to MrExcel - there are a couple of things you can do.

You could pass variables from one macro to another --

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FirstMacro()
<SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
x = 50000
y = 7
<SPAN style="color:#00007F">Call</SPAN> OtherMacro(x, y)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> OtherMacro(x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>)
MsgBox x
MsgBox y
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Or you could define the variables as Public

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Public</SPAN> y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> FirstMacro()
x = 50000
y = 7
<SPAN style="color:#00007F">Call</SPAN> OtherMacro
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> OtherMacro()
MsgBox x
MsgBox y
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
You can change the scope of the variables from procedure level all the way up to global level (this skips the intermediate scope of module level, where the variable would be visible to all procedures within a module, but invisible to other modules).

You use the same declaration syntax as the Dim statement in a procedure, but you replace Dim with Public, and move the statement into the declarations section of the module.

The one cautionary note is that you should limit the use of the global variables 1)because they require more memory and 2)because it can become difficult to keep variable names and value changes straight.
 
Upvote 0
In the 1st line of the Module not the Sub: Define your Var. You can then use them anywhere, as long as the application is open. When you close the application all values are lost

Public myVar As Long


Or load the value to a Cell off to the Right or on a Hidden Sheet, these values can be re-set by code or left as is, where if the application is saved then closed and re-opened, you still have all your values:

To load:

Sheets("Sheet4").Range("AA5").Value = myValue


To retrieve:

myValue2 = Sheets("Sheet4").Range("AA5").Value
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top