excelvba force array elements to alphanumeric code example

Example: excel vba force array elements to alphanumeric

'VBA function (extremely fast) to force an array column 
'to ONLY alpha-numeric characters (with spaces):

Function ForceArrayColumnAlphaNumeric(v, Optional col& = 1)
    Dim i&, j&, p&, max&, t&
    Dim b() As Byte, res() As Byte, Keep(0 To 255) As Boolean

    Const VALS$ = "0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
    
    For j = 1 To Len(VALS)
        Keep(Asc(Mid$(VALS, j, 1))) = 1
    Next
    
    For i = LBound(v) To UBound(v)
        p = 0
        max = Len(v(i, col))
        ReDim res(0 To max)
        b = StrConv(v(i, col), vbFromUnicode)
        For j = 0 To max - 1
            t = b(j)
            If Keep(t) Then
                res(p) = t
                p = p + 1
            End If
        Next
        v(i, col) = StrConv(res, vbUnicode)
    Next
End Function

'----------------------------------------------------------------------------------

array = [A1:Z999].Value2
ForceArrayColumnAlphaNumeric array, 3 '<--the 3rd column of array is now cleansed


'NB: Adjust the VALS$ constant to include only the characters you want to keep.
'NB: Array must be 2D. The column cleansed defaults to column 1.