Passing arguments to a macro in another file

**abhimanyut** · 06-05-2009, 03:49 AM

Hi,

I use the following macro "modifystri" in a workbook "Modify" to pass a variable to a macro ("macro1") located in a sheet ("Sheet1") in another workbook ("Test.xls")

Please Login or Register  to view this content.

The definition of macro1 located in workbook "Test.xls" is:

Please Login or Register  to view this content.

The value of stri after calling "macro1" is still empty. I want to use "macro1" to modify the contents of stri. Please help.

**Andy Pope** · 06-05-2009, 05:25 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Couple of things. The value of stri is passed rather than the variable so you have nothing via which to return a value.

Instead of a subroutine use a function to return a value.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The other problem is that the function does not return a value if it is within a sheet module. So either move the function to a standard code module or write a new function in a code module within the workbook that uses the sheet level code.

**abhimanyut** · 06-05-2009, 05:51 AM

Thanks for the suggestion. But aren't arguments by default passed by reference in VBA? In which case, the macro should modify the value.

Also, I wish to pass more than one strings to the macro and modify them. I know I can concatenate their modified values inside the function and return the single result string, but is there any way I can pass these variables to a macro residing in a different workbook?

Thanks for the point regarding not putting functions in a sheet module!