Dictionary object: Error assigning user defined data type to item

Hi,

I'm a VBA newbie - hacking out some code when I need it, but often
having to refer to the help as I don't use VBA enought to know the
various objects and methods off the top of my head.

DEVELOPMENT ENVIRONMENT
=======================
OS = Win2K Workstation
Excel = Excel XP


PROBLEM TO SOLVE
================
Create an associative array (Dictionary object in VBA) in which the
desired data type to store is a user defined type. Ideally, I'd like to
store an array, but VBA help says this isn't allowed.


WHAT I DID
==========
Since this was my first time using a Dictionary object, I did a web
search and cut/pasted/modified some existing code to do a simple test.

After I got the rudimentary test code to work (where string data is
stored in the dictionary), I decided to store one of my user-defined
data items.


THE VBA ERROR
=============
When I try and run the code, 'udtSeatCell' is highlighted in the
following line of code.

d.Add "111", udtSeatCell

Also, I get the following error:
'Compile Error:'
'Only user-defined types in public object modules can be coerced to or
from a variant or passed to late-bound functions'


MY VBA CODE
===========
' Define a physical seat desriptor (cell location in worksheet)
Public Type SeatCell
Row As Integer
Column As Integer
End Type

Dim udtSeatCell As SeatCell

Sub DictTest1()
Dim d 'Create a variable
Set d = CreateObject("Scripting.Dictionary")

Dim KeyToFind

' Initialize the test object
udtSeatCell.Row = 1
udtSeatCell.Column = 1

d.Add "105", "Section 105"
d.Add "120", "Section 120"
d.Add "219", "Section 219"
d.Add "111", udtSeatCell

MsgBox ("Dictionary 'd' has " & d.Count & " key/value pairs")
Do
KeyToFind = InputBox("Section to find?")
If d.exists(KeyToFind) = False Then
MsgBox ("Section " & KeyToFind & "was not found")
Else
MsgBox (d.Item(KeyToFind))
End If
Loop Until (KeyToFind = "")

End Sub

ANY SUGGESTIONS????

Urb

> 'Only user-defined types in public object modules can be coerced to or
> from a variant or passed to late-bound functions'
> d.Add "111", udtSeatCell

Hi. I think you need to be more specific with "udtSeatCell"
See if this idea works...

d.Add "111", udtSeatCell.Row ' Or .Column
'or store both...
d.Add "111", Array(udtSeatCell.Row, udtSeatCell.Column)

HTH. :>)
--
Dana DeLouis
Win XP & Office 2003


"Paul Urbanus" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm a VBA newbie - hacking out some code when I need it, but often having
> to refer to the help as I don't use VBA enought to know the various
> objects and methods off the top of my head.
>
> DEVELOPMENT ENVIRONMENT
> =======================
> OS = Win2K Workstation
> Excel = Excel XP
>
>
> PROBLEM TO SOLVE
> ================
> Create an associative array (Dictionary object in VBA) in which the
> desired data type to store is a user defined type. Ideally, I'd like to
> store an array, but VBA help says this isn't allowed.
>
>
> WHAT I DID
> ==========
> Since this was my first time using a Dictionary object, I did a web search
> and cut/pasted/modified some existing code to do a simple test.
>
> After I got the rudimentary test code to work (where string data is stored
> in the dictionary), I decided to store one of my user-defined data items.
>
>
> THE VBA ERROR
> =============
> When I try and run the code, 'udtSeatCell' is highlighted in the following
> line of code.
>
> d.Add "111", udtSeatCell
>
> Also, I get the following error:
> 'Compile Error:'
> 'Only user-defined types in public object modules can be coerced to or
> from a variant or passed to late-bound functions'
>
>
> MY VBA CODE
> ===========
> ' Define a physical seat desriptor (cell location in worksheet)
> Public Type SeatCell
> Row As Integer
> Column As Integer
> End Type
>
> Dim udtSeatCell As SeatCell
>
> Sub DictTest1()
> Dim d 'Create a variable
> Set d = CreateObject("Scripting.Dictionary")
>
> Dim KeyToFind
>
> ' Initialize the test object
> udtSeatCell.Row = 1
> udtSeatCell.Column = 1
>
> d.Add "105", "Section 105"
> d.Add "120", "Section 120"
> d.Add "219", "Section 219"
> d.Add "111", udtSeatCell
>
> MsgBox ("Dictionary 'd' has " & d.Count & " key/value pairs")
> Do
> KeyToFind = InputBox("Section to find?")
> If d.exists(KeyToFind) = False Then
> MsgBox ("Section " & KeyToFind & "was not found")
> Else
> MsgBox (d.Item(KeyToFind))
> End If
> Loop Until (KeyToFind = "")
>
> End Sub
>
> ANY SUGGESTIONS????
>
> Urb
>

