How to get Arabic month names using Excel VBA

Question

In Excel VBA, I try to put the "Arabic name of the month" in a variable, and I found a way. But my path requires a buffer cell to place the values, change the format of the cell, get the text value of the cell, and then put that value in a variable.

Here is my VBA code:

    Sub GetArabicName()
         Sheets("Sheet1").Cells(1, 1).Value = date() 
         Sheets("Sheet1").Cells(1, 1).NumberFormat = "[$-10A0000]mmmm;@" 
         ArabicMonth = Sheets("Sheet1").Cells(1, 1).Text
         MsgBox ArabicMonth & " The Arabic Name of the Month"
    End Sub

Is there an easier way to do this with VBA and without using a buffer cell? In addition, how can I make the MsgBoxdisplay of the Arabic meaning not "?????"

Thanks in advance.

Answer 1

, :

Public Declare Function MessageBoxU Lib "user32" Alias "MessageBoxW" _
                            (ByVal hwnd As Long, _
                             ByVal lpText As Long, _
                             ByVal lpCaption As Long, _
                             ByVal wType As Long) As Long

Sub GetArabicName()
    Dim ArabicMonth As String
    With Sheets("Sheet1").Cells(1, 1)
         .Value = Date
         .NumberFormat = "[$-10A0000]mmmm;@"
         .Font.Name = "Arial Unicode MS"
         ArabicMonth = .Text
    End With
    MessageBoxU 0, StrPtr(ArabicMonth), StrPtr("MsgBox Substitute"), 0
    MsgBox ArabicMonth & " The Arabic Name of the Month"
End Sub

:

Renaud Bompuis

EDIT # 1:

Axel Richter, :

Sub GetArabicNames_II()
    Dim ArabicMonth As String
    ArabicMonth = Application.WorksheetFunction.Text(Date, "[$-10A0000]mmmm;@")
    MessageBoxU 0, StrPtr(ArabicMonth), StrPtr("MsgBox Substitute"), 0
End Sub