为什么这多行代码中存在VBA语法错误？

Question

我有一行这么长的代码。VBE说它有语法错误。我检查了密码，我很确定应该没问题。我超过极限了吗？

Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 26) = "=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"",0,IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,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)),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))+(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)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ") _
            +2*(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.3*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*2*1.3*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*2*1.3*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0 _
            ,0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & 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)*1.5+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*1.5*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*1.5*0.1),0)),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0 _
            ,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)),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))+(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)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*2*1.3 _
            +(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*2*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & 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)*1.3+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*1.3*1.3 _
            +(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*1.3*0.1),0))))"

Answer 1

尝试以下语句，以确保字符串文本中不会出现换行符：

Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 26) = "=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"",0,IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,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)),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))+(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)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")" & _
            "+2*(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.3*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*2*1.3*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*2*1.3*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0" & _
            ",0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & 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)*1.5+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*1.5*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*1.5*0.1),0)),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0" & _
            ",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)),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))+(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)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*2*1.3" & _
            "+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*2*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & 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)*1.3+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!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)*1.3*1.3" & _
            "+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!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)*1.3*0.1),0))))"

我不知道代码是否会实际运行，但至少可以避免语法错误。