Posts by browncoat

Quote from rory

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.

Quote from rory

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 help

' Class property


Public Property Get Concentrations() As Variant()
If IsEmpty(mvConcentrations) Then
    Concentrations = Null
Else
    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 tried

Private Sub Test_Type_Button_Click()
Dim test_obj As clsMyClass
Set test_obj = New clsMyClass

Test_Object_Type(test_obj)

End Sub

Public Sub Test_Object_Type(my_obj As Object)
    Debug.Print TypeOf my_obj Is clsMyClass



End Sub

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.

Thanks

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?

Thanks

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?

As usual, once I actually post for help I end up finding the answer... I swear I was trying to figure this out for like an hour!

Needed to move it to the BeforeUpdate sub for the listbox as found here

I'm using the first row of the listbox to populate column headings and want to unselect it when the user selects it. I can accomplish this fine by clicking a button that sets the listindex = -1 but I can't get it to do this when the user clicks or when the listbox changes. I've tried using a global boolean to disable the change event but no matter what, after the subs run, listindex returns to 0 and the first row is selected. I've also tried setting .selected(0) = false but this also does not work.

Here's the code I currently have

Option Explicit
 Public skipchange As Boolean


Private Sub PlateList_Change()    ' I've also tried putting this code in the click event for the listbox
Dim x As Integer, c As Integer, strarr() As String, selplatedata As String

If skipchange = True Then
    skipchange = False
    Exit Sub
end if

If SetupForm.PlateList.ListIndex = -1 Then
    Exit Sub
End If

If SetupForm.PlateList.ListIndex = 0 Then
    skipchange = True
    SetupForm.PlateList.ListIndex = -1
    skipchange = False
    Exit Sub
End If



End Sub

Quote from sobi_256

i can only come up with the attached solution,had to insert some columns, thr might be a way to get the desired answer without inserting anything, but i do not know that

Thanks Sobi

Sure, sorry

=AVERAGE(AVERAGEIF(Standards['[35S']GTPyS],[@Nominal],Standards[Rep1]),AVERAGEIF(Standards['[35S']GTPyS],[@Nominal],Standards[Rep2]),AVERAGEIF(Standards['[35S']GTPyS],[@Nominal],Standards[Rep3]))

I have three values I'd like to average based on the adjacent cell (@NOMINAL) in a table and it works if all the cells have values but if one of them is empty it returns #DIV/0. Is there a way I can have it average only the two values if the third is empty?

Thanks

I found this function somewhere on the internet and it works exactly as I need it, however when the save path is set to the UNC ("//server/folder/subfolder/"), I get "File not found" when the file should be created if it doesn't exist. However, it works if I use the path I have mapped, e.g. Z:\folder\suberfolder\. I need it to use the UNC path since the file will be shared and not everyone has the drive mapped.

Here is the full function:

Public Function ExportRange(WhatRange As Range, Where As String, Delimiter As String) As String
Dim HoldRow As Long    'test for new row variable
Dim c As Range
HoldRow = WhatRange.row
'loop through range variable
 
For Each c In WhatRange
 
    If HoldRow <> c.row Then
 
        'add linebreak and remove extra delimeter
        ExportRange = Left(ExportRange, Len(ExportRange) - 1) & vbCrLf & c.text & Delimiter
        HoldRow = c.row
    Else
        ExportRange = ExportRange & c.text & Delimiter
    End If
Next c
'Trim extra delimiter
 
ExportRange = Left(ExportRange, Len(ExportRange) - 1)
 

 Debug.Print Where
'Kill the file if it already exists
 If check_path(Where) = True Then
     Kill Where
End If
 

 Open Where For Append As #1
Print #1, ExportRange
 
Close #1
Debug.Print Where

 
 
End Function

Sorry I don't understand how this is not clear. In the Visual Basic editor there is a button with a blue square on it and it is next to the pause/break button and the run button. Pushing this button ends the current executing code. When an error occurs in my code that cannot be debugged while the code is running it needs to be stopped. I am simply wondering if there is a way for a macro to execute after the code is stopped .

Obviously but that's not the point. It's not any specific code. I am continually working on this and new errors pop up as I work on it so when it happens I'd like for a macro to automatically execute. It's really a simple answer I'm looking for: whether or not it's possible for a macro to execute when the reset/stop button is hit on executing code.