class module variable declaration issue

[Ken]

Since receiving Peter's response to my post on using a fixed length
string variable in a class module I have gotten a lot smarter on Get/
Let pairs and class modules in general (mostly from Chip Pearson's
site); but unfortunately I'm stll not smart enough.

Below is a trimmed down (only 1 variable instead of 24) version of my
code. I don't get any errors, but, I don't get any immediate window
output either.

In a regular module I have:

Option Explicit

Sub CreatePOHeader()

Dim POHeaders As New Collection
Dim i As Integer
Dim PH As POHeader

Dim PO_ID As String * 10

PO_ID = "LM123145"

Set PH = New POHeader
PH.PO_ID = PO_ID

POHeaders.Add PH, PH.PO_ID

For i = 1 To POHeaders.Count
Debug.Print POHeaders(i).PO_ID
Next i

End Sub

In my POHeader Class module I have:

Option Explicit

Private pPO_ID As String * 10

Public Property Get PO_ID() As String
PO_ID = pPO_ID
End Property

Public Property Let PO_ID(value As String)
pPO_ID = PO_ID
End Property

Once I get to the point where I can my 24 fixed length string
variables in a class I will loop through a range on my spreadsheet to
create a collection, and ultimately use the collection to generate the
data file that is needed for the accounting system preprocessor.

Thanks

Ken


Initial Post:

I am generating a fixed length text file from my Excel 2003 data for
import into an accounting system. I have it working okay by padding
the cells with spaces and trimming appropriately before stringing the
cell values together for export. I was hoping to simplify my process
with a class module with properly lengthed string variables; but, I
don't seem to be able to declare fixed length string variable as
Public. I have a POHeader class module and i want to have e.g.

public PO_ID As String * 10


I can use Dim PO_ID As String * 10 in my regular module and it works;
but if I use that in the POHeader class module I can't get to it from
the regular module. Is this a limitation of class modules or am I
overlooking something or does anyone have any other ideas?


Thanks


Ken

Peter's response:

There are various things that can't be declared as public in class/
object
modules, and a fixed length string is one of them.

A simple workaround is to declare as private and use a [public]
property
let/get pair to access it.

Regards,

 

[Tim Zych]

Didn't test anything but this is one change to make:

this

Public Property Let PO_ID(value As String)
pPO_ID = PO_ID
End Property

should be

Public Property Let PO_ID(value As String)
pPO_ID = value
End Property

 

[Bernie Deitrick]

Ken,

Regular Module

Option Explicit

Dim PHeaders As Collection
Dim PH As POHeader

Sub CreatePOHeader()

Dim i As Integer
Dim strPO_ID As String

Set PHeaders = New Collection

strPO_ID = "LM123145"

Set PH = New POHeader
PHeaders.Add PH
PH.PO_ID = strPO_ID

For i = 1 To PHeaders.Count
Debug.Print PHeaders(i).PO_ID
Next i

End Sub


Class Module

Option Explicit

Private pPO_ID As String

Public Property Get PO_ID() As String
PO_ID = pPO_ID
End Property

Public Property Let PO_ID(Value As String)
pPO_ID = Value
End Property



--
HTH,
Bernie
MS Excel MVP

Since receiving Peter's response to my post on using a fixed length
string variable in a class module I have gotten a lot smarter on Get/
Let pairs and class modules in general (mostly from Chip Pearson's
site); but unfortunately I'm stll not smart enough.

Below is a trimmed down (only 1 variable instead of 24) version of my
code. I don't get any errors, but, I don't get any immediate window
output either.

In a regular module I have:

Option Explicit

Sub CreatePOHeader()

Dim POHeaders As New Collection
Dim i As Integer
Dim PH As POHeader

Dim PO_ID As String * 10

PO_ID = "LM123145"

Set PH = New POHeader
PH.PO_ID = PO_ID

POHeaders.Add PH, PH.PO_ID

For i = 1 To POHeaders.Count
Debug.Print POHeaders(i).PO_ID
Next i

End Sub

In my POHeader Class module I have:

Option Explicit

Private pPO_ID As String * 10

Public Property Get PO_ID() As String
PO_ID = pPO_ID
End Property

Public Property Let PO_ID(value As String)
pPO_ID = PO_ID
End Property

Once I get to the point where I can my 24 fixed length string
variables in a class I will loop through a range on my spreadsheet to
create a collection, and ultimately use the collection to generate the
data file that is needed for the accounting system preprocessor.

Thanks

Ken


Initial Post:

I am generating a fixed length text file from my Excel 2003 data for
import into an accounting system. I have it working okay by padding
the cells with spaces and trimming appropriately before stringing the
cell values together for export. I was hoping to simplify my process
with a class module with properly lengthed string variables; but, I
don't seem to be able to declare fixed length string variable as
Public. I have a POHeader class module and i want to have e.g.

public PO_ID As String * 10


I can use Dim PO_ID As String * 10 in my regular module and it works;
but if I use that in the POHeader class module I can't get to it from
the regular module. Is this a limitation of class modules or am I
overlooking something or does anyone have any other ideas?


Thanks


Ken

Peter's response:

There are various things that can't be declared as public in class/
object
modules, and a fixed length string is one of them.

A simple workaround is to declare as private and use a [public]
property
let/get pair to access it.

Regards,

 

[Ken]

Bernie
I will have to study the differences between yours and mine to figure
out the exact difference, other than the most important difference
which is yours works, mine doesn't. Also, assuming you worked on
yours the entire 55 minutes between the time I posted to your
response, then I probably worked on mine about 10 times longer.
Thanks to all.
Ken

 

[Bernie Deitrick]

Ken,

The biggest difference is that I used a collection, and changed the Let statement to = Value.

And, no, I didn't work on it for 55 minutes ;-) Maybe 5... but I've worked on Excel programming for
nearly 20 years - so that should count for something....

Bernie
MS Excel MVP