Posts by browncoat

I've found that rather than having each function/sub within an object run a new query and create a recordset, that passing the recordset from one object to another seems to really improve the speed when the database connection is over a network. Now there are a lot of different recordsets I would need to pass between objects because of how many different object types are contained within others, and so on, e.g.

Object1 --> Object2 -->Object4

--> Object3 --> Object5--->Object6

--->Object7

So I'm thinking I could just make a public recordset that reflects all the data from a table and each function/sub that handles that recordset, something like this (just spitballing here)

Public rsTableB as ADODB.Recordset ' In a Module

Public Function getObjectTypeB(ByRef objectB As clsObjectB) As Boolean 

Dim rsBookmark as Long
Dim rsFilterStr as String
Dim tableBinUse as Boolean

If rsTableB Is Nothing Then 
    loadRecordSet("TableB", ByRef rsTableB) ' separate function loads all records from table "TableB" in database
Else
    tableBinUse = True

    rsBookmark = rsTableB.Bookmark
    rsFilterStr = rsTableB.Filter
End If

Set objectB = New clsObjectB


' code to handle loading data from table into object here


If tableBinUse = True Then
    rsTableB.Filter = rsFilterStr
    rsTableB.Move 0, rsBookmark
Else
    rsTableB.Filter = ""
    rsTableB.Movefirst
End If

At some point these recordsets will need to be resynced/updated so I would need to figure out how and when to do that but admit I'm having trouble thinking of a good way to implement that. Alternatively I could just have the functions all pass these recordsets to one another optionally, and if it's not passed then that function loads the recordset itself. This latter strategy seems better but with the sheer number of Tables (around 30) it makes writing the functions parameters a bit confusing/tedious. Hoping to get feedback on whether this is just a terrible idea altogether or if there's a good way to go about this.

I have data pasted into a worksheet with column headings and I want to loop through the cells in the first row and the first column of the selection and look for specific fields and values. For example, I want to find which column contains "Name" in the first row so then when I loop through all the rows I can pick this value out for each row. But I can't seem to grasp how to refer to cells within a range and not absolute in the sheet.

This doesn't seem to work for me. Only a couple of named ranges get copied even though they are all set to worksheet scope. Is this even possible?

Sorry forgot to include that

Public Function Open_File() As String
Dim clarioFile As String
With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select the Clariostar File"
        .ButtonName = "Make It So"

        If .Show = -1 Then
            
            clarioFile = .SelectedItems(1)
            If Dir(clarioFile) = vbNullString Or (Right$(clarioFile, 4) <> "xlsx" And Right$(clarioFile, 3) <> "csv") Then
                MsgBox ("Invalid File")
                clarioFile = vbNullString
                Open_File = clarioFile
                Exit Function
                
            End If

        Else

            MsgBox ("A folder location must be chosen.")
            clarioFile = vbNullString
            Open_File = clarioFile
        End If
    End With
    
Open_File = clarioFile

End Function

This pops up after I copy data from one sheet to another using a VBA sub. The sheet contains named ranges with a worksheet scope so I don't know why that would be causing this error. If I click one of the cells with the #NAME? error and drag the formula across the other cells the error goes away and the values return. If I hit CTRL+Z to undo the values are still there. I have tried disabling and enabling automatic formulas to no avail.

This is driving me insane. I cannot figure out why this doesn't continue running. Once it's run it will open the file and then stop. It will only run again if I put a break right before opening the file and hit F8 to get it through, then I can let it continue with F5. This makes no sense to me!!

edit: OK, it seems to be an issue with using a shortcut key to start it. If I run it manually this doesn't happen.

Public Sub LoadData()

Dim fcell As Range, ncell As Range, row As Integer, col As Integer
Dim cellval As String, wb As Workbook, clarioBook As Workbook, clarioFile As String, ws As Worksheet

Set wb = ActiveWorkbook

clarioFile = Open_File

Set clarioBook = Workbooks.Open(clarioFile) 
' randomly stops here using the same file that works, no error given

Set ws = wb.Sheets.Add

ws.Name = Left(clarioBook.Name, 31)

clarioBook.Sheets(1).Range("A:AC").Copy Destination:=wb.Sheets(1).Range("A:AC")
clarioBook.Close SaveChanges:=False

End Sub

Figured it out:

insRange.Offset(foundCount).Value = timeRange.Cells(cel.Row - valRange.Row, cel.Column - valRange.Column).Value

I'm looping through all the cells in a range and want to get the value of the cell in the same position in another range that's the same size when the cell value in the first range equals a certain value and insert it into another cell. I can't quite figure how to do this. Here's where I am

Public Sub FindRelativeValue()
Dim valRange As Range, condRange As Range, cel As Range, foundCount As Integer, insRange As Range, timeRange As Range


Set valRange = Sheet1.Range("R19:AC26")   ' these two are
Set condRange = Sheet1.Range("PlateMap") '  the same size
Set insRange = Sheet1.Range("BM22")
Set timeRange = valRange.Offset(, 67)

For Each cel In valRange
   
    If condRange.Cells(cel.Row, cel.Column).Value = "DMSO" Then
        insRange.Offset(foundCount).Value = timeRange.Cells(cel.Row, cel.Column).Value
        insRange.Offset(foundCount, 1).Value = cel.Value
        foundCount = foundCount + 1
    End If
Next cel

End Sub

I found a solution:

https://superuser.com/question…g-as-if-it-were-a-formula

Requires a custom function that calls the Evaluate function in VBA.

Quote from Carim

Glad you could fix your problem

Thanks for your Thanks ..AND for the Like

Actually this isn't doing what I was looking for sorry, I got excited and didn't realize it wasn't using cell C2.

I want to be able to take cell C2 directly and use it in a formula, does that make sense? Thanks again

Quote from Carim

May be the following test file can help ...

Yes that's exactly what I was trying to do, thanks!

Quote from Carim

Hello,

Your explanation is not very clear ... with =amount * 7 ... if amount = 5 ... you will get 35 ... and not 40 ...

Why don't you attach a sample file to illustrate your objective ...

Right, but the calculation I want to be able to do is in another cell that will take that formula and incorporate it into another formula. In the example I gave, it is simply adding the value of cell B3 which is 5 with the formula given that has a value of 35, so the final result would be 40. This is a simplified example which can be solved in other ways, but I just kept it simple for the sake of explaining the behavior I want. I want to be able to insert equations as text basically so that they can be used as formulas in other cells. I am working with lots of different models with equations that can often be substituted in and out (within other larger equations) so being able to just change these on the fly by entering them in a single cell is what I'm looking to be able to do. Thanks

Not sure if this makes sense. Let's say I wanted to the user to just enter "amount * 7" in a cell with amount being a named range such that other cells can incorporate this into their existing formula,

e.g. I've tried "=B3+INDIRECT(A1)" which obviously doesn't work, but ideally if B3 is 5 and amount is 5, then it would calculate as 40. I'm really just wanting to be able to write custom formulas into a cell and have them update a whole bunch of cells. Or is there a better way to do this that I'm not thinking?

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?

thanks

Just figured it out... changed/added this:

well.WrapText = True
well.EntireColumn.ColumnWidth = 255
well.EntireRow.RowHeight = 100

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

Dim well as Range, MyRange as Range

For Each well In MyRange
    well.HorizontalAlignment = xlCenter
    well.VerticalAlignment = xlVAlignCenter
    well.WrapText = False
    MyRange.Columns.AutoFit
    MyRange.rows.AutoFit
Next well

Resolved

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?