Making Calls to Global Routines

[Finlay Miller]

I'm writing a program that involves a lot of the same kind of loop, just with one small variable changing each time. Is there a place where I can code the loop once, and declare a string that represents the value that changes each time, then, in a form subroutine, declare the string variable to what I want it to be, then make a call to said loop? What's the code I'll need to make a call to a loop? Say, "Col_Finder" was the loop name, and how would I get to the place where I could code that loop, and where is it?

Thanks

[xladept]

Hi Finlay Miller,

What you want is called passing variables and the call can be for a function or subroutine

Cells(k, Col_Finder("Company")

Please Login or Register  to view this content.

or with a sub-it could be: Call Col_Finder("Company")

Please Login or Register  to view this content.

[Leith Ross]

Hello Finlay Miller ,

Welcome to the Forum!

For a Function, Sub procedure, or variable to be Global in scope (known as Public in VBA), it must be declared in a standard VBA Module in your workbook's VBA Project. In Office 2007 and 2010, you must also change your Trust setting level to allow macros to run.

It would be better to pass the string as an argument to the Sub or Function rather than declaring it separately. It is more efficient and will remove the possibility of the global variable not containing the correct value. Here is an example that selects the first cell of the given column letter.

Please Login or Register  to view this content.

Changing Trust Center Settings in Excel 2010

[tlafferty]

I think this will do the trick. If you declare your variable at the top of the module before any subroutines, it will make it globally available to every sub in that module. You then make calls to your sub that handles the loop, and pass in and out the value of the variable.

Please Login or Register  to view this content.

[Finlay Miller]

That's great, thanks!

One last question: What does the code in-between the brackets do?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks!

[xladept]

Between the brackets are the arguments (just one argument here) - it's called passing variables, and it lets the routine know what it's working on.

[Finlay Miller]

Thank you all so much, this really helped!