Need VBA Function in Word table formula

[nschroeder]

We have loan approval Word documents that contain numerous tables of customer information, often redundant. We want to be able to enter common borrower data in one place and have it replicated where needed, and this applies to potentially multiple secondary borrowers in the same document. We tried using Insert - Quick Parts - Document Property, which works fine but is limited to 15 values, and we need more. I'm trying to create VBA functions and pull the values into table formulas, but I'm getting a syntax error. For example, in the document code, I have:

Function Bor1Name()
    Bor1Name = "Joe Deadbeat"
End Function

In the table, if I put a formula: =Bor1Name, I get "!Undefined Bookmark, BOR1NAME". If I put =Bor1Name() I get "!Syntax Error, ("



Any suggestions on how to get the formula/function to work? Or is there a better way to accomplish what we need? Links to data in an Excel document won't work for this app, since the data is coming from a web site.

[fumei]

We want to be able to enter common borrower data in one place and have it replicated where needed,

Try using a REF field.

[macropod]

If you modify your code to populate custom document properties (one set for the primary borrower and one set each for the maximum # of secondary borrowers) with one instance each of your variables, you can use however many DOCPROPERTY fields in the document you need to replicate the info wherever you a given variable to appear. Supplemented with IF field coding, the document content can likewise be varied, all via a simple .Fields.Update directive.

[nschroeder]

I like where you're going, but I'm not sure how to get there. I'm an Excel/Access/VBA guy, but haven't done much in Word/VBA. I figured out how to create a custom Bor1Name property, but it doesn't show up in Quick Parts - Document Properties, so that would imply that the only way to access custom properties is through code. Could you please give more specifics about how to create and update custom properties through code, and then how to use them to populate table fields? I tried to record creating the property, but the macro came up blank.

Thanks for your help.

[gmaxey]

Why don't you use mapped content controls?

http://gregmaxey.com/word_tip_pages/...ols_addin.html

[nschroeder]

Thank you all for your suggestions. It appears there are a lot of options. We'll play around and figure out what will work best for us.

Thanks again!

[macropod]

Originally Posted by nschroeder

I like where you're going, but I'm not sure how to get there. I'm an Excel/Access/VBA guy, but haven't done much in Word/VBA. I figured out how to create a custom Bor1Name property, but it doesn't show up in Quick Parts - Document Properties, so that would imply that the only way to access custom properties is through code. Could you please give more specifics about how to create and update custom properties through code, and then how to use them to populate table fields? I tried to record creating the property, but the macro came up blank.

Thanks for your help.

having created your custom Bor1Name property, you can reference it anywhere in the document via a DOCPROPERTY field, which you can create via Ctrl-F9, then typing 'DOCPROPERY Bor1Name' between the field braces, thus: {DOCPROPERTY Bor1Name}, then pressing F9 to update. Adding fields through code isn't particularly difficult either.

For some code examples using custom document properties, see:
https://www.msofficeforums.com/word-...numbering.html
https://www.msofficeforums.com/word-...ent+propert%2A
https://www.msofficeforums.com/word/...ferencing.html
A thread that discusses both mapped content controls and the use of custom document properties is:
https://www.msofficeforums.com/word-...t-control.html
Although the above examples don't involve other applications (eg Access/Excel), the code for the Word side of things would be much the same - automating Word from those other apps isn't especially complicated.