时间戳

首页 > 解决方案 > 将较长的 excel 公式转换为 VBA 函数（VBA 函数中没有任何单元格引用）

vba - 将较长的 excel 公式转换为 VBA 函数（VBA 函数中没有任何单元格引用）

问题描述

我有这个 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)),"

标签： vbaexcelexcel-formula

解决方案

这是第一步——我很快就要回家了，所以我没时间了——但这可能会让你开始:-)

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

推荐阅读