Thanks Rory, that makes things easy!
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
Thanks, was looking for a VBA solution. I wasnt sure if there was already a built in way to determine this. Thanks.
For example, the range C4:H9, a 6x6 range, if I were to have cell C5 selected and wanted to know that it was the 7th cell going from left to right, up to down, how could I easily determine that?
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.
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.
That's incredibly helpful. The amount of time I would have saved had I known about Shift+F8... thanks very much for that link
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 helpCode
- ' Class property
- Public Property Get Concentrations() As Variant()
- If IsEmpty(mvConcentrations) Then
- Concentrations = Null
- Concentrations = mvConcentrations
- End If
- End Property
- Public Property Let Concentrations(conc_arr() As Variant)
- mvConcentrations = conc_arr
- End Property
- ' code that is supposed to check this property and avoid the error
- If IsNull(Plate_clsReagent.Concentrations) = False Then
- ' do something with the array
- End If
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 triedCode
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.
Ah yes of course thanks!
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?
- Private Sub Load_Plate_Button_Click()
- Dim selplateid As String, choice As Integer, rng As Range, row As Integer, i As Integer, selcount As Integer
- Dim fwddpm As String, revdpm As String
- Dim load_clsPlate As New clsPlates
- Dim list_clsPlates As New Collection
- 'Dim arr_clsPlate() as clsPlates
- ' selcount = 0
- For i = 1 To PlateForm.PlateList.ListCount - 1
- If PlateForm.PlateList.Selected(i) = True Then
- selcount = selcount + 1
- load_clsPlate.Load_Plates (PlateForm.PlateList.List(i, 16))
- 'MsgBox (load_clsPlate.PlateID_Plates)
- ' ReDim Preserve arr_clsPlate(1 To selcount)
- ' Set arr_clsPlate(selcount) = load_clsPlate
- list_clsPlates.add load_clsPlate
- End If
- Next i
- For Each load_clsPlate In list_clsPlates
- Debug.Print load_clsPlate.PlateID_Plates
- Next load_clsPlate
- end sub
I have a userform that contains a grid-like map of 125 items and I would like to be able to just capture the control name without needing to go through code for each 125 different controls. Is there anyway to have a routien that can just capture whatever control the mouse is hovering over when it is clicked?
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?