如何通过VBA代码将公式的结果直接写入excel范围 [英] how to write results of a formula directly into an excel range by VBA code

问题描述

对于每个listobject列，我都有一系列功能.该文件很重且崩溃，因此我只想将每个公式的结果保留为静态值.我可以为范围分配公式，并要求excel将范围转换为值.但是我想知道是否有一种方法可以要求VBA只写范围内的静态值而不是公式本身. 这是我到目前为止的内容:

I have a series of functions for each of listobject columns. The file is heavy and crashing, so I want just to keep the results of each formula as static values. I can allocate formula to the range and ask excel to convert the range to value. But I am wondering if there is a way to ask VBA to write only the static values in the range instead of the formula itself. Here is what I have so far:

Sub calculate2()
Dim i As Long, t As Long
t = Timer
    With Sheet3.ListObjects(1)
      For i = 3 To 9
         .ListColumns(i).DataBodyRange.ClearContents
         .Range.Cells(2, i).Formula = sheets3.range("formula").cells(i,1).formula
         .ListColumns(i).DataBodyRange = .ListColumns(i).DataBodyRange.Value
      Next i
    End With
Debug.Print Timer - t
End Sub

推荐答案

作为对我自己问题的回答， 我一直在寻找:"application.Evaluate"我张贴的内容是，如果有人通过搜索来到这里可以找到我最终找到的想法和解决方案.这是一个示例:

As an answer to my own question, What I was looking for is: " application.Evaluate " I am posting that so if anyone came here by search can find the idea and the sollution I eventually found. Here is an example:

Sheet3.ListObjects(1).ListColumns(3).DataBodyRange = [IFERROR(IF(COUNTIFS(ZZ84!$B:$B,[WO],ZZ84!$E:$E,"=*V99"‌​,ZZ84!$L:$L,"<>")=1,1,0),"")]

在这种情况下，

无需循环，并且每个范围都必须编写必要的代码行(将其嵌入VBA中的功能，这正是我所寻找的).上面将函数直接放在excel单元中的唯一不同之处是使用[WO]而不是[@WO].因此，求值将计算一个数据数组，并将其直接写入指定范围内. (此处为列表列3的主体范围).

in this case there is no need to loop and for each range has to write needful line of code (Embed the function in VBA, what I excatly was looking for). The only different in above function with directly putting that in a excel cell is using [WO] instead of [@WO]. So evaluat caculate an array of data and directly write that in specified range. (here body range of list columns 3 ).

对我来说，它有助于避免崩溃，因为我的函数计算出油膜.

For me it helped to avoid crashes beacause of voilate calculation by my functions.

另一个简单的示例是:

range("b1:b10")=[if(row(1:10),if(a1:a10>3,"big","Small"))] 

或

range("b1:b10") = evaluate("if(row(1:10),if(" & range("a1:a10").address&">3,""big"",""small""))")

亲切的问候， M

这篇关于如何通过VBA代码将公式的结果直接写入excel范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！