VBA Function to Separate Arabic Sentences from Numbers

This document outlines a comprehensive VBA function that separates Arabic sentences from numeric values within a string. Each sentence is delimited by a slash (/).

Function Overview

Purpose

The purpose of this function is to parse a provided string, extracting Arabic sentences and numeric values into two separate collections.

Parameters

• inputString (String): The string containing Arabic sentences and numbers separated by slashes.

Return Type

• Returns a Collection with two items:
  • ArabicSentences (Collection): A collection of extracted Arabic sentences.
  • Numbers (Collection): A collection of extracted numbers.

Exceptions

• Raises an error if the input string is empty.

VBA Code

Option Explicit

Function SeparateArabicAndNumbers(inputString As String) As Collection
    ' This function separates Arabic sentences from numbers in a string
    ' Parameters:
    '   inputString: The string containing Arabic sentences and numbers divided by '/'
    ' Returns:
    '   A collection with two items: ArabicSentences and Numbers
    ' Raises:
    '   Error if inputString is empty

    Dim result As Collection
    Dim sentences As Variant
    Dim i As Long
    Dim arabicCollection As Collection
    Dim numbersCollection As Collection
    Dim currentItem As String
    
    ' Validate input
    If Len(Trim(inputString)) = 0 Then
        Err.Raise vbObjectError + 1, "SeparateArabicAndNumbers", "Input string cannot be empty."
    End If

    ' Initialize collections
    Set result = New Collection
    Set arabicCollection = New Collection
    Set numbersCollection = New Collection

    ' Split input string by '/'
    sentences = Split(inputString, "/")
    
    ' Loop through each item
    For i = LBound(sentences) To UBound(sentences)
        currentItem = Trim(sentences(i))
        ' Check if the item is numeric or Arabic
        If IsNumeric(currentItem) Then
            numbersCollection.Add currentItem
        ElseIf ContainsArabicCharacters(currentItem) Then
            arabicCollection.Add currentItem
        End If
    Next i

    ' Add collections to result
    result.Add arabicCollection, "ArabicSentences"
    result.Add numbersCollection, "Numbers"
    
    ' Return the result
    Set SeparateArabicAndNumbers = result
End Function

Private Function ContainsArabicCharacters(text As String) As Boolean
    ' Checks if the string contains Arabic characters
    Dim i As Long
    Dim charCode As Long
    
    For i = 1 To Len(text)
        charCode = AscW(Mid(text, i, 1))
        ' Arabic characters range: 1536-1791
        If charCode >= 1536 And charCode <= 1791 Then
            ContainsArabicCharacters = True
            Exit Function
        End If
    Next i
    ContainsArabicCharacters = False
End Function

Code Explanation

1. Input Validation: Checks if the input string is empty and raises an error if so.
2. Initialization: Creates collections for both Arabic sentences and numbers.
3. Splitting: The input string is split based on the slash (/) delimiter into an array.
4. Looping Through Sentences:
   • Each sentence is trimmed to remove excess spaces.
   • If the sentence is numeric, it’s added to the Numbers collection.
   • If it contains Arabic characters, it’s added to the ArabicSentences collection using a helper function.
5. Output: Returns a collection of Arabic sentences and numbers.

Usage Example

To utilize this function in your VBA project, you may call it as follows:

Sub TestSeparateArabicAndNumbers()
    Dim result As Collection
    Dim arabicSentences As Collection
    Dim numbers As Collection
    Dim inputString As String
    Dim arabicItem As Variant
    Dim numberItem As Variant

    ' Sample input string
    inputString = "مرحبا/1234/كيف حالك؟/5678/كتاب"

    ' Call the function
    Set result = SeparateArabicAndNumbers(inputString)
    
    ' Retrieve collections
    Set arabicSentences = result("ArabicSentences")
    Set numbers = result("Numbers")

    ' Output results to the Immediate Window
    Debug.Print "Arabic Sentences:"
    For Each arabicItem In arabicSentences
        Debug.Print arabicItem
    Next arabicItem

    Debug.Print "Numbers:"
    For Each numberItem In numbers
        Debug.Print numberItem
    Next numberItem
End Sub

Explanation of Example

• The TestSeparateArabicAndNumbers subroutine demonstrates how to call the main function and outputs the results to the Immediate Window in the VBA editor for verification.

Conclusion

This robust and structured function provides an efficient way to separate Arabic text from numbers, adhering to best practices in VBA coding. For more advanced analytics and insights within Excel, consider exploring further resources available on the Enterprise DNA Platform.