Solved Run-Time Error 5, Joining Strings and Trimming end commas

[alikona]

Hi all,

I have a database where the user selects a number of tests and then a report is generated with a combination of verbiage for whatever tests were selected. In a field on the report, a string is generated that lists the names of the tests included, with commas as well as a final "and" between the last two values and "testing" at the end. My code works, until I only select 1 test and then I am getting the Run-Time Error 5 message on my function.

Any help would be appreciated, also apologize for the mess still not great at condensing code. The part of the function that the error highlights is the last part, FixAnd = IIf(lngX > 0, Left(strIn, lngX - 1) & " and", "") & Mid(strIn, lngX + 1)

Function:

Public Function FixAnd(strIn As String) As String
    Dim lngX As Long

lngX = InStrRev(StringCheck:=strIn, StringMatch:=",")
     FixAnd = IIf(lngX > 0, Left(strIn, lngX - 1) & " and", "") _
           & Mid(strIn, lngX + 1)
   
End Function

For the report code, I am goin to chop this up a bit since it is really long. Essentially on my form I have a check mark for each test. The report looks to see if each check mark is selected and sets values.

Here is an example of a few of them:

Private Sub Report_Load()
Dim SU_CoC As Variant, SU_MatCoC As Variant, SU_83 As Variant, SU_97 As Variant, SU_286 As Variant, SU_442 As Variant, SU_446 As Variant, SU_453 As Variant, SU_486 As Variant
Dim SU_488 As Variant, SU_489 As Variant, SU_490 As Variant, SU_520B As Variant, SU_585 As Variant, SU_636 As Variant
Dim T_CoC As Variant, T_MatCoC As Variant, T_83 As Variant, T_97 As Variant, T_286 As Variant, T_442 As Variant, T_446 As Variant, T_453 As Variant, T_486 As Variant
Dim T_488 As Variant, T_489 As Variant, T_490 As Variant, T_520B As Variant, T_585 As Variant, T_636 As Variant
Dim PSIVal1 As String, PSIVal2 As String
Dim Sub_Text As String

