• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to pass variable values between sub procedures?

J

jbaich

Member
Hi all! I am working on a macro and I want to be able to pass the values of severable variables (these are mostly file paths... so variables are As String) from the first Sub into the other Subs in the module. I thought if I defined them as Public at the beginning, that would do the trick, but it seems like that does not pass any stored values for the variables.

I've tried placing the variables as arguments between the brackets of the second Sub and that works, but only if I call the Step_2 Sub from the first Sub, which isn't ideal as I don't want the Step_2 Sub to start immediately... I would like the User to be able to initiate the Step_2 Sub whenever they are ready and just have the variables from Step_1 available in Step_2 (and additional subs in the module) so that I don't have to redefine them for each Step.

Can anyone shed some light on what I'm doing or not doing and why?

Code: 
Option Explicit

Public FldrRoot As String
Public AMDExt As String
Public PAABMgmt As String
Public Jones As String
Public FRep As String
Public CYPVS As String
Public PYPVS As String
Public INDatt As String
Public IMA As String

Sub Step_1()

'//Does some work and ends with...

  MsgBox ("Step 1 is complete! Please save required reports in the designated locations and click the next Step")
  
    Step_2 AMDExt, PAABMgmt, Jones, FRep, CYPVS, PYPVS, INDatt, IMA

    End Sub
  
Sub Step_2(AMDExt As String, PAABMgmt As String, Jones As String, FRep As String, CYPVS As String, PYPVS As String, INDatt As String, IMA As String)

''?? I would like to send these variable values from Step_1 sub to steps 2 and 3 subs, but i want the user to initiate these subs when they are
    ' ready by clicking a button in the workbook.

Thanks,
Joe
__________________________________________________________________
Mod edit : post moved to appropriate forum …​
 
Hi jbaich,
why not store the values in a worksheet, and refer to them in your other macros?
Kind regards
 
Hi Joe ,

Any variable which needs to be used by several procedures within a project is to be declared in a standard code module , using the keyword Public. This variable can then be read from / written to by any procedure within the VBA project.

Narayan
 
Thanks Stevie, I may end up having to do something like that... I'm trying to learn VBA right now and I guess I'm really just trying to understand scope and exactly how variables work... I thought I understood the concept to be similar to what Narayank991 just posted, but I think I must have been mistaken... My interpretation now is that the variable itself can be made available as a defined variable (ie Variable As String) but you cannot make the 'Value' of this variable defined in a Sub (ie. Variable = "Hello World") available in other Subs...? The value "Hello World" gets erased when the first sub ends, but the variable defined as type string would be available with no value for other subs in the module if it is declared as Public in the first Sub? Is this how it works?

Thanks,
Joe
 
Hi Marc, that sounds like what I want to do, but I'm not quite getting it... :(

I am trying to define the variable CYPVS as follows...

Code: 
Static CYPVS("This is a test") As String

But I keep getting a Type mismatch error...

In real life, I'm hoping to be able to assign a file path to that variable, so that's why I was trying to declare it as a String type...

I have also tried moving the text out of the brackets such as
Code: 
Static CYPVS As String = "This is a test"
, which gives me an expected end of statement error and the = sign is highlighted...

I also am curious if you still need to make something Public to use the static variable in other Subs?

Your advice would be greatly appreciated :)

Thanks,
Joe
 
Perhaps what you are after is Const key word.
e.g
Code: 
Private Const x As String = "Hello", y As String = "Bye"

Sub test1()
    MsgBox x & " Good" & y
End Sub

Sub test2()
    MsgBox "Good" & y & " " & x
End Sub
However, I would avoid use of global variable as much as possible.
e.g
Code: 
Sub main()
    Const x As String = "Hello", y As String = "Bye"
    test1 x, y
    test2 y, x
End Sub
Sub test1(str1 As String, str2 As String)
    MsgBox str1 & " Good" & str2
End Sub
Sub test2(str1 As String, str2 As String)
    MsgBox "Good" & str1 & " " & str2
End Sub
 
Hi Joe ,

Two attributes are associated with code variables , Scope and Lifetime , though in general these two are related.

