Why am I getting an Out of Memory error with this specific VBA code?

Question

I have Power Query table in an Excel spreadsheet that filters a different table for a specific Index number using a List Box of Client Names.

The first table has 3 columns:

Index	Attribute	Value
1	Attribute 1	Value A
1	Attribute 2	Value B
2	Attribute 1	Value C
2	Attribute 2	Value D
3	Attribute 1	Value E
3	Attribute 2	Value F

I used a list box which has a list of client names, which when selected, updates a table. So if I selected Client 1 in the list box, the table would update to say:

Attribute	Value
Attribute 1	Value A
Attribute 2	Value B

The code to update this table is pretty simple and I haven't had any issues with it until recently.

ActiveWorkbook.Worksheets("Sheet Name").ListObjects("Table_Name").QueryTable.Refresh BackgroundQuery:=False

I have recently started getting Error 7 Out of memory, especially when I let the spreadsheet sit. When I hit 'Debug', it always highlights this section of code. I am not sure how to go about diagnosing what is causing this issue. I have read the documentation from Microsoft, but am not sure where to start troubleshooting this for my own situation.

Here is the Power Query code:

let Source = Excel.CurrentWorkbook(){[Name="Review_Data"]}[Content], 
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Attribute", type text}, {"Value", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Index] = #"Name Filter")), 
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type)
in #"Added Index"

Which version of Excel/Office, and which OS (Mac/Win) ? How long/soon before the problem shows itself? — Tim Williams
– Tim Williams, Commented Nov 20 at 21:26
the problem is most probably not with your VBA code, but likely caused by Power Query code and the data together (looks like an infinite loop is triggered). Please share your power query code. — Máté Juhász
– Máté Juhász, Commented Nov 21 at 7:49
Consider migrating to an actual database app like Access instead of forcing Excel to act like a database. — June7
– June7, Commented Nov 24 at 9:14
@MátéJuhász 'let Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Attribute", type text}, {"Value", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Index] = #"Name Filter")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type) in #"Added Index"' Sorry not sure how to format this like code — Brian Schmitz
– Brian Schmitz, Commented Nov 25 at 15:23
Edit your question to show relevant code, not in a comment. So what are actual sheet and table names? Access linked to SQLServer would be my preferred approach. Since we can't reproduce issue, not sure how we can help. Might post in a forum that allows attaching files and provide your Excel workbook. — June7
– June7, Commented Nov 25 at 19:37

Dominique · Accepted Answer · 2025-11-24 12:17:46Z

0

It looks like you are asking all the data from PowerQuery and once you have that data, you start working on it.
In case one of the things you do is filtering, then I would advise you to perform the filtering while you request the data from PowerQuery.

In other cases, try to chop the data in different chunks and retrieve them bit by bit, and glue those together for further elaboration.

answered Nov 24 at 12:17

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brian Schmitz Nov 25 at 15:32

So the data comes originally from a SQL Server database and is filtered into the first power query and then i filter into the second power query (which reads the first power query as an excel table). I've been using this process since January without issue, but I (in a silly moment on my part) decided to add a list box to the worksheet and that is when the memory issue started.

June7 Nov 25 at 19:42

Your question doesn't say issue started after adding listbox. Since issue appears to be caused by listbox and not your queries, not sure we can help since we can't replicate the issue. Go back to version of workbook without listbox and see what happens. Why listbox and not combobox?

Brian Schmitz Nov 26 at 18:19

I misspoke somewhat, I was previously using a List Box but in a User Form, when I added the List Box directly to the spreadsheet now. There was no particular reason for picking a List Box over a Combo Box. I am mainly teaching myself this stuff so I am testing out different things. I guess I also liked being able to see all the choices at once.

June7 Nov 26 at 19:37

Those details should be in your question. So client list will never outgrow listbox?

Collectives™ on Stack Overflow

Why am I getting an Out of Memory error with this specific VBA code?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related