User Defined Types

[Anne P.]

Hi,

I have template project that I am working on for a client. One of the
things that I am doing I think can be done better using UDTs. However, I am
having a bit of a problem using them.

First, I have a global template (SKGlobal.dot) in the startup directory.
Next, I have approximately 8 other templates that currently call various
code from this template. Each of the other templates has a reference set to
the global template.

In the global template I have a userform to SaveUserInfo. It has 8 controls
for users to fill out information such as name, title, office, etc. When
they click save, the information is combined into one string using the
following code:

strATCategory = "UserInfo" 'this variable and next variable used later in
procedure to store strUserInfo as an autotext entry in the global template
strATName = txtUserName
'combine values into string
strUserInfo = txtUserName & "|" & txtTitle & "|" & txtInitials & "|" &
txtSecyInitials _
& "|" & txtOffice & "|" & txtEmployeeNumber & "|" & txtPhone & "|" &
txtEmail

If chkInsertInto = True Then
strUserInfo = strUserInfo & "|" & "Yes"
Else
strUserInfo = strUserInfo & "|" & "No"
End If

Later, in one of the other templates (i.e., Memo), I have a userform with
comboboxes for the sender name (cmbAtty) and the office (cmbOffice). When
the user selects a name from cmbAtty the following code in the
cmbAtty_Change event occurs:

If strUserInfo <> "" Then
Dim astrUserInfo() As String
StringToArray strUserInfo, astrUserInfo(), "|"

strUserName = astrUserInfo(0)
strTitle = astrUserInfo(1)
strInit = astrUserInfo(2)
strSecyInit = astrUserInfo(3)
strOffice = astrUserInfo(4)
strEmpNum = astrUserInfo(5)
strPhone = astrUserInfo(6)
strEMail = astrUserInfo(7)
If astrUserInfo(8) = "No" Then
chkTitle.Value = False
Else
chkTitle.Value = True
End If

StringToArray above is a function stored in the global template. I am using
additional lines of code to assign the various elements of the array
astrUserInfo to other variables (all which are declared in the general
declarations section) because it is difficult to know which element
contains which information. Even though I have declared these variables
(strUserName, strTitle, etc) as public, when the focus moves from the
cmbAtty control to another control, the variables lose their values. When I
get to the code for the cmdOK_Click event, I have to repeat the above
snippet of code so that the information can be retrieved again and placed in
the bookmarks. At this point, neither the astrUserInfo array elements or
the variables listed above have retained the information that was given to
them in the cmbAtty_Change event.

I currently have 7 other templates (and more coming) that need to do the
same thing and need to have this code repeated in two or three different
userform events. If I ever need to change any of this code (suppose a new
element is added to the UserInfo procedure), I will have to change it in 16
or more different places throughout all of the templates.

I thought of using User Defined Types to store this information, but I am
having no luck with this. I defined the following two UDTs in the global
template:

Public Type Sdr
Name As String
Title As String
TitleinLtr As String
Init As String
SecyInit As String
Office As String
EmpNum As String
Phone As String
Email As String
End Type

Public Type Recip
Name As String
Title As String
Company As String
Address As String
Phone As String
Fax As String
Email As String
FName As String
LName As String
End Type

I would like to use the Sdr type with the SaveUserInfo code. However, I
don't know where or how to instantiate the user type Sdr so that it is
available to all the userform events in the Memo template. I tried both Dim
sdrInfo as Sdr and Public sdrInfo as Sdr in the general declarations section
but I get an error message that says constants, user defined types (and
several other items) can't be defined here. I tried the following:

Public Sub GetUserInfo()
Public sdrInfo as Sdr
End Sub

inside the userform module and then placed the following line in the
userform_activate event:

Call GetUserInfo

I changed the code in the cmbAtty_Change event to this:

If strUserInfo <> "" Then
sdrInfo = Split(strUserInfo, "|")
If sdrInfffice = "NY" Then
' StringToArray strUserInfo, SdrInfo(), "|"
' If SdrInfffice = "NY" Then
cmbOffices.Value = "Partner Letterhead NY"
ElseIf sdrInfffice = "DC" Then
cmbOffices.Value = "Partner Letterhead DC"
End If
End If

If sdrInfo.TitleinLtr = "No" Then
chkTitle.Value = False
Else
chkTitle.Value = True
End If

and I get a "Variable not defined" error message.

I have Googled User Defined Types and looked at tons of websites that came
up in the search, but cannot find anything that is helping me to figure out
how to use UDTs.

Can anyone give me a good explanation of how they work, or point me to some
really good websites that can explain it?

Sorry if this is long and winded, but I am at my wit's end both with
variable scope and UDTs.

Thanks,
Anne P.

 

[Jezebel]

What you need to read up on is VBA's scoping rules. The short answer is to
define the type, and declare a public variable of that type, in an ordinary
module --

Public Type MyMenagerieType
Dog As String
Cat As String
End Type
Public MyMenagerie As MyMenagerieType

Then refer to MyMenagerie from anywhere within the project. However you run
into two problems: 1) the type definition isn't available outside the
project; and 2) using global variables -- especially in a project with
multiple templates and references to the 'core' project -- is poor
programming. Although it's not inherently harmful, it is unquestionably a
major source of bugs.

A better approach in this case would be to use a class module. Define it in
the global project; then all the other templates can instantiate their own
copies and pass them around as function arguments.

 

[Anne P.]

Thanks Jezebel.

Can you point me to where I can get more information on class modules?

Thanks,
Anne P.