Any variable declared using Dim within a procedure is restricted in scope to that procedure , for the lifetime of that procedure. This means that it cannot be accessed by any other procedure , and it will cease to exist once the procedure has completed executing.

Variables which are passed as parameters between procedures / functions serve a different purpose ; see these links for more details :

http://www.globaliconnect.com/excel...rameter-arrays-paramarray&catid=79&Itemid=475

http://www.cpearson.com/excel/byrefbyval.aspx

Variables which are declared using the Public or Private keyword are modified with regard to their scope and lifetime ; see this link for more details :

http://www.thespreadsheetguru.com/blog/2014/3/5/explaining-private-vs-public-declarations

Narayan
 
Well I have no idea how or why, but it seems all you need to do is use "Let" in front of the Variable when you define it... I didn't see anything about this in any of the Microsoft articles or "Help Files" I read... If anyone can explain what's going on in the code below and how or why it works, I would very much appreciate it.

Oh and what I mean by "works" is that after I used
Code: 
Let CYPVS = "This is a test"
excel remembered the value ("This is a test") of this variable in each of the following test Subs... So, I guess this is not a static variable or a constant, is it just a Public variable?

Code: 
Option Explicit
Public CYPVS As String


Sub Test()

Let CYPVS = "This is a test"

Range("A1").Value = CYPVS

End Sub

Sub Test2()

Range("A3").Select
Selection = CYPVS

End Sub
Sub Test3()

MsgBox (CYPVS)
End Sub
 
Hi Narayan, jindon and Marc... sorry about the choppy feeling response, I'd written it before i'd seen your responses, but it seems to have been delayed being posted so it came in after. I think what i'm after is just a variable that behaves like a constant... in the first sub, the user defines a file path that remains constant and is referenced throughout the remaining 3 sub routines that follow (all in the same module)... The link Narayan posted last was a bit over my head, and I didn't really get much out of that one, but the rest seem to support my initial thought that all I was trying to do was define a public variable, who's value (file path) could be accessed by other subs in the module... but just writing public and declaring it between the option explicit and sub () line did not seem to store the value (file path) for the other subs... so at this point, i have no idea anymore if the file path i'm trying to define as a variable which is available to all subs in the module is a constant or a variable or what... Also, what is the difference (in terms of syntax) that makes a variable global vs public? and what does it mean that writing "Let" in front of the variable definition (declaration?) makes it function the way I was hoping that writing public in front of the declaration(?) would have on its own? Does writing "Let" make it global?

Thanks,
Joe
 
i should maybe also mention that the user chooses the file path using folder picker in the first Sub, so the path can't be defined until the user has chosen... if that means anything... this is why i didn't think i could make a constant because i thought i'd have to have the file path predetermined before the first Sub had even begun...?

Thanks,
Joe
 
before i used it, the values were "erased" after the first sub ended... after i used it, they were not... that was the only thing i changed in my trials and errors...?
 
i see from that link what you mean about "let" being optional, so i can't explain why it only worked after i used it... I've been trying to figure this out for almost a week and this is the first time it ever "worked"...
 
Let/Set
Let is used for assigning value to the variable that is not object.
Set is used for setting reference to the object.

In Excel VBA, you can omit Let keyword so
Code: 
CYPVS = "This is a test"
is good enough.

Global/Public

Global include all the variables which can be used from various procedure.
When declared as Public, you can refer it from any procedure from any module.
Whereas Private can only be referred from the procedure within the module that is written. (Good for capsuling purpose)

Just try
1) Module1
Code: 
Public x As String

Sub g()
    x = "abc"
End Sub
and in Module2
Code: 
Sub t()
    Call g
    MsgBox x
End Sub
You can call "x" from Module2, However, if you change the variable from Public to Private, you can not call it from Module2.

These days there are many people who don't declare variables within a procedure and you should be very careful about it.

If the variable names are accidentally the same as Public variable, it may destroy your project.
 
Hi Joe ,

After all this interaction by several members , I am nowhere near understanding what you want to do.

If you can explain exactly what you want to do , using say 3 variables A , B and C , to be used in procedures P , Q and R , everything can be explained to your satisfaction.

Which variable do you want to use in which procedure , and in what manner , read-only , write-only , read-write ?

