How to store the name of a variable in another variable used to store the return values from a function in another workbook

JaimeArturoB

New Member
Joined
Oct 4, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
I'm coding a menu in Excel VBA that retrieves the name of the workbook, a macro/function/userform on that workbook with iup to 5 arguments to pass in the command line.
These menu entries and code are on a separate and dedicated menu worksheet that can be reused for other applications while the macros and data reside in the other workbooks.

After processing the workbook name from a string variable into a workbook object, the resulting command line executers the remote function with the correct parameters being passed as shown below, where menuwb is the object for the workbook and menuSuborFuncName is the name of the remote function

Application.Run("'" & menuwb.Name & "'!" & menuSuborFuncName, menuArg1, menuArg2)

After a few hours searching everywhere for the correct syntax to equate this entry to the function's return variable, I found the small change needed to get the return value into a variable, as follows:

myReturnvar = Application.Run("'" & menuwb.Name & "'!" & menuSuborFuncName, menuArg1, menuArg2)

However, to keep this dynamic command line working for any menu entry on the table, I need the ability of using that single variable name to refer to the names and types of other variables for the rest of the code to work

Thus, if Menu A entry returns a Boolean named dupFound and Menu B returns a string of names called memberList,, is there a way in VBA to use/redirect/indirect both of those variables to MyRetrunvar.?

The only other option I'm considering is to store the entire command line with the correct variable name, but I'm no sure how to submit that for execution. The menuSuborFuncName, will be still dynamically substituted

dupfound = Application.Run("'" & menuwb.Name & "'!" & menuSuborFuncName, menuArg1, menuArg2)
memberlLst = Application.Run("'" & menuwb.Name & "'!" & menuSuborFuncName, menuArg1, menuArg2)

or I could store the entire separatecommand line item on each menu entry like this
dupfound = Application.Run("'" & WBObjName & "'!" & checkforDups "ABCD", "Sales"
memberList = Application.Run("'" & WBObjName2 & "'!" & getCustList "LA", "Boston"

what would be the syntax to run that command then?

Any suggestions?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,059
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
To be honest, this approach makes no sense to me as you appear to be trying to make a generic code that then has to return a specific type, and I can't see any practical application for that.

If you really need to adopt this method, I'd suggest you just use a Variant for the returned data and then process based on its type but, again, I suspect you're just pushing a problem further down the line.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top