How to write to file the contents of a generic array/collection

[deltaquattro]

Hi,

I'm trying to write a general subroutine to print the contents of an
array/collection to a file . So far I've come up with the following
subroutine:

Function WriteDataToFile(FileName As String, Container As Variant) As
Boolean
'Write each element contained in Container (either an array or a
collection) to file FileName
Dim FNumber As Long, Element As Variant

'Find free file number
FNumber = FreeFile

'Create new file or overwrite existing one
Open FileName For Output As #FNumber

'Write array to file
For Each Element In Container
Print #FNumber, Element
Next

'Close file and exit
Close #FNumber

End Function

Albeit simple, this function is able to handle perfectly
multidimensional arrays and collection of standard and user-derived
type. However, the Print # function doesn't work for an Object data
type, so it doesn't work for arrays/collections which contain Objects.
How can I write a function which is able to write to file the contents
of an array/collection, irrespective of what type of variables it
holds?

My idea: add code into WriteDataToFile which, For Each Element In
Container, determines whether Element is a variable of standard/user
defined data type or an Object. In the first case I use the Print
function, in the second I call a Print method which I will define in
all of the class module I write. Two questions arise:

1. How do I determine if Element is of standard/user defined data type
or Object data type?
2. How do I write the Print method for my Classes? I tried something
like:

' Print Method
Public Function Print(FileUnit As Long)
'here goes code which prints each Property to file, something like:
Print #FileUnit, Name
Print #FileUnit, Surname
..
..
..
End Function

but this didn't work because Print is a reserved keyword, so I renamed
it PrintToFile (btw, is there any workaround which would still allow
me to name the method Print?)

Well, that's all for now, I'll appreciate if you can answer my
questions, or suggest a completely different approach, in case you
think mine is unfeasible. Thanks in advance,

Best Regards

deltaquattro

 

[joel]

the easiest way is to use the beforeprint event. Then return Cancel s
excel doesn't run the real pring routine

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk in Worksheets
wk.Calculate
Next
End Sub



You could also creatte a class module that overrides the real prin
function.

 

[Peter T]

I'm not really following what you are trying to do but tat the end of the
day you need to write text in each loop.

For Each Element In Container
Print #FNumber, Element
Next

This will only work if Element defaults to text, even if a little coercion
is required (if say a number). It would work if say Element is a single
cell range object and Container a larger range of cells.

If Element is an object, and it's default property is not text (or easily
coercible to text) you need to include whatever property or set of
properties is required to return the text. It might be something as simple
as

For Each Element In Container
Print #FNumber, Element.Text
Next

But it really depends on the object and in particular it's default property.

Regards,
Peter T

 

[deltaquattro]

Hi, joel,

thanks for your interest in my problem. I'm sorry I do not understand
your answer: I am talking about the Print # function which writes
display-formatted data to a file, not about printing worksheets on
paper. Also, how do I override a function with a class module? If I
try to define a method called Print in a class, the VBE issues an
error. Thanks,

Best Regards,

Sergio Rossi (deltaquattro)

 

[deltaquattro]

Ciao Peter!

Yes, you got my point! I want to write text to a file: the text is the
contents of the various objects/used-defined types which are contained
in an array or a collection.
If I used an older programming language such as Fortran, I would have
to write a a different subroutine for each of these:
- write to file the contents of a 1D array containing Long;
- write to file the contents of a 2D array containing Long;
- ...
- a 1D array containing Double;
- ...
- a Collection containing Cfoo (Objects of a class written by me);

and so on. In VBA, instead, I hoped to be able to write a single
subroutine which works for all of them. Now, as you correctly point
out, the simple loop

For Each Element In Container
Print #FNumber, Element
Next

works fine in a lot of cases, since standard data types and most user-
defined data types (at least, all the ones I defined so far) are
coercible to text. This doesn't work for Objects of a class defined by
me, though, because I don't define default properties for them.
Defining a default property is not possible with the VBE of Excel, but
it can be done exporting the class module to text and using a text
editor:

http://www.cpearson.com/excel/DefaultMember.aspx

However, even if I did that, that wouldn't solve my issue, since I
want to write to file *all* the contents of each Object, not just its
default property. So I thought of the following scheme:

1. For each Element of the Container, I check whether it is an Object
or not:

