How to use a single variable in multiple subs in a macro in excel VBA

Question

0.00/5 (No votes)

See more:

Hi All,

I have a macro that is divided into 2 subs.

The user interface has 2 buttons, each assigned to a sub.

My query is in the first sub, there is a calculation, suppose it gives x=2. In the second sub i need to check the value of x.

I can't call one sub from another as they work from different button click.

Edit:
On first button click, user will browse files and the file count will be stored in x.
On second button click, there will be a check if x > 0.
User can wait for sometime after clicking first button. Or can change the no of files.

So subs are exclusive of each other except the variable x.

How to do that??
Thank you..!!

What I have tried:

1. "Dim x As Integer"
outside all subs. But it does not retain the value.

2. I tried to use function but that did not get me anywhere!!

Posted 29-Jun-17 23:48pm

planetz

Updated 30-Jun-17 0:51am

v2

Add a Solution

Comments

Patrice T 30-Jun-17 6:56am

Show the code !

planetz 3-Jul-17 1:20am

dim fcount as integer

public sub browseFiles()
'browse files and count no of files
fcount=fcount+1
end sub

public sub runMacro()
if fcount >0 then
'do something
end if
end sub

**the two subs run on two different button clicks

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-06-30T00:00:00

If you want to define global variable, insert new module and declare it this way:

VB

Public xSharedValue As Integer

But...

You can call one sub from another:

VB

Private Sub Button1_Click
    'your code here
    '....
    
    'call another sub/procedure
    DoSomething(x)
End Sub

Private Sub DoSomething(ByVal x As Integer)
    'your code here
End Sub

If a DoSomething procedure is in a different module, you have to change its declaration to:

VB

Private Public Sub DoSomething(ByVal x As Integer)
    'your code here
End Sub

For further details, please see: Scope of variables in Visual Basic for Applications[^]

F. Xaver · Answer 2 · 2017-06-29T23:55:00

Solution 1

outside a sub/function its not Dim .. use Private or Public for your field.

anyway,.. sure u can call another sub/function

Private Data As Integer

Public Sub a()
    Data = 5
    Call b
End Sub

Public Sub b()
    MsgBox "Data is: " & Data
End Sub

Posted 29-Jun-17 23:55pm

F. Xaver

Comments

planetz 30-Jun-17 6:48am

Thanks for your support..!! But the requirement is not to call another sub from a sub. They should run on button click by user.

F. Xaver 3-Jul-17 3:06am

you can still call a and b with buttons... that call was just an example for that variable, not loosing its data.

Member 14820041 2-May-20 16:11pm

thank you so much!
Was stuck in a similar scenario !

How to use a single variable in multiple subs in a macro in excel VBA

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How to use a single variable in multiple subs in a macro in excel VBA

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us