I’ve got a simple class thats executes SQL query, then fill array with query results, next convert it into Range, a finally there is a list object created:

Dim data() As Variant
...
ReDim data(0 To rowCount, 1 To colCount)
... populating data
Set rng = Range("A1:" & ColumnLetter(colCount) & CStr(rowCount + 1))
rng = data << Exception is raised here
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)

What I want to achieve is to enable, that query returns excel formula, for example, i can add to my select query:

SELECT
...
'=2*A1' AS COL1

And it works (=2*A1 is stupid/useless example, but i want to explain what i mean).

Now, i want to use structured references formulas, for example:

SELECT
...
T.SALE AS SALE,
'=[@Sale]/AGGREGATE(9;5;[Sale])' AS SALE_PART,"
...

but the problem is that [Sale] i a reference to table column – which doesn’t exist yet, at a data population moment – so VBA throws exceptions – “Out of memory”. I dont know why is about out of memory, but im sure its about cell with that formula (other cells before that one, are populated correctly).

So – the question is – can I temporary disable somehow formula validation (prevent error dialog) ? I could then reenable it, after creating a listobject, and then those problematic formulas will gone ([Sale] become a valid reference)

I think, the problem is connected with structured references. When I enter invalid formula like:

=xyz

there is no error dialog, but this formula is incorrect (#NAME?), but when i enter:

=[xyz]

there is a dialog box 🙁