Vectorial formula for cell validation in Excel using VBA

Question

I am writing a VBA formula to check that all characters in a cell "TestChars" are allowed, where allowed means that each character appears in a list defined by another cell "AllowedChars". To make things even harder, I would like this formula to work on ranges of cells rather than on a single cell.

The current code seems to work:

Option Explicit


Public Function AllCharsValid(InputCells As Range, AllowedChars As String) As Boolean
' Check that all characters in InputCells are among
' the characters in AllowedChars

    Dim Char As String
    Dim Index As Integer
    Dim RangeTestChars As Range
    Dim TestChars As String
    For Each RangeTestChars In InputCells
        TestChars = RangeTestChars.Value
        For Index = 1 To Len(TestChars)
            Char = Mid(TestChars, Index, 1)
            If InStr(AllowedChars, Char) = 0 Then
                AllCharsValid = False
                Exit Function
            End If
        Next Index
    Next RangeTestChars
    AllCharsValid = True
End Function

I have the following questions:

1. The formula takes a range and returns a single boolean. I would prefer a vectorized function, where, given an input range, you get a corresponding range of booleans. It seems like built-in formulas like 'EXACT' can do this (those formulas where you have to press ctrl-shift-enter to execute them and where you get curly-brackets). Is there a way to do that with user-defined functions?
2. I am not new to programming, however I am completely new to VBA (I started literally today). Is there any obvious problem, weirdness with the above code?
3. Are there special characters, extremely long texts or particular input values that would cause the formula to fail?
4. Is there an easier way to achieve the same effect? Is the code slow?
5. when you start typing built-in formulas in excel you get suggestions and auto-completion. This doesn't seem to work with my formula, am I asking for too much or is it possible to achieve this?

I realize that this question contains several weakly related sub-questions, so I would be very happy also with sub-answers.

Answer 1

The following code will return a range of boolean values offset one column from the initial input range. Simply create a new tab in Excel and run testAllCharsValid and show the Immediate window in the IDE to see how it works.

Sub testAllCharsValid()

    Dim i As Integer
    Dim cll As Range, rng As Range
    Dim allowedChars As String

    ' insert test values in sheet: for testing purposes only
    With ActiveSheet ' change to Thisworkbook.Sheets("NameOfYourSheet")
        Set rng = .Range("A1:A10")
        For i = 1 To 10
            .Cells(i, 1) = Chr(i + 92)
        Next i
    End With

    ' fill allowedChars with letters a to z: for testing purposes only
    For i = 97 To 122
        allowedChars = allowedChars & Chr(i)
    Next i

    ' get boolean range
    Set rng = AllCharsValid(rng, allowedChars)

    ' check if the returned range contains the expected boolean values
    i = 0
    For Each cll In rng
        i = i + 1
        Debug.Print i & " boolean value: " & cll.Value
    Next cll

End Sub

' Check that all characters in InputCells are among
' the characters in AllowedChars
Public Function AllCharsValid(InputCells As Range, allowedChars As String) As Range

    Dim BoolTest As Boolean
    Dim Char As String
    Dim Index As Integer
    Dim RangeTestChars As Range, RangeBooleans As Range, RangeTemp As Range
    Dim TestChars As String
    For Each RangeTestChars In InputCells
        BoolTest = True
        TestChars = RangeTestChars.Value
        For Index = 1 To Len(TestChars)
            Char = Mid(TestChars, Index, 1)
            If InStr(allowedChars, Char) = 0 Then BoolTest = False
        Next Index

        Set RangeTemp = RangeTestChars.Offset(0, 1) ' change offset to what suits your purpose
        RangeTemp.Value = BoolTest

        If RangeBooleans Is Nothing Then
            Set RangeBooleans = RangeTestChars
        Else
            Set RangeBooleans = Union(RangeBooleans, RangeTemp)
        End If

    Next RangeTestChars

    Set AllCharsValid = RangeBooleans

End Function

cf 2) If the length of the test string is zero, the function will return True for the cell in question, which may not be desirable.

cf 3) There is a limit to how many characters an Excel cell can contain, read more here. I suppose, if you concatenated some very long strings and sent them to the function, you could reach the integer limit of +32767, which would cause a run-time error due to the integer Index variable. However, since the character limit of Excel cells is exactly +32767, the function should work as is without any problems.

cf 4) None that I know of.

cf 5) This is not the easiest thing to achieve, but there is help to be found here.