How do I get an Excel Macro to return a string to another macro?

Question

I'm an Excel VBA beginner. I'm writing a macro (call it A) that calls another macro (B). When B is finished, I want it to return a string to macro A. How would I go about doing that?

Answer 1

By making B a function in this form:

Function Bmacro(InputParm1 As Long, InputParm2 As String) As String

...

...

Bmacro = "The string you want to return or the name of a variable containing the string"

End Function

The value Bmacro (the function name - different for every function) must be set somewhere in the macro or it will return a zero length string (for a String data type).  Good coding practice is to always set the value in the last statement for the macro.  You can have as many or few input parameters as you need.  If the As datatype is missing the function will return a variant.  Is is always wise to specify the data type (Integer, Long  Date, Variant, etc.) even if you want to return a variant.

The code in A would look like

sValue = Bmacro(15, "SomeString")

to place the value returned by the function Bmacro into and the string variable sValue.