VB/VBA If..ElseIf..Then, where each ElseIf..Then is on one line

Question

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?

Answer 1

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