VBA Function to Separate Arabic Sentences from Numbers

Overview

This VBA function is designed to separate sentences written in Arabic from numbers within a given string. Arabic sentences are expected to be separated by the symbol /. The function will return an array of sentences, leaving behind any numbers.

Function Definition

Function SeparateArabicSentences(inputStr As String) As Variant
    ' This function separates Arabic sentences from numbers in a string.
    ' 
    ' Parameters:
    ' inputStr - A string containing Arabic sentences and numbers separated by "/"
    '
    ' Returns:
    ' An array containing only the Arabic sentences.
    '
    ' Raises:
    ' ErrInvalidString - If the input string is empty or invalid.
    
    Dim sentenceArray() As String
    Dim resultArray() As String
    Dim i As Long
    Dim count As Long

    ' Input validation: Check if the string is empty
    If Trim(inputStr) = "" Then
        Err.Raise vbObjectError + 1, , "Invalid input string. Input cannot be empty."
    End If

    ' Split the input string by the delimiter '/'
    sentenceArray = Split(inputStr, "/")
    count = 0

    ' Loop through each sentence
    For i = LBound(sentenceArray) To UBound(sentenceArray)
        ' Trim the sentence to avoid leading or trailing spaces
        Dim currentSentence As String
        currentSentence = Trim(sentenceArray(i))

        ' Check if the sentence contains any Arabic characters
        If ContainsArabic(currentSentence) Then
            count = count + 1
            
            ' Resize the result array to add the new sentence
            ReDim Preserve resultArray(0 To count - 1)
            resultArray(count - 1) = currentSentence
        End If
    Next i

    ' Return the result array containing only Arabic sentences
    If count = 0 Then
        SeparateArabicSentences = Array()  ' Return an empty array if no sentences found
    Else
        SeparateArabicSentences = resultArray
    End If
End Function

Private Function ContainsArabic(text As String) As Boolean
    ' Checks if the provided text contains Arabic characters.
    '
    ' Parameters:
    ' text - A string to check for Arabic characters.
    '
    ' Returns:
    ' True if the string contains Arabic characters; False otherwise.
    
    Dim i As Long
    For i = 1 To Len(text)
        ' Check if character falls within Arabic Unicode range
        If AscW(Mid(text, i, 1)) >= &H600 And AscW(Mid(text, i, 1)) <= &H6FF Then
            ContainsArabic = True
            Exit Function
        End If
    Next i
    ContainsArabic = False
End Function

Explanation of the Code

1. Function Header: The main function SeparateArabicSentences takes an input string and returns an array.
2. Input Validation: It checks if the input string is empty and raises an error if it is.
3. Splitting the String: The input string is split into an array using the / delimiter, which helps in isolating each segment.
4. Looping and Checking: For each segment, the code checks for the presence of Arabic characters using the helper function ContainsArabic.
5. Building the Result: If an Arabic sentence is found, it is stored in the result array.
6. Return value: The function returns the array of Arabic sentences or an empty array if none are found.

Usage Example

Here’s how to use the SeparateArabicSentences function within a VBA module:

Sub TestSeparateArabicSentences()
    Dim inputString As String
    Dim results As Variant
    Dim i As Long
    
    inputString = "هذا اختبار/123/هذا أيضاً جملة عربية/456"
    results = SeparateArabicSentences(inputString)

    ' Output the results
    For i = LBound(results) To UBound(results)
        Debug.Print results(i)
    Next i
End Sub

Explanation of the Example

• The TestSeparateArabicSentences subroutine tests the functionality of the SeparateArabicSentences function.
• It defines an input string that includes Arabic sentences and numbers.
• The results are printed to the immediate window (Debug.Print), allowing users to see the extracted Arabic sentences.

Conclusion

This VBA function efficiently separates Arabic sentences from numbers in a string, showcasing best practices in coding, including input validation and systematic development. For further exploration of data science and advanced coding techniques, consider enrolling in courses on the Enterprise DNA platform.