Dictionary object: Error assigning user defined data type to item

[Paul Urbanus]

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

 

[Dana DeLouis]

'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. :>)

 

[Tushar Mehta]

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