Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting Dictionary to hold User Defined Type
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting Dictionary to hold User Defined Type
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 "billbell52" wrote in message oups.com... |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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting Dictionary to hold User Defined Type
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) -- HTH :) Dana DeLouis "billbell52" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dictionary object: Error assigning user defined data type to item | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
"User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
Word.Document - user defined type not defined | Excel Programming |