Use a object based on a class definition rather than a variable based
on a UDT. Then, create and add an object of that class type to the
dictionary.

I see no documentation that indicates one cannot add an array to a
dictionary. But, if you have a problem with an array, a simple
workaround is to create a variant, redim it to an array and use the
variant wherever an array is disallowed!

The first code segment adds an array to a dictionary.

Option Explicit

Sub addArrayToDictionary()
Dim x As Dictionary
Set x = New Dictionary

Dim z(1) As Variant
'ReDim z(1)
z(0) = 100: z(1) = 101
x.Add "Variant array", z
MsgBox x.Item("Variant array")(0)

End Sub

The next demonstrates how to use an object with a dictionary and a
variant to workaround array problems. Create a Class module named
Class1. Add the code below to it:

Option Explicit

Public Row As Long, Column As Long

In a standard module, add the code below and run the testDictionary
subroutine. Make sure you have some cell in some worksheet at the
active object.

Option Explicit
Option Base 0
Sub testDictionary()
Dim x As Dictionary
Set x = New Dictionary

Dim y As New Class1
y.Row = ActiveCell.Row
y.Column = ActiveCell.Column
x.Add "Object", y

Dim z As Variant
ReDim z(1)
z(0) = 100: z(1) = 101
x.Add "Variant array", z

Dim a, b
Set a = x.Item("Object")
b = x.Item("Variant array")
MsgBox a.Row & "," & b(0)

MsgBox x.Item("Object").Row & "," & x.Item("Variant array")(0)
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Hi,
>
> I'm a VBA newbie - hacking out some code when I need it, but often
> having to refer to the help as I don't use VBA enought to know the
> various objects and methods off the top of my head.
>
> DEVELOPMENT ENVIRONMENT
> =======================
> OS = Win2K Workstation
> Excel = Excel XP
>
>
> PROBLEM TO SOLVE
> ================
> Create an associative array (Dictionary object in VBA) in which the
> desired data type to store is a user defined type. Ideally, I'd like to
> store an array, but VBA help says this isn't allowed.
>
>
> WHAT I DID
> ==========
> Since this was my first time using a Dictionary object, I did a web
> search and cut/pasted/modified some existing code to do a simple test.
>
> After I got the rudimentary test code to work (where string data is
> stored in the dictionary), I decided to store one of my user-defined
> data items.
>
>
> THE VBA ERROR
> =============
> When I try and run the code, 'udtSeatCell' is highlighted in the
> following line of code.
>
> d.Add "111", udtSeatCell
>
> Also, I get the following error:
> 'Compile Error:'
> 'Only user-defined types in public object modules can be coerced to or
> from a variant or passed to late-bound functions'
>
>
> MY VBA CODE
> ===========
> ' Define a physical seat desriptor (cell location in worksheet)
> Public Type SeatCell
> Row As Integer
> Column As Integer
> End Type
>
> Dim udtSeatCell As SeatCell
>
> Sub DictTest1()
> Dim d 'Create a variable
> Set d = CreateObject("Scripting.Dictionary")
>
> Dim KeyToFind
>
> ' Initialize the test object
> udtSeatCell.Row = 1
> udtSeatCell.Column = 1
>
> d.Add "105", "Section 105"
> d.Add "120", "Section 120"
> d.Add "219", "Section 219"
> d.Add "111", udtSeatCell
>
> MsgBox ("Dictionary 'd' has " & d.Count & " key/value pairs")
> Do
> KeyToFind = InputBox("Section to find?")
> If d.exists(KeyToFind) = False Then
> MsgBox ("Section " & KeyToFind & "was not found")
> Else
> MsgBox (d.Item(KeyToFind))
> End If
> Loop Until (KeyToFind = "")
>
> End Sub
>
> ANY SUGGESTIONS????
>
> Urb
>
>