Finding the problem with the code that gives the message "The picture is too large and will be truncated" ...

[chazrab]

This only happens when I run this code. The result displays all filtered rows on sheet CARDS2024 that match the value "clothes" in the Memo column 4 that contains
multiple lines of descriptive text. Even after I added Application Enable Events and Application Screen Updating to False at the beginning of the code and rese to True at the end of the code it still gives the message "The picture is too large and wijll be truncated". CoPilot suggested this is a Clear clipboard issue, which I did and it still shows the message. This does no happen with any other code block in this large application - only this one given below. So, it must be someting in the code. Rebooting to recover more memory does not fix it either. I'm at a loss. If using arrays in the code would help I'll do that but have no idea where to insert the array(s) as I have not used arrlays before.

Private Sub cmdFILTERAUTO_Click()
    Dim lastrow As Long, wc, wr As Worksheet
    Set wc = ThisWorkbook.Sheets("CARDS2024")
    Set wr = ThisWorkbook.Sheets("REPORT")
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
   
    wr.Cells.Clear
    lastrow = wc.Cells(wc.Rows.count, 5).End(xlUp).row
    wc.Range("A2").AutoFilter
    wc.Range("$E$1:$EI$" & lastrow).AutoFilter Field:=4, Criteria1:="=*clothes*", Operator:=xlAnd
    wc.AutoFilter.Range.SpecialCells(xlVisible).copy Destination:=wr.Range("A1")
    Application.CutCopyMode = False
    wc.AutoFilterMode = False

    With wr
        If .Range("A1") <> "" Then
            If WorksheetFunction.CountA(.Range("E:E")) = 1 Then
                .Range("E1:E1").Name = "ResultTotal"
                .Range("ResultTotal").Offset(2, 0).Formula = "=SUM(ResultTotal)"
                .Range("ResultTotal").Offset(2, 0).Name = "SumResultTotal"
                .Range("SumResultTotal").NumberFormat = "$#,##0.00"
            Else
                .Range("E1:E" & .Range("E1").End(xlDown).row).Name = "ResultTotal"
                .Range("ResultTotal").End(xlDown).Offset(2, 0).Formula = "=SUM(ResultTotal)"
                .Range("ResultTotal").End(xlDown).Offset(2, 0).Name = "SumResultTotal"
                .Range("SumResultTotal").NumberFormat = "$#,##0.00"
            End If
            UpdateTotalInTextBox2 wr
        End If
    End With
   
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Private Sub UpdateTotalInTextBox2(wr As Worksheet)
    Dim total As Variant
    total = wr.Range("SumResultTotal").value
    If IsNumeric(total) Then
        UserForm1.TextBox2.value = Format(total, "Currency")
    Else
        UserForm1.TextBox2.value = "N/A"
    End If
    UserForm1.Show
End Sub

The code works great except for this quirk. Any insights to solve this surely would be appreciated Thanks for anyone's help.
cr

v

 

[chazrab]

Did some look back. It seems I adjusted the control size to fit the range. I'm not sure if this is useful for your project but here's an adaptation. HTH. Dave