For Each Element In Container
' Some way to check if Element is of Object data type or not
If NotAnObject Then
Print #FNumber, Element
Else
Element.PrintToFile(#FNumber)
End If
Next

So, first question is: how do I check if a variable is of Object data
type or not? Even better, can I check if it belong to a Class which I
defined, or if it's a standard Excel Object, such as a Range?

2. Second question: I need to add a PrintToFile method to each of the
Classes I define, which prints out to file all the properties of the
Object. How do I do that? I tried something like:

' Print Method
Public Function Print(FileUnit As Long)
'here goes code which prints each Property to file, something like:
Print #FileUnit, Name
Print #FileUnit, Surname
..
..
End Function

but this didn't work because Print is a reserved keyword, so I renamed
it PrintToFile (btw, is there any workaround which would still allow
me to name the method Print?). Is this a good way? Is there a more
general/easier/better way? Thanks

Best Regards,

Sergio Rossi

 

[Peter T]

You seem to be asking for some generic routine that will return all text
type properties of any arbitrary object. Whilst it might be theoretically
possible (with an incredible amount of work) if the object is defined in a
type library, it's not viable. Obviously there will be nothing defined in a
library about your own objects class objects. Take the cell (range object),
it has Value, Value2, Text, ID etc. So apart from the default you need to
get each by property-name. You might even need to dig deeper into the
object.

As for your own objects why not include a method to get all text properties,
maybe something like this

' Class1
Private msName As String
Private msAddress As String
Private mnID As Long

Public Property Let Employee(sName, sAddress, nID)
mnID = nID
msName = sName
msAddress = sAddress
End Property

Public Function GetHeaders() As String
GetHeaders = "ID" & vbTab & "Name" & vbTab & "Address"
End Function

Public Function GetProps() As String
GetProps = CStr(mnID) & vbTab & msName & vbTab & msAddress
End Function

' Normal module
Sub test()
Dim col As Collection
Dim C As Class1
Set col = New Collection
For i = 1 To 2
Set C = New Class1
C.Employee("Tom-" & i, i & " Main St") = i * 100
col.Add C, CStr(i)
Next

Debug.Print col(1).GetHeaders
For Each C In col
Debug.Print C.GetProps
Next
End Sub


Before starting with your Print function try and get all your properties
returned as strings first. Personally I wouldn't bother with setting the
default property of a class object, generally better to explicitly get what
you are after (that applies to objects in general also).

Best never use a keyword as the name of a procedure or variable, so stick
with PrintToFile instead of Print.

Regards,
Peter T


Ciao Peter!

Yes, you got my point! I want to write text to a file: the text is the
contents of the various objects/used-defined types which are contained
in an array or a collection.
If I used an older programming language such as Fortran, I would have
to write a a different subroutine for each of these:
- write to file the contents of a 1D array containing Long;
- write to file the contents of a 2D array containing Long;
- ...
- a 1D array containing Double;
- ...
- a Collection containing Cfoo (Objects of a class written by me);

and so on. In VBA, instead, I hoped to be able to write a single
subroutine which works for all of them. Now, as you correctly point
out, the simple loop

For Each Element In Container
Print #FNumber, Element
Next

works fine in a lot of cases, since standard data types and most user-
defined data types (at least, all the ones I defined so far) are
coercible to text. This doesn't work for Objects of a class defined by
me, though, because I don't define default properties for them.
Defining a default property is not possible with the VBE of Excel, but
it can be done exporting the class module to text and using a text
editor:

http://www.cpearson.com/excel/DefaultMember.aspx

However, even if I did that, that wouldn't solve my issue, since I
want to write to file *all* the contents of each Object, not just its
default property. So I thought of the following scheme:

1. For each Element of the Container, I check whether it is an Object
or not:

For Each Element In Container
' Some way to check if Element is of Object data type or not
If NotAnObject Then
Print #FNumber, Element
Else
Element.PrintToFile(#FNumber)
End If
Next

So, first question is: how do I check if a variable is of Object data
type or not? Even better, can I check if it belong to a Class which I
defined, or if it's a standard Excel Object, such as a Range?

2. Second question: I need to add a PrintToFile method to each of the
Classes I define, which prints out to file all the properties of the
Object. How do I do that? I tried something like:

' Print Method
Public Function Print(FileUnit As Long)
'here goes code which prints each Property to file, something like:
Print #FileUnit, Name
Print #FileUnit, Surname
..
..
End Function

but this didn't work because Print is a reserved keyword, so I renamed
it PrintToFile (btw, is there any workaround which would still allow
me to name the method Print?). Is this a good way? Is there a more
general/easier/better way? Thanks

Best Regards,

Sergio Rossi