如何使用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?

查看：159
本文介绍了如何使用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天全站免登陆