First of all, from your description it sounds like the Function you are calling should really be a Sub. The purpose in life of a Function is to return a value. If you are invoking it with a Call statement, then you are not doing that. So in my description below I am going to refer to them both as Subs.
Second, using Public variables will certainly work, but this creates what is called common environment data coupling. This is a form of tight coupling that is best avoided. This was a concept first articulated by Yourdon in the late 1970s and quickly embraced by the software engineering community. There are a few reasons for this, primarily that it is difficult to diagnose bugs involving common data, and easy to introduce new bugs when new code refers to common data.
The textbook method is to simply add three arguments to the Sub you are calling. The lower-level Sub assigns values to the three strings, and they are returned to the caller. It is important to understand that in VBA, arguments are passed by default by reference. That's why this solution works. That is, when a Sub is called, and it refers to one of its arguments, it is referring to the exact same location in memory as the variable used by the caller. (There is an option to refer to an argument by value. In this case, the lower-level Sub makes a copy of the variable and works on its copy, and doesn't touch the location owned by the caller.)
This is sample code (compiled but not executed):
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks