Correct way to get Function's output into macro?

[Ed]

Thanks to someone whose name I have forgotten, I have a Function that
returns a folder name and puts it into a string variable. I call the
function in a macro to allow me to browse to a specific folder. At the
moment, the only way I have of getting the string back into the macro is to
declare the string variable at the beginning of the whole module. I'm aware
this isn't best practice, although it works. But how else do I get the
string from the function into the macro?

Ed

 

[Jay Freedman]

Thanks to someone whose name I have forgotten, I have a Function that
returns a folder name and puts it into a string variable. I call the
function in a macro to allow me to browse to a specific folder. At the
moment, the only way I have of getting the string back into the macro is to
declare the string variable at the beginning of the whole module. I'm aware
this isn't best practice, although it works. But how else do I get the
string from the function into the macro?

Ed

Hi Ed,

Put the declaration of the string variable inside the procedure where
you call the function. The function itself doesn't need to know about
the string.

Think of it this way: If you call a cement company to deliver a yard
of concrete, they only care that there's a hole to dump the stuff
into. You have to get a permit from your town government to dig the
hole, but you don't need a permit from the town that contains the
cement plant. Well, the function only cares that there's someplace
in memory to dump the value of the string, but the declaration of that
location (variable) doesn't have to be visible inside the function.

In code,

Public Sub MyMacro
Dim myString As String
myString = FolderBrowser()
' do something with myString
End Sub

Private Function FolderBrowser() As String
' some magic
FolderBrowser = <selected folder's name>
End Function

Somewhere inside the function (usually as the last thing), the result
of the function is assigned to the function's name. Back in the
calling macro, that value is assigned to the string variable and then
used for some purpose. The string variable should be declared inside
that macro. The only exception is when there's a need to use the same
value in more than one sub; and even then there are usually other,
safer alternatives to declaring the variable at module scope.

 

[Ed]

Thank you, Jay! I got it now!

Ed

Hi Ed,

Put the declaration of the string variable inside the procedure where
you call the function. The function itself doesn't need to know about
the string.

Think of it this way: If you call a cement company to deliver a yard
of concrete, they only care that there's a hole to dump the stuff
into. You have to get a permit from your town government to dig the
hole, but you don't need a permit from the town that contains the
cement plant. Well, the function only cares that there's someplace
in memory to dump the value of the string, but the declaration of that
location (variable) doesn't have to be visible inside the function.

In code,

Public Sub MyMacro
Dim myString As String
myString = FolderBrowser()
' do something with myString
End Sub

Private Function FolderBrowser() As String
' some magic
FolderBrowser = <selected folder's name>
End Function

Somewhere inside the function (usually as the last thing), the result
of the function is assigned to the function's name. Back in the
calling macro, that value is assigned to the string variable and then
used for some purpose. The string variable should be declared inside
that macro. The only exception is when there's a need to use the same
value in more than one sub; and even then there are usually other,
safer alternatives to declaring the variable at module scope.

 

[Jeff]

Set the value of the function on its last line

Sub Test()
Dim FunctionResult as String
FunctionResult=Myfunction("MyParam")
End Sub
Function Myfunction(param) as string
Myfunction=SomeManipulationOf(param)
'SomeManipulationOf is pseudo code if that's not obvious!
End Function