If [Forms]![frmCert].ck_COC = True Then
    SU_CoC = "Certificate of Conformance"
    T_CoC = (DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'CoC'")) & "<br>"
Else
    SU_CoC = Null
    T_CoC = "<div><font face=Arial size=2 color=black>This is to certify the product referenced above has been manufactured to meet specifications including the following testing:</font></div>" & "<br>"
End If

If [Forms]![frmCert].ck_97 = True Then
    SU_97 = "S-97"
    T_97 = (Replace(DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'S-97'"), "PSIVALUE1", PSIVal1)) & "<br>"
Else
    SU_97 = Null
    T_97 = Null
End If

If [Forms]![frmCert].ck_83 = True Then
    SU_83 = "S-83"
    T_83 = (DLookup("[Verbiage]", "tblCerts", "[CertNumb] = 'S-83'")) & "<br>"
Else
    SU_83 = Null
    T_83 = Null
End If

Once the SU_* values are set, the following code runs to join the values together:

Sub_Text = SU_CoC & (", " + SU_MatCoC) & (", " + SU_83) & (", " + SU_97) & (", " + SU_286) & (", " + SU_442) & (", " + SU_446) & (", " + SU_453) & (", " + SU_486) & (", " + SU_489) & (", " + SU_490) & (", " + SU_520B) & (", " + SU_585) & (", " + SU_636)

If Left(Sub_Text, 2) = ", " Then
    Sub_Text = Right(Sub_Text, Len(Sub_Text) - 2)
End If

If Right(Sub_Text, 11) = "Conformance" Then
    Me.txtSubject = Sub_Text
ElseIf Right(Sub_Text, 13) = "Certification" Then
    Me.txtSubject = FixAnd(Sub_Text)
Else
    Me.txtSubject = FixAnd(Sub_Text) & " Testing"
End If

If I only click on S-97 I will get the Run-Time Error 5 message.
If I click on S-97 and CoC, I will get the expected "Certificate of Conformance and S-97 Testing".
If I click on S-83, S-97 and CoC I get the expected "Certificate of Conformance, S-83 and S-97 Testing".

 

[theDBguy]

Hi. Did you try to step through the code? What do you get for the value of lngX if you only select one test?

 

[Josef P.]

If lngX is 0, then this statement occurs: left(strIn, -1).
Note: IIf is a function in VBA to which parameter values are passed.

   lngX = InStrRev(StringCheck:=strIn, StringMatch:=",")
   If lngX > 0 Then
      FixAnd = Left(strIn, lngX - 1) & " and"
   End If
   FixAnd = FixAnd & Mid(strIn, lngX + 1)

 

[alikona]

If lngX is 0, then this statement occurs: left(strIn, -1).
Note: IIf is a function in VBA to which parameter values are passed.

   lngX = InStrRev(StringCheck:=strIn, StringMatch:=",")
   If lngX > 0 Then
      FixAnd = Left(strIn, lngX - 1) & " and"
   End If
   FixAnd = FixAnd & Mid(strIn, lngX + 1)

That fixed it, ty.

Confusing though, I thought the syntax for IIF was (Expression, TruePart, FalsePart), which I assumed would trigger this statement as false since 0 is not > than 0, resulting in the "" value.

 

[The_Doc_Man]

You have a solution. However, if in the future you want to learn how to avoid the confusion you just expressed, you would do well to place a breakpoint in the code in question. Once you take a breakpoint, you can examine variables. Either hover the mouse over the variable OR open the immediate window and use DEBUG.PRINT variable-name to see what is in the variable. That way you would know WHY something isn't necessarily doing what you want because you could examine the inputs to your formulas. You are not limited to examining the variables in the one line that is the breakpoint.

 

[Josef P.]

Confusing though, I thought the syntax for IIF was (Expression, TruePart, FalsePart), which I assumed would trigger this statement as false since 0 is not > than 0, resulting in the "" value.

In order for the parameter values to be passed to the iif function, they must first be calculated. The values for the True and Else parts must be calculated. The calculation can trigger an error.

Simple example:

Dim X As Double
Dim Y As Double

X = 0
Y = IIf(X = 0, -1, 1 / X)

=> Division by zero

 

[The_Doc_Man]

Good point, @Josef P. - the function cannot be called until all of its parameters are evaluated, even in the case of IIF where one of the three parameters will not actually be used. This segues into a great teaching opportunity.

There are THREE parameters in the IIF: The "test" argument, the "if true" argument, and the "if false" argument. Any ONE of them could cause a run-time error. Also to be noted: If you have NESTED function calls, the inner functions must be evaluated first, working your way to the outer functions. So... (this is a contrived example):

IIF( LEFT(STRING2,1)='Z', INSTR( 1, STRING1, STRING 2 ), MID( STRING2, 5 3 ) )

evaluates the LEFT() function and then its expression; then the INSTR(); then the MID(); and then finally the IIF. An error in any one of those steps could trigger a run-time message calling out the same line.

Suppose that the test argument evaluates as TRUE. You will not use the false option - but that doesn't matter, it gets computed anyway. The ability for VBA to know that a particular option in a function does not need to be computed is sometimes called "keyhole optimization" - which is done in SOME versions of computational languages, but is NOT done in VBA. There are other examples besides functions, but for VBA it doesn't matter. It doesn't do that type of expression optimization at all.

 

[Josef P.]

see also

The IIf() Function vs. The IIf() Statement

They may look identical, but there is a very important difference in how they get evaluated.

nolongerset.com

 

[Gasman]

That fixed it, ty.

Confusing though, I thought the syntax for IIF was (Expression, TruePart, FalsePart), which I assumed would trigger this statement as false since 0 is not > than 0, resulting in the "" value.

I thought the same TBH but if you walk through the code, I could not even get the string to split on the ,

 

[alikona]

In order for the parameter values to be passed to the iif function, they must first be calculated. The values for the True and Else parts must be calculated. The calculation can trigger an error.

Simple example:

Dim X As Double
Dim Y As Double

X = 0
Y = IIf(X = 0, -1, 1 / X)

=> Division by zero

Good point, @Josef P. - the function cannot be called until all of its parameters are evaluated, even in the case of IIF where one of the three parameters will not actually be used. This segues into a great teaching opportunity.

There are THREE parameters in the IIF: The "test" argument, the "if true" argument, and the "if false" argument. Any ONE of them could cause a run-time error. Also to be noted: If you have NESTED function calls, the inner functions must be evaluated first, working your way to the outer functions. So... (this is a contrived example):

IIF( LEFT(STRING2,1)='Z', INSTR( 1, STRING1, STRING 2 ), MID( STRING2, 5 3 ) )

evaluates the LEFT() function and then its expression; then the INSTR(); then the MID(); and then finally the IIF. An error in any one of those steps could trigger a run-time message calling out the same line.

Suppose that the test argument evaluates as TRUE. You will not use the false option - but that doesn't matter, it gets computed anyway. The ability for VBA to know that a particular option in a function does not need to be computed is sometimes called "keyhole optimization" - which is done in SOME versions of computational languages, but is NOT done in VBA. There are other examples besides functions, but for VBA it doesn't matter. It doesn't do that type of expression optimization at all.

Thank you both for the detailed explanation, it is very helpful/appreciated. I tend to think in the logic of excel formulas as that is what I am more experienced in. Obviously it occasionally leads me into issues like this, hard habit to break.