Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Custom Data Types for list box array (Word 2000)

    I am trying to use a custom data type to create an array for a multi-column list box in a Word 2000 user form. I declare the type as public in the declarations section of my module, and also publicly declare an array of this type. When I try to use the array variable in initialising the list box in the user form, I'm given an error message. Whichever way I read the message, I feel as though I've done what's required, but obviously something's missing! Here is the test code I've been using:

    Option Explicit

    'Declare the type in the declarations area of module basUtilities
    Public Type Employee
    FirstName As String * 15
    LastName As String * 15
    Phone As String * 13
    Salary As Single
    DateHired As Date
    End Type

    'Declare an array using the Employee type in the declarations area of module basUtilities
    Public AllEmployees(5) As Employee

    'Create a subroutine to initialise the first two records in the array and display the user form
    Sub TestNewArray()

    AllEmployees(0).FirstName = "John"
    AllEmployees(0).LastName = "Doe"
    AllEmployees(0).Phone = "555 1234"
    AllEmployees(0).Salary = 1234.56
    AllEmployees(0).DateHired = #1/1/2002#

    AllEmployees(1).FirstName = "Alice"
    AllEmployees(1).LastName = "Smith"
    AllEmployees(1).Phone = "555 9876"
    AllEmployees(1).Salary = 9876.54
    AllEmployees(1).DateHired = #31/12/2000#

    UserForm1.Show

    End Sub

    'Initialise the list box on UserForm1 with the AllEmployees array
    Private Sub UserForm_Initialize()

    Me.ListBox1.List = AllEmployees

    End Sub

    The code stops in the UserForm1 module and highlights the Me.ListBox1.List = AllEmployees line, with the following error message in a text box:

    Compile error
    Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

    Am I trying to do the impossible, or have I missed some crucial step?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Data Types for list box array (Word 2000)

    It wants the module basUtilities itself to be made public, which probably isn't a good idea. You might be better off creating a public module with nothing in it but the type declaration.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Custom Data Types for list box array (Word 2000)

    OK, now for the really dumb question - how do I create a public module? I have a small class module, a couple of ordinary modules and some forms in my project, which is a Word template that will be added to the Startup folder to make it global (but I haven't done that last bit while I'm still developing and testing).

    Perhaps I'm heading out of my depth*, but there is a real dearth of opportunities for training in this stuff where I work. I therefore have to rely on manuals and on-line help, and I find the manuals either make lots of assumptions about prior knowledge, or don't expect the reader to need to know this detail (it's not clear which angle most of them are coming from!).

    I'm usually able to nut this stuff out eventually, but have hit a couple of brick walls lately (* maybe I've got to the deep end of the paddling pool!). <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    Thanks for your help.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Hi Alison,
    Is there a particular reason you can't just use something like:
    <pre>Public AllEmployees(5, 4) As Variant

    'Create a subroutine to initialise the first two records in the array and display the user form
    Sub TestNewArray()
    AllEmployees(0, 0) = "John"
    AllEmployees(0, 1) = "Doe"
    AllEmployees(0, 2) = "555 1234"
    AllEmployees(0, 3) = 1234.56
    AllEmployees(0, 4) = #1/1/2002#

    AllEmployees(1, 0) = "Alice"
    AllEmployees(1, 1) = "Smith"
    AllEmployees(1, 2) = "555 9876"
    AllEmployees(1, 3) = 9876.54
    AllEmployees(1, 4) = #12/31/2000#


    UserForm1.Show

    End Sub

    </pre>

    which is much simpler! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Data Types for list box array (Word 2000)

    You think multidimensional arrays are *simpler* than custom datatypes? You can create an array of the datatype and have the best of both worlds without worrying about the indexes at all!
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Data Types for list box array (Word 2000)

    I can't tell you how in Word, so I'll let one of the Word gurus jump in on that question. In VB itself, you don't have this problem, but the modules created by the Office apps are not public when they're created. Generally, you have to export the module to a text format, open it in Notepad or some other text editor and play with the attributes like Attribute VB_GlobalNameSpace = False and Attribute VB_Exposed, but I can never remember which does what. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I'll retire before I confuse both of us.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Thanks for the suggestion, Rory. I had realised I could do it your way, but there were particular reasons why I wanted to do it this way:

    - defining the type enables me to allocate specific data types
    - the list could end up getting quite extensive
    - most of the documentation I have access to warns against using variants where specific data types can be used

    However, I'm reaching the point where I'll probably go back to a simple variant array!

    Thanks again.

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Thanks for your input, anyway, Charlotte. At least I don't feel quite so stupid now I know it's not an answer that's been staring me in the face all this time!

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Hi Alison,
    I figured that might be the case! I can only suggest that you try something like:
    <pre>Private Sub UserForm_Initialize()
    Dim n As Integer, arrEmps() As String
    ReDim arrEmps(UBound(AllEmployees), 4)
    For n = 0 To UBound(AllEmployees)
    With AllEmployees(n)
    arrEmps(n, 0) = .FirstName
    arrEmps(n, 1) = .LastName
    arrEmps(n, 2) = .Phone
    arrEmps(n, 3) = .Salary
    arrEmps(n, 4) = .DateHired
    End With
    Next n
    ListBox1.List = arrEmps
    End Sub
    </pre>

    and see if that works for you.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Hi Charlotte,
    I don't think multi-dimensional arrays are necessarily simpler, but given the choice between that and trying to hack a module to make it public and then coerce a one-dimensional array of user-defined datatypes into a 2D string array, I know which one I'd choose.... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Data Types for list box array (Word 2000)

    The point is that you don't have to coerce the array of datatypes into anything. You just work with the elements of each member of the 1-d array directly. That's one of the things that makes datatypes such appealing tools, you can deal with the entire instance of the datatype at once in writing to a file or you can individually address a specific element of that instance of the datatype by name. As for making modules public, it seems to me that HansV posted something about that, but I'll be darned if I can find it.
    Charlotte

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Data Types for list box array (Word 2000)

    Take a look at this thread and see if it helps. They were discussing the same issue.
    Charlotte

  13. #13
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Thanks for that thread, Charlotte - it's given me some more ideas to work with. I agree with you about the coercion comments - that's what I thought when I was setting up the type!

    I really appreciate the way you've come back with more ideas on this one. It's nearly midnight here in Oz, so I don't think I'll try developing it any further tonight, but will let you know how it goes tomorrow.

  14. #14
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Custom Data Types for list box array (Word 2000)

    Thanks for the additional suggestion, Rory. I had a feeling I might need to ReDim as I build the list - do I also need to use Preserve to make sure I don't lose the existing data? I'll actually get back to working on this tomorrow - it's getting late here in Oz (speaking as a parent, not as a programmer! <img src=/S/snore.gif border=0 alt=snore width=32 height=15> ), so I don't think I'll try it tonight.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Custom Data Types for list box array (Word 2000)

    You do however have to coerce that array if you want to use the .List property to populate the multicolumn listbox, rather than using .additem in a loop structure, unless I'm missing something?
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •