Excel, VBA, disable "Invalid formula" error dialog - Stack Overflow

admin管理员组

文章数量:1130349

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 :(

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 :(

• excel
• vba

Share Improve this question Follow asked Dec 15, 2024 at 21:23 b0bikb0bik 3377 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You could disable events with Application.EnableEvents = False, but instead of fudging and breaking things only to fix them later, why not do it right from the start? You seem to be complicating things unnecessarily. If you know you will be using a table later on, create it first without any data so you can reference it in your formulas properly.

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 :(

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 :(

• excel
• vba

Share Improve this question Follow asked Dec 15, 2024 at 21:23 b0bikb0bik 3377 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You could disable events with Application.EnableEvents = False, but instead of fudging and breaking things only to fix them later, why not do it right from the start? You seem to be complicating things unnecessarily. If you know you will be using a table later on, create it first without any data so you can reference it in your formulas properly.

本文标签： EXCELVBAdisable quotInvalid formulaquot error dialogStack Overflow