Function that returns multiple values?

[ahartman]

Hi all,

I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:

Please Login or Register  to view this content.

It basically checks each sheet in the current workbook to see if any have the name specified, in order to avoid duplicating sheets. I think I can write this as a function that would take as an argument the desired name and would return TRUE/FALSE if that sheet existed, and an integer representing the sheet # for that sheet. However, I can't figure out how to get a function to return multiple values. I tried the code below, based on some Microsoft examples, but it doesn't work because I don't know how to call the function with the intent of using one or both of its outputs:

Please Login or Register  to view this content.

Can someone point me in the direction of a good, beginner's guide to functions w/ multiple outputs?

[StephenR]

Try this. Select two adjacent cells (e.g. A1 and B1) and enter the formula with Ctrl+Shift+Enter as it is an array formula.

Please Login or Register  to view this content.

[ahartman]

Try this. Select two adjacent cells (e.g. A1 and B1) and enter the formula with Ctrl+Shift+Enter as it is an array formula.

Please Login or Register  to view this content.

So maybe I don't even want it to be a function? I want to use the returned values in the macro, not in any cells. I want to be able to just include a line in my macro code that says bCheckSheets(sCurrentLog) that returns TRUE/FALSE and the sheet #.

[StephenR]

You're not really explaining what you want to do, but it appears that the individual elements of the array formula can be referenced.

Please Login or Register  to view this content.

[ahartman]

StephenR,

Sorry if my explanations have been unclear. I think the key piece of info I was missing is included in your code:

Please Login or Register  to view this content.

I didn't know that syntax. Thanks for the help!