Call Word VBA function from add-in template?

[larrysulky]

Hi! I have a Word (2003) template that, among other things, maps
typefaces from nice fancy ones to roughly equivalent plain everyday
ones, according to weight, roman/italic, and relative size.

Ideally I would set up a hash table (associative array), but to my
knowledge VBA doesn't have such a concept. So instead I have a
function, which can be called by whatever routine needs it, to look up
the mapping via a Select statement and return an array (see below).

I'm trying to put the mapping data into a side file somehow so that I
don't have to modify the main template all the time with new mappings,
and it would be great if it could be set up such that a non-techie
could maintain the table.

My questions:
1) Am I wrong about the lack of a hash table capability in VBA?
2) Is there a more clever way than setting up a public function that
returns an array?
3) If the public function idea is okay, is there some way I can either:
(a) put it into a separate template, but still call it and return an
array from it (I know how to call a _subroutine_ in this situation, but
not how to call a _function_ and return an array value); or
(b) put the mapping data in a _non-techie-user-editable format_ in a
side file, read it in, and parse it (though this would seem to require
a hash table, or a lot of I/O to read values only when needed)

I would really appreciate any feedback. Thanks

--larry

''''''''''''''''''''''''''''''''''''
Public Function getMappedFont(ByVal FontName As String) As Variant
' _Must_ be Variant to return array.

Dim myMapping(3) As Variant

Select Case UCase(FontName)

Case "SANS-SERIF"
myMapping(0) = "Arial"
myMapping(1) = False
myMapping(2) = False
myMapping(3) = "+0"
Case "SERIF"
myMapping(0) = "Times New Roman"
myMapping(1) = False
myMapping(2) = False
myMapping(3) = "+0"
Case "ACASLON-BOLD"
myMapping(0) = "Times New Roman"
myMapping(1) = True
myMapping(2) = False
myMapping(3) = "+1"
' ... and a hundred more like this ...
Case Else
myMapping(0) = "Times New Roman"
myMapping(1) = False
myMapping(2) = False
myMapping(3) = "+0"
missingFontMapMsgs = missingFontMapMsgs & vbCrLf & FontName
' Append to a global string

End Select
getMappedFont = myMapping ' Assigns an array to the return variable.

End Sub

 

[Shauna Kelly]

Hi Larry

1. I'm not aware of any hash table capability in VBA, but others may chime
in and point something out.

2. A public function that returns the info seems like as good an idea as
any.

3(a) You can call a function in an add-in (ie a .dot file loaded through
Tools > Templates and Add-ins or, easier, loaded automatically when Word
starts if you put it in Word's startup folder) from any template or another
add-in. In the VBE, use Tools > References to create a reference to the
add-in. Then call the function as, eg x=MyAddin.MyFunction

3(b) It depends on (a) how non-techie the non-techie people are, (b) how
much data you have and (c) how strict you want the user interface to be,
preferably without having to write one!

For this kind of thing, depending on the circumstances, you might try:

- An old-fashioned .ini file (look up PrivateProfileString in VBA help).
Disadvantage: No error checking for the user; must use Notepad or some such
to edit unless you'd like to write a UI.

- An Excel file. You can get at the data in several ways. The slow way is to
open Excel and open the file (see
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm). A fast way
for read-only is to use DAO (see
http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm). Advantage: The
non-techie gets the existing Excel user interface with all the bells and
whistles of protection, data validation, conditional formatting etc that you
care to create. Create the data either with an old-fashioned dynamic range
(see http://www.contextures.com/xlNames01.html) or in one of the new Data
Lists so the range name grows automatically as the user adds data.

- An XML file editable in Word. You can get at the data using MSXML (set a
Reference to it in the VBE). For the user to edit it in Word, you'll need to
create an XSD, add it to the Schema Library, and teach the non-techie how to
tag text to create a new record and how to save it as an .XML file (not a
..doc file), or write some code to do it.

- A Word file, possibly the add-in. Put the data in a table, and give the
users strict instructions on how to complete it. Technically, this will
work, but the advantages of the built-in mechanisms in the UI in Excel
outweigh Word for something like this in my experience.

I use Excel files a lot for this kind of thing, where (by database
standards) there's very little data, and you want a non-techie to edit it. I
recently had good experience with an XML file to be edited in Word, but I
think that depends on the audience.

By the way, I assume you do know about Tools > Options > Compatibility >
Font Substitution. I hope you're not reinventing a wheel here!

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[larrysulky]

Shauna Kelly wrote:
---BIG SNIP OF REALLY VALUABLE INFO---

Thanks so much, Shauna -- I was missing the step of adding the template
project as a reference! Just being able to call the function from an
add-in will be good enough for now...a big improvement.

I knew a bit about the automatic font substitution, but this stuff has
to go well beyond that, and in a controlled method for what we're
doing.

Thanks again!
--larry

 

[Shauna Kelly]

Larry

I'm glad it helped.

Shauna

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word