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 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

"Thorsten Walenzyk" wrote:

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

"Thorsten Walenzyk" wrote in
message ...

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.

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:

Just to mention. If you save your workbook as a Web page (.htm format),
then the .ID property does gets saved with the cell.

---
Regards,
Norman



"Alan" wrote in message
...
"Thorsten Walenzyk" wrote in
message ...

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.

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

---
Regards,
Norman



"Frank Kabel" wrote in message
...
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

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


"Alan" wrote in message
...
"Thorsten Walenzyk" wrote in
message ...

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.

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.

--
Regards,
Tom Ogilvy

"Thorsten Walenzyk" wrote in message
...
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

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

Any other ideas?

Thanks Thorsten


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Thorsten Walenzyk" wrote in message
...
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

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?


--
Regards,
Tom Ogilvy


"Thorsten Walenzyk" wrote in message
...
I have already done something like this, but I wonder, if this might cause
some performance problems...

Any other ideas?

Thanks Thorsten


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Thorsten Walenzyk" wrote in message
...
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

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




"Tom Ogilvy" wrote in message
...
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?


--
Regards,
Tom Ogilvy


"Thorsten Walenzyk" wrote in message
...
I have already done something like this, but I wonder, if this might
cause
some performance problems...

Any other ideas?

Thanks Thorsten


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Thorsten Walenzyk" wrote in message
...
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 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

"Thorsten Walenzyk" skrev i melding
...
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




"Tom Ogilvy" wrote in message
...
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?


--
Regards,
Tom Ogilvy


"Thorsten Walenzyk" wrote in message
...
I have already done something like this, but I wonder, if this might
cause
some performance problems...

Any other ideas?

Thanks Thorsten


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Thorsten Walenzyk" wrote in message
...
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