Private Sub UserForm_Initialize()
Dim Rng As Range, Tmp As Double, Tstr As String
' Find the last row with data in column A
Set ws = ThisWorkbook.Sheets("sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set the range for the ListBox RowSource
Set Rng = ws.Range("A1:G" & lastRow)
' Set the RowSource property of ListBox1
Me.ListBox1.RowSource = Rng.Address(external:=True)
'Me.ListBox1.Height = Rng.Height
'DoEvents
Me.ListBox1.ColumnCount = 7
Me.ListBox1.Width = Rng.Width
DoEvents
Tmp = Me.ListBox1.Width / Me.ListBox1.ColumnCount
Tmp = Tmp - 1
For cnt = 1 To Me.ListBox1.ColumnCount
Tstr = Tstr & Tmp & ","
Next cnt
Me.ListBox1.ColumnWidths = Left(Tstr, Len(Tstr) - 1)
End Sub

Here's more update info on this issue:

me to CoPilot: This code gives this error. I put it in the userform code at the top

CoPilot back to me, everything below:
Ah, I see the problem. The error occurs because the code is placed at the top of the UserForm code module, outside of any procedure or function. In VBA, all executable code must be placed within a procedure (Sub or Function). To fix this, you need to place the code inside an appropriate event procedure. The UserForm's Initialize event is a good place to set the ListBox's RowSource. Here's how you can do it:

Open your UserForm in the VBA editor.
Double-click the UserForm to open its code window.
Add the following code inside the UserForm_Initialize event procedure:

vba

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
' Set the worksheet

Set ws = ThisWorkbook.Sheets("REPORT")
' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set the range for the ListBox RowSource
Set rng = ws.Range("A1:G" & lastRow)

' Set the RowSource property of ListBox1
Me.ListBox1.RowSource = rng.Address(external:=True)
End Sub

This code will run whenever the UserForm is initialized and set the RowSource property of ListBox1 based on the actual number of rows in the filtered data. Give this a try and let me know if it resolves the issue!

Me back to CoPilot:
Seems to work great with just a few test runs. If this conserves memory and space as opposed to setting a predetermined Rowsource of =REPORT!A1:G1000, and prevents this "This picture is too large and will be truncated" essage, as well as an "Out of memory" message, then this is a major problem Microsoft has failed to correct and would be very easy - IF this continues to work with all future runs. Also, putting copying results to a sheet stored in an array instead of writing it back to the sheet each time, was indicated to me a while back, that this is much faster and way more efficient since the action is all stored in memory. I need, whoever you are to comment on these two things, because I don't want to go on a false rabbit trail and end up disappointed. Comments, please

CoPilot to me:
I’m glad to hear the dynamic RowSource adjustment seems to work well for you so far! Let’s dive into the two points you mentioned:
Memory and Space Considerations

Dynamically setting the RowSource property to match the exact number of rows in the autofilter results can indeed help conserve memory and avoid issues related to the predefined, larger range. This approach ensures that the ListBox only holds the necessary data, which minimizes memory usage and reduces the risk of encountering the "This picture is too large and will be truncated" message, as well as potential "Out of memory" errors. It's a more efficient way to manage the data, especially if your autofiltered results vary in size.

Using Arrays for Efficiency
Storing results in an array instead of writing back to the sheet each time is generally a more efficient approach. Here's why:
Speed: Reading and writing to arrays is much faster than interacting with worksheet cells because arrays are stored in memory.
Efficiency: Arrays reduce the number of read/write operations on the worksheet, which can significantly improve performance, especially with large datasets.
Memory Management: While arrays do use memory, they are often more efficient in handling large amounts of data compared to repeatedly accessing the worksheet.
In VBA, you can use an array to store data, manipulate it, and then write the results back to the sheet in one operation, reducing overhead. Here’s a basic example of how you can use an array in VBA:

vba

Dim dataArray() As Variant
Dim lastRow As Long
Dim ws As Worksheet

' Set the worksheet
Set ws = ThisWorkbook.Sheets("REPORT")
' Find the last row with data
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Read data into the array
dataArray = ws.Range("A1:G" & lastRow).Value
' Process data in the array (example)
For i = LBound(dataArray, 1) To UBound(dataArray, 1)
' Example processing: Convert to uppercase
dataArray(i, 1) = UCase(dataArray(i, 1))
Next i

' Write data back to the sheet (if needed)
ws.Range("A1:G" & lastRow).Value = dataArray
[B]
Summary

In summary, dynamically setting the RowSource and using arrays are both effective strategies to optimize memory usage and improve performance in your VBA application. These approaches can prevent memory-related issues and enhance the efficiency of your code.[/B]
Feel free to continue testing, and don't hesitate to reach out if you encounter any further challenges. Happy coding!

To me, this is good information.  I'm not a big fan of For Loops but if it works, I'll use it.  On web comments on this issue, too many people have struggled with this truncated and memory issue, and if all this research and testing finds true causes, then its a blessing.  I didn't look at your code response yet, as I wanted to continue the tread reply with these updates.   cr

 

[chazrab]

Further updates:

Me back to CoPilot:
On using arrays, there's an issue:

If I read what arrays do and don't do correctly - using an array may be faster, more efficient and conserve memory - but - any search is not saved since no copying and writing back to a sheet occurs. Everything is erased once the app closes. If a user wants to access stored data which contains updates and changes, they won't be able to since no sheet changes were made and saved if n array is used. Correct me if I'm wrong. Hence, this would be a very major drawback of ever consideri using an array. Am I seeing this right or not?

CoPilot to me:

You're absolutely correct. Using arrays for in-memory data manipulation has many benefits in terms of speed and efficiency, but the main drawback is that any changes made to the data stored in the array are not saved unless explicitly written back to the worksheet or another storage medium before the application closes.

Here's a summary of the key points:

Advantages of Using Arrays​

1. Speed: Arrays allow for quick access and manipulation of data, which can significantly improve performance.
2. Efficiency: Reduces the number of read/write operations on the worksheet, which can be slow and resource-intensive.
3. Memory Management: Handles large datasets more efficiently in memory.

Drawbacks of Using Arrays​

1. Volatility: Any changes made to the data in the array are lost once the application closes unless those changes are written back to a persistent storage location, such as a worksheet or a file.
2. No Automatic Saving: Users will not have access to updated data after the session ends unless the changes are explicitly saved.

Combining Approaches​

To get the best of both worlds, you can use arrays for temporary data manipulation and then write the updated data back to the worksheet when needed. This way, you can benefit from the speed and efficiency of arrays while ensuring that changes are saved. Here's a combined approach:

1. Read Data into Array: Perform operations and updates in memory.
2. Write Data Back to Worksheet: Save the final results to the worksheet to ensure persistence.

Here's a sample code snippet illustrating this approach:

vba
Dim dataArray() As Variant<br>Dim lastRow As Long<br>Dim ws As Worksheet<br><br>' Set the worksheet<br>Set ws = ThisWorkbook.Sheets("REPORT")<br><br>' Find the last row with data<br>lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row<br><br>' Read data into the array<br>dataArray = ws.Range("A1:G" &amp; lastRow).Value<br><br>' Process data in the array (example)<br>For i = LBound(dataArray, 1) To UBound(dataArray, 1)<br> ' Example processing: Convert to uppercase<br> dataArray(i, 1) = UCase(dataArray(i, 1))<br>Next i<br><br>' Write data back to the sheet to save changes<br>ws.Range("A1:G" &amp; lastRow).Value = dataArray<br>
By writing the data back to the worksheet at the end, you ensure that any updates are saved and accessible in future sessions. This approach balances the performance benefits of using arrays with the need to persist data changes.

I hope this clears up any confusion! If you need further assistance or have more questions, feel free to ask.





Message Copilot

 

[NdNoviceHlp]

I'm not sure that I understand "your" response. Maybe see the top of the page red lettering. The last code I posted was userform initialize code. Was it useful? Dave

 

[chazrab]

Hi Dave - this should help. Let me know if you don't understand whats happening - at least in my app

Me to CoPilot:



This code and all the revious code misses on vital element which I now found the error messages of Out of memory it generates. See snip: the code is not clearing the clipboard from memory and if a lot of searching is done clipboard memory keeps getting added to. This is a serious error in code and it has to be accounted for.

CoPilot to me:


Thank you for pointing that out! Clearing the clipboard can indeed prevent memory overflow issues, especially when dealing with a lot of copying and pasting in VBA. We can add code to clear the clipboard at the end of your procedure.


Here's your updated cmdCARDSEARCH_Click procedure with the addition of clearing the clipboard:

Private Sub cmdCARDSEARCH_Click()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim rng As Range
Dim scchval As String
Dim TotalCell As Range
Dim totalSum As Double
Dim dataArray() As Variant
Dim lastRow As Long
Dim i As Long

' Assign variables to worksheets
Set wsDest = Worksheets("REPORT")
scchval = CARDFIND.TextBox1.Value ' Get the value from Textbox1 on the userform

' Set wsSource based on ComboBox1 selection
Set wsSource = Worksheets(ComboBox1.Value)

' Clear REPORT sheet
wsDest.Cells.Clear

' Clear any previous filter
wsSource.AutoFilterMode = False

' Apply filter on column D with the value in scchval
Set rng = wsSource.Range("A1", wsSource.Cells(wsSource.Rows.Count, "F").End(xlUp))
rng.AutoFilter Field:=4, Criteria1:="*" & scchval & "*"


' Find the last row with data in column A
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row



' Copy filtered data into an array
dataArray = wsSource.Range("A1:G" & lastRow).SpecialCells(xlCellTypeVisible).Value


' Write filtered data from the array to the REPORT sheet starting from cell A1
wsDest.Range("A1").Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray

' Remove the filter
wsSource.AutoFilterMode = False


' Calculate the sum of the amounts in column E of the filtered values
totalSum = Application.WorksheetFunction.Sum(wsDest.Range("E1:E" & wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Row).SpecialCells(xlCellTypeVisible))


' Set the cell to display the total two rows below the last row
Set TotalCell = wsDest.Cells(wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Row + 2, "E")
TotalCell.Value = totalSum
TotalCell.NumberFormat = "$#,##0.00" ' Format as currency


' Assign a name to the total cell
TotalCell.Name = "CustomTotal"



' Update Textbox1 on the UserForm CARDRESULTS with the total sum formatted as currency
CARDRESULTS.TextBox1.Value = Format(totalSum, "$#,##0.00")

' Store the search value in the REPORT sheet
wsDest.Range("H1").Value = scchval ' Assuming cell H1 is used to store the search value



' Clear the clipboard to prevent memory issues
Application.CutCopyMode = False


' Optionally, clear the clipboard using an external method (for certain Excel versions)
' ThisWorkbook.Application.CommandBars("Clipboard").Visible = False


CARDRESULTS.Show
End Sub

Explanation​

• Clearing the Clipboard: The line Application.CutCopyMode = False clears the clipboard, which helps prevent memory overflow issues.
• Optional External Method: For certain versions of Excel, you can use the CommandBars method to clear the clipboard, but it's commented out as it's generally less common.

This should address the "Out of memory" errors you've encountered. Give it a try, and let me know if it resolves the issue or if you need further assistance!

These comments are from CoPilot. Here are mine:
RE: your not understanding what I'm sending you: I sent my back and forth dialog with CoPilot and the wnsuing code given as corrections by CoPilot
to drill down to the problem of "The picture is too large and will be truncated" message. In addition, I was also experiencing an "Out of memory" message, both which troubled me great deal. After all this bantering back and forth, what I dixcovered was that at lest y code was caused by the clipboard accumulating copy to's and not releasing them from its memory. When I did this manually, both messages stopped. But, who wants to do anything manually? Not me. So, the code now includes a line which is supposed to clear all this stuff on the left panel of the snip which is a view of the memory clipboard. BTW: these clipboard paragrahs extend a very long way down, not just these 3. It was at least for now, a clopboard memory hog issue.

cr

 

[NdNoviceHlp]

Seems like you have figured it out. However you are no longer using copy/paste with your last code... you are using RESIZE which does not use the clipboard. So it doesn't seem there would be a need to clear the clipboard. Anyways, I hope the solution will continue to work for you. Dave