我有这个MS Excel公式，我想将其添加为VBA函数，因为它与普通Excel公式太长。
它基本上应该从部分"动态"的不同文本块创建一个文本(这意味着它们是某些范围内的部分依赖值和部分固定( - 如果满足某些条件，则基于。
End 公式应如下所示："Desc((" - 无需指定任何单元格或范围，因为所有内容都已在公式中定义(基于它引用的范围(。
IF(
AND(
IF(COUNTIF(Attributes,bbinput!$C$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$C$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$D$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$D$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$E$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$E$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$F$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$F$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$G$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$G$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$H$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$H$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$I$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$I$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$J$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$J$2))),TRUE),
IF(COUNTIF(Attributes,bbinput!$K$2)>0,NOT(ISBLANK(INDIRECT(bbinput!$K$2))),TRUE),
LEN(
TRIM(
IF(COUNTIF(Attributes,bbinput!$C$2)>0,INDIRECT(bbinput!$C$2),bbinput!$C$2)&" "&
IF(COUNTIF(Attributes,bbinput!$D$2)>0,INDIRECT(bbinput!$D$2),bbinput!$D$2)&" "&
IF(COUNTIF(Attributes,bbinput!$E$2)>0,INDIRECT(bbinput!$E$2),bbinput!$E$2)&" "&
IF(COUNTIF(Attributes,bbinput!$F$2)>0,INDIRECT(bbinput!$F$2),bbinput!$F$2)&" "&
IF(COUNTIF(Attributes,bbinput!$G$2)>0,INDIRECT(bbinput!$G$2),bbinput!$G$2)&" "&
IF(COUNTIF(Attributes,bbinput!$H$2)>0,INDIRECT(bbinput!$H$2),bbinput!$H$2)&" "&
IF(COUNTIF(Attributes,bbinput!$I$2)>0,INDIRECT(bbinput!$I$2),bbinput!$I$2)&" "&
IF(COUNTIF(Attributes,bbinput!$J$2)>0,INDIRECT(bbinput!$J$2),bbinput!$J$2)&" "&
IF(COUNTIF(Attributes,bbinput!$K$2)>0,INDIRECT(bbinput!$K$2),bbinput!$K$2)&" "&
IF(COUNTIF(Attributes,bbinput!$L$2)>0,INDIRECT(bbinput!$L$2),bbinput!$L$2)))
<80
),
TRIM(
IF(COUNTIF(Attributes,bbinput!$C$2)>0,INDIRECT(bbinput!$C$2),bbinput!$C$2)&" "&
IF(COUNTIF(Attributes,bbinput!$D$2)>0,INDIRECT(bbinput!$D$2),bbinput!$D$2)&" "&
IF(COUNTIF(Attributes,bbinput!$E$2)>0,INDIRECT(bbinput!$E$2),bbinput!$E$2)&" "&
IF(COUNTIF(Attributes,bbinput!$F$2)>0,INDIRECT(bbinput!$F$2),bbinput!$F$2)&" "&
IF(COUNTIF(Attributes,bbinput!$G$2)>0,INDIRECT(bbinput!$G$2),bbinput!$G$2)&" "&
IF(COUNTIF(Attributes,bbinput!$H$2)>0,INDIRECT(bbinput!$H$2),bbinput!$H$2)&" "&
IF(COUNTIF(Attributes,bbinput!$I$2)>0,INDIRECT(bbinput!$I$2),bbinput!$I$2)&" "&
IF(COUNTIF(Attributes,bbinput!$J$2)>0,INDIRECT(bbinput!$J$2),bbinput!$J$2)&" "&
IF(COUNTIF(Attributes,bbinput!$K$2)>0,INDIRECT(bbinput!$K$2),bbinput!$K$2)&" "&
IF(COUNTIF(Attributes,bbinput!$L$2)>0,INDIRECT(bbinput!$L$2),bbinput!$L$2)),
IF(
AND(
IF(COUNTIF(Attributes,bbinput!$C$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$C$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$D$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$D$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$E$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$E$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$F$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$F$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$G$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$G$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$H$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$H$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$I$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$I$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$J$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$J$3))),TRUE),
IF(COUNTIF(Attributes,bbinput!$K$3)>0,NOT(ISBLANK(INDIRECT(bbinput!$K$3))),TRUE),
LEN(
TRIM(
IF(COUNTIF(Attributes,bbinput!$C$3)>0,INDIRECT(bbinput!$C$3),bbinput!$C$3)&" "&
IF(COUNTIF(Attributes,bbinput!$D$3)>0,INDIRECT(bbinput!$D$3),bbinput!$D$3)&" "&
IF(COUNTIF(Attributes,bbinput!$E$3)>0,INDIRECT(bbinput!$E$3),bbinput!$E$3)&" "&
IF(COUNTIF(Attributes,bbinput!$F$3)>0,INDIRECT(bbinput!$F$3),bbinput!$F$3)&" "&
IF(COUNTIF(Attributes,bbinput!$G$3)>0,INDIRECT(bbinput!$G$3),bbinput!$G$3)&" "&
IF(COUNTIF(Attributes,bbinput!$H$3)>0,INDIRECT(bbinput!$H$3),bbinput!$H$3)&" "&
IF(COUNTIF(Attributes,bbinput!$I$3)>0,INDIRECT(bbinput!$I$3),bbinput!$I$3)&" "&
IF(COUNTIF(Attributes,bbinput!$J$3)>0,INDIRECT(bbinput!$J$3),bbinput!$J$3)&" "&
IF(COUNTIF(Attributes,bbinput!$K$3)>0,INDIRECT(bbinput!$K$3),bbinput!$K$3)&" "&
IF(COUNTIF(Attributes,bbinput!$L$3)>0,INDIRECT(bbinput!$L$3),bbinput!$L$3)))
<80
),
TRIM(
IF(COUNTIF(Attributes,bbinput!$C$3)>0,INDIRECT(bbinput!$C$3),bbinput!$C$3)&" "&
IF(COUNTIF(Attributes,bbinput!$D$3)>0,INDIRECT(bbinput!$D$3),bbinput!$D$3)&" "&
IF(COUNTIF(Attributes,bbinput!$E$3)>0,INDIRECT(bbinput!$E$3),bbinput!$E$3)&" "&
IF(COUNTIF(Attributes,bbinput!$F$3)>0,INDIRECT(bbinput!$F$3),bbinput!$F$3)&" "&
IF(COUNTIF(Attributes,bbinput!$G$3)>0,INDIRECT(bbinput!$G$3),bbinput!$G$3)&" "&
IF(COUNTIF(Attributes,bbinput!$H$3)>0,INDIRECT(bbinput!$H$3),bbinput!$H$3)&" "&
IF(COUNTIF(Attributes,bbinput!$I$3)>0,INDIRECT(bbinput!$I$3),bbinput!$I$3)&" "&
IF(COUNTIF(Attributes,bbinput!$J$3)>0,INDIRECT(bbinput!$J$3),bbinput!$J$3)&" "&
IF(COUNTIF(Attributes,bbinput!$K$3)>0,INDIRECT(bbinput!$K$3),bbinput!$K$3)&" "&
IF(COUNTIF(Attributes,bbinput!$L$3)>0,INDIRECT(bbinput!$L$3),bbinput!$L$3)),
IF(
AND(
IF(COUNTIF(Attributes,bbinput!$C$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$C$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$D$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$D$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$E$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$E$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$F$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$F$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$G$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$G$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$H$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$H$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$I$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$I$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$J$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$J$4))),TRUE),
IF(COUNTIF(Attributes,bbinput!$K$4)>0,NOT(ISBLANK(INDIRECT(bbinput!$K$4))),TRUE),
LEN(
TRIM(
IF(COUNTIF(Attributes,bbinput!$C$4)>0,INDIRECT(bbinput!$C$4),bbinput!$C$4)&" "&
IF(COUNTIF(Attributes,bbinput!$D$4)>0,INDIRECT(bbinput!$D$4),bbinput!$D$4)&" "&
IF(COUNTIF(Attributes,bbinput!$E$4)>0,INDIRECT(bbinput!$E$4),bbinput!$E$4)&" "&
IF(COUNTIF(Attributes,bbinput!$F$4)>0,INDIRECT(bbinput!$F$4),bbinput!$F$4)&" "&
IF(COUNTIF(Attributes,bbinput!$G$4)>0,INDIRECT(bbinput!$G$4),bbinput!$G$4)&" "&
IF(COUNTIF(Attributes,bbinput!$H$4)>0,INDIRECT(bbinput!$H$4),bbinput!$H$4)&" "&
IF(COUNTIF(Attributes,bbinput!$I$4)>0,INDIRECT(bbinput!$I$4),bbinput!$I$4)&" "&
IF(COUNTIF(Attributes,bbinput!$J$4)>0,INDIRECT(bbinput!$J$4),bbinput!$J$4)&" "&
IF(COUNTIF(Attributes,bbinput!$K$4)>0,INDIRECT(bbinput!$K$4),bbinput!$K$4)&" "&
IF(COUNTIF(Attributes,bbinput!$L$4)>0,INDIRECT(bbinput!$L$4),bbinput!$L$4)))
<80
),
TRIM(
IF(COUNTIF(Attributes,bbinput!$C$4)>0,INDIRECT(bbinput!$C$4),bbinput!$C$4)&" "&
IF(COUNTIF(Attributes,bbinput!$D$4)>0,INDIRECT(bbinput!$D$4),bbinput!$D$4)&" "&
IF(COUNTIF(Attributes,bbinput!$E$4)>0,INDIRECT(bbinput!$E$4),bbinput!$E$4)&" "&
IF(COUNTIF(Attributes,bbinput!$F$4)>0,INDIRECT(bbinput!$F$4),bbinput!$F$4)&" "&
IF(COUNTIF(Attributes,bbinput!$G$4)>0,INDIRECT(bbinput!$G$4),bbinput!$G$4)&" "&
IF(COUNTIF(Attributes,bbinput!$H$4)>0,INDIRECT(bbinput!$H$4),bbinput!$H$4)&" "&
IF(COUNTIF(Attributes,bbinput!$I$4)>0,INDIRECT(bbinput!$I$4),bbinput!$I$4)&" "&
IF(COUNTIF(Attributes,bbinput!$J$4)>0,INDIRECT(bbinput!$J$4),bbinput!$J$4)&" "&
IF(COUNTIF(Attributes,bbinput!$K$4)>0,INDIRECT(bbinput!$K$4),bbinput!$K$4)&" "&
IF(COUNTIF(Attributes,bbinput!$L$4)>0,INDIRECT(bbinput!$L$4),bbinput!$L$4)),
)))
我已经在 VBA 中有了以下代码：
"=IF(" & Chr(10) & "AND(" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C3)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C3))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C4)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C4))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C5)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C5))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C6)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C6))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!" & _
"R2C7)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C7))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C8)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C8))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C9)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C9))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C10)>0,NOT(ISBLANK(INDIRECT(bbinput!R2C10))),TRUE)," & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C11)>0,NOT(ISBLANK(INDIRECT(bbinput!" & _
"R2C11))),TRUE)," & Chr(10) & "" & Chr(10) & "LEN(" & Chr(10) & "TRIM(" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C3)>0,INDIRECT(bbinput!R2C3),bbinput!R2C3)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C4)>0,INDIRECT(bbinput!R2C4),bbinput!R2C4)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C5)>0,INDIRECT(bbinput!R2C5),bbinput!R2C5)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C6)>0,INDIRECT(bbinput!R2C6),bbinput!R2C6)&"" ""&" & Chr(10) &
"IF(COUNTIF(A" & _
"ttributes,bbinput!R2C7)>0,INDIRECT(bbinput!R2C7),bbinput!R2C7)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C8)>0,INDIRECT(bbinput!R2C8),bbinput!R2C8)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C9)>0,INDIRECT(bbinput!R2C9),bbinput!R2C9)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C10)>0,INDIRECT(bbinput!R2C10),bbinput!R2C10)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C11)>0,INDIRECT(b" & _
"binput!R2C11),bbinput!R2C11)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C12)>0,INDIRECT(bbinput!R2C12),bbinput!R2C12)))" & Chr(10) & "<80" & Chr(10) & "),"
& Chr(10) & "" & Chr(10) & "TRIM(" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C3)>0,INDIRECT(bbinput!R2C3),bbinput!R2C3)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C4)>0,INDIRECT(bbinput!R2C4),bbinput!R2C4)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C5)>0,INDIRECT(bbinput!R2C5),bbinput!R2C5)" & _
"&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C6)>0,INDIRECT(bbinput!R2C6),bbinput!R2C6)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C7)>0,INDIRECT(bbinput!R2C7),bbinput!R2C7)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C8)>0,INDIRECT(bbinput!R2C8),bbinput!R2C8)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C9)>0,INDIRECT(bbinput!R2C9),bbinput!R2C9)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2" & _
"C10)>0,INDIRECT(bbinput!R2C10),bbinput!R2C10)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C11)>0,INDIRECT(bbinput!R2C11),bbinput!R2C11)&"" ""&" & Chr(10) &
"IF(COUNTIF(Attributes,bbinput!R2C12)>0,INDIRECT(bbinput!R2C12),bbinput!R2C12)),"
这是第一点 - 我很快就要回家了，所以我没有时间了 - 但这可能会让你开始：-(
public function Desc() as string
dim s as string
dim r as range
dim att as range
set att = range("attributes")
dim ok as boolean
for each r in worksheets("bbinput").range("c2:k2")
if application.worksheetfunction.counta(att,r)>0 then
s = s & range(r.text) & " "
end if
next r
desc=s
end function