Scripting Dictionary to hold User Defined Type

[billbell52]

I have an array of user defined types that hold info about some
columns in the spreadsheet. I currently loop through to find the
column name I am interested in. I would like to use the scripting
dictionary to do this since I think it would be a little faster. I do
this a lot in my addin. I have tried numerous things and it does not
appear to allow this. I thought someone may know of a way to do it.
Here is a simplified test case.

I thought of having the dictionary hold the index to the array of user
defined types. This would work but it would be cumbersome to manage.
Perl and Ruby do this with ease.

Option Explicit

Public Type headUT
colName As String
colWidth As Integer
End Type

Sub testdic()

' Add reference to Microsoft Scripting Dictionary.

Dim xDic As New Scripting.Dictionary
Dim x() As Variant
Dim z() As headUT

ReDim x(0)
ReDim z(0)
' Assign values to user defined type
z(0).colName = "This column"
z(0).colWidth = 12
' Cannot assign User type directly to Dictionary .
xDic("COL_INFO") = z(0) ' Comment out to try next item
' Cannot assign User type to Variant then assign variant to
Dictionary
x(0) = z(0)
xDic("COL_INFO") = x(0) 'Can assign variant to dictionary
End Sub

 

[Jim Rech]

I think it would be a little faster.

I'd happily bet the other side. Array processing is pretty quick in Excel
and the Dictionary object is pretty slow.

Frankly I think you might be going about this the hard way. How about:

ActiveCell.EntireColumn.Name = "ThisColumn"

and later:

MsgBox Names("ThisColumn").RefersToRange.Column

--
Jim
|I have an array of user defined types that hold info about some
| columns in the spreadsheet. I currently loop through to find the
| column name I am interested in. I would like to use the scripting
| dictionary to do this since I think it would be a little faster. I do
| this a lot in my addin. I have tried numerous things and it does not
| appear to allow this. I thought someone may know of a way to do it.
| Here is a simplified test case.
|
| I thought of having the dictionary hold the index to the array of user
| defined types. This would work but it would be cumbersome to manage.
| Perl and Ruby do this with ease.
|
| Option Explicit
|
| Public Type headUT
| colName As String
| colWidth As Integer
| End Type
|
| Sub testdic()
|
| ' Add reference to Microsoft Scripting Dictionary.
|
| Dim xDic As New Scripting.Dictionary
| Dim x() As Variant
| Dim z() As headUT
|
| ReDim x(0)
| ReDim z(0)
| ' Assign values to user defined type
| z(0).colName = "This column"
| z(0).colWidth = 12
| ' Cannot assign User type directly to Dictionary .
| xDic("COL_INFO") = z(0) ' Comment out to try next item
| ' Cannot assign User type to Variant then assign variant to
| Dictionary
| x(0) = z(0)
| xDic("COL_INFO") = x(0) 'Can assign variant to dictionary
| End Sub
|

 

[Dana DeLouis]

I couldn't get it to work either.
Would this be a workaround?

z(0).colName = "This column"
z(0).colWidth = 12
xDic.Add "Col_Info", Array(z(0).colName, z(0).colWidth)