Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write to file the contents of a generic array/collection
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write to file the contents of a generic array/collection
the easiest way is to use the beforeprint event. Then return Cancel so 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 print function. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197295 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write to file the contents of a generic array/collection
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write to file the contents of a generic array/collection
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) On 21 Apr, 15:35, joel wrote: the easiest way is to use the beforeprint event. *Then return Cancel so 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 print function. -- joel ------------------------------------------------------------------------ joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=197295 http://www.thecodecage.com/forumz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write to file the contents of a generic array/collection
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 On 21 Apr, 17:48, "Peter T" <peter_t@discussions wrote: 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" wrote in message ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write to file the contents of a generic array/collection
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 "deltaquattro" wrote in message ... 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 On 21 Apr, 17:48, "Peter T" <peter_t@discussions wrote: 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" wrote in message ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generic file path using vba | Excel Programming | |||
Array vs. Collection | Excel Programming | |||
check if file with generic name already open | Excel Programming | |||
write 1-dimensional array of integers to text file | Excel Programming | |||
Write to Array from other closed Excel file | Excel Programming |