Public or Private Variable

[jo15765]

I have a module and in the Declaration Section I am declaring about 4 variables as Public. This works fine, allows me to pass variables to functions that I call etc etc. The problem that I have now is that I used the same variable name throughout multiple modules. For example in Module 1 I named a variable fileName1, well declaring it Public fileName1 in my Declaration Section is now throwing an ambiguous name detected compile error. To my understanding I need to keep the variable as Public in order to pass the data to the functions I am calling. Do I just need to go through and rename all my variables to different names so that no 2 variables share the same name regardless of what module they are in, or is there another workaround that can rectify this?

I named the variable the same regardless of which module they were in for the fact that I plan to call the functions in numerous modules and just want to pass the variable fileName to the function and have the commands run! I am trying to cutdown on code writing, but I may have totally blown up my coding.

[JBeaucaire]

Yes, I would recommend you keep public variable names unique. The point of the public variable is to not have problems like you're describing, to be able to declare a variable and use it all over the place. If you're declaring filenames for various purposes, indicate that in the variable name

Please Login or Register  to view this content.

[jo15765]

Argh, that leads to my next question then. I have this Public function that I wrote where I would be passing fileName to the function from various modules. Since I can only declare fileName Public in ONE module, how can I set it to be able to re-use this function over and over?

Please Login or Register  to view this content.

Again I declared it Public as that was the only way I knew to pass the fileName to the function. Is there another way for me to be able to pass variables from one procedure to another?

[OnErrorGoto0]

Public variables are available to all modules in a project. If you declare a variable using Dim or Private at the top of a module, then it is available to all routines in that module, but not to other modules.
Here there is no need for a public variable at all (and generally I would recommend avoiding them if you can pass the variable directly)

Please Login or Register  to view this content.

[jaslake]

Hi jo15765

I've read your post several times and come away with several different interpretations. I'm going to respond based on my experience (limited as it is).
Assume you've declared at the VERY TOP of a General Module "Public fileName1 as (fill in the blank...String? Workbook? Whatever)" . If you have this same declaration in a procedure ("Dim fileName1 as (fill in the blank...String? Workbook? Whatever)", the procedure declaration will take precedent and your Public Variable will be "", blank, empty.

If you have the same Variable declared in a procedure, simply eliminate that line of code in the procedure...your Public Variable will then be in effect.

[JBeaucaire]

Based on what I've seen, I wouldn't use PUBLIC at all. I would declare the variables needed in each macro and pass variables from one macro to another explicitly as demonstrated by OnErrorGoto0 in post #4. My only difference is I would move the variables into the Test() macro.

Also, the parameter filename actually matches an inbuilt Excel parameter. Always avoid doing that.

Please Login or Register  to view this content.

[jo15765]

From post # 4 --- I haven't had time to test the code yet, but I was curious as to how fName is going to reference fileName --- I do not see fName declared anywhere?

Please Login or Register  to view this content.

[OnErrorGoto0]

It is declared in the declaration line of the Update_Query function.

[snb]

Do not use functions if nothing has to be calculated/values returned.

Please Login or Register  to view this content.

[OnErrorGoto0]

BTW, you forgot to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[snb]

@onerrorgoto0

Thank you for pointing that out.
I amended the previous code, removing the reference to the variable in that line altogether.

[jo15765]

In my limited knowledge of VBA it looks like the variable at one point is being called "fn" from this line of code:

Please Login or Register  to view this content.

And then switching over to fname in this line of code:

Please Login or Register  to view this content.

[snb]

Read more on the difference between variables (in this example fn) and arguments ( in this example fName)

[jo15765]

Will do, googling now

[jo15765]

So that leads me to another question.....If I wanted to use the Sub Update_Query with another module

Please Login or Register  to view this content.

Would I need to declare my variable in the other module as fn as well so that I can re-use this?

[JBeaucaire]

No, the Update_Query sub can be called from anywhere since it isn't private. Just be sure in all macros that call it you call it with the needed parameter, too.

Please Login or Register  to view this content.

[jo15765]

I don't think I understand your example. Let me elaborate a little more on what I am trying to do with these modules...I want to use Update_Query as a template, and be able to call Update_Query from multiple modules, and be able to pass fileName from each different module to Update_Query. So for Sub_1 I would need to pass the varaibles Fire and Ice to Update_Query, but for Sub_2 I need to pass variables Liquor and Rum. Do I need to name my variables fileName in each module so that I can re-use?

Please Login or Register  to view this content.

[JBeaucaire]

IN both of your examples you failed to give the feed the parameter to the Update_Query when you called it. Note in my examples I highlighted in red the "string" I was feeding to the Update_Query? That "string" becomes the fName argument in the Update_Query when it runs.

[jo15765]

Since I want to pass whatever the value of fileName is, if I change my coding to this it will pass the variable:

Please Login or Register  to view this content.

[JBeaucaire]

I need to repeat my earlier warning. You're using a variable name Filename which exactly matches one of Excel's builtin parameters. I would not recommend you keep doing that.

Please Login or Register  to view this content.

[jo15765]

Oh I missed your earlier warning, I will def change my variable from fileName. Other than that warning was the syntax of my above coding correct? Also, when I call a sub or a function it forces me to put the quotation marks around the sub or function name. So my coding has to be:

Please Login or Register  to view this content.

And from there I can't figure out how to add the variable to the end w/o getting a compile error?

[jo15765]

I am not sure what I did different, but I walked away from it and came back and now I am able to use the Call [procedureName]

Weird!

Thanks to all for the help in passing variables!