Excel VBA 更改表格中的公式?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/17102839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 21:38:39  来源:igfitidea点击:

Excel VBA changing my formulas in a table?

excelvbaexcel-2010listobject

提问by DPD

Has anyone come across a situation where Excel seems to manipulate your formulas. I have a sheet where I have an Index value in Column A. The First row starts with any non zero Value. Subsequent rows in the column increment the value. Eg

有没有人遇到过 Excel 似乎操纵您的公式的情况。我有一张工作表,其中 A 列中有一个索引值。第一行以任何非零值开头。列中的后续行增加该值。例如

A1 = 1000
A2= A1+ 1
A3= A2 + 1 

and so on/ I have another column B whose values will either be blank or a formula pointing to column A(usually the subsequent rows) Eg:

依此类推/我有另一列 B 其值将是空白或指向 A 列的公式(通常是后续行)例如:

B1.Formula = "=A2"
B2.Formula = "=A3"
B3.Value = ""
B4.value = "=A6"

Now I have a backup-restore functionality that lets me write out the data/formulas to a text file and then read it back in another workbook. In the case of columns A and B, I am checking if the text value starts with "=" and then set either the value or formula of that cell depending on whether there is a formula or not.

现在我有一个备份-恢复功能,可以让我将数据/公式写出到一个文本文件,然后在另一个工作簿中读回。在 A 列和 B 列的情况下,我正在检查文本值是否以“=”开头,然后根据是否有公式设置该单元格的值或公式。

So far the functionality has worked fine. It lets me restore accurately.

到目前为止,该功能运行良好。它让我准确地恢复。

Now, if I convert this data range to a table and modify the code accordingly the behaviour is strange. I am using the ListObject structure to refer to the table. So for Column B my restore code is:

现在,如果我将此数据范围转换为表格并相应地修改代码,则行为很奇怪。我正在使用 ListObject 结构来引用该表。所以对于 B 列,我的恢复代码是:

 If Left(soureString) = "=" Then
               'This is a formula
               Sheets("MySheet").ListObjects(1).ListColumns("Next").DataBodyRange(row).Formula = sourcestring
Else
               'This is a value
                Sheets("MySheet").ListObjects(1).ListColumns("Next").DataBodyRange(row).Value = soureString
End If

once I am done writing a row, I loop to the start and

一旦我写完一行,我就会循环到开始并

Dim newRow AS listrow
Set newRow = Sheets("MySheet").Listrows.Add(AlwaysInsert:=False)
row = newRow.Index

But this time when I run the process. this is what I get:

但是这次当我运行这个过程时。这就是我得到的:

B1.Formula = "=A5"
B2.Formula = "=A5"
B3.Value = ""
B4.value = "=A5"

Why are my formula values all changing to the same value when I use a table instead of a range?

当我使用表格而不是范围时,为什么我的公式值都更改为相同的值?

回答by Andy Sinclair

I had the same issue when populating a ListObject (Table) from an Excel Add-in, setting AutoFillFormulasInListswas the solution.

从 Excel 加载项填充 ListObject (Table) 时,我遇到了同样的问题,设置AutoFillFormulasInLists是解决方案。

My workaround is to save the current setting, set AutoFillFormulasInListsto false, populate the table with data, formulas etc, then set AutoFillFormulasInListsback to the original setting.

我的解决方法是保存当前设置,设置AutoFillFormulasInLists为 false,用数据、公式等填充表格,然后设置AutoFillFormulasInLists回原始设置。

bool OriginalAutoFillFormulaInListsFlag = app.AutoCorrect.AutoFillFormulasInLists;
app.AutoCorrect.AutoFillFormulasInLists = false;

//[ListObject population code....]

if (OriginalAutoFillFormulaInListsFlag == true)
{
    app.AutoCorrect.AutoFillFormulasInLists = true;
}

Hope this helps someone.

希望这可以帮助某人。

回答by DickM

I have found that the only way to solve the problem of formulas changing in Excel Tables when you insert in VBA is to insert at the first row of the table, NOT the bottom or the middle. You can sort after.

我发现在 VBA 中插入时解决 Excel 表格中公式更改问题的唯一方法是插入表格第一行,而不是底部或中间。你可以排序。

And I always select or reference the EntireRowto do my insert in the Worksheet object not in the table itself. I always put a table in its own Worksheet anyway using xx.Entirerow.Insert.

而且我总是选择或引用EntireRow在 Worksheet 对象中而不是在表本身中进行插入。无论如何,我总是使用xx.Entirerow.Insert.

回答by User

I faced a similar issue. Ideally you could tell excel to stop doing this but I haven't been able to figure out how. Supposedly doing the following is supposed to keep excel from copying the formulas:

我遇到了类似的问题。理想情况下,您可以告诉 excel 停止这样做,但我一直无法弄清楚如何。据说执行以下操作应该可以防止 excel 复制公式:

xlApp.AutoCorrect.AutoFillFormulasInLists = false

xlApp.AutoCorrect.AutoFillFormulasInLists = false

but it didn't work for me.

但它对我不起作用。

Using the answer from this question How to create running total using Excel table structured references?helped me. It doesn't feel like the ideal solution but it does do the job.

使用此问题的答案如何使用 Excel 表结构化引用创建运行总计?帮助过我。感觉这不是理想的解决方案,但确实可以胜任。

I used this formula where Weightis a column name from my table. #This Rowis a "Special item specifier" and has a special meaning. The syntax looks a little funky because it's what's called a Structured Reference:

我使用了这个公式,其中Weight是我表中的列名。 #This Row是“特殊项目说明符”,具有特殊含义。语法看起来有点时髦,因为它被称为结构化引用

=AVERAGE(INDEX([Weight],1):[[#This Row],[Weight]])

=AVERAGE(INDEX([Weight],1):[[#This Row],[Weight]])

The INDEX([Weight],1)part gives the reference for the 1st row in the Weight column

INDEX([Weight],1)部件给出了重量列中第一行的参考

While the [[#This Row],[Weight]]part gives the reference for the current row in the Weight column.

[[#This Row],[Weight]]零件在权重列中提供当前行的参考。

So for example, if Weight is column J, and the current row is, say, 7 then this is equivalent to

例如,如果权重是 J 列,而当前行是 7,那么这相当于

=AVERAGE(J1:J7)

=AVERAGE(J1:J7)

and on the 8th row it will be equivalent to

在第 8 行,它将相当于

=AVERAGE(J1:J8)and so on

=AVERAGE(J1:J8)等等