MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

how to preserve variable value between subroutine calls?

  Asked By: Magenta    Date: Nov 02    Category: MS Office    Views: 1953

Is there a way to preserve the value of a variable ("x" for example)
from one subroutine call to the next?
Should I use global variables to do this, or is there another
mechanism that I can use?



10 Answers Found

Answer #1    Answered By: Aalia Arain     Answered On: Nov 02

You probably want something like this...

Sub Test()
Dim x as string

X = "Something"
Call Test2(X)'Pass value to next procedure
End Sub

Sub Test2(str as String)'receiving variable  cannot be same name as
calling procedure, and must be same dimension. i.e. passing string to

Write code here!

End Sub

Answer #2    Answered By: Terence Mitchell     Answered On: Nov 02

There are a number of ways to do this. Are you using a userform? You could
maybe use the TAG property of one of the controls. You could use document
variables as well in the same way. You could also use a "global" variable  as
you've suggested.

You should also pay attention to ByRef and ByVal for parameters. The default
is ByRef and this means that if you change the value of a parameter in a
subroutine then it's changed in the calling routine as well.

What do you actually want to do?

Answer #3    Answered By: Adalwin Fischer     Answered On: Nov 02

Thanks for the response. I'll try to clarify what I'm trying to do.

I'm trying to write a macro that I will link to a button on my mouse.
When the button is pressed, the active cell will jump 3 cells to the
right. But if there are 2 vertically adjacent cells active, it will
jump 6 cells to the right. If three vertically adjacent cells are
selected, the active cell will jump 9 cells to the right. And so forth.
The number of vertically adjacent cells active will modify the jump

But here's the hitch. If only one cell is active, I want the subroutine
to repeat the previous jump size.

To accomplish this, I want my single subroutine  to remember a value "x"
which will store the number of vertically adjacent cells that were
selected in the previous execution. This way, whatever I set my jump
size to be will persist until I change it by selecting a different
number of vertically adjacent cells when I press the button.

Does this make sense?

In summary, I want to create a single subroutine, that remembers the
value of "x" from execution to execution.

Answer #4    Answered By: Skye Hughes     Answered On: Nov 02

From your description, it sounds like a Public or Global variable  will work
fine. Is your macro something like this?

Public Prev As Integer

Sub TEST()
Dim x As Integer
'Set default value for Prev%.
If Prev% = 0 Then
Prev% = Selection.Rows.Count
End If
'Count the vertical cells selected.
x% = Selection.Rows.Count
'If 1 cell is selected, move Prev% * 3 columns
'to the right.
If x% = 1 Then
ActiveCell.Offset(x% - 1, Prev% * 3).Activate
'If more than one cell is selected, move x% * 3
'columns to the right.
ActiveCell.Offset(x% - 1, x% * 3).Activate
End If
'Store the number of vertical cells selected this time.
Prev% = x%
End Sub

Answer #5    Answered By: Funsani Chalthoum     Answered On: Nov 02

This helped a lot. Thanks to all who gave me advice on this project.
Here is my first macro, for everyone to enjoy.

Public JumpSize As Integer
'This routine should be programmed to a button on your mouse.
'It makes the active cell jump to the right. The user can modify the
'size of the jump simply by selecting several vertically adjacent cells
'To reset the size of the jump to 1, select 2 horizontally adjacent cells
'The JumpMultiplier can be used to make the jumps bigger without
'the user having to select a large number of vertically adjacent cells.
'by ./mat/.

Sub JumpRight()
Dim x As Integer
Dim y As Integer
Dim JumpMultiplier As Integer

'Count the vertical and horizontal cells selected.
x = Selection.Rows.Count
y = Selection.Columns.Count

'Initialize JumpMultiplier. The active cell will jump JumpSize *
JumpMultiplier = 1

'Set default value for JumpSize.
If JumpSize = 0 Then
JumpSize = x
End If

'If 2 horizontaly adjacent cells are selected, reset JumpSize to 1
If x = 1 And y = 2 Then
JumpSize = 1

'If a single cell is selected, do not change JumpSize
ElseIf x = 1 And y = 1 Then

'If more than one vertically adjacent cells are selected,
'change the jump size accordingly.
ElseIf x > 1 Then
JumpSize = x
End If

'move Active Cell JumpSize * JumpMultiplier columns to the right.
ActiveCell.Offset(0, JumpSize * JumpMultiplier).Activate
'for testing purposes:
'ActiveCell = JumpSize
End Sub

Answer #6    Answered By: Randall Franklin     Answered On: Nov 02

what did the % after your variables  do?

Answer #7    Answered By: Josie Roberts     Answered On: Nov 02

The % just indicates that the variable  it is attached to is an integer (as
opposed to a string, long integer, double, etc.)

Answer #8    Answered By: Marc Anderson     Answered On: Nov 02

I'm not a pro programmer and started my VBA in humble beginnings. I
asked lots, but I found a few readings very important to start with.

I foond the MSDN and support.microsoft articles to be understandable,
for the most part, but then I've seen lots of code.

I have hundreds of bookmarks for VBA.

Here's good ones for Variable Scope and lifetime:


THe % and all others are explained here.

Answer #9    Answered By: Kiet Jainukul     Answered On: Nov 02

If you use Static instead of Dim when you declare the variable  in your sub,
the value is retained while the workbook is open. It is not visible to other
subs and is destroyed when the workbook is closed.

Answer #10    Answered By: Mae Roberts     Answered On: Nov 02

You need to use private variables  at the module level to hold your

I.e. associated routines are all kept in a module. At the top of the
module - i.e. after the "Option Explicit" and before the first Sub or
Function - you define your long-lived variables as private. They will have
the same lifetime as the module - i.e. for the whole workbook session for
normal modules and the modules belonging to the individual worksheets.

(For class modules, the module variables will have the same lifetime as the
objects created from the class modules. For form modules, IIRC, the life
will extend from when the individual form is first instantiated until it is
finally unloaded, but across successive show/hide sessions. But be careful
with this type of module - you get a "second copy" of everything when you
instantiate a new instance.)

Avoid public global  variables wherever possible - they are simply not good
programming practice. However, module-level PRIVATE variables are good

Didn't find what you were looking for? Find more on how to preserve variable value between subroutine calls? Or get search suggestion and latest updates.