Storing various data in .ID property

[Thorsten Walenzyk]

Hi all,

I asked how to store additional data per some cells in my Table.
I've got the hint, that I can use the .ID property for storing single data.

But I need to store various data.

I tried something like this:

Public Type MyType
somedata As Integer
someother As String
End Type

Private Sub test()
Dim CellData As MyType
CellData.somedata = 42
CellData.someother = "fourty-two"

Sheets(1).Cells(2, 2).ID = CellData
End Sub


But I've got the error:
"Only user-defined types defined in public object modules can be coerced to
or from a variant or passed to late-bound functions"

As I have defined MyType as a Public type, I guess the problem is the
"public object module".

What is a "public object module" and how can I create one ???

Thanks a lot, Thorsten

 

[Guest]

Hi Thorsten
the range object has no ID property. So this won't work. some ideas how to
store data for a specific cell:
- create a comment programmatically
- use defined names
- use a hidden worksheet

 

[Alan]

Hi all,

I asked how to store additional data per some cells in my Table.
I've got the hint, that I can use the .ID property for storing single
data.

But I need to store various data.

I tried something like this:

Public Type MyType
somedata As Integer
someother As String
End Type

Private Sub test()
Dim CellData As MyType
CellData.somedata = 42
CellData.someother = "fourty-two"

Sheets(1).Cells(2, 2).ID = CellData
End Sub


But I've got the error:
"Only user-defined types defined in public object modules can be
coerced to or from a variant or passed to late-bound functions"

As I have defined MyType as a Public type, I guess the problem is the
"public object module".

What is a "public object module" and how can I create one ???

Thanks a lot, Thorsten

Hi,

Just a note, but I seem to recall when I tried that aways back, that
the property does not save with the file (ie that data is lost when
you close the worksheet).

Not sure if that is an issue for you, or even if that is still the
case with later versions of excel, but you might want to check.

HTH,

Alan.

 

[Norman Jones]

Hi Alan,

Just a note, but I seem to recall when I tried that aways back, that
the property does not save with the file (ie that data is lost when
you close the worksheet).

You are correct but, in reponse to Thorsten's previous post, Dana DeLouis
posted:

 

[Norman Jones]

Hi Frank,

the range object has no ID property

Sub Test()
Dim rng As Range
Set rng = Range("A1")
rng.ID = "ID Test"
MsgBox rng.ID
End Sub

 

[Thorsten Walenzyk]

Thanks a lot Alan.

I heared about that behaviour before and it is ok for me, because I don't
need the data stored permanently...

Thorsten

 

[Tom Ogilvy]

A quick trip to help would show you that the ID property is of type String.
So you can build a string (concatenate multiple values) and store the
string. You can't store a user defined type or array, etc.

 

[Thorsten Walenzyk]

I have already done something like this, but I wonder, if this might cause
some performance problems...

Any other ideas?

Thanks Thorsten

 

[Tom Ogilvy]

If you want to use the ID property, and it will only store a string value,
what other idea would there be - also, if you tried it, did it cause
performance problems?


- or are you asking about other ideas besides using the ID property?

 

[Thorsten Walenzyk]

I haven't completely implemented it for all cells where it would be needed,
so I don't know if it really causes performance problems.

(My sheets will have between 20.000 and 60.000 cells, so I can imagine, that
it will drop teh performance...)

The ID property is the only way I currently know off.
If there are other possibilities, let me know.

Somebody already mentioned a hidden worksheet. This sounds like a
possibility, too.
Has any body experience what this would mean to the performance?

BR Thorsten

 

[Harald Staff]

Hi again Thorsten

You can build a "virtual grid", an array, containing almost any custom type,
class, object, given that storage still isn't an issue. See if you can get
something useful from this little demo. Run SetThisUp once and then run
Testthis from various cell locations:

Option Explicit

Public Type MyType
somedata As Long
someother As String
End Type

Public AllTypes() As MyType

Sub SetThisUp()
Dim R As Long, C As Long
Dim ThisType As MyType
ReDim AllTypes(1 To 60000, 1 To 15)
For R = 1 To 60000
For C = 1 To 20
AllTypes(R, C).somedata = R
AllTypes(R, C).someother = "Thorsten says column " & C & " row " & R
Next
Next
End Sub

Sub TestThis()
If ActiveCell.Column <= 20 Then _
MsgBox AllTypes(ActiveCell.Row, ActiveCell.Column).someother
End Sub

HTH. Best wishes Harald