If you can explain all of this clearly , you would get an answer in exactly two posts , one for the problem description and the other for the solution.

Narayan
 
Hi Narayan, i'm sorry for the confusion... i think this should be really, really simple, i don't know why i've been having trouble with it... I will try to explain as clearly as i possibly can...

The module i'm working on contains 3 sub routines; Sub 1(), Sub 2() and Sub 3(). In the first Sub 1(), i want to declare variables that will be available for Sub 2() and Sub 3(). These Variables will be file paths, which will be the result of the user selecting the folder locations as a result of the msofiledialoguepicker application. So for example the code is intended to look like...

Code: 
Option Explicit
Public FolderA as String
Public RootFldr as String

Sub 1()

'user chooses location of root folder which is stored as variable RootFldr i.e. C:\desktop\test\

FolderA = RootFldr & "FolderA"   ' the desired result is: FolderA = C:\desktop\test\FolderA

End Sub

Sub 2()

Dim FileName as String

FileName = "File1.xlsx"

Workbooks.Open (FolderA & "\" & FileName)

'The desired result is open C:\desktop\test\FolderA\File1.xlsx

End Sub


I just want the file path defined above as a variable to be available in this Sub 2 and Sub 3 so that anytime i use the variable FolderA, excel translates that into the value from Sub 1()... full file path C:\desktop\test\FolderA


That's it... should be super easy... only, the variables have been resetting to zero at the end of Sub 1() unless, as I just discovered today, I use "Let" before the variable name when i define it... so...

FolderA = RootFldr & "FolderA" would reset at the end of Sub 1()
Let FolderA = RootFldr & "FolderA" would not reset... I don't know why

I am new to VBA, trying to learn, currently in the Chandoo VBA Classes and going through the lessons, thought I understood the concepts of variables and scopes, but then could not for the life of me get this to work... So i thought I must be misunderstanding something... I feel like understanding variables and scopes is pretty important for learning the rest of everything VBA...

I don't have access to my project at the moment, I will provide a more specific example of a piece of code in Sub 2() that tries to reference the variable from Sub 1() tomorrow if you think that would help make this more clear, but the problem simply is that the variables reset after sub 1() and referring to them in later subs gives an "undefined" error, hence multiple attempts to "pass" them through various other methods to the later Subs...

As always, your advice is very much appreciated!

Thanks,
Joe
 
Hi Joe ,

Run the procedures Test1 and Test2 in the code given below , in the following order :

Test2

Test1

Test2

and see what is displayed in the Immediate window.

This code can be placed in any of the following :

section pertaining to any Sheet

section pertaining to ThisWorkbook

section pertaining to any Module

Code: 
Option Explicit
Dim FolderA As String
Dim RootFldr As String

Sub Test1()

'user chooses location of root folder which is stored as variable RootFldr i.e. C:\desktop\test\
RootFldr = "C:\desktop\test\"
FolderA = RootFldr & "FolderA"  ' the desired result is: FolderA = C:\desktop\test\FolderA
Debug.Print Application.VBE.ActiveCodePane.CodeModule.Name, FolderA
End Sub

Sub Test2()

Dim FileName As String

FileName = "File1.xlsx"
Debug.Print Application.VBE.ActiveCodePane.CodeModule.Name, FolderA

'Workbooks.Open (FolderA & "\" & FileName)

'The desired result is open C:\desktop\test\FolderA\File1.xlsx

End Sub
Narayan
 
Hi Narayan, every it tries to run the Debug line in either sub it gives me a Run-time error '1004: Method of 'VBE' of object'_Application' Failed.

So I tried inserting the 'With Application.FileDialog(msoFileDialogFolderPicker)' code that I'm using to define the Rootfldr variable, but that didn't change anything... However... The variables seem to be functioning now as I'd hoped and expected they would... even when I remove "Let" from in front, I can still see their values when I hover over them in other Subs. I have no idea, why they are behaving as expected now, but hadn't been before... it's very strange. And since I'm only using these variables in Subs stored in a single module, I can declare them as Dim instead of Public, but still keep them between the Option Explicit and the Sub() lines, correct?

Thanks everyone for all your help! Sorry this kind of turned out to be a wild goose chase.
 
You must log in or register to reply here.
Back
Top