Passing a variable (Word 2002)

[holdew]

I have a letterhead template with an AutoNew macro which displays a list of names from which the users chooses. The list is actually generated from an Excel file and the excel file contains full name, closing, and email address. This information is used to customize/personalize letterheads.

A couple of my users complain because they do letters for only one or two people so they do not want to constantly choose a name from the list. They want a single button to click to do the customized letterhead. As the manager of the templates, I only want the one letterhead template.

I am trying to write a macro in which I can code the name I want, call my letterhead template and pass the name to the AutoNew macro and not display the dialogue box. I have:

Public strName As String

Sub test()
strName = "James E. Fortin"
Documents.Add Template:= _
"Letterhead.dot", _
NewTemplate:=False, _
DocumentType:=0
End Sub

Stepping through the code, when I am in the code in the letterhead template, strName is empty. Shouldn't the public declaration let this go to the other module? What am I missing?

[jscher2000]

So if you put MsgBox strName in your AutoOpen macro, the box is blank?

There are rules for when variables retain their values and when they don't, when they are visible across projects and when they aren't, and so on, but it's better not to count on them. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> It is safer to pass a value, when possible, or to store it either in a document or on disk.

I recently experimented with creating a "Most Recently Used" list in a disconnected ADO recordset, which is something like a database file without the database. I found these a bit tricky to work with for reasons that are too hard to understand, let alone explain, but if you don't need fancy functionality, you can simply store a list of names in an INI file and use Word's System.PrivateProfileString property, or VBA/VBScript text file methods, to read and update it.

[MarkD]

Along the same lines, you can always read and write values to the Registry using simplistic VBA SaveSetting, GetSetting, and DeleteSetting statements. Example:

Option Explicit

Public Const APP_NAME = "MyDocApp"

Public Sub SetRegSetting(ByVal strSection As String, _
ByVal strKey As String, _
ByVal strSetting As String)

SaveSetting APP_NAME, strSection, strKey, strSetting

End Sub

Public Function GetRegSetting(ByVal strSection As String, _
ByVal strKey As String) As String

'Returns "0" if not found:
GetRegSetting = GetSetting(APP_NAME, strSection, strKey, "0")
' Test msg:
' MsgBox GetRegSetting, vbInformation, "TEST GET SETTING"

End Function

Public Sub DeleteRegKey(ByVal strSection As String, _
ByVal strKey As String)
' Delete single key:
DeleteSetting APP_NAME, strSection, strKey

End Sub

Public Sub DeleteRegSection(ByVal strSection As String)

' Delete single section and any subkeys:
DeleteSetting APP_NAME, strSection

End Sub

Public Sub DeleteRegSettings()

' Delete all settings:
DeleteSetting APP_NAME

End Sub

Examples:

SetRegSetting "DocVariables","Variable1","Some Value"
SetRegSetting "DocVariables","Variable2","1001"
GetRegSetting "DocVariables","Variable1"
DeleteRegKey "DocVariables","Variable1"

Normally you would define an "application" name as a constant (recommend a unique name) and use this name in wrapper functions to read, write, or delete values as illustrated. Note limitations of these VBA statements: You can only set string values - any numerical values would have to be converted to strings when set and converted back to numbers when retrieved. All keys are written under the HKEY_CURRENT_USERSoftwareVB and VBA Program Settings key only. For more advanced functionality, you have to use Windows API's. Also note, according to Help file, the Section argument for DeleteSetting statement is required; but if you omit this argument, you can delete the entire key for your "app" (and all subkeys) with single statement w/o error (see DeleteRegSettings).

While I normally wouldn't use these functions in Word, they work reliably in other custom applications built using VBA. If you're paranoid about cluttering up the Registry with superfluous settings (as if it is not already cluttered) make sure you delete all settings when app or document is closed if they do not need to be persisted.

HTH