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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

F. Xaver · Answer 1 · 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 !

Maciej Los · Answer 2 · 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[^]