如何使用 VBA 将数组公式作为长度超过 255 个字符的字符串自动输入到 Excel 单元格中? [英] How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?

查看：45
本文介绍了如何使用 VBA 将数组公式作为长度超过 255 个字符的字符串自动输入到 Excel 单元格中?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的代码适用于非数组公式，但是当我将它作为数组公式尝试时，它最终粘贴了整行代码，而不是它应该给出的结果.这是它的样子:

My codes work for non-array formulas, but when I try it as an array formula, it ends up pasting the whole line of code instead of the result it should give out. This is how it looks like:

我的VBA代码如下图(无需阅读完整代码):

My VBA code is as shown below (no need to read whole code):

Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20) = "{=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P" & Total_Rows_Formulas + 1 & "*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8,IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))>0,IF(AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,IF(OR(AF2>=24,AF2<=8)=TRUE," & _
        "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/2)/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))" & _
        "-IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=0,(8-SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & "))*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8),0),0))," & _
        "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/2)/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))" & _
        "-IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=0,(8-SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & "))*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8),0),0)))),IF(Z" & Total_Rows_Formulas + 1 & ">0,0,IF(AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,P" & Total_Rows_Formulas + 1 & "*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8))))}"

尝试Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20).FormulaArray = ...:

最新尝试:

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
        .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/8,2424)"
        .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))>0, IF(AI2=""Sunday"",0, IF(OR(AF2>=24,AF2<=8)=TRUE,1111+2222+3333,b.)),4444+5555+6666)"
        .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
        .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
        .Replace "+3333", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
        .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
        .Replace "+6666", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
End With

仍然无法使用与上面显示的相同的运行时错误

Still does not work with the same run-time error as shown above

工作代码(感谢 Chris Mack):

Working Code (thanks to Chris Mack):

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
        .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*7777/8,2424)"
        .Replace "7777", "INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))"
        .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))>0, IF(AI2=""Sunday"",0, IF(OR(AF2>=24,AF2<=8)=TRUE,1111+2222+3333,4444+5555+6666)),3434)"
        .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
        .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
        .Replace "+3333", "-IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
        .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
        .Replace "+6666", "-IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "3434", "IF(Z2>0,0,IF(AI2=""Sunday"",0,P2*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/8))"
End With

推荐答案

尝试:

Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20).FormulaArray = ...

更新:

关于在设置 FormulaArray 属性时克服限制，解决此问题的一种方法是将您的公式拆分为多个组件，然后使用别名来表示它们.然后，您可以用实际的公式字符串替换别名.

With regards to overcoming the limit when setting the FormulaArray property, one way to get around this is to split your formula into components and then use an alias to represent them. You can then replace the alias with the actual formula string.

两条规则:

  1. 在每个阶段，您的公式都必须遵守 Excel 公式的语法规则.
  2. 每个替换字符串的长度必须小于或等于 255 个字符.

(更新:根据 https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/:如果您使用 A1 表示法，则 R1C1 等效项必须少于 255 个字符.")

(Update: Per https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/: "If you are using A1 notation then the R1C1 equivalent must be less than 255 characters.")

单个组件的良好候选者是公式的任何部分，这些部分通过运算符(例如，加法、乘法、大于)产生一个值以用于另一个值.因此，如果您可以得到您输入的初始公式，如下所示，您将走在正确的轨道上(您甚至可以使用评估公式"来获得一些想法，因为它会评估您公式中的组件并向您展示每一步).

Good candidates for individual components are any sections of the formula that produce a value to be used against another value via an operator (e.g., addition, multiplication, greater than). So if you can get the initial formula you enter looking something like the following you'll be on the right track (you could even use 'Evaluate Formula' to get some ideas for this, as it will evaluate components within your formula and show you each step).

{=IF(FirstPart=SecondPart,ThirdPart*FourthPath,FifthPart)}

再次提醒，每个部分的长度必须小于或等于 255 个字符.如果不是，通常情况下可以将它们分解为更多组件.

Again, remember that each of these parts must be less than or equal to 255 characters in length. If they aren't, it's usually the case that they can be broken down into further components.

示例:

Sub OvercomeFormulaArrayLimit()

    Selection.FormulaArray = "=SUM(IF(A2:A9=12,IF(B2:B9=23,C2:C9)))+XYZ"

    For r = 1 To 10

        Selection.Replace "XYZ", "SUM(IF(Sheet1!A2:A9=12,IF(Sheet1!B2:B9=23,Sheet1!C2:C9)))+XYZ"

    Next

    Selection.Replace "XYZ", "SUM(IF(Sheet1!A2:A9=12,IF(Sheet1!B2:B9=23,Sheet1!C2:C9)))"

End Sub

所以在你的代码中，一个例子是:

So in your code, an example would be:

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)

    .FormulaArray = "=IF(replExtra=""Extra"" ..."
    .Replace "replExtra", "INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))"
    'etc.

End With

一个可行的解决方案，基于您上面的代码:

A working solution, based on your code above:

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
        .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*7777/8,2424)"
        .Replace "7777", "INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))"
        .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))>0, IF(AI2=""Sunday"",0, IF(OR(AF2>=24,AF2<=8)=TRUE,1111+2222+3333,b.)),4444+5555+6666)"
        .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
        .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
        .Replace "+3333", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
        .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
        .Replace "+6666", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
End With

这篇关于如何使用 VBA 将数组公式作为长度超过 255 个字符的字符串自动输入到 Excel 单元格中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