call function from within a sub

[TryHarder]

Hi Thanks for looking at this.
i have a function to check a worksheet and return a string (Answer)that i can use in a sub to create a word document and set some customDocumentproperites.
Each block of code works but i would welcome help of how to get the string produced by the function written into the sub.
Answer produces the string required

Please Login or Register  to view this content.

[Arkadi]

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)

[TryHarder]

Arkadi
Thanks for pointing this out i understand now

[Arkadi]

Thanks very much for fixing that TryHarder, and welcome to the forum

I wonder why you have a global variable "Answer" is that to try and get the Answer into the sub? It should not be needed.

Just dim a variable as a string in your SUB, and within the sub make it equal the function....

Small changes to the function:

Please Login or Register  to view this content.

Then in the Sub:

Please Login or Register  to view this content.

At this poing myString should have the result from GetAnswer as its value, and you can use it as needed.

[TryHarder]

Arkadi
Thank you for your prompt reply.
i have made your changes and my string returns a value.(That is Great)

however it only returns the last value from the loop.

Please Login or Register  to view this content.

This is great but that value is not passed to the new document.
however if i paste that line in it works fine.
Do you have any ideas please?

[TryHarder]

Arkadi

Thank you answering my question.
i can now see how to return the value.
I will try to work out how to make the calling routine work with it and if need be ask another question.

Best Regards
TryHarder

[Arkadi]

Sorry, haven't looked since I posted... I'll have a look in a couple of minutes

[Arkadi]

I need to better understand what your desired result is....
Do you want the returned value to be multiple lines of text?

If so then try this:

Please Login or Register  to view this content.

[TryHarder]

Arkadi,
Thank you for your further help , i am sorry that i have only just seen it.
Your code works fine and returns to a msgbox the text that is needed.
Thanks for explaining for it works.

What i am trying to do with the returned text is to set a list of customDocumentProperties within the code to produce a new word document created from a template.

Please Login or Register  to view this content.

Do i need to call another function to run the code ?

I am sorry that i did not make this clear to start with.

[Arkadi]

Hope I have understood correctly....

Please Login or Register  to view this content.

[TryHarder]

Arkadi,
what a great answer thank you for your ongoing efforts ,
it works just fine and is clear and easy to understand.

Thank you again.
Best Regards

Tryharder

[Arkadi]

Hi TryHarder,

I'm glad it worked out for you, and appreciate your feedback. It is always nice to help people find a good solution to their question/problem. Take care, and see you next time