Excel VBA Classes Returning a Field with multiple properties set on it 1

[JasonEnsor]

Hiya,

I am doing some data manipulation in Excel, reading files from a folder and updating a masterfile with the data. I have this working as it's quite a straightforward piece of work.

in my source document I have data listed vertically
as an example it might be
Title | Value
Name | Jason
Age | 34

etc ... there are about 70 rows in document that are then transposed in to the master file.

I have a class cRow that maps the source document in to a collection and then when I have looped through all documents I add the rows to my Master Workbook.

Each time the process is ran I clear out the master document except for the company details and the headers. This means I am having to set the format of the cells for the 70 items which again I can do. What I am wanting to do is to build up a second class (cField) that creates a 'Field' object

then for all 70 fields create an instance of the class and populate properties such as Range, Name and Format (these would all be strings as that might be easier)

So in my cField class I would have something like

Option Explicit

Private pFieldName As String
Private pFieldInfo As cFieldInfo

Public Property Get FieldName() As String
     FieldName = pFieldName
End Property

Public Property Let FieldName(Value As String)
     pFieldName = Value
End Property

Public Property Get FieldInfo() As cFieldInfo
     FieldInfo = pFieldInfo
End Property

Public Property Let FieldInfo(Value As cFieldInfo)
     pFieldInfo = Value
End Property

and then my cFieldInfo class would be something along the lines of

Option Explicit

Private pFieldName As String
Private pColumn As String
Private pFormat As String

Public Property Get FieldName() As String
     FieldName = pFieldName
End Property

Public Property Let FieldName(Value As String)
     pFieldName = Value
End Property

Public Property Get Column() As String
     Column = pColumn
End Property

Public Property Let Column(Value As String)
     pColumn = Value
End Property

Public Property Get Format() As String
     Format = pFormat
End Property

Public Property Let Format(Value As String)
     pFormat = Value
End Property

Then my cRow class would then start to look something like (listing all 70 fields)

Option Explicit

Private pName As cField
Private pAge As cField

Public Property Get Name() As cField
     Name = pName
End Property

Public Property Let Name(Value As cField)
     pName = Value
End Property

Public Property Get Age() As cField
     Age = pAge
End Property

Public Property Let Age(Value As cField)
     pAge = Value
End Property

Then when I test it I use something like

Option Explicit

Sub Test()

    Dim r As New cRow
    Set r = New cRow
    
    Dim f As New cField
    Set f = New cField
    
    Dim fi As New cFieldInfo
    Set fi = New cFieldInfo
    
    f.FieldName = "Name"
    fi.Column = "B"
    fi.FieldName = "Name"
    fi.Format = "General"
    
    f.FieldInfo = fi

    r.Name = f
    Debug.Print r.Name.FieldInfo.Format


End Sub

So this kind of achieves what I am wanting to do, as I can now chain some of my data together which will hopefully allow me to automate the creation of some of this (I use c# to build my classes).

Just wondering if anyone has any thoughts on a better approach to this. I know it seems cumbersome at the moment for 2 fields as demo'd but I think when my objects get built up it could be useful. I plan to have a sub that would returns all the data that I need for each individual cell. I'm trying to get VBA as object orientated as possible. There isn't a lot of documentation out there that I have seen.

Any Thoughts and ideas would be appreciated.

J.

Regards

J.

 

[combo]

Classes are templates of objects, so in VBA:
- should be assigned to properties using [tt]Set[/tt] instead of [tt]Let[/tt],
- internal variables storing objects should be initialised, for instance in [tt]Class_Initialize()[/tt] event procedure.
Example:

Option Explicit

Private pFieldName As String
Private pFieldInfo As cFieldInfo

Private Sub Class_Initialize()
    Set pFieldInfo = New cFieldInfo
End Sub

Public Property Get FieldName() As String
     FieldName = pFieldName
End Property

Public Property Let FieldName(Value As String)
     pFieldName = Value
End Property

Public Property Get FieldInfo() As cFieldInfo
     Set FieldInfo = pFieldInfo
End Property

Public Property Set FieldInfo(Value As cFieldInfo)
     Set pFieldInfo = Value
End Property

Set internal object variable to [tt]Nothing[/tt] in [tt]Class_Terminate()[/tt] event procedure.
Adding fi to f ([tt]Test[/tt] procedure): [tt]Set f.FieldInfo = fi[/tt]
If you don't plan to add any validation to property procedures or make properties read or write only, variable declarations (public) will be sufficient, but in this case classes could be replaced by user defined types.

combo

 

[JasonEnsor]

Cheers for the advice combo, definitely food for thought. Although I’ve been doing bits of VBA for quite a few years it still feels like I am just scratching the surface.

Regards

J.