Posts by browncoat

    From what im reading variant types are inefficient and should be avoided so I’m wondering if its possible to avoid them when handling database record values. I have custom objects that have populated properties from a database and am using variant type for all of them so that I can determine if the field is null (e.g. I have fields where the value could be 0 or a negative number so using 0 or negative numbers to indicate they are empty this does not work). Is there a way to do this so I’m using the data type (e.g. long) rather than variant or is variant the best way to do this?


    I hope this makes sense. I have a few lines of text separated by line breaks (vbLf) and want the column widths and row heights to adjust so that each line of text is fully visible, i.e. the column width is as wide as the longest line of text and the row height is as high as the number of lines of text. I've tried WrapText both ways. Thanks

    1. Dim well as Range, MyRange as Range
    2. For Each well In MyRange
    3.     well.HorizontalAlignment = xlCenter
    4.     well.VerticalAlignment = xlVAlignCenter
    5.     well.WrapText = False
    6.     MyRange.Columns.AutoFit
    7.     MyRange.rows.AutoFit
    8. Next well

    Still not really clear. A Recordset is an object, so you should be able to simply query all the records you want in one go and assign them to a recordset. If you need to assign each record to a custom object, you can do that in a loop through your recordset, but if you need to make changes and write them back to the database, leaving them in a recordset seems simplest to me.

    Thanks Rory. That's an interesting idea. Is it possible to update a recordset object after the connection has closed? I'm currently populating custom class objects that are used in the application for data manipulation, etc, so the updates would need to be done at a later time after the user has gone through and made changes. For example at one point they might need data from Table A and be working on that and then need to pull data from Table B which will impact information in Table A and at the end they all need to be updated. I liked having the data in custom objects because I also populate the objects with other helpful information from related tables so I can access it very easily, i.e. a table has as field with a primary key of another table so I'll automatically populate a property with the "Name" in the other table's associated record, and I also like that I can easily identify the information I'm looking for with intellisense.

    In the meantime I've just created functions that basically create a new copy of the object to do it that way but the idea of being able to easily update tables using the recordsets seems like it might make things easier. I'm just not sure how I would handle doing the update at a later time and if there would be an easy way to reference the fields like I'm doing with the custom classes.

    Thanks again.

    There is no built-in way to copy your own objects. You'd have to add a copy or clone method (that you write) to your object. From what little you've provided so far, I'm not seeing any need to copy objects at all though. It sounds like you just need to create new instances, populate them, then put each one in the dictionary.

    Thanks Rory. The main reason I wanted to be able to copy was I have many records in a database which I'd like to be accessible via objects and rather than having to repeatedly query which can be slow when working remotely I wanted to just load the records into a bunch of objects in one fell swoop but perhaps I'm thinking about this the wrong way?

    Just when I thought I understood OOP in VBA I realize I'm still not figuring out how best to handle creating new objects. I am using a dictionary to store objects of the same type but am finding that I can't seem to copy the object into the dictionary to create a new object, it just references the original object. Is there a way to "copy" objects?

    I am basically populating the objects from a database and had been hoping it would help avoid having to repeatedly query the database if I could just get the data I wanted and stored into objects but if I have to create a "new" object independent of the object storing all the data then I believe I have to just query the database again which I was hoping to avoid.

    This happens occasionally and it's endlessly frustrating.. most of the time clicking debug when my code errors it takes me directly to the line that errored. But occasionally, it takes me many, sometimes, hundreds of lines of code before the line that actually caused the error and it takes a very long time to find the line that errored. Is there a reason for this behavior and is there a way to get around it? Thanks

    I have a class that has a variant array property. Sometimes this property has not been set but code attempts to get the value from it. I can't figure out how to check this without getting a type mismatch. Thanks for all help

    Thanks, though I am still having trouble. How can I pass a class object without specifying it's type or am I supposed to be doing this differently? I've tried

    I get runtime error 438, Object doesn't support this property or method.

    Thanks for your help

    So I've been using object classes with much success but one thing that is currently holding me up is passing different types of object classes to a userform and determining the type, if that is even possible. I want to customize the userform when it loads based upon the type of object that would be passed from another userform but am not sure how to go about this or if it is even possible. The only way I can currently think to do it is create a global object variable and check if it is not nothing and go from there but wondering if there is a more direct way to pass to the userform.


    I'm finally learning to use class objects and I wish I had done this sooner! It's making things much simpler to deploy and maintain but one bit I'm hung up on is creating an array or collection of a class objects. For example, I can load a record from a database into my class object, save it back to the database, etc, but if I want to load a bunch of records based on some criteria, I can't figure out how to group them. I've tried adding into an array and a collection but in both instances each record in the array, collection refers to the last object that was set because I'm using a 'placeholder' object to load into from a loop, see below (i've commented out the previous attempt at using an array), i.e. the debug line prints out the same PlateID from the last record that was selected in the table, even though it correctly loads each record before setting to the array or collection. Seems like these only point to the object but don't actually store it so my question is, is there any way to do what I'm trying to do or is there a better way I should be doing this?


    I have a bunch of xlsb files that contain data but also macros and there is a bug in the code that crashes the workbook upon opening because it tries to modify a protected sheet. There are hundreds of these files and I really don't want to go through and do this manually. I have a script that works on the workbook structure to pull out the data but it won't run on these workbooks because they crash upon being opened by the script. Is it possible to circumvent this?