英文:
VB/VBA If..ElseIf..Then, where each ElseIf..Then is on one line
问题
我想使用Excel和字符串连接来生成一些VB代码，用于映射（值A和值B => 值C）。我实际上将把生成的VB代码插入到一个使用VB语法的Crystal Reports自定义函数中。
在Excel中，我有三列包含映射值的内容：前两列包含映射/函数的输入值，而第三列包含输出值。我在这些列中填充了一堆值（想象一下所有的xxx、yyy和zzz都是唯一的值）。
第四列将是一个字符串连接公式，用于在一个巨大的VB If..ElseIf..ElseIf..ElseIf..EndIf语句中实现映射。每一行必须生成一个ElseIf..Then VB语句。我需要生成的代码如下（我将手动输入If行和EndIf行。）
' This first line just lets all other lines be an "ElseIf" statement, making the Excel formula that renders this code the same for every entry.
If False Then foo = ""
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
End If
生成代码的公式示例可能如下：
="ElseIf strProductCode = """ & A1 & """ And strPackageLength = """ & B1 & """ then foo = """ & C1 & """"
这个公式忠实地生成了VB代码。然而，上述VB代码生成了语法错误：
"编译错误。Else without If"
是的，我知道"Then"部分通常在新行上，但是否有一些语法可以使代码格式化如上所示，以便我的Excel代码生成公式不必在公式中使用换行符？
英文:
I want to use Excel and string concatenation to do a little VB code generation of a mapping (value A and value B => value C). I'm actually going to insert the resulting VB code into a function in a Crystal Reports custom function that uses VB syntax.
In Excel, I have three columns containing the mapping values: The first two have the map's/function's input values, while the third has the output value. I filled these columns with a bunch of values (imagine that all the xxx, yyy, and zzz's are unique values).
A fourth column will be a string concatenation formula that implements the mapping in a giant VB If..ElseIf..ElseIf..ElseIf..EndIf statuement. Each row must generate an ElseIf..Then VB statement. I need to resulting code to look like below (I will manually type out the If line and the EndIf line.)
' This first line just lets all other lines be an "ElseIf" statement, making the Excel formula that renders this code the same for every entry.
If False Then foo = ""
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
ElseIf strProductCode = "xxx" And strPackageLength = "yyy" Then foo = "zzz"
EndIf
An example of the formula to generate the code would be something like:
="ElseIf strProductCode = """ & A1 & """ And strPackageLength = """ & B1 & """ then foo = """ & C1 & """"
The formula faithfully generates the VB. However, the above VB generates a syntax error:
"Compile error. Else without If"
Yes, I know that the Then portion is usually on a new line but is there some syntax that allows the code to be formatted as above, so that my Excel code generation formula does not have to resort to linefeeds in the formula?
答案1
得分: 3
Select Case 而不是 If...ElseIf...End If
Excel 公式：
=REPT(" ", 8) & "Case strProductCode = """ & A1 & """ And strPackageLength = """ & B1 & """: foo = """ & C1 & """"
VBA 代码：
Sub Test()
Dim strProductCode As String: strProductCode = "A2"
Dim strPackageLength As String: strPackageLength = "20"
Dim foo As String
Select Case True
Case strProductCode = "A1" And strPackageLength = "10": foo = "C1"
Case strProductCode = "A2" And strPackageLength = "20": foo = "C2"
Case strProductCode = "A3" And strPackageLength = "30": foo = "C3"
Case strProductCode = "A4" And strPackageLength = "40": foo = "C4"
Case Else: foo = ""
End Select
MsgBox """foo"" 等于 " & foo & ".", vbInformation
End Sub
英文:
Select Case Instead of If...ElseIf...End If
Excel Formula
=REPT(" ",8)&"Case strProductCode = """&A1&""" And strPackageLength = """&B1&""": foo = """&C1&""""
VBA Code
Sub Test()
Dim strProductCode As String: strProductCode = "A2"
Dim strPackageLength As String: strPackageLength = "20"
Dim foo As String
Select Case True
Case strProductCode = "A1" And strPackageLength = "10": foo = "C1"
Case strProductCode = "A2" And strPackageLength = "20": foo = "C2"
Case strProductCode = "A3" And strPackageLength = "30": foo = "C3"
Case strProductCode = "A4" And strPackageLength = "40": foo = "C4"
Case Else: foo = ""
End Select
MsgBox """foo"" is equal to """ & foo & """.", vbInformation
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论