0

I promise I've searched for a solution but can't find anything to help me here. I am working on a procedure that will search between two reasonably sized ranges (less than 200 records) to make sure every ID number in one range is represented in the other. If a record is missing, a UserForm is prompted. I am getting a VBA out of memory error when the userform chooses to delete a record. I don't think I have any significant memory leaks, but can't seem to reorganize this to solve the problem. I have posted the code below.

I've never asked a question on here before, so please let me know if I need to provide any additional information. Thanks!

Public UFTargetCell As Range

Sub RxConfirmation2()

Dim RxWbk As Variant
Dim RxWks As Worksheet
Dim ConRDCol As Range
Dim ConRxCol As Range
Dim ConRxCell As Range
Dim OpsMetRxTemp As Worksheet
Dim C As Range

'Defines some variables using another procedure
FlagInit

'Opens Rx Selected Rx Chart

Set OpsMetRxTemp = Sheets.Add(After:=OpsMetWbk.Worksheets(2))
OpsMetRxTemp.Name = "RxTemp"

RxWbk = Application.GetOpenFilename(FileFilter:="Excel File (*.xlsm), *.xlsm")
Set RxWbk = Workbooks.Open(RxWbk)
Set RxWks = RxWbk.Sheets(1)
RxWks.Activate
RxWks.ListObjects("TableRx").Range.Copy
OpsMetRxTemp.Activate
OpsMetRxTemp.Range("A1").PasteSpecial
Application.CutCopyMode = False
RxWbk.Close

Set ConRDCol = RDSheet.Range("ImportRange").Columns("E")
Set ConRxCol = OpsMetRxTemp.Range("TableRx[Consultation ID]")

'Memory issue in here somewhere? Problem occurs when user opts to delete record. Code for that form is below.

For Each ConRxCell In ConRxCol
    Set C = ConRDCol.Find(ConRxCell, LookIn:=xlValues)
    ConRxCell.Select
    If C Is Nothing Then
        Set UFTargetCell = ConRxCell
        AddOrRemRecForm.Show

    Else:
        If C.Offset(, 29).Value <> ConRxCell.Offset(0, 1).Value Then
            C.Offset(, 29).Value = ConRxCell.Offset(0, 1).Value
            FlagUpdate (9)
        End If
    End If
    Next

Application.DisplayAlerts = False
OpsMetRxTemp.Delete
Application.DisplayAlerts = True

End Sub

Private Sub CommandButtonDel_Click()

UFTargetCell.Rows.Delete
Set UFTargetCell = Nothing
Unload Me

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Peyton Fry
  • 21
  • 1
  • 4
  • The error is possibly not even in the code itself. Do you have any add-ins running in the EXCEL.EXE process? What does `AddOrRemRecForm` do exactly? – Mathieu Guindon Mar 22 '18 at 16:56
  • I see this line at the end of your code: `Set UFTargetCell = Nothing`, but **five or six** variables which would also have objects assigned to them (any variable where `Set` is required) and should be set to `Nothing` when no longer in use. You should check that. – Mistella Mar 22 '18 at 16:56
  • The `AddOrRemRecForm` relates only contains the code under `CommandButtonDel_Click`. The error comes up after I select to delete a record. – Peyton Fry Mar 22 '18 at 17:03
  • Step through your code. If you find that you are coming back to your code several times and that the call stack gets deeper and deeper, you have created a recursive problem. That would be a plausible reason for out of memory error – Sam Mar 22 '18 at 17:09
  • Sam, I am very new to VBA so I am not what you are referring to in regards to the call stack. – Peyton Fry Mar 22 '18 at 17:20
  • Press Ctrl-L while debugging. You will see the chain of function calls that led you to where you are at the moment. – Sam Mar 22 '18 at 17:36
  • Refactoring your code to [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) may also help. And creating a [mcve] for us may actually help you solve your problem before you even need to give us the mcve. Right now your code is very broad even with other code calls that we cannot see. – Scott Holtzman Mar 22 '18 at 18:02

0 Answers0