×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

CODE --> vba

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

CODE --> vba

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)

CODE --> vba

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

CODE --> vba

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.

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

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

CODE --> VBA_cField_class

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 Nothing in Class_Terminate() event procedure.
Adding fi to f (Test procedure): Set f.FieldInfo = fi
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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close