CODEWITHSUNDEEP
vbaexcel
jerussell
jerussell

Reputation: 146

Advice on most conventional method of parsing data from an input file

I'm a somewhat novice writer of Excel Macros for people in my company. I can tackle the issue at hand in a few different ways but would like some advice on the most efficient and conventional manner to do so.

The macro needs to retrieve data from an input file and compare this data to other data from a second input file. These files are consistent in their structure but obviously different in the data they contain. Each file may contain information for up to 96 samples. Each sample has ~20 categories of information and each category may have 10 pieces of data. So, each sample could have up to 200 pieces of data tied to it.

It seems to me that the best way to store this information is to either create a Class to define an Object and then have a Collection of those Objects like:

Dim Samples as Collection
Dim Smp as CSample

Set Samples = New Collection

For x = 1 to NumberOfSamplesInFile
   Set Smp = New CSample
   'Set the properties of Smp for each piece of data
   Samples.Add Smp
Next x

I assume that I can have certain properties of Smp be arrays? I.e. can a property of the Class be defined as:

Private pSampleID as String
Private pAreaUnderCurve(1 to 10) as double
Private pRetentionTime(1 to 10) as double

Such that

Smp.SampleID = "XYZ"

but

Smp.AreaUnderCurve(1) = 1234
Smp.AreaUnderCurve(2) = 2345
Smp.AreaUnderCurve(3) = 123.78

and the same for retention time (different values obviously)?

The other way I imagined doing this is with the Type declaration:

Type Sample
   SampleID as String
   AreaUnderCurve(1 to 10) as double
   RetentionTime(1 to 10) as double
End Type

My questions is which way is most conventional/recommended?

Upvotes: 0

Views: 92

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

you could also import the two files into excel in 2 worksheets, then run a comparison on them either by looping through or putting in formulae to match the records and highlight unmatched data...

Is the format of the data files suitable for being imported into Excel?

Upvotes: 1

Related Questions