"flexible" looping through a User Defined Type

hi all,

I'm just learning about User Defined Types (UDT's) and am trying to code to allow "flexible"/dynamic looping through a UDT to clear the values (no matter how many elements (terminology?) exist). 1) Is this possible?
2) Or should I learn some more & use a Class approach?

I have 6 UDT's in my code that I'm using to hold values which are pulled from text files & temporarily held in memory before being written to a spreadsheet in one go as an array.
As well as clearing the values, there may be times when I want to limit which "elements" of the Type are written to the spreadsheet. I am hoping to do this by commenting out the element in the Type declaration at the top of the module without having to make further changes within in the code. The way I thought I would be able to do this, was to use a "For each" or "For i = lbound(type) to ubound(type)" but I haven't been able to make it work. I was still hopeful until I found the below info...

Excel 2007 Help Files:
The For...Each...Next statement syntax has these parts:
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
element Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.
group Required. Name of an object collection or array (except an array of user-defined types).
statements Optional. One or more statements that are executed on each item in group.

Another page in the Help files reinforces this by stating:

For Each may not be used on array of user-defined type or fixed-length strings
For Each constructs are only valid for collections and arrays of intrinsic types, including arrays of objects. Also, arrays of fixed-length strings can't be iterated using For Each. This error has the following causes and solutions:
- The elements of the array in your For Each construct have a user-defined type.
- Use an ordinary For...Next loop to iterate the elements of the array.

Here's what I've tried so far...

Please Login or Register  to view this content.

Thanks in advance
Rob

Thanks Pike - I think it's mostly a case of not knowing the right words to ask my question - If I did, I could probably be a lot more succinct!

Update,
I've found a quick way of resetting the values of my types on another of Chip's pages.
Search http://www.cpearson.com/excel/VBAArrays.htm for the first occurence of "SetVariableToDefault" which then states

You can easily set a user defined type back to its default state by declaring a second variable of that type, e.g., Dim DefaultType As MyType and letting the elements take their default value. Then use LSet to set another instance of your UDT to DefaultType...

I'd never heard of LSet before but am adding it to my wee arsenal. The code I've added is:

Please Login or Register  to view this content.

I've got a LOT more reading to do before I get through Chip's link & may find the answer there but in the mean time...
(I don't think I was very clear about this in my first post)

QUESTIONS:
Is there a way of flexibly looping through the elements of a UDT?
- This would remove my current use of a "hardcoded" Temporary array (see code in post 1).

Thanks
Rob

hi all,

bump...

Thanks
Rob

Please Login or Register  to view this content.

:embarrassed:

Thanks Shg,
I'm a bit embarrassed that it is that simple & that I didn't manage to try that myself before starting the thread. I thought that "LSet" was a slightly strange/overkill approach after reading the Help file notes, but I went with it because I'd found the reference to it on Chip's site.


Is there a way of flexibly looping through the elements of a UDT to write them back to a sheet in one go?
For example...

Please Login or Register  to view this content.

Thanks
Rob

I knew you'd grimace when you saw that

Is there a way of flexibly looping through the elements of a UDT to write them back to a sheet in one go?

I would write one routine that accepts a UDT and a range, extracts all the data, and writes it out starting at the specified corner. That would permit some flexibility (the result need not be written out in a single column, for example). The routine would be, per force, ad hoc to the particular UDT.

I reckon it's conceptually possible to sniff out the data structure in any given UDT, in the same way that one (not me) could sniff a SafeArray structure, and make it UDT-independent. In practice, though, I don't think the published documentation exists to make it straightforward.

That would be interesting.

I'm a bit dozy so iy's llights out time for me...

Originally Posted by shg

...The routine would be, per force, ad hoc to the particular UDT.

Ahhh, I was scared you were going to say that, since it is adhoc, I'll stick with my current approach for the moment (see below... & laugh or cry or whatever - perhaps offer tips ).

Originally Posted by shg

I reckon it's conceptually possible to sniff out the data structure in any given UDT, in the same way that one (not me) could sniff a SafeArray structure, and make it UDT-independent. In practice, though, I don't think the published documentation exists to make it straightforward.

That's what I like, a practical challenge, making it UDT independent is the goal*. I'll start sniffing out the "safeArray" & let you know what I find
*It may slip off the radar though since I have the inelegant but functional solution.

'fyi, here's my WIP code for now - zipped because it was too long. I know it's useless for testing but the Print definition has confidential info & I'm more after the concept.

thanks
Rob

Originally Posted by Tom Schreiner

Just a thought after reading this. There is a very complex type call a recordset. It provides all of the methods you are enquiring about and much more. It also provides the flexibility of SQL and the ability to serialize itself. The range object from 2000 on has a built in method called CopyFromRecordset. If your textfiles are in any organized format, you can probably query them into the recordset and then do what you will from there. I can give you some examples if you are not dead set on using UDT's.

Thanks Tom,
An example or two would be great
I'm not "dead set on using UDT's" but I've learnt/know more about the ease of transferring variant arrays than I have/do about Recordsets. In saying that, I have seen RomperStomper & others mention copyfromrecrdset in other threads but I never considered it for this task. I'm running 2007 & the text files are reasonably structured with defined header sections & a varying number of rows.
Off the top of your head, how is a recordset approach likely to compare with my existing variant array approach (see zipped bas file)?

Edit: my existing approach has processed about 1500 files in approximately/potentially 5 minutes (?). I'm not sure exactly because the first 800 or so files, were a lot slower. I had forgotten to turn the calculation off & I had multiple calls to a UDF & some CSE formulae that were slowing the process down. Also, it's becoming more conceptual (or interest only) as I probably only have another 2000 files to process. The bottleneck is transferring the AS400 outputs from the print queue to a directory where I can process them.


thanks
Rob