VBA Formula too long

Question

Hello guys,

we had an earlier template at work, where the formula had to be copied by hand to the last row to validate data. Now I have to get the Formula into vba to one click validate the data. This did work fine for all formulas except one, it seems to be too long. But when trying to append it with & _ it doesnt work.

Can anyone tell me what I am doing wrong?

=WENN(
LÄNGE('Step 2 - Add Contact Informatio'!A2)>100;""too many characters"";
WENN('Step 2 - Add Contact Informatio'!A2="""";""Email is mandatory"";
WENN(ISTZAHL(FINDEN(""!"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""*"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("":"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""="";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""`"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""\"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""]"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""["";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""}"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""{"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""´"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""?"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("")"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""("";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""/"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""&"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""%"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""$"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""§"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""~"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""“"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""^"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""°"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""<"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("" "";'Step 2 - Add Contact Informatio'!A2));""spaces are not allowed"";
WENN(ISTZAHL(FINDEN(""#"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""'"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("","";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("">"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTFEHLER(D5);""error"";WENN(D5=FALSCH;""error"";""ok"")))))))))))))))))))))))))))))))))

this is the original formula, I know it is ugly but I have to use it, as said I have tried splitting up the formula by

"=WENN(
LÄNGE('Step 2 - Add Contact Informatio'!A2)>100;""too many characters"";
WENN('Step 2 - Add Contact Informatio'!A2="""";""Email is mandatory"";
WENN(ISTZAHL(FINDEN(""!"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""*"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("":"";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""="";'Step 2 - Add Contact Informatio'!A2));""special characters are not allowed"";" & _ 

But this leads to Syntax errors. Can anybody help? Thanks a lot!

Answer 1

For what it's worth, you can cut down the formula logic for checking characters. Following formula checks the special characters in one go.

=IF(ISNA(LOOKUP(2^15,FIND(MID("!*:=`\][}{´?)(/&%$§~""^°< #',>",ROW($A$1:$A$29),1),A2,1))),"OK","space and special chars not allowed!")

You need to adjust the characters to test as per your requirements and have to adjust $A$1:$A$29 part to get to substrings of the characters (above formula is testing 29 characters). This would be easier to implement if you have plans to implement pure formula solution through VBA.

Edit: Explanation for formula.

Part 1: MID formula is written as:

MID("!*:=`][}{´?)(/&%$§~""^°< #',>",ROW($A$1:$A$29),1)

where we pass the characters in double quotes. Only notable difference is passing double quote(") itself as character to check which we pass as pair ("").

ROW function generates a numeric array like {1,2,3...29}. This has to be of the same size as the number of characters being passed to MID function to split i.e. If we pass say 35 characters then ROW($A$1:$A$29) should be ROW($A$1:$A$35).

This then splits passed string into individual items like {"!","*",":"....} which we use in second part to check for existence in the target string.

Part 2: FIND(midformula,A2,1) formula then tests each individual character in the target string and returns its position. It returns error where it doesn't find a specific character. The array returned by this portion looks like {2,"#VALUE!",1,"#VALUE!","#VALUE!",....} depending on whether it finds character or not.

Part 3:LOOKUP(2^15,findformula) then checks if there's at least one numeric entry (i.e. at least one character is found or not). Number 2^15 comes from Excel specification. Maximum number of characters allowed in a cell are 32767 (i.e. 2^15-1) so we use a number which is high enough. One can use any number but this suffices for LOOKUP formula we are using. If a number is found, then the function returns a numerical result and if not then returns #N/A. The beauty of LOOKUP is that it ignores Error Results returned by Part 2 and handles array smoothly without having to resort to Array entry (CTRL+SHIFT+ENTER).

Part 4: IF(ISNA(lookupformula),"OK","space and special chars not allowed!") then just verifies the result and displays appropriate message.

Answer 2

For checking the Special Characters, you may use Regular Expression like this...

Function IsStringValid(ByVal Str As String) As String
Dim RE
Set RE = CreateObject("VBScript.RegExp")
With RE
    .Global = True
    .Pattern = "\W"
End With
If RE.test(Str) Then
    IsStringValid = "Special characters not allowed!"
End If
End Function

OR you may return the Boolean (True/False) like this...

Function IsStringValid(ByVal Str As String) As Boolean
Dim RE
Set RE = CreateObject("VBScript.RegExp")
With RE
    .Global = True
    .Pattern = "\W"
End With
If Not RE.test(Str) Then
    IsStringValid = True
End If
End Function

So IsStringValid("Hello World") will return True whereas IsStringValid("Hello World!") will return False.

You can use the UDF either in another sub routine or on the sheet itself. e.g. if you want to validate the string in A1, you may try...

=IsStringValid(A1) 

Answer 3

This is a good example of how to translate this to a VBA user-defined formula :

Function detectSpecial(sInput As String) As String

    Dim sSpecialChars   As String
    Dim i               As Long
    Dim i2              As Long
    Dim isFound         As Boolean

    sSpecialChars = "\/:*?""<>|"

    For i = 1 To Len(sInput)
        For i2 = 1 To Len(sSpecialChars)
            If Mid(sInput, i, 1) = Mid(sSpecialChars, i2, 1) Then
                detectSpecial = "No special characters allowed!"
                Exit Function
            End If
        Next
    Next i

    detectSpecial = sInput

End Function

If you want to increase the number of special characters, then simply do it by changing the sSpecialChars.